1

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.

Community
  • 1
OneOfOne
  • 222
  • 4
  • 13
  • What would you recommend for the memory settings? – OneOfOne Jan 14 '11 at 22:39
  • (moved my comment to an answer) Dunno, depends on what else is running on the host? Also what storage engines are in use and so on. There are lots of guides on tuning mysql. Maybe start here http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ – Zoredache Jan 14 '11 at 22:41
  • *moved from superuser* ? Need to be a pretty super user to fix that problem :-) – Déjà vu Jan 15 '11 at 03:15
  • 2
    Where did you get 12GHz CPU and how long it took to port AMD64 Debian to it? – AndrejaKo Jan 14 '11 at 22:04
  • It's a virtual machine on the cloud and there was no porting needed, as far as I can tell the provider is using Xen. – OneOfOne Jan 14 '11 at 22:12
  • @OneOfOne Still, it's strange to have 12 GHz. Are you sure it's not 6 2GHz cores? Also, did you try with [ServerFault](http://serverfault.com/)? I'm not 100% sure, but I think that your question would fit better there (their FAQ states explicitly that they are about servers among other things). – AndrejaKo Jan 14 '11 at 22:21
  • Hmm good call on serverfault thank you. and yes its 6x2ghz cores (12 is from the Virtual machine creation, can't control how many cores). – OneOfOne Jan 14 '11 at 22:24
  • @OneOfOne Oh, well you can't just multiply cores like that. It's like having six cars which go 180 km/h each and having one car which goes 1080 km/h. – AndrejaKo Jan 14 '11 at 22:36
  • In actuality, you should have 3008MB RAM and 160GB disk according to GigenetCloud's offering, as I highly doubt you have 7520GB of RAM. – gekkz Jan 15 '11 at 12:03
  • We're not using the whole disk on that specific machine. – OneOfOne Jan 15 '11 at 14:37

6 Answers6

3

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.

Publiccert
  • 1,110
  • 1
  • 8
  • 22
3

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?

Community
  • 1
danlefree
  • 2,873
  • 1
  • 18
  • 20
  • I'm not sure how a reverse proxy would stop invoking PHP/MySQL. – OneOfOne Jan 15 '11 at 07:12
  • @OneOfOne - Given that most of your site's traffic is probably coming from unauthenticated users who will only read the content, it is unnecessary to send their requests through vBulletin as they would be satisfied with cached content served by a reverse proxy. – danlefree Jan 15 '11 at 22:28
3

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.

Elliott
  • 328
  • 2
  • 4
  • 1
    +1. OneOfOne, find out how much memory your webserver consumes during peaks, and try to allocate as much as you can to MySQL. Find out how big your MyISAM and InnoDB datasets are, and allocate respectively. You can deploy frontend caching using Varnish, but that might require more investigation from your side. I'm happy to give you pointers. – 3molo Jan 16 '11 at 07:57
2

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.

Zoredache
  • 128,755
  • 40
  • 271
  • 413
1

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.
TomTom
  • 50,857
  • 7
  • 52
  • 134
0
  • 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)
pitr
  • 567
  • 3
  • 6