Many thanks for your reply!
The status of the slave thread is 'Locked' for quite some time. Just nowI am currently syncronizing two MySQL servers (version 3.23.49) on a very high traffic website.
There are, at peak times upwards of 600 updates a second (and many many more selects)
During these times the slave database will fall out of sync, sometimes by several thousand seconds (im aware that this calculation is the 'time now - timestamp of last update from the master)
What is the status of the slave thread in show processlist?
If it is 'Locked' then the slave thread may be starving because of too many (long) selects on the slave.
I have checked and a single query (normally very quick) was in Locked status for 11 seconds.
Hmm, ok. I dont have low-priority-updates in the config file (i did see that in the manual)If your my.cnf contains low-priority-updates and your slaves are serving selects continuously, then the updates won't come through. This setting is mentioned in the manual.
If you remove low-priority-updates, the updates will be processed sooner but you'll get in trouble if you have long running selects. These will delay all following selects on the slave when an update is waiting for the long query to end.
Unfortunately I am in a position where we can not have lag on the databases (people pay for live data, if their select gets run on the slave and the results are delayed due to slave lag, they tend to be pissed off) so is there anything I else I can do to reduce this while still keeping the redundnacy of master/slave? (we cant run the website just on one DB, it falls over with 'can not create thread errors' when we run out of memory :()
I am splitting up the database at the moment, and moving some of the biggest tables on to a new server, and this week I will be upgrading both these servers to mysql 4.0.16 which i understand will potentially give us quite a performance increase.
Do you have any other suggestions that you may be able to offer?
Ive copied the my.cnf files from the master and the slave at the bottom of this email if that may offer any clues to what else we can do.
many thanks in advance for any assistance you can give. -- Jonathan Tullett
my.cnf on the MASTER:
# Here follows entries for some specific programs [safe_mysqld] err-log = /var/log/mysql/mysql.err
# The MySQL server [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 skip-locking basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english set-variable = key_buffer=384M set-variable = max_allowed_packet=1M set-variable = table_cache=512 set-variable = sort_buffer=2M set-variable = record_buffer=2M set-variable = thread_cache=8 set-variable = max_connections=400 set-variable = max_user_connections=360 # Try number of CPU's*2 for thread_concurrency set-variable = thread_concurrency=8 set-variable = myisam_sort_buffer_size=64M #log = /var/log/mysql/mysql.log server-id = 1 master-host = 10.0.2.1 master-user = replicate master-password = replicate master-connect-retry = 60 replicate-ignore-db = mysql skip-slave-start
log-bin = /mysql-log/db1-new-bin log-slow = /mysql-log/db1-new-slow.log
my.cnf on the SLAVE:
# Here follows entries for some specific programs [safe_mysqld] err-log = /var/log/mysql/mysql.err
# The MySQL server [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 skip-locking basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english set-variable = key_buffer=384M set-variable = max_allowed_packet=1M set-variable = table_cache=512 set-variable = sort_buffer=2M set-variable = record_buffer=2M set-variable = thread_cache=8 set-variable = max_connections=400 set-variable = max_user_connections=360 # Try number of CPU's*2 for thread_concurrency set-variable = thread_concurrency=8 set-variable = myisam_sort_buffer_size=64M #log = /var/log/mysql/mysql.log server-id = 2 master-host = 10.0.2.2 master-user = replicate master-password = replicate master-connect-retry = 60 replicate-ignore-db = mysql skip-slave-start
log-bin = /mysql-log/db2-bin log-slow = /mysql-log/db2-slow.log
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
