SSD vs RAM for MySQL

Started by Forum Labs, October 02, 2011, 01:26:07 PM

Previous topic - Next topic

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?
Premium Support & Services for SMF

Something like that

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.

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.


Premium Support & Services for SMF

Something like that

Quote from: Forum Labs on October 06, 2011, 07:17:49 PM
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.

Quote
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.

Forum Labs

#4
Heres my output

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)




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


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

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)


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

Premium Support & Services for SMF

Something like that

Quote from: Forum Labs on October 06, 2011, 10:24:52 PM
I still do not know how you get your read request ratio from the values above. Can you please decode this for me ;)

read request ratio: (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100 = cached percentage

Your cached percentage is 99.999%, so you don't have a problem there. You could decrease your buffer pool size if you needed more RAM elsewhere.

Quote
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.

My server is not far from the point where a standard hard drive's write latency is an issue (very small, occasional delays). When I upgraded the machine, I got a battery-backed RAID 5 setup and the cache handles the write bursts well.

Quote
Here's my server load right now

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)


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

Your load average is great. Turn on hyperthreading if you haven't (it's good with PHP). Your current machine is doing very well. You don't need more memory, and SSD probably wouldn't do much with your traffic levels. I'd recommend SSD if you were doing 250,000 pageviews a day or more.

Forum Labs

#6
Quote from: Mark Rose on October 07, 2011, 12:18:16 AM
Your load average is great. Turn on hyperthreading if you haven't (it's good with PHP). Your current machine is doing very well. You don't need more memory, and SSD probably wouldn't do much with your traffic levels. I'd recommend SSD if you were doing 250,000 pageviews a day or more.

Thank you for all your advice :) I really appreciate it. I am always reading your posts as I am learning a lot from it.

BTW how do you monitor your pageviews? do you use google analytics? My 60,000 pageviews per day is from google analytics.

I visited my stats page today and check my older pageviews collected by SMF statistics and here it is

2010-07-03 143,257
2010-07-04 149,114
2010-07-05 157,800
2010-07-06 156,194
2010-07-07 155,430


That was last year and since then I turned off the counting of pageviews. It should have probably doubled by now if that stats is correct. I just turned on again the tracking of daily page views so I can check how many page views I am getting :)
Premium Support & Services for SMF

Something like that

Use the SMF stats. In that case, I'd certainly consider getting SSD. You don't need more RAM though. See if your host can slap in an extra drive.

Joshua Dickerson

RAM and a couple more disk drives are a hell of a lot cheaper than a decent SSD. You're not going to see the results that you see in benchmarks with any old SSD. There are high-end, $15,000 drives, and low-end $300 drives. The difference is that the low-end is worse than a 10K SAS drive. Look at the numbers on them.

Another 2 drives and more RAM is a better option. You probably have a bunch of tweaking to do though. One of my favorite slide shows which I quote regularly - http://www.slideshare.net/matsunobu/linux-and-hw-optimizations-for-mysql-7614520

I regularly watch iostat and htop. Check your mount options if you think you're having drive issues - might be something there. Chances are, there are other problems. Check your slow query and fpm logs. Watch how much time each request takes. There's a lot of stuff to do before spending more money.

Although, $150 for that server is pretty good.
Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

Something like that

One other thing: I strong recommend running a recent 5.5 release from MySQL or Percona. The 5.5 series has a ton of performance improvements with regards to concurrent queries.

Joshua Dickerson

Forum Labs, what are you spending for your server now?

If you're using 4 drives (or two pairs), you'd want to give the data directory its own drive or at least move it to a drive that you'll use less often.

A couple of changes to the filesystem:

•   ext4 instead of ext3
•   Noatime
•   Barrier=0: http://www.slideshare.net/matsunobu/linux-and-hw-optimizations-for-mysql-7614520 / http://www.slideshare.net/matsunobu/linux-performance-tuning-stabilization-tips-mysqlconf2010  (page 17) (Yoshinori Matsunobu is one of the leading experts in MySQL optimizations) http://kerneltrap.org/mailarchive/linux-ext4/2009/9/11/6399013
•   Dir_index
•   Vm.swapiness=1 (not 0 because we aren't completely in RAM)
•   Huge Page Support: http://vekseid.com/blogs/vekseid/optimizing_a_server_for_mysql

All of those will help you out a lot. Of course, if you don't have a BBWC, I wouldn't go with the barrier=0 option.
Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

Joshua Dickerson

I forgot to mention, might want to use Barracuda engine on Percona and compress your tables. That will help you get more CPU usage and less memory/disk usage.
Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

Forum Labs

My current server only costs me $80 monthly

4GB RAM
C2Q Q6600
2x250GB

Thanks for sharing those links :)

Is percona a replacement to mysql? I might just upgrade to mysql 5.5 since I am too busy right now.

Thanks everyone
Premium Support & Services for SMF

Joshua Dickerson

Yes, Percona is a drop-in replacement to MySQL. Meaning, you don't need to make any changes to your application to make it work. It works well with SMF. Are those drives in a RAID config?
Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

vegita

SSD is very fast and reliable, my db is about 20 Gigs, and RAM is 32 GB, But i see lot of performance is with SSD hardisk itself, go with it .

ashish101

Quote from: Forum Labs on October 08, 2011, 10:06:50 PM
My current server only costs me $80 monthly

4GB RAM
C2Q Q6600
2x250GB
Which hosting provider? Managed or Unmanaged?

I am on a similar config Q9400, which I believe is a bit better than Q6600. I am paying 50% more for unmanaged but no complaints and no plans to move but just wondering. It's still decent for good servers and service.

I have optimized my site alot for performance and it's quite fast, especially in comparison what it used to be, but I still think there's some room for improvement. Just last week upgraded to MySQL 5.5, looking into Percona as an option, upgrading to better hardware is an option too. Reaching 120k-150k pageviews per day as per Google analytics with around 800GB-1TB bandwidth per month.

Advertisement: