The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

new server... slow mysql

Discussion in 'Workarounds and Optimization' started by wgalafassijr, Oct 15, 2010.

  1. wgalafassijr

    wgalafassijr Active Member

    Joined:
    Jun 23, 2005
    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    1
    Hello,
    Recently i've migrated some of my customers to a new server:
    2x Intel Xeon 5520 2.26
    12GB RAM
    300GB 15K RPM HDD

    I have some problems when i run a php script that import 2 files:
    1. 700kb xml file
    2. 8mb xml file

    The files is to sync product to a customer online store.
    When some of this 2 scripts the mysq process uses 100% or more cpu and the load increase to 3 or more and the server goes very low.

    my /etc/my.cnf
    [client]
    port = 3306
    socket = /var/lib/mysql/mysql.sock

    [mysqld]
    port = 3306
    socket = /var/lib/mysql/mysql.sock
    skip-locking
    key_buffer = 384M
    max_allowed_packet = 1M
    table_cache = 5096
    sort_buffer_size = 2M
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size = 128M
    thread_concurrency = 8
    max_connections = 400
    local-infile=0

    skip-federated

    server-id = 1

    innodb_buffer_pool_size = 384M
    join_buffer_size = 64M
    key_buffer_size = 900M
    log-slow-queries=/var/lib/mysql/slow.log
    log-queries-not-using-indexes
    [mysqldump]
    quick
    max_allowed_packet = 16M

    [mysql]
    no-auto-rehash

    [isamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M

    [myisamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M

    [mysqlhotcopy]
    interactive-timeout


    After running mysqltuner.pl i have:

    >> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at MySQLTuner
    >> Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [^[[0;34m--^[[0m] Skipped version check for MySQLTuner script
    [^[[0;32mOK^[[0m] Currently running supported MySQL version 5.0.91-community-log
    [^[[0;32mOK^[[0m] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [^[[0;34m--^[[0m] Status: ^[[0;32m+Archive ^[[0m^[[0;31m-BDB ^[[0m^[[0;31m-Federated ^[[0m^[[0;32m+InnoDB ^[[0m^[[0;31m-ISAM ^[[0m^[[0;31m-NDBCluster ^[[0m
    [^[[0;34m--^[[0m] Data in MyISAM tables: 1G (Tables: 2544)
    [^[[0;34m--^[[0m] Data in InnoDB tables: 203M (Tables: 849)
    [^[[0;34m--^[[0m] Data in MEMORY tables: 0B (Tables: 17)
    [^[[0;31m!!^[[0m] Total fragmented tables: 41

    -------- Performance Metrics -------------------------------------------------
    [^[[0;34m--^[[0m] Up for: 20m 8s (383K q [317.483 qps], 3K conn, TX: 2B, RX: 38M)
    [^[[0;34m--^[[0m] Reads / Writes: 73% / 27%
    [^[[0;34m--^[[0m] Total buffers: 1.4G global + 76.2M per thread (400 max threads)
    [^[[0;31m!!^[[0m] Maximum possible memory usage: 31.2G (265% of installed RAM)
    [^[[0;31m!!^[[0m] Slow queries: 7% (28K/383K)
    [^[[0;32mOK^[[0m] Highest usage of available connections: 2% (11/400)
    [^[[0;32mOK^[[0m] Key buffer size / total MyISAM indexes: 900.0M/619.1M
    [^[[0;32mOK^[[0m] Key buffer hit rate: 99.3% (2M cached / 18K reads)
    [^[[0;32mOK^[[0m] Query cache efficiency: 69.9% (191K cached / 273K selects)
    [^[[0;32mOK^[[0m] Query cache prunes per day: 0
    [^[[0;32mOK^[[0m] Sorts requiring temporary tables: 0% (21 temp sorts / 16K sorts)
    [^[[0;31m!!^[[0m] Joins performed without indexes: 1536
    [^[[0;32mOK^[[0m] Temporary tables created on disk: 9% (147 on disk / 1K total)
    [^[[0;32mOK^[[0m] Thread cache hit rate: 99% (12 created / 3K connections)
    [^[[0;32mOK^[[0m] Table cache hit rate: 99% (3K open / 3K opened)
    [^[[0;32mOK^[[0m] Open file limit used: 48% (5K/10K)
    [^[[0;32mOK^[[0m] Table locks acquired immediately: 99% (127K immediate / 128K locks)
    [^[[0;32mOK^[[0m] InnoDB data size / buffer pool: 203.3M/384.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Adjust your join queries to always utilize indexes
    Variables to adjust:
    *** MySQL's maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 64.0M, or always use indexes with joins)


    How to optimize my mysql installation? Some idea?

    Thanks,
    Wilson
     
  2. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello,

    First of all, remove things that aren't needed in the /etc/my.cnf or don't actually exist on Linux. Reduce those using too much memory. Here's some suggestions:

    [client]
    #port = 3306 (already the default, no need for it)
    #socket = /var/lib/mysql/mysql.sock (already the default, no need for it)

    [mysqld]
    #port = 3306 (already the default, no need for it)
    #socket = /var/lib/mysql/mysql.sock (already the default, no need for it)
    #skip-locking (already the default, no need for it)
    key_buffer_size = 384M (this is far too high, reduce it to 80-120M to start)
    max_allowed_packet = 1M
    table_cache = 5096
    #sort_buffer_size = 2M (commenting it out to take it out of memory consumption)
    #read_buffer_size = 2M (commenting it out to take it out of memory consumption)
    #read_rnd_buffer_size = 8M (commenting out to take it out of memory consumption)
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size = 128M (possibly too high)
    #thread_concurrency = 8 (doesn't exist on Linux, only on Solaris)
    max_connections = 400
    local-infile=0
    skip-federated
    server-id = 1
    innodb_buffer_pool_size = 384M (this is too high, remove or reduce it)
    join_buffer_size = 64M
    #key_buffer_size = 900M (you already have key_buffer_size previously, key_buffer doesn't exist and it's called key_buffer_size, 900M is ridiculously high)
    log-slow-queries=/var/lib/mysql/slow.log
    log-queries-not-using-indexes

    [mysqldump]
    #quick (already the default for mysqldump)
    max_allowed_packet = 16M

    [mysql]
    no-auto-rehash

    [isamchk]
    key_buffer_size = 256M
    sort_buffer_size = 256M
    read_buffer_size = 2M
    write_buffer_size = 2M

    [myisamchk]
    key_buffer_size = 256M
    sort_buffer_size = 256M
    read_buffer_size = 2M
    write_buffer_size = 2M

    [mysqlhotcopy]
    interactive-timeout

    Thanks.
     
Loading...

Share This Page