One approach to fixing a broken MySQL replication setup…. using rsync.
WordPress seems to like hiding a load of ‘transient’ (cacheable) stuff in it’s wp_options table. Unfortunately for one site, it seems it didn’t bother to clean up the transient stuff, leaving behind about 750,000 records… which made a WP version upgrade painful, as MySQL locks the wp_option table which causes all other page loads to get stuck waiting (and the site to stop working).
Fix / hacky solution after the ‘more’…
I found MySQL was being annoying earlier and not ‘accepting’ my max_connections = 450 directive on a Debian Wheezy install, and being seemingly stuck on having 214 connections….
From using MySQL, I’ve used the ‘unbuffered queries‘ feature a number of times. It’s where you don’t fetch the entire resultset into memory at once – which is necessary if you’re retrieving more data than you have memory available. If’s often also generally gets results/data back to you sooner.
Continue reading “PostgreSQL unbuffered queries and PHP (cursors)”
Do you have a slow MySQL update/insert/delete query?
Obviously, for ‘SELECT’ queries you can prepend the query with “EXPLAIN ” – however that doesn’t work for the other query types (UPDATE/INSERT/DELETE).
So, one solution which may explain why the query is slow is to turn on MySQL’s profiling functionality, like in the following example :
Continue reading “MySQL update/write query analysis (query profiling)”
- If you’re using InnoDB, ensure innodb_buffer_pool_size is set to a decent value – I choose about 25% of physical memory… ideally this is larger than your dataset size, but obviously may not be posible, and the server may have to do other stuff….
- If you’re using InnoDB stop the O/S from also trying to cache ‘stuff’ in the buffer cache – using innodb_flush_method=O_DIRECT
- Download the MySQL Tuner perl script (wget -O http://mysqltuner.pl > mysqltuner.pl) and run (e.g. perl mysqltuner.pl –user root –pass blahblah; it might point out a few variables to change; node the ‘maximum possible memory usage – you don’t want this to exceed 50% for a normal LAMP server).
- I use something like the below to optimize all tables, beware this will cause MyISAM tables to lock up … so you really need to run it in a quiet period.
SQL="select concat(TABLE_SCHEMA, '.', TABLE_NAME) from information_schema.TABLES where TABLE_SCHEMA IN ('database1','database2', 'databaseN') and Data_free > 500 AND Engine = 'MyISAM' " for table in $(mysql --skip-column-names --batch -u root -prahrah -e "$SQL") do mysql --batch -u root -pxxxxx -e "optimize table $table" sleep 10 done