Replication + InnoDB = badness
We were having terrible problems with a master/slave setup. The master does a huge amount of writes, and the slave simply started lagging behind, despite both machines being identical hardware-wise. This made the application basically unusable because eventually the slave was hours behind the master, and had no chance of ever catching up. I disabled InnoDB on the slave (skip-innodb in the my.cnf file) and now it has caught up and is keeping up fine. The weird thing is it worked fine with InnoDB enabled for many weeks. Also even after we re-converted all the slave's Inno tables back to MyISAM it *still* lagged out. Only after I disabled the Inno engine entirely did the problem abate. Any ideas why? Does InnoDB use resources even if there are no active tables using the engine? -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication + InnoDB = badness
On Tue, Aug 03, 2004 at 11:11:52AM -0700, Jon Drukman wrote: We were having terrible problems with a master/slave setup. The master does a huge amount of writes, and the slave simply started lagging behind, despite both machines being identical hardware-wise. This made the application basically unusable because eventually the slave was hours behind the master, and had no chance of ever catching up. I disabled InnoDB on the slave (skip-innodb in the my.cnf file) and now it has caught up and is keeping up fine. The weird thing is it worked fine with InnoDB enabled for many weeks. Also even after we re-converted all the slave's Inno tables back to MyISAM it *still* lagged out. Only after I disabled the Inno engine entirely did the problem abate. Any ideas why? Does InnoDB use resources even if there are no active tables using the engine? This is most confusing. You're not using InnoDB *at all* and it was slowing down the slave? What InnoDB options had you set in my.cnf anyway? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication + InnoDB = badness
Also even after we re-converted all the slave's Inno tables back to MyISAM it *still* lagged out. Only after I disabled the Inno engine entirely did the problem abate. Any ideas why? Does InnoDB use resources even if there are no active tables using the engine? This is most confusing. You're not using InnoDB *at all* and it was slowing down the slave? What InnoDB options had you set in my.cnf anyway? [mysqld] (replication commands omitted) set-variable= query_cache_size=512M set-variable= key_buffer=512M set-variable= max_allowed_packet=4M set-variable= table_cache=64 set-variable= sort_buffer=4M set-variable= record_buffer=4M set-variable= thread_cache=8 set-variable= tmp_table_size=128M set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=128M set-variable= max_connections=1800 set-variable= max_connect_errors=10 set-variable= wait_timeout=30 set-variable= max_binlog_size=5 set-variable= long_query_time=1 #innodb_data_home_dir = /var/opt/mysql/innodb #innodb_log_group_home_dir = /var/opt/mysql/innodb log-error=db3-log #log-slow-queries skip-innodb i spoke too soon - the slave still lags behind the master but the problem is not nearly as bad as it was with InnoDB enabled. it seems like the combined weight of replicating and serving tons of selects causes it to fall behind. if we disable selects for a few seconds, it catches up again. we need to add a second slave. (i've ordered two more just to be safe.) mysql needs synchronous replication. we're going to eval the EMIC clustering product in the next few weeks. i hope it works. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication + InnoDB = badness
Could it be a network bandwidth issue? Remember, all that data needs to be transmitted across to the slave. If you are on a 10-megabit network, it could be the cause. Remember, Ethernet is not exactly an efficient protocol, and efficiency drops as network traffic increases. A second machine might make it worse, not better. David Jon Drukman wrote: Also even after we re-converted all the slave's Inno tables back to MyISAM it *still* lagged out. Only after I disabled the Inno engine entirely did the problem abate. Any ideas why? Does InnoDB use resources even if there are no active tables using the engine? This is most confusing. You're not using InnoDB *at all* and it was slowing down the slave? What InnoDB options had you set in my.cnf anyway? [mysqld] (replication commands omitted) set-variable= query_cache_size=512M set-variable= key_buffer=512M set-variable= max_allowed_packet=4M set-variable= table_cache=64 set-variable= sort_buffer=4M set-variable= record_buffer=4M set-variable= thread_cache=8 set-variable= tmp_table_size=128M set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=128M set-variable= max_connections=1800 set-variable= max_connect_errors=10 set-variable= wait_timeout=30 set-variable= max_binlog_size=5 set-variable= long_query_time=1 #innodb_data_home_dir = /var/opt/mysql/innodb #innodb_log_group_home_dir = /var/opt/mysql/innodb log-error=db3-log #log-slow-queries skip-innodb i spoke too soon - the slave still lags behind the master but the problem is not nearly as bad as it was with InnoDB enabled. it seems like the combined weight of replicating and serving tons of selects causes it to fall behind. if we disable selects for a few seconds, it catches up again. we need to add a second slave. (i've ordered two more just to be safe.) mysql needs synchronous replication. we're going to eval the EMIC clustering product in the next few weeks. i hope it works. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication + InnoDB = badness
From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 03, 2004 1:04 PM Subject: Re: Replication + InnoDB = badness Could it be a network bandwidth issue? Remember, all that data needs to be transmitted across to the slave. If you are on a 10-megabit network, it could be the cause. Remember, Ethernet is not exactly an efficient protocol, and efficiency drops as network traffic increases. A second machine might make it worse, not better. no, we are nowhere near hitting even 10Mbit. the machines in question are all 100Mbit, anyway. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication + InnoDB = badness
On Tuesday 03 August 2004 01:11 pm, Jon Drukman wrote: We were having terrible problems with a master/slave setup. The master does a huge amount of writes, and the slave simply started lagging behind, despite both machines being identical hardware-wise. This made the application basically unusable because eventually the slave was hours behind the master, and had no chance of ever catching up. I disabled InnoDB on the slave (skip-innodb in the my.cnf file) and now it has caught up and is keeping up fine. This is a horrible solution.. It clearly states in the docs that a heavily write screnerio is bad news for replication.. You have to realize the slave has to process everything the master does.. Replication is good for more of more read/ some write scenerio. You need a cluster.. Jeff -- === Jabber: tradergt@(smelser.org|jabber.org) Quote:You can lead a user to the manual, but you can't make him read. === pgpgW7SSzKws8.pgp Description: PGP signature
