How can I check if I am having heavy reads?
Run
show status like 'innodb_buffer_pool_read%';mysql> show status like 'innodb_buffer_pool_read%';
+---------------------------------------+--------------+
| Variable_name | Value |
+---------------------------------------+--------------+
| Innodb_buffer_pool_read_ahead | 416 |
| Innodb_buffer_pool_read_ahead_evicted | 72 |
| Innodb_buffer_pool_read_requests | 422216183812 |
| Innodb_buffer_pool_reads | 87516 |
+---------------------------------------+--------------+
4 rows in set (0.00 sec)
Read requests is the number of times a page was requested from the buffer pool.
reads is the number of pages InnoDB needed to pull from the disk since they weren't already in the buffer pool. Both are since MySQL startup. Keep in mind when InnoDB does a write, it modifies the page in memory so it doesn't need to be read from the disk again.
The number (87516/422216183812) show that 99.9999793% of my reads were cached. Clearly increasing the buffer pool would serve almost no benefit. If you're in the 80%'s, that's great. Above 95% the returns are very diminishing with SMF. Writes dominate once you have a big enough pool.
mysql> show status like 'innodb_buffer_pool_write%';
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Innodb_buffer_pool_write_requests | 2892820909 |
+-----------------------------------+------------+
1 row in set (0.00 sec)
Over the last 146 days, my write to read request ratio is roughly 1:146, but my write to actual disk read ratio is roughly 33055:1. Clearly the disk does almost entirely writes
The forum does around 150,000 pageviews per day, roughly 1,300,000 PHP scripts per day (lots of attachment thumbs), has about 50k members and 600k posts.
How much RAM do you have installed in your server right now? Mine is just 4GB and I alloted 2GB for innodb.
16 GB. My box is complete overkill. I used to run the same traffic in 1 GB (though I had to tune things tightly to run well). A 2 GB machine would be more than enough for great performance (if not using Apache). 2GB for buffer pool is quite reasonable on a 4 GB machine. My buffer pool is big only because I have absolutely nothing else to use the RAM for.
TL;DR: If 80% or more of your reads can be satisfied without going to disk, go with SSD.
