Announcement

Announcement Module
Collapse
No announcement yet.

MySQL Performance

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL Performance

    Hello,

    This server has dual intel xeon 5430 cpu, 8GB ram and 2x15k rpm sa-scsi hard drives on raid1. It is a web server and thus apache and mysql run on the same machine for now. We have innodb databases which are several gigabytes big (mediawiki with 12000 articles and some vbulletin forums).

    Quote:

    [mysqld]
    datadir = /var/lib/mysql
    socket = /var/lib/mysql/mysql.sock
    query_cache_limit = 1M
    query_cache_size = 64M
    query_cache_type = 1
    max_user_connections = 350
    max_connections = 500
    # interactive_timeout = 10
    # wait_timeout = 10
    # connect_timeout = 10
    thread_cache_size = 128
    key_buffer_size = 128M
    join_buffer = 64MB
    max_allowed_packet = 16M
    table_cache = 1024
    record_buffer = 1M
    innodb_buffer_pool_size = 1024M
    innodb_log_buffer_size = 16M
    innodb_log_file_size = 128M
    read_buffer_size = 4M
    read_rnd_buffer_size = 4M
    sort_buffer_size = 8M
    myisam_sort_buffer_size = 8M
    max_heap_table_size = 128M
    tmp_table_size = 128M
    max_connect_errors = 10
    thread_concurrency = 8
    safe-show-database
    long_query_time = 1
    server-id = 1

    #[mysql.server]
    #user = mysql
    #basedir = /var/lib

    [safe_mysqld]
    err-log = /var/log/mysqld.log
    pid-file = /var/lib/mysql/mysql.pid
    open_files_limit = 8192

    #[mysqldump]
    #quick
    #max_allowed_packet = 16M

    [mysql]
    no-auto-rehash

    [isamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M

    [myisamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M

    [mysqlhotcopy]
    interactive-timeout



    Quote:

    mysqlreport

    __ Key __________________________________________________ __________ _____
    Buffer used 96.63M of 128.00M %Used: 75.50
    Current 114.92M %Usage: 89.78
    Write hit 54.80%
    Read hit 99.87%

    __ Questions __________________________________________________ _________
    Total 29.04M 428.8/s
    DMS 11.28M 166.5/s %Total: 38.82
    Com_ 11.15M 164.7/s 38.40
    QC Hits 3.97M 58.6/s 13.66
    COM_QUIT 2.65M 39.1/s 9.12
    -Unknown 3.59k 0.1/s 0.01
    Slow 1 s 4.00k 0.1/s 0.01 %DMS: 0.04 Log: OFF
    DMS 11.28M 166.5/s 38.82
    SELECT 9.93M 146.6/s 34.20 88.08
    INSERT 1.22M 18.0/s 4.19 10.79
    UPDATE 108.28k 1.6/s 0.37 0.96
    DELETE 15.42k 0.2/s 0.05 0.14
    REPLACE 3.94k 0.1/s 0.01 0.03
    Com_ 11.15M 164.7/s 38.40
    set_option 4.27M 63.1/s 14.72
    change_db 2.65M 39.1/s 9.12
    begin 2.60M 38.3/s 8.94

    __ SELECT and Sort __________________________________________________ ___
    Scan 78.21k 1.2/s %SELECT: 0.79
    Range 1.96M 28.9/s 19.69
    Full join 1.30k 0.0/s 0.01
    Range check 0 0/s 0.00
    Full rng join 19 0.0/s 0.00
    Sort scan 45.10k 0.7/s
    Sort range 47.62k 0.7/s
    Sort mrg pass 6 0.0/s

    __ Query Cache __________________________________________________ _______
    Memory usage 52.94M of 64.00M %Used: 82.72
    Block Fragmnt 8.89%
    Hits 3.97M 58.6/s
    Inserts 9.51M 140.4/s
    Insrt:Prune 7.93:1 122.7/s
    Hit:Insert 0.42:1

    __ Table Locks __________________________________________________ _______
    Waited 246 0.0/s %Total: 0.00
    Immediate 14.20M 209.7/s

    __ Tables __________________________________________________ __________ __
    Open 1024 of 1024 %Cache: 100.00
    Opened 6.86k 0.1/s

    __ Connections __________________________________________________ _______
    Max used 405 of 500 %Max: 81.00
    Total 2.65M 39.1/s

    __ Created Temp __________________________________________________ ______
    Disk table 16.63k 0.2/s
    Table 49.73k 0.7/s Size: 128.0M
    File 17 0.0/s

    __ Threads __________________________________________________ __________ _
    Running 3 of 7
    Cached 121 of 128 %Hit: 99.98
    Created 405 0.0/s
    Slow 11 0.0/s

    __ Aborted __________________________________________________ __________ _
    Clients 754 0.0/s
    Connects 1.55k 0.0/s

    __ Bytes __________________________________________________ __________ ___
    Sent 25.75G 380.2k/s
    Received 5.00G 73.8k/s

    __ InnoDB Buffer Pool __________________________________________________
    Usage 1.00G of 1.00G %Used: 100.00
    Read hit 99.89%
    Pages
    Free 0 %Total: 0.00
    Data 61.18k 93.35 %Drty: 0.42
    Misc 4356 6.65
    Latched 1 0.00
    Reads 215.45M 3.2k/s
    From file 242.12k 3.6/s 0.11
    Ahead Rnd 6677 0.1/s
    Ahead Sql 472 0.0/s
    Writes 24.87M 367.2/s
    Flushes 512.00k 7.6/s
    Wait Free 0 0/s

    __ InnoDB Lock __________________________________________________ _______
    Waits 29 0.0/s
    Current 0
    Time acquiring
    Total 52818 ms
    Average 1821 ms
    Max 51005 ms

    __ InnoDB Data, Pages, Rows ____________________________________________
    Data
    Reads 277.29k 4.1/s
    Writes 1.46M 21.5/s
    fsync 1.04M 15.3/s
    Pending
    Reads 0
    Writes 0
    fsync 0

    Pages
    Created 21.70k 0.3/s
    Read 597.76k 8.8/s
    Written 512.00k 7.6/s

    Rows
    Deleted 845.75k 12.5/s
    Inserted 987.67k 14.6/s
    Read 52.30M 772.1/s
    Updated 259.29k 3.8/s



    Any assistance or advice would be greatly appreciated, thanks.

  • #2
    Quote:

    Any assistance or advice would be greatly appreciated, thanks.


    Well,.. its not easy to answer such an unspecific question... Do you have actual performance problems, or do you just want to increase general page load time?


    Quote:


    __ Query Cache __________________________________________________ _______
    Memory usage 52.94M of 64.00M %Used: 82.72
    Block Fragmnt 8.89%
    Hits 3.97M 58.6/s
    Inserts 9.51M 140.4/s
    Insrt:Prune 7.93:1 122.7/s
    Hit:Insert 0.42:1


    That is not to much... a good ratio would be n:1 where n is much bigger than 1... In your case only 42% of the cached statements are reused.. Try increasing the cache size,...


    Quote:

    __ Threads __________________________________________________ __________ _
    Running 3 of 7
    Cached 121 of 128 %Hit: 99.98
    Created 405 0.0/s
    Slow 11 0.0/s


    That is a big cache... I don't know your Data Access pattern... but the cache is used to buffer the recreation of new threads...
    If your typical client access pattern is not to create dozens of new connections, without releasing some in the meantime, you can decrease this cache... But this would be very uncommon for a web application... A "normal" pattern would me more like: 3 new, 1 release, 2 new, 2 release, 2 new, 1 release, 1 new, 2 release....
    So you need only your cache to smooth your fluctuations... About 8 threads.. or maybe up to 16...


    Quote:

    innodb_buffer_pool_size = 1024M

    For a machine with 8GB RAM, this is not to much... and since you have more Data than that (Pool 100% used) you could try to increase your buffer_pool to gain some performance...



    For any other advice, I do need more information...

    Comment


    • #3
      If you find your system is getting slow, have you tried running PHP with APC compiled in? It'll make a massive difference, if you configure things properly.

      Comment

      Working...
      X