Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    96

    Thumbs up High Mysql Load :(

    it was very fast until mysql upgraded to 5.0.45 it was 4.. i canít even turn my forum if it is a busy time cos it is so slow i get page not found after a while but when it is quiet it is not too bad... but it was alot more faster with mysql 4 i don't really want to downgrade please give me some ideas to fix this issue I would appreciate your help

    AMD Dual-Core AMD Opteron(tm) Processor 2216
    8GB ram
    150GB 10.000rpm hd
    centOS
    Apache Version Apache/1.3.41 (Unix) PHP/5.2.5
    MySQL version: 5.0.45
    top
    top - 12:41:24 up 2:22, 1 user, load average: 19.78, 15.24, 11.60
    Tasks: 275 total, 1 running, 273 sleeping, 0 stopped, 1 zombie
    Cpu(s): 78.3% us, 12.6% sy, 0.0% ni, 0.1% id, 9.0% wa, 0.1% hi, 0.0% si
    Mem: 4151448k total, 3406280k used, 745168k free, 5820k buffers
    Swap: 2096440k total, 12268k used, 2084172k free, 662652k cached

    PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
    25262 mysql 15 0 177 5:58.85 2.5 235m 101m 3436 S mysqld
    23949 nobody 25 0 47 0:01.82 0.4 25364 14m 2528 S httpd
    26489 nobody 16 0 9 0:28.99 0.3 21844 11m 3372 S httpd
    26314 nobody 16 0 9 0:18.80 0.3 21716 11m 2708 S httpd
    23959 nobody 16 0 1 0:18.54 0.3 21456 11m 2708 S httpd
    26356 nobody 17 0 1 0:19.55 0.3 21452 11m 2712 S httpd
    26489 nobody 15 0 1 0:28.72 0.3 21584 11m 3372 S httpd
    24056 nobody 15 0 1 0:00.72 0.2 20348 9.8m 2684 S httpd
    6751 nobody 16 0 1 0:05.73 0.3 21728 11m 2684 S httpd
    8778 mailnull 15 0 1 0:00.02 0.1 9580 3804 2572 S exim
    23967 nobody 16 0 1 0:00.37 0.3 23224 12m 2656 S httpd
    23972 nobody 15 0 1 0:00.20 0.2 18208 7908 2456 S httpd
    24295 nobody 15 0 1 0:00.18 0.2 20084 9784 2672 S httpd
    26328 nobody 15 0 1 0:19.57 0.3 21820 11m 2712 R httpd
    26372 nobody 16 0 1 0:22.88 0.2 19876 9700 2724 S httpd
    26487 nobody 15 0 1 0:25.55 0.2 18416 8760 3120 R httpd
    2114 root 15 0 0 0:22.35 0.0 0 0 0 S kjournald
    2793 named 18 0 0 0:01.89 0.1 69732 3596 1860 S named
    8754 root 16 0 0 0:00.11 0.0 2440 1136 784 R top
    12071 nobody 16 0 0 0:22.02 0.3 21736 11m 2712 S httpd
    18551 nobody 16 0 0 0:11.94 0.3 21788 11m 3452 S httpd
    23761 nobody 16 0 0 0:01.34 0.3 21668 11m 2672 S httpd
    23768 nobody 15 0 0 0:01.67 0.3 23356 12m 2688 S httpd
    23971 nobody 16 0 0 0:00.51 0.3 21728 11m 2648 S httpd
    24132 nobody 15 0 0 0:00.35 0.2 18356 8016 2608 S httpd
    24133 nobody 16 0 0 0:00.40 0.3 23236 12m 2668 S httpd
    26531 nobody 16 0 0 0:20.87 0.3 23036 12m 2812 S httpd
    1 root 16 0 0 0:01.16 0.0 3188 548 468 S init
    2 root RT 0 0 0:00.05 0.0 0 0 0 S migration/0
    3 root 34 19 0 0:00.04 0.0 0 0 0 S ksoftirqd/0
    4 root RT 0 0 0:00.05 0.0 0 0 0 S migration/1
    5 root 34 19 0 0:00.05 0.0 0 0 0 S ksoftirqd/1
    6 root RT 0 0 0:00.18 0.0 0 0 0 S migration/2
    7 root 34 19 0 0:00.21 0.0 0 0 0 S ksoftirqd/2
    8 root RT 0 0 0:00.11 0.0 0 0 0 S migration/3
    9 root 34 19 0 0:00.13 0.0 0 0 0 S ksoftirqd/3
    10 root 5 -10 0 0:00.00 0.0 0 0 0 S events/0
    11 root 5 -10 0 0:00.00 0.0 0 0 0 S events/1
    12 root 5 -10 0 0:00.00 0.0 0 0 0 S events/2
    13 root 5 -10 0 0:00.00 0.0 0 0 0 S events/3
    14 root 8 -10 0 0:00.00 0.0 0 0 0 S khelper
    15 root 5 -10 0 0:00.00 0.0 0 0 0 S kblockd/0
    16 root 5 -10 0 0:00.00 0.0 0 0 0 S kblockd/1
    17 root 5 -10 0 0:00.00 0.0 0 0 0 S kblockd/2
    18 root 5 -10 0 0:00.00 0.0 0 0 0 S kblockd/3
    19 root 16 0 0 0:00.00 0.0 0 0 0 S khubd
    78 root 15 0 0 0:10.74 0.0 0 0 0 S kswapd0
    79 root 6 -10 0 0:00.00 0.0 0 0 0 S aio/0
    80 root 5 -10 0 0:00.00 0.0 0 0 0 S aio/1
    81 root 5 -10 0 0:00.00 0.0 0 0 0 S aio/2
    82 root 5 -10 0 0:00.00 0.0 0 0 0 S aio/3
    my.cnf
    [mysqld]
    tmpdir=/home/copluk
    safe-show-database
    #old_passwords
    back_log = 50
    skip-innodb
    max_connections = 500
    key_buffer_size = 64M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 2500
    thread_cache_size = 128
    wait_timeout = 15
    connect_timeout = 10
    tmp_table_size = 64M
    max_heap_table_size = 64M
    max_allowed_packet = 64M
    net_buffer_length = 16384
    max_connect_errors = 10
    thread_concurrency = 4
    concurrent_insert = 2
    table_lock_wait_timeout = 30
    read_rnd_buffer_size = 786432
    bulk_insert_buffer_size = 8M
    query_cache_limit = 3M
    query_cache_size = 48M
    query_cache_type = 1
    query_prealloc_size = 262144
    query_alloc_block_size = 65536
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    default-storage-engine = MyISAM
    max_write_lock_count = 16


    [mysqld_safe]
    open_files_limit = 8192

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [myisamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M
    ./mysqlreport
    MySQL 5.0.45-community uptime 0 0:19:27 Fri Mar 14 13:05:44 2008

    __ Key __________________________________________________ _______________
    Buffer used 16.32M of 64.00M %Used: 25.50
    Current 23.69M %Usage: 37.01
    Write hit 6.18%
    Read hit 98.92%

    __ Questions __________________________________________________ _________
    Total 15.88k 13.6/s
    QC Hits 7.46k 6.4/s %Total: 46.99
    DMS 5.67k 4.9/s 35.72
    Com_ 1.65k 1.4/s 10.41
    COM_QUIT 1.65k 1.4/s 10.36
    -Unknown 553 0.5/s 3.48
    Slow (10) 255 0.2/s 1.61 %DMS: 4.49 Log: OFF
    DMS 5.67k 4.9/s 35.72
    SELECT 4.65k 4.0/s 29.25 81.90
    INSERT 737 0.6/s 4.64 12.99
    UPDATE 248 0.2/s 1.56 4.37
    DELETE 30 0.0/s 0.19 0.53
    REPLACE 12 0.0/s 0.08 0.21
    Com_ 1.65k 1.4/s 10.41
    change_db 1.63k 1.4/s 10.24
    set_option 17 0.0/s 0.11
    show_proces 4 0.0/s 0.03

    __ SELECT and Sort __________________________________________________ ___
    Scan 1.35k 1.2/s %SELECT: 29.06
    Range 465 0.4/s 10.01
    Full join 1 0.0/s 0.02
    Range check 0 0/s 0.00
    Full rng join 0 0/s 0.00
    Sort scan 268 0.2/s
    Sort range 986 0.8/s
    Sort mrg pass 517 0.4/s

    __ Query Cache __________________________________________________ _______
    Memory usage 16.60M of 48.00M %Used: 34.58
    Block Fragmnt 5.25%
    Hits 7.46k 6.4/s
    Inserts 3.82k 3.3/s
    Insrtrune 3.82k:1 3.3/s
    Hit:Insert 1.95:1

    __ Table Locks __________________________________________________ _______
    Waited 674 0.6/s %Total: 7.86
    Immediate 7.90k 6.8/s

    __ Tables __________________________________________________ ____________
    Open 2358 of 2500 %Cache: 94.32
    Opened 2.36k 2.0/s

    __ Connections __________________________________________________ _______
    Max used 501 of 500 %Max: 100.20
    Total 1.65k 1.4/s

    __ Created Temp __________________________________________________ ______
    Disk table 1 0.0/s
    Table 36 0.0/s Size: 64.0M
    File 307 0.3/s

    __ Threads __________________________________________________ ___________
    Running 501 of 501
    Cached 0 of 128 %Hit: 69.65
    Created 501 0.4/s
    Slow 0 0/s

    __ Aborted __________________________________________________ ___________
    Clients 6 0.0/s
    Connects 10 0.0/s

    __ Bytes __________________________________________________ _____________
    Sent 897.53M 769.1k/s
    Received 4.59M 3.9k/s

    __ InnoDB Buffer Pool __________________________________________________
    Usage 0 of 0 %Used: 0.00
    Read hit 0.00%
    Pages
    Free 0 %Total: 0.00
    Data 0 0.00 %Drty: 0.00
    Misc 0 0.00
    Latched 0 0.00
    Reads 0 0/s
    From file 0 0/s 0.00
    Ahead Rnd 0 0/s
    Ahead Sql 0 0/s
    Writes 0 0/s
    Flushes 0 0/s
    Wait Free 0 0/s

    __ InnoDB Lock __________________________________________________ _______
    Waits 0 0/s
    Current 0
    Time acquiring
    Total 0 ms
    Average 0 ms
    Max 0 ms

    __ InnoDB Data, Pages, Rows ____________________________________________
    Data
    Reads 0 0/s
    Writes 0 0/s
    fsync 0 0/s
    Pending
    Reads 0
    Writes 0
    fsync 0

    Pages
    Created 0 0/s
    Read 0 0/s
    Written 0 0/s

    Rows
    Deleted 0 0/s
    Inserted 0 0/s
    Read 0 0/s
    Updated 0 0/s
    apache httpd.conf
    ServerType standalone
    KeepAlive On
    MaxKeepAliveRequests 100
    KeepAliveTimeout 15
    MinSpareServers 5
    MaxSpareServers 10
    StartServers 5
    MaxClients 150
    MaxRequestsPerChild 0
    forum

    Data Usage 3.20 GB database
    Index Usage 1.40 GB
    Attachment Usage 2.00 GB
    MySQL Packet Size 64.00 MB
    PHP Memory Limit 64.00 MB
    any help will appreciated

  2. #2
    Join Date
    Mar 2008
    Location
    kolkata, India
    Posts
    102
    Look like a query issue:

    Here's a utility called mysqldumpslow that might help you analyze that slow query log: http://dev.mysql.com/doc/refman/5.0/...query-log.html

    Devshed.com has a couple of good articles about improving query performance by analyzing queries and adding proper indexes, etc:
    http://www.devshed.com/c/a/MySQL/Ana...-with-EXPLAIN/
    http://www.devshed.com/c/a/MySQL/Opt...r-Query-Speed/
    http://www.devshed.com/c/a/MySQL/Enh...ry-Efficiency/

    If MySQL's CPU usage is pegged because of one or more specific queries, then it's probably because of two conditions - lots of rows are affected by a query and that query is doing something that requires CPU. Specifically, date comparison is a big one, but also encryption or hash computations are expensive. Mathematical functions aren't as bad, but if you get complex enough and cover enough rows, they might be CPU-bound. If this is the case, most of the time you can find a way to move those calculations to the application layer.

    It's probably not because you're running out of connections - if that were the case, mysql would just reject connections once it got full and you'd see php throwing errors into the web server logs.
    Sysfirm
    So you think your server is secure?
    Try our security Service
    With SysFirm

  3. #3
    Join Date
    Mar 2006
    Location
    Servers
    Posts
    1,588
    Also you might increase this:

    tmp_table_size = 64M
    max_heap_table_size = 64M


    to 256M



    Thanks,

  4. #4
    Join Date
    Dec 2006
    Posts
    477
    mysqlreport when its been up for a lot longer than 19 minutes will be useful but a few things I can see. 1. You say the total size of the indexes in the database is 1.4Gb. innodb is skipped so it must be all MyISAM. Your keybuffer is only 64M which means only 64M of the 1.4GB of indexes could be cached in RAM. Normally I'd say increase the keybuffer; however, mysqlreport is showing that only 25% of the 64Mb is in use. This could be because of the short uptime or it could be the index stats reports are incorrect. If you really do have 1.4GB of indexes then on a 4GB RAM server, I'd give at least 512M to the key buffer - possibly upto 1GB depending on how frequently all those indexes are hit. 2. Your sort merge passes are high 0.4/s when you only have 1.2 sorts/s. You can fix this by increasing your sort buffer, but a more likely explanation on a forums server if you have poor queries that are returning far too many rows of data - poorly written extensions are the prime suspects here. 3. You are only processing 4 select statements/sec - that isn't high and should not cause this sort of load problems. (apologies for poor formatting - wht forums are eating all the whitespace for some reason)

Posting Permissions

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