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.

MySQL Optimization Help needed

Discussion in 'Workarounds and Optimization' started by lfait, Nov 14, 2013.

  1. lfait

    lfait Member

    Joined:
    Nov 14, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider
    Hello,
    Dear I'm facing an issue with MYSQL optimization .

    I'm running an SQL Server to process small queries

    Server is
    24 Proc / 24 GB Ram / NAS + Raid 10 / SSD
    Server connections each 3 mins get to 2000 connection currently and will increase very soon.
    MYSQL is closing connections perfectly

    load average: 62.41, 50.75, 43.41
    Free-m


    The queries i run are small
    for example

    update number x - ( sent-not sent )
    that is it, the queries won't take more than 64K tops


    I'm trying to switch the load from CPU to Ram
    My current configuration are


    I'm looking to maximize the connections to get more than 10K each 3 mins without facing such loads .
    my tables are in InnoDB it's only 2 tables .


    Any help is really needed here .


    Regards
     
    #1 lfait, Nov 14, 2013
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    37,204
    Likes Received:
    1,297
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    #2 cPanelMichael, Nov 14, 2013
  3. lfait

    lfait Member

    Joined:
    Nov 14, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider
    I already have it :) sorry i didn't add it before.

    I know i have max memory, but it's not using the memory and i do have much free memory .


    Regards
     
    #3 lfait, Nov 14, 2013
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    37,204
    Likes Received:
    1,297
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Please ensure you let MySQL run for at least 24 hours before using the tuner script to ensure accurate results.

    Thank you.
     
    #4 cPanelMichael, Nov 14, 2013
  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Adjust those:


    table_cache = 1 - remove it
    net_buffer_length = 4096 - remove it

    add
    max_allowed_packet = 20M
    myisam_use_mmap=1

    adjust:

    tmp_table_size = 50M
    max_heap_table_size = 50M

    thread_cache_size = 1500 # thats the single value that would have the highest importance in your case (since many connections/threads)

    table_open_cache = 500 # you have 31 MyISAM tables, so dont set it too high like 102400, since this value doesn't scale well in MySQL 5.5, 500 is more than enough in your case

    read_rnd_buffer_size = 2M

    # all those settigs are too high, very important to set it much lower, even if cache prunes
    query_cache_limit = 1M
    query_cache_size = 50M
    query_cache_type = 1

    adjust
    innodb_log_buffer_size= 500M
    add
    innodb_log_file_size = 1024M (you would need to remove /var/lib/mysql/ib_logfile* before restart to apply this setting)


    # comment oout or remove all of that below
    query_prealloc_size = 1M
    query_alloc_block_size = 1M
    range_alloc_block_size = 4096
    transaction_alloc_block_size = 1024
    transaction_prealloc_size = 1024



    To handle more connections increase
    max_connections = 3000
    max_user_connections= 2850


    then restart, you can also clear slow log before restart
    The best would be to gather review of your current log, before restart with

    cd /root
    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest
    ./pt-query-digest /var/log/mysql/mysql-slow.log > slow_before.log
    later you can post slow_before.log contents for review

    then remove old slow log
    rm -rf /var/log/mysql/mysql-slow.log

    and restart here

    after a while generate slow log review from new slow log

    btw. Does your queries that runs during that xxxx conenctions work on MyISAM or InnoDB tables, those are mostly updates there ?

    please also run command mount, and post the result here
    your mysql is on those SSD or RAID 10 (7200 or 15000 rpm) ?
     
    #5 thinkbot, Nov 14, 2013
    Last edited: Nov 14, 2013
  6. lfait

    lfait Member

    Joined:
    Nov 14, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider
    Edits has been made, waiting 24 hours to update the post.

    my NAS server is Raid10 SSD so i believe it's 15K RPM, my NAS load is Perfect so i doubt it's related to the NAS .

    Regards
     
    #6 lfait, Nov 16, 2013
  7. lfait

    lfait Member

    Joined:
    Nov 14, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider
    Code:
    -------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4M (Tables: 31)
[--] Data in InnoDB tables: 293M (Tables: 71)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 15

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 8h 12m 14s (5M q [18.436 qps], 795K conn, TX: 2B, RX: 369M)
[--] Reads / Writes: 44% / 56%
[--] Total buffers: 5.1G global + 2.9M per thread (2500 max threads)
[OK] Maximum possible memory usage: 12.2G (51% of installed RAM)
[OK] Slow queries: 0% (5/5M)
[OK] Highest usage of available connections: 7% (177/2500)
[OK] Key buffer size / total MyISAM indexes: 1.0G/1.8M
[OK] Key buffer hit rate: 100.0% (117K cached / 58 reads)
[OK] Query cache efficiency: 21.0% (391K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 323 sorts)
[OK] Temporary tables created on disk: 21% (566 on disk / 2K total)
[OK] Thread cache hit rate: 99% (177 created / 795K connections)
[OK] Table cache hit rate: 89% (154 open / 172 opened)
[OK] Open file limit used: 0% (112/12K)
[OK] Table locks acquired immediately: 100% (3M immediate / 3M locks)
[OK] InnoDB data size / buffer pool: 293.2M/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    This is the current output .

    Thanks for helping, now 1 tiny question more, regarding to fragmented tables, i expect tables to be update frequently in each 5 minutes and for sure it will get fragmented any advice's ?

    Regards
     
    #7 lfait, Nov 19, 2013
  8. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    dont worry about fragmentation, it has almost no impact in your case
    you will have info about fragmented tables from mysqltuner most of the time when you run it, wherever you run it :)


    results are very nice, what load did you have while running your scripts ?

    and you can further decrease read_rnd_buffer_size = 2M
    to 256K

    read_rnd_buffer_size = 256K
     
    #8 thinkbot, Nov 20, 2013
(You must log in or sign up to post here.)
Loading...
Similar Threads - MySQL Optimization Help
  1. SpaceCowboy

    MysqlTunner optimization help

    SpaceCowboy, Nov 12, 2016, in forum: Workarounds and Optimization
    Replies:
    4
    Views:
    608
    cPanelMichael
    Nov 14, 2016
  2. Kurogane

    MySQL Optimization

    Kurogane, Oct 10, 2016, in forum: Workarounds and Optimization
    Replies:
    5
    Views:
    1,039
    cPanelMichael
    Oct 11, 2016
  3. 3awh

    Optimization 10.1.17-MariaDB with MySQLTuner 1.6.18

    3awh, Sep 12, 2016, in forum: Workarounds and Optimization
    Replies:
    5
    Views:
    916
    cPanelMichael
    Sep 22, 2016
  4. Anthony Parsons

    mariadb optimization - mysqltuner

    Anthony Parsons, Sep 7, 2016, in forum: Workarounds and Optimization
    Replies:
    6
    Views:
    1,258
    WebHostPro
    Jan 26, 2017
  5. zlati

    MySQL Optimization needed

    zlati, Jan 31, 2016, in forum: Workarounds and Optimization
    Replies:
    2
    Views:
    868
    cPanelMichael
    Feb 1, 2016

Share This Page