----------------
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

----------------