These forums are read-only!
high root i/o usage on site with busy punbb forums
  • natebeatynatebeaty
    i've been running a 2gb slice for over a year, primarily hosting a busy punbb forum (bmxmuseum.com/forums) serving ~5.5m p/vs per month, ~11k visitors/day using nginx, php-fastcgi and mysql on an ubuntu slice. (that was a mouthful.) i also run a few other lower-volume nginx/php sites and a few django sites on the slice.

    everything runs pretty smoothly except i have a very high root i/o usage, constantly:

    * Your swap IO usage over the last 4 hours is low: 0.0538 reads/s, 0.0 writes/s.
    * Your root IO usage over the last 4 hours is high: 81.7989 reads/s, 156.8362 writes/s.

    wondering if anyone has any suggestions. the forums are probably 50% private messaging using a punbb mod (which i had to optimize the db tables for, so i worry there may be more sloppy code), and most of the rest "For Sale" postings. iow: very high turnover, difficult to cache.

    mysql tuning settings:

    thread_stack = 64K
    thread_cache_size = 4
    net_buffer_length = 2k
    interactive_timeout = 100
    wait_timeout = 1800
    connect_timeout = 120
    max_allowed_packet = 16M
    query_cache_limit = 8M
    query_cache_size = 128M
    query_cache_type = 1
    join_buffer_size = 512K
    max_connections = 150
    max_heap_table_size = 256M
    max_write_lock_count = 1
    tmp_table_size = 256M
    table_cache = 512M
    key_buffer_size = 256M
    sort_buffer = 4M
    read_rnd_buffer_size = 256K
    skip-locking
    skip-innodb

    these have been adjusted at many random intervals from random sources, just trying to get my slice to deal with the punbb traffic. very much open to adjustment suggestions.

    it has hummed along for nearly a year, usually at around .6-1.2 load, just grinding away with disk access. this morning, however, load went to 5-7, and sites stopped responding, and i saw many of these in my nginx error log:

    2009/03/27 10:23:19 [error] 4603#0: *1730 upstream timed out (110: Connection timed out) while connecting to upstream, client: 64.124.140.230, server: bmxmuseum.com, request: "GET /forums/ HTTP/1.1", upstream: "fastcgi://127.0.0.1:8090", host: "bmxmuseum.com"

    (i changed port from :9000 to :8090 this morning in troubleshooting.)

    ben@slicehost did some magic to bring things back to normal, but mentioned i should address the root i/o usage.

    any help would be much appreciated. i very hesitantly don the sysadmin hat, and am about ready to just have my client hire someone to poke around as i'd rather code than sysadmin. (truth be told, i'd rather draw comics, but that's another matter entirely.)

    thanks,
    nate
  • jmstacey
    Are you using a PHP accelerator? If it were me, I would try installing APC to see how big of a boost storing the code in shared memory gives. Loading and compiling the code on every request could be significantly contributing to the I/O usage.
  • artagesw
    Compiling the code would cause CPU usage, not I/O usage. And I'd be willing to bet that frequently accessed PHP files will find their way into the file system cache, which would eliminate I/O activity for accessing those files. This sounds more likely to be the result of database activity. Note also that there is about a 2-1 write/read ratio.
  • jmstacey
    That certainly could be. I'm just thinking that because the database is I/O intensive, then the scripts would be bumped out of cache resulting in another read on the next request. I don't know how smart or efficient the Linux kernel I/O subsystem is. I would think that even if a read hit cache (physical) that it would add to the I/O number. But like I said, I don't know anything about the I/O subsystem, or how xen virtualizes it for that matter.

    I don't know why I didn't remember this before, but install iotop. That will give you a break down of I/O usage by process. Very neat. Also take a look at the MySQL status report which will give you indications on what tuning can be done there.
  • drobbins
    Just a small point... table_cache is specified as an integer, not an amount of memory. So it would be "table_cache = 512" not "table_cache = 512M". I don't know what effect the "512M" might have on MySQL, it might just ignore the "M" or it might not be using a table cache at all -- which would really affect performance :)
  • natebeatynatebeaty
    @drobbins: nice catch, thanks. i'm not sure what mysql was interpreting for the M, but it was caching more than 512. i realized this was too high anyway monitoring SHOW STATUS LIKE 'open%tables%'; so i've reduced it to 64.

    @jmstacey: yes, i'm using xcache.

    i know there's more i can do with optimizing my database (always!) and i monitor mysql slow log, picking away at whatever shows up. but with this amount and nature of traffic, i'm not sure how i can avoid a lot of disk activity when it's mostly writing to the db.

    i guess i'm primarily asking if this is normal -- if you are hosting a very busy forum where there are a ton of PM's and posts written on a near-constant basis, is there any way to avoid thrashing the disk?

    thanks again for any help.
  • jmstacey
    Something that I haven't really looked at, but may be worthwhile is data compression. I've heard that PostgreSQL is able to compress data on the fly resulting in less I/O.
  • natebeatynatebeaty
    @jmstacey: i'm not sure that would help my situation .. i'm still seeing "Your root IO usage over the last 4 hours is high: 54.5695 reads/s, 110.4544 writes/s" after eliminating the most obvious of inefficient sql queries. i have to believe either something is haywire in my.cnf causing it to write to disk on any queries requiring filesort, or ..? any ideas on how to track down what's causing the disk thrashing?
  • natebeatynatebeaty
    i assume this isn't a good thing:

    lsof | wc
    4434 40129 489468

    !!

    lsof | grep 'apache2' | wc
    960 8656 109520

    lsof | grep 'www-data' | wc
    3126 28256 344374

    umm.. i can send a direct link to lsof output to someone who cares to look.. but i assume that's *not* normal, yes?
  • natebeatynatebeaty
    i tried to use iotop but get this:

    $ iotop
    Could not run iotop as some of the requirements are not met:
    . Python >= 2.5 for AF_NETLINK support: Found
    . Linux >= 2.6.20 with I/O accounting support: Not found

    any ideas? i'm assuming my kernel doesn't support it. i do have python 2.5 installed.

    i've reduced the root io by almost half by optimizing queries, indices and various my.cnf settings over the last few days:

    - Your root IO usage over the last 4 hours is high: 40.5187 reads/s, 104.7051 writes/s.

    but it's obviously still too high. i'm having a hard time finding what's causing all the writes without iotop. i still assume it's mysql, but is there anything else that might cause that?

    thanks in advance for any help.
  • dmorin
    I know this is a few weeks old now, but if your writes are 2x because of db inserts/updates, you may want to look at switching to innodb tables instead. Also, the mysql query chache may actually be doing more harm then good with this many writes so you might want to try disabling that also. Of course, these only apply if it's your db that's causing all the writes.
  • natebeatynatebeaty
    it may be a few weeks old but i am still working on it!

    thanks for the suggestions, and i did consider these. i did a lot of research and combing of slow queries, tweaking of indexes and my.cnf, as well as adding SQL_NO_CACHE to what queries were obviously not cache-worthy. (selects based on timestamp to check punbb online status being the major one.)

    i've improved things (now around 36.5665 reads/s, 79.7907 writes/s), but i'm still tracking down what queries are not using joins with proper indexes, and which should be omitted from caching. i'd say 95% of the traffic is primarily focused on punbb, in one particularly large forum (~600k posts) and private messaging using one large table (~400k). there is also a lot of searching, and the punbb_search_matches has over 9 million records. punbb's core is pretty well written but the few mods i've added are another matter. i've had to make many tweaks to the mods' db tables and queries to deal with traffic. (primarily the PM mod of course.)

    there is one query that i've been unable to optimize that pulls the RSS feed for the ~600k forum. i think i just need to rewrite the code to use a few smaller queries than one big one, as it does a table scan no matter how i change the query or throw indexes at the table. (this is the punbb RSS code offered up by alex king ages ago.)

    attaching mysqlreport's output below. the most obvious problems are:

    __ SELECT and Sort _____________________________________________________
    Scan          484.30k     5.2/s %SELECT:  18.66

    ..from too many joins w/out indexes, and:

    __ Created Temp ________________________________________________________
    Disk table     20.36k     0.2/s
    Table          70.96k     0.8/s    Size: 512.0M
    File            3.65k     0.0/s

    ..from too many tmp tables written to disk. i've tried bumping up the query cache to very high levels for short periods of time and still have this problem, not 100% sure why.

    i'm unable to grok if this is a problem:

    __ Query Cache _________________________________________________________
    Memory usage   25.36M of  64.00M  %Used:  39.63
    Block Fragmnt  25.26%
    Hits           32.82M   355.7/s
    Inserts         2.28M    24.7/s
    Insrt:Prune   2.28M:1    24.7/s
    Hit:Insert    14.37:1

    ..from what i've read if the insert-to-prune ratio is too high (too many prunes) it's a problem. but, afaict, there are no prunes going on here, no? i'm afraid i'm completely mis-reading this section.

    as always, any help much appreciated. i'm still heavy on the learning curve here.



    more of the mysqlreport output (i have no innodb tables):

    __ Key _________________________________________________________________
    Buffer used   108.14M of 256.00M  %Used:  42.24
      Current     147.35M            %Usage:  57.56
    Write hit      75.91%
    Read hit       99.97%

    __ Questions ___________________________________________________________
    Total          36.73M   398.0/s
      QC Hits      32.82M   355.7/s  %Total:  89.36
      DMS           3.12M    33.8/s            8.50
      Com_        509.94k     5.5/s            1.39
      COM_QUIT    322.37k     3.5/s            0.88
      -Unknown     45.78k     0.5/s            0.12
    Slow 1 s          378     0.0/s            0.00  %DMS:   0.01  Log:  ON
    DMS             3.12M    33.8/s            8.50
      SELECT        2.59M    28.1/s            7.06         83.15
      UPDATE      447.67k     4.9/s            1.22         14.34
      INSERT       47.57k     0.5/s            0.13          1.52
      DELETE       30.54k     0.3/s            0.08          0.98
      REPLACE           0       0/s            0.00          0.00
    Com_          509.94k     5.5/s            1.39
      change_db   444.39k     4.8/s            1.21
      admin_comma  45.95k     0.5/s            0.13
      set_option   16.43k     0.2/s            0.04

    __ SELECT and Sort _____________________________________________________
    Scan          484.30k     5.2/s %SELECT:  18.66
    Range          29.16k     0.3/s            1.12
    Full join       3.05k     0.0/s            0.12
    Range check         0       0/s            0.00
    Full rng join       0       0/s            0.00
    Sort scan      68.83k     0.7/s
    Sort range    154.85k     1.7/s
    Sort mrg pass   1.82k     0.0/s

    __ Query Cache _________________________________________________________
    Memory usage   25.36M of  64.00M  %Used:  39.63
    Block Fragmnt  25.26%
    Hits           32.82M   355.7/s
    Inserts         2.28M    24.7/s
    Insrt:Prune   2.28M:1    24.7/s
    Hit:Insert    14.37:1

    __ Table Locks _________________________________________________________
    Waited         14.19k     0.2/s  %Total:   0.29
    Immediate       4.83M    52.3/s

    __ Tables ______________________________________________________________
    Open              744 of  750    %Cache:  99.20
    Opened            895     0.0/s

    __ Connections _________________________________________________________
    Max used           38 of   50      %Max:  76.00
    Total         322.37k     3.5/s

    __ Created Temp ________________________________________________________
    Disk table     20.36k     0.2/s
    Table          70.96k     0.8/s    Size: 512.0M
    File            3.65k     0.0/s

    __ Threads _____________________________________________________________
    Running             1 of    1
    Cached             37 of   40      %Hit:  99.99
    Created            38     0.0/s
    Slow                0       0/s

    __ Aborted _____________________________________________________________
    Clients             0       0/s
    Connects            3     0.0/s

    __ Bytes _______________________________________________________________
    Sent           14.47G  156.8k/s
    Received        3.16G   34.3k/s
Add a Comment