2

I am working on the design of a Wordpress blog which when finished will almost-always serve pages from disk cache and this is because all posts will be published in batch and then cached, with at least 5 hours between batches.

Database size is only 30MB at the moment as this is a new blog.

My server options are two:
a. Either a 4GB RAM server with an SSD or
b. An 8GB RAM server with a normal HDD.

Am I right in assuming that since pages will almost-always be served from disk cache, SSD is a better option?

Or maybe I should still go for more RAM?

I ask because years ago I worked on a blog generating almost 2m pageviews per month and when it was crashing we identified the heavy HDD usage as the problem. In fact after tweaking innodb_buffer_pool_size so as to use more RAM and less disk, we never had any more issues.

Thank you.

Edit By RolandoMySQLDBA

Please run this query

select var,concat(numunit,' ',unit) size from
(
    select var,format(num/power(1024,ex),2) numunit,SUBSTR(units,ex*2+1,2) unit
    from
    (
        select var,num,FLOOR(LOG(IF(num=0,1,num))/LOG(1024)) ex
        from
        (
            select variable_name var,variable_value*pagesize num
            from information_schema.global_status AAA,
            (
                select variable_value pagesize
                from information_schema.global_status
                where variable_name='innodb_page_size'
            ) BBB
            where AAA.variable_name like 'innodb_buffer_pool_pages%'
        ) AA
    ) A,(select 'B KBMBGBTB' units) B
) M;

and post its output so was can see the Buffer Pool's current usage.

Also, please run SELECT VERSION();

Edit

The blog is still on the development machine so I believe the first query is of no use. I can provide the result of the second though:

+------------+
| VERSION()  |
+------------+
| 5.6.16-log |
+------------+
1 row in set (0.03 sec)

It will be the very latest version when we go in Production.

Community
  • 1
WPRookie82
  • 121
  • 2

1 Answers1

1

If you are going to put MySQL in a wimpy environment (WIMP = Windows, IIS, MySQL, PHP/Perl/Python), your first priority needs to be RAM. Please get more RAM on the box.I suggest 32GB.

As for MySQL, you need to tune InnoDB as much as possible. Here is the InnoDB Architecture

InnoDB Architecture

InnoDB Architecture

Recommendation #1

By default, 25% of the Buffer Pool is used to process changes to secondary indexes. Thus, I suggest double the amount of data (you said 3-4 GB) as the Buffer Pool Size

innodb_buffer_pool_size = 8G

Recommendation #2

Since you have a dual core machine, split the Buffer Pool to prevent swapping

innodb_buffer_pool_instances = 2

Recommendation #3

You will need good read and write I/O threading

innodb_read_io_threads = 16
innodb_write_io_threads = 16

Recommendation #4

You need to the Log Buffer to be larger for increased write performance

innodb_log_buffer_size = 64M

Recommendation #5

You need the Log Buffer flushed often since you are in Windows

innodb_flush_log_at_trx_commit = 1

CAVEAT #1: The default for innodb_flush_log_at_trx_commit is 1 to maintain ACID compliance. Do not change this in Windows since Windows has the final say on disk caching.

CAVEAT #2: There is an option for InnoDB to cache data instead of letting the OS do it called innodb_flush_method. Unfortunately, the setting O_DIRECT is not available for Windows. If you switch to Linux, setting this option takes disk caching by the OS out of the equation.

Recommendation #6

Sequential and Random Reads from SSD can be the same or a mixed bag depending on the model SSD you get. If your IOPs exceed 10,000, you can change innodb_io_capacity to 10,000 and innodb_io_capacity_max to the maximum IOPs.

Summary

Get 32GB of RAM for the WIMP machine and use these setting in my.ini

[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 2
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 10000
innodb_io_capacity_max = (Maximum IOPs of the SSD)

Alternative to SSD Only

If you are will to configure it, there is a design that a Facebook Engineer uses to split up InnoDB tables and logs to lengthen the life of the SSD by writing the sequential stuff for InnoDB (Double Write Buffer, Log Files) to a fast HDD.

I have discussed this before

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536