Results 1 to 5 of 5

Thread: Runaway I/O %

  1. #1

    Runaway I/O %

    hi guys, im running a medium sized forum(~8000 members, 150 online at once peaks) and i'm running into an issue where i have runaway I/OWAIT which results in the entire system freezing up - Currently running a futurehosting VPS, 512mb ram, 30gb hdd space etc etc

    When the IO starts to climb im usually not even close to using up all the memory

    Can a bad database cause this? or is it more likely to be faulty disks/array controllers in the vps node?

    atm i'm just running the recommended vps setting for mysql.conf

    Code:
    [mysqld]
    max_connections = 400
    key_buffer = 16M
    myisam_sort_buffer_size = 32M
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 1024
    thread_cache_size = 286
    interactive_timeout = 25
    wait_timeout = 1000
    connect_timeout = 10
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 1M
    query_cache_size = 16M
    query_cache_type = 1
    tmp_table_size = 16M
    skip-innodb
     
    [mysqld_safe]
    open_files_limit = 8192
     
    [mysqldump]
    quick
    max_allowed_packet = 16M
     
    [myisamchk]
    key_buffer = 32M
    sort_buffer = 32M
    read_buffer = 16M

  2. #2
    Join Date
    Sep 2006
    Location
    Smiths Falls, ON
    Posts
    772
    Best suggestion to start is to file a ticket with FutureHosting and let them know. Might be a node-wide issue.
    Ryan G.
    Owner
    Umgardi.ca

  3. #3
    Join Date
    Jan 2006
    Location
    Sydney, Australia
    Posts
    251
    Database server is usually the one that thrashes the disk IO, and sometimes it can be fixed by throwing more memory on to it. If you still have plenty of memory, try increase query_cache_size (to 32M maybe?) to cache frequently used queries.

    Also good idea to check MySQL runtime information page in phpMyAdmin as it shows where you can tweak the settings.

    Quote Originally Posted by Tensop View Post
    atm i'm just running the recommended vps setting for mysql.conf

    Code:
    [mysqld]
    max_connections = 400
    key_buffer = 16M
    myisam_sort_buffer_size = 32M
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 1024
    thread_cache_size = 286
    interactive_timeout = 25
    wait_timeout = 1000
    connect_timeout = 10
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 1M
    query_cache_size = 16M
    query_cache_type = 1
    tmp_table_size = 16M
    skip-innodb
     
    [mysqld_safe]
    open_files_limit = 8192
     
    [mysqldump]
    quick
    max_allowed_packet = 16M
     
    [myisamchk]
    key_buffer = 32M
    sort_buffer = 32M
    read_buffer = 16M

  4. #4
    okie doke, heres some info from phpmyadmin

    Code:
    Handler_read_rnd             478                The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.                                Handler_read_rnd_next             442 k              The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
    
    Created_tmp_disk_tables             45                The number of temporary tables on disk created automatically by the server while executing statements. 
    
    
    
    If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.
    
    Key_reads             7,861                The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.
    
    Select_full_join             5                The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.
    
    Opened_tables             203                            The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.
    Table_locks_waited             524                The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.
    Those are the ones listed in Red
    Sounding like a possible indexing issue?
    Last edited by Tensop; 02-07-2008 at 07:25 AM.

  5. #5
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    There's a known issue with the SAN on some FH nodes (or has been very recently). Check with them before you start tweaking.
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •