473,379 Members | 1,252 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,379 software developers and data experts.

Innodb slave Insert Performance

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
============================
Jul 23 '05 #1
0 2462

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Vanchau Nguyen | last post by:
Hi all, Hoping someone can help us with this strange Innodb problem. At about 5:15 AM 2/2/04 (this morning), our slave server stopped accepting connections. The machine was pingable, but you...
3
by: Andy Tran | last post by:
I built a system using mysql innodb to archive SMS messages but the innodb databases are not keeping up with the number of SMS messages coming in. I'm looking for performance of 200 msgs/sec where...
0
by: Kurt Tragant | last post by:
Hi List, I'd like to setup the following: Server A <-- Server B <-- Server C Data A some of Data A some of Data A Data B Data B Server A should be the...
0
by: Mike Chirico | last post by:
Hopefully the following will be useful or interesting: TIP 29: An example of using referential integrity with InnoDB tables. Referential Integrity with InnoDB tables. STEP 1 (First create...
5
by: steve | last post by:
Hi, Besides transaction capability, is there anything else about innodb tables that makes it superior to myISAM? I really donít need transactions, but for example, I need superior reliability...
4
by: Good Man | last post by:
Hi there I have a database with about 20 or so tables, maybe a few thousand rows in each. I am starting to do more complex things with my insertions etc, and I want to start to use...
0
by: shakahshakah | last post by:
Just started investigating InnoDB after having used MyISAM tables in the 4.0.12 version, almost immediately ran into a locking issue with INSERTs, DELETEs, and transactions. Given the following...
2
by: daniel | last post by:
I have the following scenario. A mysql database running 3 databases. It is version 5.0.27 on Windows XP Prof.. All innodb databases. The one database is particularly large (7.8GB of...
2
by: MysqlBeginner | last post by:
Is this possible to have a MyISAM table on master server and change its storage engine (to InnoDB) on slave server in a replication environment? If this is possible, what should I consider before...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.