Home / server / Questions / 222819
Accepted
OneOfOne
Asked: 2011-01-15 13:52:49 +0800 CST

MySQL is killing the server IO [duplicate]

  • 772

Possible Duplicate:
MySQL is killing the server IO.

I manage a fairly large/busy vBulletin forums (running on gigenet cloud), the database is ~ 10 GB (~9 milion posts, ~60 queries per second), lately MySQL have been grinding the disk like there's no tomorrow according to iotop and slowing the site.

The last idea I can think of is using replication, but I'm not sure how much that would help and worried about database sync.

I'm out of ideas, any tips on how to improve the situation would be highly appreciated.

Specs : 

Debian Lenny 64bit
~12Ghz (6 cores) CPU, 7520gb RAM, 160gb disk.  
Kernel : 2.6.32-4-amd64  
mysqld  Ver 5.1.54-0.dotdeb.0 for debian-linux-gnu on x86_64 ((Debian))

Other software:

vBulletin 3.8.4
memcached 1.2.2
PHP 5.3.5-0.dotdeb.0 (fpm-fcgi) (built: Jan  7 2011 00:07:27)
lighttpd/1.4.28 (ssl) - a light and fast webserver

PHP and vBulletin are configured to use memcached.

MySQL Settings : 

[mysqld]
key_buffer              = 128M
max_allowed_packet      = 16M
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections        = 1024
query_cache_limit       = 2M
query_cache_size        = 128M
expire_logs_days        = 10
max_binlog_size         = 100M

key_buffer_size = 128M
join_buffer_size = 8M
tmp_table_size = 16M
max_heap_table_size = 16M
table_cache = 96

Other :

> vmstat
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 9  0  73140  36336   8968 1859160    0    0    42    15    3    2  6  1 89  5

> /etc/init.d/mysql status
Threads: 49  Questions: 252139  Slow queries: 164  Opens: 53573  Flush tables: 1  Open tables: 337  Queries per second avg: 61.302.

Edit Additional info.

mysql optimization cloud

6 Answers

  1. Regardless of whether you can reduce the overhead, I would recommend replication of MySQl to a secondary server. With some load-balancers, you can dramatically decrease your downtime AND ease the load on the server. Just a thought. Message me if you want some guidance on setting up replication.

    • 3

  2. If your forum's traffic is anything like the traffic I've witnessed in managing vBulletin, you really shouldn't be invoking PHP or MySQL for 50-75% of the requests (i.e. more than half of the requests come from unauthenticated "lurkers").

    Take a look at implementing a reverse proxy for unauthenticated users if you haven't done so already - unless you've made some serious modifications to vBulletin, unauthenticated users won't be seeing any dynamic content anyway.

    Update: Related reading: How to set up Nginx as a caching reverse proxy?

    • 3
  3. Best Answer

    First, though only tangentially related, you should probably look into switching from MyISAM to InnoDB. It will perform better under the kind of concurrency you likely have, and is much less likely to lose data in the event of a crash.

    How much memory are you feeding to the memcached instance? Increasing this may help if your evictions and misses are high, but it will require some experimentation.

    128MB is definitely too low of a key_buffer considering your data set size and available RAM. I would say it should be more like 1-2GB if you can spare the RAM (or, if you switch to InnoDB, replace "key_buffer" with "InnoDB buffer pool size"). Your "blocks in" is 3x your blocks out, which probably means MySQL is having to hit the disk for a significant portion of the reads. You can use mysqltuner or the stats in phpmyadmin to see if things like the sort buffer etc need tuning, but they're most likely not the largest problem.

    Check your detailed stats like hit rate on the query cache. There's a decent chance it's not actually doing you any good and should be turned off, especially since you're also using memcache.

    The good news is, you're read-bound rather than write-bound, which means you can improve performance relatively easily via cacheing. Worst case, if you don't have enough free RAM to increase key_buffer or memcached and can't expand your current server, you can move lighttpd and memcached to a separate nearby server and dedicated the entire ~8GB to MySQL. With a 10GB dataset, that will be ample. There's no need to resort to a replication slave for the sake of performance, though as someone else mentioned it is beneficial for backups and failover.

    • 3

  4. If you have 8GB of RAM, the memory values you have for the mysql server seem kind of small. Is your database, and web server on the same host? The simple solution might be to just setup a second host and separate the Web, and Database services if you haven't already.

    • 2

  5. This ia typical SQL problem (irrelevant 3whether it is oracle, sql-server, mysql).

    Databases ARE io bound. Having lots of fast discs often is needed. VPS often dont allow knowing what you have here. If you say "169gb disc" then the questio n is - what IO budget do you ahve here? If that is a simple small virtual disc on a simple disc, or raid 5... shared... cheap... welcome to slow IO. if it is on FAST discs, raid 10.... this i a problem. Also, in a size like this your server is very "unusual" in that is it small. 10gb database I would keep it all in memory (12-16gb server memory only for the db server). and 6 cores and 7gb is a little odd (little memory for so many cores).

    To optimize, you can:

    • Analyze the SQL statements, especialyl those taking time and doing lots of IO. Not sure how to get that info from MySQL (I do mostly sql server). mabe there is some index missing? 9 million (!) posts is notwhat every forum has, so it may e you run into a db optimization problem that is no poblem for most people.
    • Make disc IO checks and find out how bad the disc IO budget is.
    • Change your Mysql stats. The buffers you quote there loook VERY small for a 10gb database. For example: tmp_table_size = 16M - if there is a need for the temp table for something, it may well be too small for a 10gb database. Sme with pretty much all other items - the database is not 1gb large, so maybe some tuning there is needed.
    • 1
    • check http://mysqltuner.pl/mysqltuner.pl advice and consider tuning cache sizes and tmp tables according to them
    • go through your most accessed slow queries and consider optimizing (or avoiding) them
    • if you're using sata disk drives, migrate to faster drives - e.g. SAS (this is important)
    • if you're using raid1 with only 2 disk drives, try to expand it to 4 in raid10
    • if you're limited by budget in doing the two above, consider extending available RAM and put database in ramdisk with replication to second host with harddrives (this is extremely risky, but if you can afford to lose a bit of recent data in case of crash, and it's not the core of your busines, it can be an option)
    • 0