Innodb slave Insert Performance

[Rajesh Kapur]

I have a master slave configuration on linux machines running MySQL 4.0.21.
Once every hour, a process deletes about 9000 rows and re-inserts fresh data
on the master. The master process completes processing under a minute. The
updates arrive on the slave within seconds. However, the slave takes about
15-20 minutes to post the same updates. Both master and slave have InnoDB
tables. The master and the slave are almost identical in capacity and MySQL
configuration. Can someone please suggest how I can improve insert
performance on the slave?

MY.CNF for the slave (slave has 2G main memory)

innodb_data_home_dir = /opt/mysql/var/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysql/var/
innodb_log_arch_dir = /opt/mysql/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
SHOW INNODB STATUS on the slave during slow inserts ...

=====================================
050629 11:03:47 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 10461, signal count 8415
Mutex spin waits 81152, rounds 764297, OS waits 4380
RW-shared spins 10540, OS waits 3747; RW-excl spins 5325, OS waits 2326
------------
TRANSACTIONS
------------
Trx id counter 0 559241043
Purge done for trx's n:o < 0 559240805 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 32132, OS thread id 2594987
MySQL thread id 218508, query id 3860732 dumas 10.2.5.95 root
show innodb status
---TRANSACTION 0 559236492, not started, process no 29474, OS thread id
2250956
MySQL thread id 194334, query id 3846090 foster 10.25.2.16 apmstations
---TRANSACTION 0 559241041, not started, process no 11717, OS thread id
10251 flushing log
mysql tables in use 1, locked 1
MySQL thread id 3, query id 3860730 update
INSERT INTO tmp_ftr_coll VALUES ('/features/2005/06/15_kerre_krauss',
'2005-06-15', 'Nicole Krauss writes &quot;The History of Love&quot; ',
'Nicole Krauss called her new novel &quot;The History of Love&quot;, but
it\'s much more than that. It\'s the story of a book that touches the lives
of people
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 1; buffer pool: 0
23402 OS file reads, 292534 OS file writes, 271730 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 20.99 writes/s, 20.99 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2,
295 inserts, 295 merged recs, 166 merges
Hash table size 1593833, used cells 342543, node heap has 379 buffer(s)
6070.64 hash searches/s, 1390.87 non-hash searches/s
---
LOG
---
Log sequence number 32 889573352
Log flushed up to 32 889573074
Last checkpoint at 32 887444884
1 pending log writes, 0 pending chkp writes
268194 log i/o's done, 20.99 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 462835244; in additional pool allocated 3130496
Buffer pool size 24576
Free buffers 2
Database pages 24195
Modified db pages 401
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 31551, created 101, written 62135
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 11714, id 7176, state: sleeping
Number of rows inserted 264002, updated 607, deleted 263264, read 336331358
20.66 inserts/s, 0.00 updates/s, 77.97 deletes/s, 7001.67 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================