SMF Support > Server Performance and Configuration

SSD vs RAM for MySQL

(1/4) > >>

Forum Labs:
I am planning to upgrade my current server by adding SSD for MySQL and more RAM for innodb caching. My database is already at 7GB+ InnoDB.  If I do that do you think the SSD would be useless since the DB is already cached in RAM?

Mark Rose:
SSD will give you much faster updates and inserts.

On my box, MySQL is currently using 9.2 GiB, most of which is the InnoDB buffer pool, but the hot data/indexes are really around 1 GiB at most.

Unless you're seeing heavy reads (cache too small), go with the SSD first.

Forum Labs:

--- Quote from: Mark Rose on October 04, 2011, 04:40:22 PM ---SSD will give you much faster updates and inserts.

On my box, MySQL is currently using 9.2 GiB, most of which is the InnoDB buffer pool, but the hot data/indexes are really around 1 GiB at most.

Unless you're seeing heavy reads (cache too small), go with the SSD first.

--- End quote ---

How can I check if I am having heavy reads?

How much RAM do you have installed in your server right now? Mine is just 4GB and I alloted 2GB for innodb.


Mark Rose:

--- Quote from: Forum Labs on October 06, 2011, 07:17:49 PM ---How can I check if I am having heavy reads?

--- End quote ---

Run show status like 'innodb_buffer_pool_read%';

--- Code: ---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)

--- End code ---

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.


--- Code: ---mysql> show status like 'innodb_buffer_pool_write%';
+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Innodb_buffer_pool_write_requests | 2892820909 |
+-----------------------------------+------------+
1 row in set (0.00 sec)

--- End code ---

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.


--- Quote ---How much RAM do you have installed in your server right now? Mine is just 4GB and I alloted 2GB for innodb.

--- End quote ---

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.

Forum Labs:
Heres my output

--- Code: ---mysql> show status like 'innodb_buffer_pool_read%';
+-----------------------------------+--------------+
| Variable_name                     | Value        |
+-----------------------------------+--------------+
| Innodb_buffer_pool_read_ahead_rnd | 171605       |
| Innodb_buffer_pool_read_ahead_seq | 25087        |
| Innodb_buffer_pool_read_requests  | 516444324392 |
| Innodb_buffer_pool_reads          | 7589029      |
+-----------------------------------+--------------+
4 rows in set (0.00 sec)

--- End code ---



--- Code: ---mysql>  show status like 'innodb_buffer_pool_write%';
+-----------------------------------+-----------+
| Variable_name                     | Value     |
+-----------------------------------+-----------+
| Innodb_buffer_pool_write_requests | 544142056 |
+-----------------------------------+-----------+
1 row in set (0.00 sec)

--- End code ---

I still do not know how you get your read request ratio from the values above. Can you please decode this for me ;)

I am only getting 60,000 page views a day.  The forum has 3,205,782 total posts.

Your forum looks more active have you considered using SSD? Or the performance of your current setup is enough?  If your current setup can handle your forum without SSD maybe my server can do it as well.

Here's my server load right now

--- Code: ---Operating System: CentOS release 5.6 (Final)
Processor:          IntelĀ® Core(TM)2 Quad CPU @ 2.40GHz (1600.000MHz)
Load averages: 2.12, 1.77, 1.68
Memory usage: 99.15% (3873496k / 3906684k)
Swap:                       2.055% (172360k / 8385912k)

--- End code ---

I do not know if I actually need to upgrade my server or I just need to optimize mysql. I am already using nginx + PHP-FPM + APC

I would be getting this server if I need to upgrade :)

3.2GHz quad core Xeon E3-1230 CPU
16GB ram
2x 500GB 7200rpm Hard Drive in RAID1
Price: $150 monthly

Navigation

[0] Message Index

[#] Next page

Go to full version