----------------
ISSUE
----------------
Running 2 databases which I will call livedb and testdb.
Both databases are on the same server, which is the same server as apache, mail, etc.
The livedb is performing a lot of reads, and it seems efficient at this.
We implemented a system that needs to perform a lot of inserts and updates such as:
UPDATE `table` SET `field`=6108, `modified`='2008-03-31 11:17:49' WHERE id IN (9218)
The query runs fine on testdb but very slow on livedb.
----------------
----------------
CURRENT HARDWARE
----------------
OS: Centos 4.6
CPU: quadcore xeon 5405
RAM: 1gb DDR2 667
DISK: 160gbx2 SATA RAID1
----------------
----------------
mysql> use testdb;
----------------
ran this SQL 5 times:
mysql> UPDATE `table` SET `field`=6108, `modified`='2008-03-31 11:17:49' WHERE id IN (9218);
----------------
Query OK (0.00 sec)
Query OK (0.00 sec)
Query OK (0.02 sec)
Query OK (0.00 sec)
Query OK (0.00 sec)
----------------
----------------
mysql> use livedb;
----------------
ran this SQL 5 times:
mysql> UPDATE `table` SET `field`=6108, `modified`='2008-03-31 11:17:49' WHERE id IN (9218);
----------------
Query OK (2.51 sec)
Query OK (0.89 sec)
Query OK (0.28 sec)
Query OK (0.84 sec)
Query OK (3.17 sec)
----------------
----------------
top (at normal usage)
----------------
top - 09:55:46 up 65 days, 19:59, 1 user, load average: 4.21, 5.04, 4.36
Tasks: 112 total, 1 running, 111 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0% us, 34.8% sy, 55.1% ni, 10.1% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 2067820k total, 1997032k used, 70788k free, 65984k buffers
Swap: 2096472k total, 57688k used, 2038784k free, 950588k cached
PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
1974 mysql 22 6 302 10124:53 14.3 456m 289m 4224 S mysqld
----------------
----------------
mytop (at normal usage)
----------------
MySQL on localhost (5.0.45-community-log) up 12+10:07:14 [09:56:00]
Queries: 222.8M qps: 218 Slow: 0.0 Se/In/Up/De(%): 75/00/00/00
qps now: 259 Slow qps: 0.0 Threads: 22 ( 22/ 106) 79/00/00/00
Key Efficiency: 100.0% Bps in/out: 0.0/ 0.0 Now in/out: 13.9/ 5.5k
----------------
----------------
mysql version()
----------------
5.0.45-community-log
----------------
----------------
phpinfo()
----------------
mysql
Client API version 5.0.45
----------------
----------------
my.cnf
----------------
[mysqld]
datadir=/var/lib/mysql
skip-locking
skip-innodb
skip-networking
safe-show-database
query_cache_limit=1M
query_cache_size=64M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=100
max_connections=500
interactive_timeout=10
wait_timeout=20
connect_timeout=20
thread_cache_size=128
key_buffer=256M ## 128MB for every 1GB of RAM
join_buffer=1M
max_connect_errors=20
max_allowed_packet=16M
table_cache=1024
tmp_table_size=64M
max_heap_table_size=64M
record_buffer=1M
sort_buffer_size=2M ## 1MB for every 1GB of RAM
read_buffer_size=2M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=2M ## 1MB for every 1GB of RAM
thread_concurrency=8 ## Number of CPUs x 2
myisam_sort_buffer_size=64M
server-id=1
log_slow_queries=/var/log/mysql-slow-queries.log
# Don't Forget To Do
# touch /var/log/mysql-slow-queries.log
# chown mysql.mysql /var/log/mysql-slow-queries.log
long_query_time=2
collation-server=latin1_general_ci
ft_min_word_len = 2
#[mysql.server]
#user=mysql
#basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
#safe-updates
[isamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=32M
write_buffer=32M
[myisamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=32M
write_buffer=32M
[mysqlhotcopy]
interactive-timeout
----------------