MySQL/Optimization

Caching

One thing you should look at is the query cache. By default, the query cache is turned off. You should turn it on, and set it in the tens of megabytes to see how it affects performance (like 80MB for starters). A huge cache will hurt performance (don't give it 4GB just because you have 32GB of RAM). But some amount of cache will definitely improve performance. So you just have to find the sweetspot for your hardware and application. That being said, if your tables are exclusively InnoDB tables, then you might be able to turn query cache off.

The cache is for SELECT queries obviously. Any insert or update invalidates the cache so you can't get stale data from cache. Low memory prunes indicates how many queries are being removed to make room for new queries that are being cached and the cache prune strategy is LRU (least recently used) so that queries that are seen the most frequently are the ones that get priority for caching. It wouldn't make much sense to cache a query that is rare v. a query that is used on every page load.

-- check the settings
SHOW VARIABLES LIKE '%cache%';
-- check the status
SHOW STATUS LIKE 'Qcache%';
-- turn it on 
SET GLOBAL query_cache_size = 80000000;
-- Measure performance
-- Uncomment line 2 to check the math
SELECT FORMAT(((QCACHE_HITS / (Qcache_hits + Qcache_inserts + Qcache_not_cached))*100),2) AS query_cache_hit_rate_percentage
--, QCACHE_FREE_BLOCKS, QCACHE_FREE_MEMORY, QCACHE_HITS, QCACHE_INSERTS, QCACHE_LOWMEM_PRUNES, QCACHE_NOT_CACHED, QCACHE_QUERIES_IN_CACHE, QCACHE_TOTAL_BLOCKS
FROM (
SELECT
MAX(IF(VARIABLE_NAME = 'QCACHE_FREE_BLOCKS', VARIABLE_VALUE, NULL)) QCACHE_FREE_BLOCKS,
MAX(IF(VARIABLE_NAME = 'QCACHE_FREE_MEMORY', VARIABLE_VALUE, NULL)) QCACHE_FREE_MEMORY,
MAX(IF(VARIABLE_NAME = 'QCACHE_HITS', VARIABLE_VALUE, NULL)) QCACHE_HITS,
MAX(IF(VARIABLE_NAME = 'QCACHE_INSERTS', VARIABLE_VALUE, NULL)) QCACHE_INSERTS,
MAX(IF(VARIABLE_NAME = 'QCACHE_LOWMEM_PRUNES', VARIABLE_VALUE, NULL)) QCACHE_LOWMEM_PRUNES,
MAX(IF(VARIABLE_NAME = 'QCACHE_NOT_CACHED', VARIABLE_VALUE, NULL)) QCACHE_NOT_CACHED,
MAX(IF(VARIABLE_NAME = 'QCACHE_QUERIES_IN_CACHE', VARIABLE_VALUE, NULL)) QCACHE_QUERIES_IN_CACHE,
MAX(IF(VARIABLE_NAME = 'QCACHE_TOTAL_BLOCKS', VARIABLE_VALUE, NULL)) QCACHE_TOTAL_BLOCKS
FROM information_schema.global_status
WHERE VARIABLE_NAME LIKE 'QCACHE%'
) AS stats;

To make your changes permanent, set it in your global option configuration file (/etc/mysql/my.cnf)

query_cache_size = 80M

InnoDB status

Look for long-running transactions; and deadlocks

The best tuning parameters are

1. innodb_buffer_pool_size as much as you can spare after OS and MySQL A rule of thumb would be to make it 10% larger than your database size. If it's 10% smaller than your db, you probably won't notice a problem. This is one reason to run your DB on a dedicated server: so that you can allocate as much memory as possible to the database without worrying about other OS and application memory requirements. See the manual for resizing the buffer.
2. innodb_log_file_size at least enough for 60 minutes of log writes (because sequential writes to a log file are faster than disk) Do this at peak usage, or use a bigger time window for sampling.

   -- turn on the pager; setting it to 'grep sequence'
   pager grep sequence
   -- run our status report; wait 60 seconds and run it again
   show engine innodb status\G select sleep(60); show engine innodb status\G
   -- turn the pager off
   nopager

   Now you take the last number minus the first number, and convert to MB

   select (43599802491 - 43599758916) / 1024 / 1024 as MB_per_minute;

   Multiply that number by 60 to get an hours worth of log (and divide by 2 because there are two log files) Compare that to the current configuration

   SHOW variables like 'innodb_log_file_size';
   -- can be written as a SELECT statement using shorthand like this, and the value display is not limited
   SELECT @@innodb_log_file_size;

   The default is 5242880 which is 5MB

3. innodb_io_capacity based on your disk IOPS