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 /httpd tuning

Discussion in 'Workarounds and Optimization' started by sc0t, May 14, 2013.

  1. sc0t

    sc0t Member

    Joined:
    May 14, 2013
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hello people,

    I'm running an adserver (too much traffic to show banners ) on my server with this specifications:
    Intel(R) Xeon(R) CPU E5450 @ 3.00GHz, 8 cores
    32GB RAM
    1gbit

    I'm always getting unable to connect to database error when traffic go high, also the cpu is almost 700% used !

    Here is my.cnf:
    Code:
    [mysqld]
    default-storage-engine = myisam
    innodb=OFF
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    symbolic-links=0
    user=mysql
    ;plugin-load=rpl_semi_sync_master=semisync_master.so
    ;plugin-load=rpl_semi_sync_slave=semisync_slave.so
    ;rpl_semi_sync_master_enabled=1
    ;rpl_semi_sync_master_timeout=10
    ;rpl_semi_sync_slave_enabled=1
    ;performance_schema
    local-infile=0
    skip-networking 
    
    
    max_connections = 1700
    key_buffer_size = 256M
    myisam_sort_buffer_size = 200M
    join_buffer_size = 16M
    tmp_table_size = 32M
    max_heap_table_size = 32M
    read_buffer_size = 2M
    sort_buffer_size = 2M
    thread_cache_size = 64
    wait_timeout = 1800
    connect_timeout = 10
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 16M
    query_cache_size = 128M
    query_cache_type = 1
    table_cache = 50000
    log-slow-queries=/var/lib/mysql/slow.log
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
    
    and here is mysqltuner.pl

    Code:
     ./mysqltuner.pl
    
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    Please enter your MySQL administrative login: root
    Please enter your MySQL administrative password:
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.31-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 126M (Tables: 386)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 25
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1m 51s (60K q [542.252 qps], 5K conn, TX: 33M, RX: 13M)
    [--] Reads / Writes: 35% / 65%
    [--] Total buffers: 416.0M global + 20.5M per thread (1700 max threads)
    [!!] Maximum possible memory usage: 34.4G (109% of installed RAM)
    [OK] Slow queries: 0% (0/60K)
    [OK] Highest usage of available connections: 12% (220/1700)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/42.1M
    [OK] Key buffer hit rate: 100.0% (31M cached / 780 reads)
    [OK] Query cache efficiency: 45.6% (8K cached / 18K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3K sorts)
    [!!] Joins performed without indexes: 2754
    [!!] Temporary tables created on disk: 49% (3K on disk / 6K total)
    [OK] Thread cache hit rate: 93% (366 created / 5K connections)
    [OK] Table cache hit rate: 99% (1K open / 1K opened)
    [OK] Open file limit used: 1% (1K/101K)
    [!!] Table locks acquired immediately: 64%
    
    -------- 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
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Optimize queries and/or use InnoDB to reduce lock wait
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        join_buffer_size (> 16.0M, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 32M)
    
    and finally my httpd.conf: ( I don't think that the problem comes from apache)

    Code:
    <IfModule prefork.c>
    StartServers       1000
    MinSpareServers    1000
    MaxSpareServers   2000
    ServerLimit      4000
    MaxClients       4000
    MaxRequestsPerChild  4000
    </IfModule>
    Thank you for your help
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    First thing, running such site on Apache prefork, is not the most efficient thing to do in terms of RAM memory.
    Setup of nginx + php-fpm would be better


    Anyway, for this cpanel + apache setup:
    Is it only to serve dynamically generated banners ?

    Please run tuning primer and mysqlreport also, so we can see how many threads are running at once
    You would probably need to install some MySQL fork with better scallability when there is more threads
    Suggested would be Percona MySQL

    MySQL:
    Lower sort_buffer_size to 256K from 2M (yes, make it lower)
    Lower join_buffer_size from 16M to 8M

    Now, thread_cache_size, you got 64
    But when you have 1000 started apache processes, and 1000 concurrent connections, you got 1000 threads
    So please publish mysqlreport info, and we will need to increase thread_cache_size value

    Lower connect_timeout = 10 to 1s or 2s
    Lower table_cache = 50000 to 1000
    Lower long_query_time to 0.1s, so it will track slow queries that takes longer than 0.1
    Set log-queries-not-using-indexes to track queries without indexes


    About mysqltuner.pl
    your mysql was running only for "Up for: 1m 51s ", thats to less to give accurate info


    Few more things:
    If your server is serving only dynamically generated banners, and each request requires mysql connection, than it would be better to use persistent mysql connections in your code,

    How many connections you got at once ?
    max_connections = 1700 ?

    You setup ServersLimit of apache prefork to 4000, I doubt that you will fit all those servers to 32GB memory, so it will be swapping, means going dead.
    But when your servers count will be higher than 1700 (mysql conn limit), they would be useless anyways, couse they wont be able to connect to mysql

    So as you see, there is a lot more to get this server optimized for your use.

    Anyway, first run mysqlreport and tuning primer, than update the mysql values as I wrote, then let mysql run for some time, and run mysqltuner, mysqlreport, and tuning primer again

    Regards

    Edit:
    One more thing, since probably each request of banner, makes some UPDATE query in database, and since you got such concurrency, you would most likely be much much happier with InnoDB than MyISAM in your use. Anyway, all the variables above as I said, needs to be corrected
     
    #2 thinkbot, May 14, 2013
    Last edited: May 14, 2013
  3. sc0t

    sc0t Member

    Joined:
    May 14, 2013
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi again,

    Thank you for your quick reply.

    here we go again:
    my.cnf:
    Code:
    [mysqld]
    default-storage-engine = myisam
    innodb=OFF
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    symbolic-links=0
    user=mysql
    ;plugin-load=rpl_semi_sync_master=semisync_master.so
    ;plugin-load=rpl_semi_sync_slave=semisync_slave.so
    ;rpl_semi_sync_master_enabled=1
    ;rpl_semi_sync_master_timeout=10
    ;rpl_semi_sync_slave_enabled=1
    ;performance_schema
    local-infile=0
    skip-networking 
    
    max_connections = 2000
    key_buffer_size = 256M
    myisam_sort_buffer_size = 200M
    join_buffer_size = 8M
    tmp_table_size = 32M
    max_heap_table_size = 32M
    read_buffer_size = 6M
    sort_buffer_size = 256K
    thread_cache_size = 1000
    wait_timeout = 4
    connect_timeout = 2
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 16M
    query_cache_size = 128M
    query_cache_type = 1
    table_cache = 1000
    long_query_time = 0.1
    slow-query-log=1
    log_queries_not_using_indexes =1
    slow-query-log-file=/home/test/mysql.log
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    mysqltuner.pl
    Code:
     ./mysqltuner.pl
    
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    Please enter your MySQL administrative login: root
    Please enter your MySQL administrative password:
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.31-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 130M (Tables: 386)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 31
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 51m 28s (3M q [502.979 qps], 322K conn, TX: 1B, RX: 733M)
    [--] Reads / Writes: 34% / 66%
    [--] Total buffers: 416.0M global + 14.8M per thread (2000 max threads)
    [!!] Maximum possible memory usage: 29.2G (92% of installed RAM)
    [!!] Slow queries: 21% (738K/3M)
    [OK] Highest usage of available connections: 27% (557/2000)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/42.5M
    [OK] Key buffer hit rate: 100.0% (1B cached / 16K reads)
    [OK] Query cache efficiency: 42.7% (423K cached / 991K selects)
    [!!] Query cache prunes per day: 45912
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 196K sorts)
    [!!] Joins performed without indexes: 162764
    [!!] Temporary tables created on disk: 49% (190K on disk / 380K total)
    [OK] Thread cache hit rate: 99% (557 created / 322K connections)
    [!!] Table cache hit rate: 3% (1K open / 30K opened)
    [OK] Open file limit used: 12% (1K/10K)
    [!!] Table locks acquired immediately: 75%
    
    -------- 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
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
        Optimize queries and/or use InnoDB to reduce lock wait
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 128M)
        join_buffer_size (> 8.0M, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 32M)
        table_cache (> 1000)
    
    mysqlreport
    Code:
    Use of uninitialized value in subtraction (-) at ./mysqlreport line 648, <STDIN> line 1.
    Use of uninitialized value in subtraction (-) at ./mysqlreport line 648, <STDIN> line 1.
    Use of uninitialized value in multiplication (*) at ./mysqlreport line 648, <STDIN> line 1.
    Use of uninitialized value in multiplication (*) at ./mysqlreport line 652, <STDIN> line 1.
    Use of uninitialized value in multiplication (*) at ./mysqlreport line 652, <STDIN> line 1.
    MySQL 5.5.31-log         uptime 0 1:52:29       Tue May 14 22:21:20 2013
    
    __ Key _________________________________________________________________
    Buffer used     2.17M of 256.00M  %Used:   0.85
      Current      48.68M            %Usage:  19.02
    Write hit      81.90%
    Read hit      100.00%
    
    __ Questions ___________________________________________________________
    Total           3.39M   502.8/s
      DMS           1.64M   242.6/s  %Total:  48.25
      Com_        984.60k   145.9/s           29.02
      QC Hits     426.56k    63.2/s           12.57
      COM_QUIT    325.46k    48.2/s            9.59
      +Unknown     19.36k     2.9/s            0.57
    Slow 100 ms   744.47k   110.3/s           21.94  %DMS:  45.47  Log:  ON
    DMS             1.64M   242.6/s           48.25
      UPDATE      610.59k    90.5/s           17.99         37.29
      SELECT      572.59k    84.8/s           16.87         34.97
      DELETE      276.72k    41.0/s            8.15         16.90
      INSERT      177.41k    26.3/s            5.23         10.84
      REPLACE           0       0/s            0.00          0.00
    Com_          984.60k   145.9/s           29.02
      set_option  644.60k    95.5/s           19.00
      change_db   339.99k    50.4/s           10.02
      show_variab       7     0.0/s            0.00
    
    __ SELECT and Sort _____________________________________________________
    Scan           99.04k    14.7/s %SELECT:  17.30
    Range         153.92k    22.8/s           26.88
    Full join     164.26k    24.3/s           28.69
    Range check         0       0/s            0.00
    Full rng join       0       0/s            0.00
    Sort scan     197.84k    29.3/s
    Sort range         85     0.0/s
    Sort mrg pass       0       0/s
    
    __ Query Cache _________________________________________________________
    Memory usage  127.25M of 128.00M  %Used:  99.41
    Block Fragmnt   0.15%
    Hits          426.56k    63.2/s
    Inserts       373.10k    55.3/s
    Insrt:Prune   98.26:1    54.7/s
    Hit:Insert     1.14:1
    
    __ Table Locks _________________________________________________________
    Waited        542.77k    80.4/s  %Total:  24.71
    Immediate       1.65M   245.1/s
    
    __ Tables ______________________________________________________________
    Open             1000 of 1000    %Cache: 100.00
    Opened         30.91k     4.6/s
    
    __ Connections _________________________________________________________
    Max used          557 of 2000      %Max:  27.85
    Total         325.46k    48.2/s
    
    __ Created Temp ________________________________________________________
    Disk table    191.73k    28.4/s
    Table         191.81k    28.4/s    Size:  32.0M
    File                0       0/s
    
    __ Threads _____________________________________________________________
    Running             6 of    6
    Cached            551 of 1000      %Hit:  99.83
    Created           557     0.1/s
    Slow                0       0/s
    
    __ Aborted _____________________________________________________________
    Clients             0       0/s
    Connects           33     0.0/s
    
    __ Bytes _______________________________________________________________
    Sent            1.84G  273.2k/s
    Received      740.20M  109.7k/s
    
    

    Please notice, That I paused 50% of the traffic to let the server work normally or it will go down.

    Thanks
     
  4. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Please run mysqltuner.pl and mysqlreport again and save the results before doing next steps ( so we have the results when mysql works for some longer than about 2h as previous result )


    I see you even increased read_buffer to 6 MB :(
    Please set read_buffer_size to 128K

    sort and read buffers should be kept low in most (95%) situations, the rest needs precise benchmarks to valuate if needed more or less
    Setting sort or read buffer to high might couse worse results than small buffers

    I would also suggest lowering query_cache_size = 128M to 50M
    and
    max_connections = 2000 to 1000 for now

    In mysql uptime from mysqltuner (1h 51m 28s) you had max 557 connections used
    But
    __ Threads _____________________________________________________________
    Running 6 of 6

    Usually running much less, so when they reached 557 they might stuck on something (slow query, table lock)

    Becouse of:
    [--] Reads / Writes: 34% / 66%
    [!!] Joins performed without indexes: 162764
    [!!] Temporary tables created on disk: 49% (190K on disk / 380K total)
    [!!] Table locks acquired immediately: 75%
    __ Created Temp ________________________________________________________
    Disk table 191.73k 28.4/s
    Table 191.81k 28.4/s Size: 32.0M

    Waited 542.77k 80.4/s %Total: 24.71

    It's obvious that you got a lot of table locking in MyiSAM table where update/insert queries are executed
    And read join queries are not using indexes, thus temporary files are created, which extremely slows down the execution of the query

    Please download
    wget percona.com/get/pt-query-digest
    chmod +x pt-query-digest

    and run
    ./pt-query-digest /home/test/mysql.log > slow.txt
    and copy the result of slow.txt here

    after that we can tell what to do next,
    but for sure it would be:
    1. some indexes needs to be added (checked)
    2. some queries might need rewrte
    3. move from MyISAM to InnoDB (since many update queries)

    Regards
     
  5. sc0t

    sc0t Member

    Joined:
    May 14, 2013
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi,

    This is reports before the new changes;

    mysqltuner
    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.31-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 131M (Tables: 386)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 28
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 11h 51m 12s (18M q [438.408 qps], 1M conn, TX: 8B, RX: 4B)
    [--] Reads / Writes: 30% / 70%
    [--] Total buffers: 416.0M global + 14.8M per thread (2000 max threads)
    [!!] Maximum possible memory usage: 29.2G (92% of installed RAM)
    [!!] Slow queries: 20% (3M/18M)
    [OK] Highest usage of available connections: 70% (1402/2000)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/42.9M
    [OK] Key buffer hit rate: 100.0% (11B cached / 548K reads)
    [OK] Query cache efficiency: 37.7% (1M cached / 4M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (4 temp sorts / 1M sorts)
    [!!] Joins performed without indexes: 1036277
    [!!] Temporary tables created on disk: 49% (1M on disk / 2M total)
    [OK] Thread cache hit rate: 99% (2K created / 1M connections)
    [!!] Table cache hit rate: 0% (1K open / 1M opened)
    [OK] Open file limit used: 12% (1K/10K)
    [!!] Table locks acquired immediately: 66%
    
    -------- 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
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
        Optimize queries and/or use InnoDB to reduce lock wait
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        join_buffer_size (> 8.0M, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 32M)
        table_cache (> 1000)
    

    mysqlreports
    Code:
    MySQL 5.5.31-log         uptime 0 11:53:0       Wed May 15 21:37:13 2013
    
    __ Key _________________________________________________________________
    Buffer used     2.14M of 256.00M  %Used:   0.84
      Current      48.78M            %Usage:  19.06
    Write hit      85.54%
    Read hit      100.00%
    
    __ Questions ___________________________________________________________
    Total          18.76M   438.4/s
      DMS           9.05M   211.6/s  %Total:  48.27
      Com_          5.91M   138.2/s           31.53
      COM_QUIT      1.97M    46.1/s           10.51
      QC Hits       1.65M    38.6/s            8.81
      +Unknown    166.39k     3.9/s            0.89
    Slow 500 ms     3.80M    88.8/s           20.26  %DMS:  41.96  Log:  ON
    DMS             9.05M   211.6/s           48.27
      UPDATE        3.80M    88.9/s           20.27         41.99
      SELECT        2.73M    63.8/s           14.55         30.14
      DELETE        1.35M    31.5/s            7.18         14.87
      INSERT        1.18M    27.5/s            6.28         13.00
      REPLACE           0       0/s            0.00          0.00
    Com_            5.91M   138.2/s           31.53
      set_option    3.93M    92.0/s           20.97
      change_db     1.98M    46.3/s           10.55
      show_plugin      14     0.0/s            0.00
    
    __ SELECT and Sort _____________________________________________________
    Scan           88.20k     2.1/s %SELECT:   3.23
    Range         987.31k    23.1/s           36.18
    Full join       1.04M    24.3/s           38.07
    Range check         0       0/s            0.00
    Full rng join       0       0/s            0.00
    Sort scan       1.07M    25.1/s
    Sort range        638     0.0/s
    Sort mrg pass       4     0.0/s
    
    __ Query Cache _________________________________________________________
    Memory usage  109.57M of 128.00M  %Used:  85.60
    Block Fragmnt   0.10%
    Hits            1.65M    38.6/s
    Inserts         1.63M    38.1/s
    Insrt:Prune   1.63M:1    38.1/s
    Hit:Insert     1.01:1
    
    __ Table Locks _________________________________________________________
    Waited          4.14M    96.8/s  %Total:  33.32
    Immediate       8.28M   193.6/s
    
    __ Tables ______________________________________________________________
    Open             1000 of 1000    %Cache: 100.00
    Opened          1.84M    42.9/s
    
    __ Connections _________________________________________________________
    Max used         1402 of 2000      %Max:  70.10
    Total           1.97M    46.1/s
    
    __ Created Temp ________________________________________________________
    Disk table      1.07M    25.0/s
    Table           1.07M    25.0/s    Size:  32.0M
    File                8     0.0/s
    
    __ Threads _____________________________________________________________
    Running             9 of    9
    Cached            991 of 1000      %Hit:  99.88
    Created         2.41k     0.1/s
    Slow               67     0.0/s
    
    __ Aborted _____________________________________________________________
    Clients            69     0.0/s
    Connects          223     0.0/s
    
    __ Bytes _______________________________________________________________
    Sent            8.25G  192.8k/s
    Received        4.44G  103.9k/s
    
    as for mysql.log it was a huge file , that's why I made a recent one then I ran digest :

    Code:
    # Overall: 79.18k total, 59 unique, 71.66 QPS, 4.38x concurrency _________
    # Time range: 2013-05-15 21:41:32 to 21:59:57
    # Attribute          total     min     max     avg     95%  stddev  median
    # ============     ======= ======= ======= ======= ======= ======= =======
    # Exec time          4842s    39us      1s    61ms   253ms   110ms     9ms
    # Lock time          1559s    13us   785ms    20ms    95ms    45ms   167us
    # Rows sent         32.65k       0   1.47k    0.42    0.99    9.26       0
    # Rows examine      53.41M       0  84.62k  707.37   2.38k   1.81k   56.92
    # Query size        34.93M      22   1.32k  462.52   1.26k  556.57  107.34
    
    # Profile
    # Rank Query ID           Response time   Calls R/Call V/M   Item
    # ==== ================== =============== ===== ====== ===== =============
    #    1 0x5DD4D98ED12F60DA 3721.0863 76.8% 23017 0.1617  0.15 SELECT xa_ads xa_c xa_ads_ip
    #    2 0x1B1620F55D56834C  664.2357 13.7% 22714 0.0292  0.07 UPDATE xa_c
    #    3 0x4317A1B02C705FCA  156.9024  3.2% 19156 0.0082  0.00 DELETE xa_ads_show
    #    4 0x803FB8BAEB9524B3  150.3412  3.1%   853 0.1762  0.13 SELECT xa_ads xa_c xa_ads_ip
    # MISC 0xMISC              149.5135  3.1% 13440 0.0111   0.0 <55 ITEMS>
    
    # Query 1: 20.83 QPS, 3.37x concurrency, ID 0x5DD4D98ED12F60DA at byte 45248460
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.15
    # Time range: 2013-05-15 21:41:32 to 21:59:57
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         29   23017
    # Exec time     76   3721s   763us      1s   162ms   477ms   153ms   105ms
    # Lock time     46    726s   111us   785ms    32ms   148ms    59ms   214us
    # Rows sent     65  21.35k       0       1    0.95    0.99    0.22    0.99
    # Rows examine   2   1.46M       0     126   66.49   80.10   14.35   69.19
    # Query size    83  29.25M   1.29k   1.30k   1.30k   1.26k       0   1.26k
    # String:
    # Databases    db
    # Hosts        localhost
    # Users        user
    # Query_time distribution
    #   1us
    #  10us
    # 100us  #
    #   1ms  ###
    #  10ms  ###############################################
    # 100ms  ################################################################
    #    1s  #
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads'\G
    #    SHOW CREATE TABLE `db`.`xa_ads`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_c'\G
    #    SHOW CREATE TABLE `db`.`xa_c`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads_ip'\G
    #    SHOW CREATE TABLE `db`.`xa_ads_ip`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    select xa_ads.ad_type,xa_ads.kind,xa_ads.n,xa_c.camp,xa_c.price_i,xa_ads.norepeat,xa_ads.banner,xa_ads.url,xa_ads.alt,xa_ads.raw,xa_ads.javascript,xa_ads.def,xa_ads.user,xa_c.i_nu,xa_c.zone,xa_c.c_nu,xa_c.unlimit,xa_c.d_valid_by,xa_c.t2,xa_c.emailed,xa_c.i_d_nu,MD5(RAND()) AS m,
    xa_ads.url1,xa_ads.url2,xa_ads.url3,
    xa_ads.text1,xa_ads.text2,xa_ads.text3,xa_ads.text4,xa_ads.text5,xa_ads.text6,xa_ads.text7,xa_ads.text8,xa_ads.text9,xa_ads.text10,
    xa_ads.picture1,xa_ads.picture2,xa_ads.picture3,xa_ads.picture4,xa_ads.picture5 
      FROM (xa_ads,xa_c ,xa_c_countries) left join xa_ads_ip 
      ON xa_c.n = xa_ads_ip.ad AND xa_ads_ip.ip = '84.98.146.32' 
      WHERE (xa_c.zone = '15') AND xa_ads.ad_complete = '1' AND xa_ads.ad_type = '20' AND xa_ads.accept = '1' AND xa_ads.en_w = '1' AND xa_c.en_u = '1' AND xa_c.en_w = '1' AND (xa_c.i_nu>'0' OR xa_c.c_nu>'0' OR xa_c.d_valid_by>'1368647891' OR xa_c.unlimit='1') AND (xa_c.i_d_max='0' OR xa_c.i_d_nu>'0') AND (xa_c.c_d_max='0' OR  xa_c.c_d_nu>'0') AND xa_c.t1<'1368647891' AND xa_c.t2>'1368647891' AND xa_c.d4 = '1' AND xa_c.tz5 = '1' AND (xa_c.some_countr = '0' OR (xa_c_countries.country = 'FR' AND xa_c.n = xa_c_countries.ad AND xa_c.camp = xa_c_countries.c)) AND xa_c.n = xa_ads.n AND xa_ads.def = 0 AND xa_ads_ip.ad IS NULL 
      GROUP BY xa_c.n 
      ORDER BY xa_ads.weight desc,m LIMIT 1\G
    
    # Query 2: 20.56 QPS, 0.60x concurrency, ID 0x1B1620F55D56834C at byte 45277385
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.07
    # Time range: 2013-05-15 21:41:32 to 21:59:57
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         28   22714
    # Exec time     13    664s   171us   631ms    29ms   105ms    45ms     8ms
    # Lock time     42    658s    20us   631ms    29ms   105ms    45ms     7ms
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine   1 909.45k      41      41      41      41       0      41
    # Query size     6   2.39M     110     111  110.41  107.34       0  107.34
    # String:
    # Databases    db
    # Hosts        localhost
    # Users        user
    # Query_time distribution
    #   1us
    #  10us
    # 100us  ##################################################
    #   1ms  ###############################
    #  10ms  ################################################################
    # 100ms  ##########
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_c'\G
    #    SHOW CREATE TABLE `db`.`xa_c`\G
    update xa_c set last = '1368647891', i_got = i_got + 1, r = (c_got/i_got)*100 where (n = '808' AND camp = '1')\G
    # Converted for EXPLAIN
    # EXPLAIN /*!50100 PARTITIONS*/
    select  last = '1368647891', i_got = i_got + 1, r = (c_got/i_got)*100 from xa_c where  (n = '808' AND camp = '1')\G
    
    # Query 3: 17.34 QPS, 0.14x concurrency, ID 0x4317A1B02C705FCA at byte 11473648
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-05-15 21:41:32 to 21:59:57
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         24   19156
    # Exec time      3    157s     6ms    58ms     8ms    11ms     2ms     8ms
    # Lock time      0      4s    14us    26ms   214us    60us     1ms    23us
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine  83  44.69M   2.21k   2.56k   2.39k   2.50k   81.83   2.38k
    # Query size     2 916.64k      49      49      49      49       0      49
    # String:
    # Databases    db
    # Hosts        localhost
    # Users        user
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms  ################################################################
    #  10ms  ###########
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads_show'\G
    #    SHOW CREATE TABLE `db`.`xa_ads_show`\G
    delete from xa_ads_show where time < '1368647026'\G
    # Converted for EXPLAIN
    # EXPLAIN /*!50100 PARTITIONS*/
    select * from  xa_ads_show where time < '1368647026'\G
    
    # Query 4: 0.77 QPS, 0.14x concurrency, ID 0x803FB8BAEB9524B3 at byte 31951979
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.13
    # Time range: 2013-05-15 21:41:32 to 21:59:56
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          1     853
    # Exec time      3    150s    50ms      1s   176ms   477ms   152ms   116ms
    # Lock time      1     26s   117us   488ms    31ms   134ms    57ms   194us
    # Rows sent      2     853       1       1       1       1       0       1
    # Rows examine   0  63.45k      56     119   76.17  102.22   13.97   72.65
    # Query size     3   1.10M   1.32k   1.32k   1.32k   1.26k       0   1.26k
    # String:
    # Databases    db
    # Hosts        localhost
    # Users        user
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ##########################################
    # 100ms  ################################################################
    #    1s  #
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads'\G
    #    SHOW CREATE TABLE `db`.`xa_ads`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_c'\G
    #    SHOW CREATE TABLE `db`.`xa_c`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads_ip'\G
    #    SHOW CREATE TABLE `db`.`xa_ads_ip`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    select xa_ads.ad_type,xa_ads.kind,xa_ads.n,xa_c.camp,xa_c.price_i,xa_ads.norepeat,xa_ads.banner,xa_ads.url,xa_ads.alt,xa_ads.raw,xa_ads.javascript,xa_ads.def,xa_ads.user,xa_c.i_nu,xa_c.zone,xa_c.c_nu,xa_c.unlimit,xa_c.d_valid_by,xa_c.t2,xa_c.emailed,xa_c.i_d_nu,MD5(RAND()) AS m,
    xa_ads.url1,xa_ads.url2,xa_ads.url3,
    xa_ads.text1,xa_ads.text2,xa_ads.text3,xa_ads.text4,xa_ads.text5,xa_ads.text6,xa_ads.text7,xa_ads.text8,xa_ads.text9,xa_ads.text10,
    xa_ads.picture1,xa_ads.picture2,xa_ads.picture3,xa_ads.picture4,xa_ads.picture5 
      FROM (xa_ads,xa_c ,xa_c_countries) left join xa_ads_ip 
      ON xa_c.n = xa_ads_ip.ad AND xa_ads_ip.ip = '94.129.99.66' 
      WHERE (xa_c.zone = '15' OR xa_c.zone = '44') AND xa_ads.ad_complete = '1' AND xa_ads.ad_type = '20' AND xa_ads.accept = '1' AND xa_ads.en_w = '1' AND xa_c.en_u = '1' AND xa_c.en_w = '1' AND (xa_c.i_nu>'0' OR xa_c.c_nu>'0' OR xa_c.d_valid_by>'1368647601' OR xa_c.unlimit='1') AND (xa_c.i_d_max='0' OR xa_c.i_d_nu>'0') AND (xa_c.c_d_max='0' OR  xa_c.c_d_nu>'0') AND xa_c.t1<'1368647601' AND xa_c.t2>'1368647601' AND xa_c.d4 = '1' AND xa_c.tz5 = '1' AND (xa_c.some_countr = '0' OR (xa_c_countries.country = 'KW' AND xa_c.n = xa_c_countries.ad AND xa_c.camp = xa_c_countries.c)) AND xa_c.n = xa_ads.n AND xa_ads.def = 0 AND xa_ads_ip.ad IS NULL 
      GROUP BY xa_c.n 
      ORDER BY xa_ads.weight desc,m LIMIT 1\G
    
    
    

    and now, I update my.cnf and reboot mysqld.


    Thanks
     
  6. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    now we can clearly see that the problem are slow, non optimized, bad indexed queries and table locking :)

    please run explain on this query


    explain select xa_ads.ad_type,xa_ads.kind,xa_ads.n,xa_c.camp,xa_c.price_i,xa_ads.norepeat,xa_ads.banner,xa_ads.url,xa_ads.alt,xa_ads.raw,xa_ads.javascript,xa_ads.def,xa_ads.user,xa_c.i_nu,xa_c.zone,xa_c.c_nu,xa_c.unlimit,xa_c.d_valid_by,xa_c.t2,xa_c.emailed,xa_c.i_d_nu,MD5(RAND()) AS m,
    xa_ads.url1,xa_ads.url2,xa_ads.url3,
    xa_ads.text1,xa_ads.text2,xa_ads.text3,xa_ads.text4,xa_ads.text5,xa_ads.text6,xa_ads.text7,xa_ads.text8,xa_ads.text9,xa_ads.text10,
    xa_ads.picture1,xa_ads.picture2,xa_ads.picture3,xa_ads.picture4,xa_ads.picture5
    FROM (xa_ads,xa_c ,xa_c_countries) left join xa_ads_ip
    ON xa_c.n = xa_ads_ip.ad AND xa_ads_ip.ip = '84.98.146.32'
    WHERE (xa_c.zone = '15') AND xa_ads.ad_complete = '1' AND xa_ads.ad_type = '20' AND xa_ads.accept = '1' AND xa_ads.en_w = '1' AND xa_c.en_u = '1' AND xa_c.en_w = '1' AND (xa_c.i_nu>'0' OR xa_c.c_nu>'0' OR xa_c.d_valid_by>'1368647891' OR xa_c.unlimit='1') AND (xa_c.i_d_max='0' OR xa_c.i_d_nu>'0') AND (xa_c.c_d_max='0' OR xa_c.c_d_nu>'0') AND xa_c.t1<'1368647891' AND xa_c.t2>'1368647891' AND xa_c.d4 = '1' AND xa_c.tz5 = '1' AND (xa_c.some_countr = '0' OR (xa_c_countries.country = 'FR' AND xa_c.n = xa_c_countries.ad AND xa_c.camp = xa_c_countries.c)) AND xa_c.n = xa_ads.n AND xa_ads.def = 0 AND xa_ads_ip.ad IS NULL
    GROUP BY xa_c.n
    ORDER BY xa_ads.weight desc,m LIMIT 1

    and this:

    explain select last = '1368647891', i_got = i_got + 1, r = (c_got/i_got)*100 from xa_c where (n = '808' AND camp = '1')

    and post the results,

    It would be good, if you could post schema of tables with indexes xa_ads, xa_c, xa_c_countries, so we can check what data types are there and if they use indexes
     
  7. sc0t

    sc0t Member

    Joined:
    May 14, 2013
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hello again,

    Thank you for your answer :)
    Please let me know when I should I post again mysqltuner and mysqlreports.

    Here the needed informations:
    Code:
    mysql> explain select xa_ads.ad_type,xa_ads.kind,xa_ads.n,xa_c.camp,xa_c.price_i                                                                                                 ,xa_ads.norepeat,xa_ads.banner,xa_ads.url,xa_ads.alt,xa_ads.raw,xa_ads.javascrip                                                                                                 t,xa_ads.def,xa_ads.user,xa_c.i_nu,xa_c.zone,xa_c.c_nu,xa_c.unlimit,xa_c.d_valid                                                                                                 _by,xa_c.t2,xa_c.emailed,xa_c.i_d_nu,MD5(RAND()) AS m,
        -> xa_ads.url1,xa_ads.url2,xa_ads.url3,
        -> xa_ads.text1,xa_ads.text2,xa_ads.text3,xa_ads.text4,xa_ads.text5,xa_ads.t                                                                                                 ext6,xa_ads.text7,xa_ads.text8,xa_ads.text9,xa_ads.text10,
        -> xa_ads.picture1,xa_ads.picture2,xa_ads.picture3,xa_ads.picture4,xa_ads.pi                                                                                                 cture5
        -> FROM (xa_ads,xa_c ,xa_c_countries) left join xa_ads_ip
        -> ON xa_c.n = xa_ads_ip.ad AND xa_ads_ip.ip = '84.98.146.32'
        -> WHERE (xa_c.zone = '15') AND xa_ads.ad_complete = '1' AND xa_ads.ad_type                                                                                                  = '20' AND xa_ads.accept = '1' AND xa_ads.en_w = '1' AND xa_c.en_u = '1' AND xa_                                                                                                 c.en_w = '1' AND (xa_c.i_nu>'0' OR xa_c.c_nu>'0' OR xa_c.d_valid_by>'1368647891'                                                                                                  OR xa_c.unlimit='1') AND (xa_c.i_d_max='0' OR xa_c.i_d_nu>'0') AND (xa_c.c_d_ma                                                                                                 x='0' OR xa_c.c_d_nu>'0') AND xa_c.t1<'1368647891' AND xa_c.t2>'1368647891' AND                                                                                                  xa_c.d4 = '1' AND xa_c.tz5 = '1' AND (xa_c.some_countr = '0' OR (xa_c_countries.                                                                                                 country = 'FR' AND xa_c.n = xa_c_countries.ad AND xa_c.camp = xa_c_countries.c))                                                                                                  AND xa_c.n = xa_ads.n AND xa_ads.def = 0 AND xa_ads_ip.ad IS NULL
        -> GROUP BY xa_c.n
        -> ORDER BY xa_ads.weight desc,m LIMIT 1;
    +----+-------------+----------------+--------+---------------+------+---------+-                                                                                                 --------------------------+------+----------------------------------------------                                                                                                 +
    | id | select_type | table          | type   | possible_keys | key  | key_len |                                                                                                  ref                       | rows | Extra                                                                                                                                         |
    +----+-------------+----------------+--------+---------------+------+---------+-                                                                                                 --------------------------+------+----------------------------------------------                                                                                                 +
    |  1 | SIMPLE      | xa_c           | range  | work          | work | 2       |                                                                                                  NULL                      |   37 | Using where; Using temporary; Using filesort                                                                                                  |
    |  1 | SIMPLE      | xa_ads         | eq_ref | n,work        | n    | 4       |                                                                                                  xteeendaaadverrttt.xa_c.n |    1 | Using where                                                                                                                                   |
    |  1 | SIMPLE      | xa_ads_ip      | ref    | ip            | ip   | 257     |                                                                                                  const                     |    1 | Using where; Not exists                                                                                                                       |
    |  1 | SIMPLE      | xa_c_countries | index  | work          | work | 12      |                                                                                                  NULL                      | 1849 | Using where; Using index; Using join buffer                                                                                                   |
    +----+-------------+----------------+--------+---------------+------+---------+-                                                                                                 --------------------------+------+----------------------------------------------                                                                                                 +
    4 rows in set (0.01 sec)
    
    ..
    
    mysql> explain select last = '1368647891', i_got = i_got + 1, r = (c_got/i_got)*100 from xa_c where (n = '808' AND camp = '1');
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | xa_c  | ALL  | NULL          | NULL | NULL    | NULL |   38 | Using where |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    1 row in set (0.00 sec)
    
    ..
    
    describe xa_ads;
    +-------------+----------------------+------+-----+---------+----------------+
    | Field       | Type                 | Null | Key | Default | Extra          |
    +-------------+----------------------+------+-----+---------+----------------+
    | n           | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
    | title       | varchar(255)         | NO   |     | NULL    |                |
    | ad_type     | int(10) unsigned     | NO   | MUL | NULL    |                |
    | accept      | tinyint(1)           | NO   |     | NULL    |                |
    | en_w        | tinyint(1)           | NO   |     | NULL    |                |
    | weight      | tinyint(1)           | NO   |     | NULL    |                |
    | w           | smallint(5) unsigned | NO   |     | NULL    |                |
    | h           | smallint(5) unsigned | NO   |     | NULL    |                |
    | norepeat    | int(10) unsigned     | NO   |     | NULL    |                |
    | campaigns   | tinyint(1) unsigned  | NO   |     | NULL    |                |
    | zones       | text                 | NO   |     | NULL    |                |
    | keywords    | text                 | NO   | MUL | NULL    |                |
    | banner      | varchar(255)         | NO   |     | NULL    |                |
    | url         | varchar(255)         | NO   |     | NULL    |                |
    | alt         | varchar(255)         | NO   |     | NULL    |                |
    | raw         | text                 | NO   |     | NULL    |                |
    | kind        | varchar(40)          | NO   |     | NULL    |                |
    | javascript  | tinyint(1)           | NO   |     | NULL    |                |
    | ad_complete | tinyint(1)           | NO   |     | NULL    |                |
    | url1        | text                 | NO   |     | NULL    |                |
    | url2        | text                 | NO   |     | NULL    |                |
    | url3        | text                 | NO   |     | NULL    |                |
    | text1       | text                 | NO   |     | NULL    |                |
    | text2       | text                 | NO   |     | NULL    |                |
    | text3       | text                 | NO   |     | NULL    |                |
    | text4       | text                 | NO   |     | NULL    |                |
    | text5       | text                 | NO   |     | NULL    |                |
    | text6       | text                 | NO   |     | NULL    |                |
    | text7       | text                 | NO   |     | NULL    |                |
    | text8       | text                 | NO   |     | NULL    |                |
    | text9       | text                 | NO   |     | NULL    |                |
    | text10      | text                 | NO   |     | NULL    |                |
    | picture1    | varchar(255)         | NO   |     | NULL    |                |
    | picture2    | varchar(255)         | NO   |     | NULL    |                |
    | picture3    | varchar(255)         | NO   |     | NULL    |                |
    | picture4    | varchar(255)         | NO   |     | NULL    |                |
    | picture5    | varchar(255)         | NO   |     | NULL    |                |
    | created     | int(10) unsigned     | NO   |     | NULL    |                |
    | user        | int(11)              | NO   |     | NULL    |                |
    | username    | varchar(15)          | NO   |     | NULL    |                |
    | preview     | text                 | NO   |     | NULL    |                |
    | def         | tinyint(1)           | NO   |     | NULL    |                |
    +-------------+----------------------+------+-----+---------+----------------+
    42 rows in set (0.01 sec)
    
    ..
    
     describe xa_c;
    +---------------+-----------------------+------+-----+---------+-------+
    | Field         | Type                  | Null | Key | Default | Extra |
    +---------------+-----------------------+------+-----+---------+-------+
    | n             | int(10) unsigned      | NO   |     | NULL    |       |
    | camp          | tinyint(3) unsigned   | NO   |     | NULL    |       |
    | zone          | int(11)               | NO   |     | NULL    |       |
    | javascript    | tinyint(1)            | NO   |     | NULL    |       |
    | banner_target | varchar(50)           | NO   |     | NULL    |       |
    | accept        | tinyint(1)            | NO   |     | NULL    |       |
    | en_u          | tinyint(1)            | NO   | MUL | NULL    |       |
    | en_w          | tinyint(1)            | NO   |     | NULL    |       |
    | i_got         | int(10) unsigned      | NO   |     | NULL    |       |
    | c_got         | int(10) unsigned      | NO   |     | NULL    |       |
    | r             | double(4,2) unsigned  | NO   |     | 0.00    |       |
    | price_i       | double(10,6) unsigned | NO   |     | NULL    |       |
    | price_c       | double(10,6) unsigned | NO   |     | NULL    |       |
    | i_nu          | int(11)               | NO   |     | NULL    |       |
    | c_nu          | int(11)               | NO   |     | NULL    |       |
    | unlimit       | tinyint(1)            | NO   |     | NULL    |       |
    | d_total       | int(10) unsigned      | NO   |     | NULL    |       |
    | d_valid_by    | int(10) unsigned      | NO   |     | NULL    |       |
    | t1            | int(10) unsigned      | NO   |     | NULL    |       |
    | t2            | int(10) unsigned      | NO   |     | NULL    |       |
    | d1            | tinyint(1)            | NO   |     | NULL    |       |
    | d2            | tinyint(1)            | NO   |     | NULL    |       |
    | d3            | tinyint(1)            | NO   |     | NULL    |       |
    | d4            | tinyint(1)            | NO   |     | NULL    |       |
    | d5            | tinyint(1)            | NO   |     | NULL    |       |
    | d6            | tinyint(1)            | NO   |     | NULL    |       |
    | d7            | tinyint(1)            | NO   |     | NULL    |       |
    | tz1           | tinyint(1)            | NO   |     | NULL    |       |
    | tz2           | tinyint(1)            | NO   |     | NULL    |       |
    | tz3           | tinyint(1)            | NO   |     | NULL    |       |
    | tz4           | tinyint(1)            | NO   |     | NULL    |       |
    | tz5           | tinyint(1)            | NO   |     | NULL    |       |
    | some_countr   | tinyint(1)            | NO   |     | NULL    |       |
    | i_d_max       | int(10) unsigned      | NO   |     | NULL    |       |
    | c_d_max       | int(10) unsigned      | NO   |     | NULL    |       |
    | i_d_nu        | int(10) unsigned      | NO   |     | NULL    |       |
    | c_d_nu        | int(10) unsigned      | NO   |     | NULL    |       |
    | last          | int(10) unsigned      | NO   |     | NULL    |       |
    | user          | int(10) unsigned      | NO   |     | NULL    |       |
    | username      | varchar(15)           | NO   |     | NULL    |       |
    | emailed       | int(10) unsigned      | NO   |     | NULL    |       |
    +---------------+-----------------------+------+-----+---------+-------+
    41 rows in set (0.01 sec)
    
    ..
    
    mysql> describe xa_c_countries;
    +---------+---------------------+------+-----+---------+-------+
    | Field   | Type                | Null | Key | Default | Extra |
    +---------+---------------------+------+-----+---------+-------+
    | ad      | int(10) unsigned    | NO   | MUL | NULL    |       |
    | c       | tinyint(3) unsigned | NO   |     | NULL    |       |
    | country | varchar(5)          | NO   |     | NULL    |       |
    +---------+---------------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    
    
    Thank you

    Regards.
     
  8. sc0t

    sc0t Member

    Joined:
    May 14, 2013
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hello,

    I finally reinstalled everything, Now I have centos6.4 with nginx+php-fpm and Percona as a mysql server.
    Of course I converted all tables to InnoDb.

    The first day it worked like a charm , according to GA live I had 2600 connected people at the same time without any problems.

    Then I started tuning , and stuff goes worst and worst.. and Now it can't handle even 1200 visitors...

    I don't know what's wrong exactly... but I think the problem is coming from php-fpm and not from Percona, because when I restart php-fpm everything is ok

    Here my.cnf ( generated via percona website)
    Code:
    [mysql]
    
    # CLIENT #
    port                           = 3306
    socket                         = /var/lib/mysql/mysql.sock
    
    [mysqld]
    
    # GENERAL #
    user                           = mysql
    default_storage_engine         = InnoDB
    socket                         = /var/lib/mysql/mysql.sock
    pid_file                       = /var/lib/mysql/mysql.pid
    
    # MyISAM #
    key_buffer_size                = 32M
    myisam_recover                 = FORCE,BACKUP
    
    # SAFETY #
    max_allowed_packet             = 16M
    max_connect_errors             = 1000000
    innodb                         = FORCE
    
    # DATA STORAGE #
    datadir                        = /var/lib/mysql/
    local-infile=0
    
    # BINARY LOGGING #
    log_bin                        = /var/lib/mysql/mysql-bin
    expire_logs_days               = 2
    sync_binlog                    = 1
    
    # CACHES AND LIMITS #
    tmp_table_size                 = 32M
    max_heap_table_size            = 32M
    query_cache_type               = 0
    query_cache_size               = 0
    max_connections                = 7000
    thread_cache_size              = 500
    open_files_limit               = 65535
    table_definition_cache         = 4096
    table_open_cache               = 4096
    
    # INNODB #
    innodb_flush_method            = O_DIRECT
    innodb_log_files_in_group      = 2
    innodb_log_file_size           = 512M
    innodb_flush_log_at_trx_commit = 1
    innodb_file_per_table          = 1
    innodb_buffer_pool_size        = 23G
    
    # LOGGING #
    log_error                      = /var/lib/mysql/mysql-error.log
    log_queries_not_using_indexes  = 1
    slow_query_log                 = 1
    slow_query_log_file            = /var/lib/mysql/mysql-slow.log
    
    #Added
    
    wait_timeout=3
    #Ignore Warnings
    
    log_warnings_suppress = 1592
    here is php-fpm.conf
    Code:
    ;;;;;;;;;;;;;;;;;;;;;
    ; FPM Configuration ;
    ;;;;;;;;;;;;;;;;;;;;;
    
    ; All relative paths in this configuration file are relative to PHP's install
    ; prefix.
    
    ; Include one or more files. If glob(3) exists, it is used to include a bunch of
    ; files from a glob(3) pattern. This directive can be used everywhere in the
    ; file.
    include=/etc/php-fpm.d/*.conf
    
    ;;;;;;;;;;;;;;;;;;
    ; Global Options ;
    ;;;;;;;;;;;;;;;;;;
    
    [global]
    ; Pid file
    ; Default Value: none
    pid = /var/run/php-fpm/php-fpm.pid
    
    ; Error log file
    ; Default Value: /var/log/php-fpm.log
    error_log = /var/log/php-fpm/error.log
    
    ; Log level
    ; Possible Values: alert, error, warning, notice, debug
    ; Default Value: notice
    ;log_level = notice
    
    ; If this number of child processes exit with SIGSEGV or SIGBUS within the time
    ; interval set by emergency_restart_interval then FPM will restart. A value
    ; of '0' means 'Off'.
    ; Default Value: 0
    emergency_restart_threshold = 10
    
    ; Interval of time used by emergency_restart_interval to determine when 
    ; a graceful restart will be initiated.  This can be useful to work around
    ; accidental corruptions in an accelerator's shared memory.
    ; Available Units: s(econds), m(inutes), h(ours), or d(ays)
    ; Default Unit: seconds
    ; Default Value: 0
    emergency_restart_interval = 1m
    
    ; Time limit for child processes to wait for a reaction on signals from master.
    ; Available units: s(econds), m(inutes), h(ours), or d(ays)
    ; Default Unit: seconds
    ; Default Value: 0
    process_control_timeout = 5
    
    ; Send FPM to background. Set to 'no' to keep FPM in foreground for debugging.
    ; Default Value: yes
    daemonize = yes
    
    ;;;;;;;;;;;;;;;;;;;;
    ; Pool Definitions ; 
    ;;;;;;;;;;;;;;;;;;;;
    
    ; See /etc/php-fpm.d/*.conf
    
    
    here is www.conf
    Code:
    ; Start a new pool named 'www'.
    [www]
    
    ; The address on which to accept FastCGI requests.
    ; Valid syntaxes are:
    ;   'ip.add.re.ss:port'    - to listen on a TCP socket to a specific address on
    ;                            a specific port;
    ;   'port'                 - to listen on a TCP socket to all addresses on a
    ;                            specific port;
    ;   '/path/to/unix/socket' - to listen on a unix socket.
    ; Note: This value is mandatory.
    listen = /var/run/php5-fpm.sock
    
    ; Set listen(2) backlog. A value of '-1' means unlimited.
    ; Default Value: -1
    ;listen.backlog = 4096
     
    ; List of ipv4 addresses of FastCGI clients which are allowed to connect.
    ; Equivalent to the FCGI_WEB_SERVER_ADDRS environment variable in the original
    ; PHP FCGI (5.2.2+). Makes sense only with a tcp listening socket. Each address
    ; must be separated by a comma. If this value is left blank, connections will be
    ; accepted from any ip address.
    ; Default Value: any
    ;listen.allowed_clients = 127.0.0.1
    
    ; Set permissions for unix socket, if one is used. In Linux, read/write
    ; permissions must be set in order to allow connections from a web server. Many
    ; BSD-derived systems allow connections regardless of permissions. 
    ; Default Values: user and group are set as the running user
    ;                 mode is set to 0666
    ;listen.owner = nobody
    ;listen.group = nobody
    ;listen.mode = 0666
    
    ; Unix user/group of processes
    ; Note: The user is mandatory. If the group is not set, the default user's group
    ;       will be used.
    ; RPM: apache Choosed to be able to access some dir as httpd
    user = apache
    ; RPM: Keep a group allowed to write in log dir.
    group = apache
    
    ; Choose how the process manager will control the number of child processes.
    ; Possible Values:
    ;   static  - a fixed number (pm.max_children) of child processes;
    ;   dynamic - the number of child processes are set dynamically based on the
    ;             following directives:
    ;             pm.max_children      - the maximum number of children that can
    ;                                    be alive at the same time.
    ;             pm.start_servers     - the number of children created on startup.
    ;             pm.min_spare_servers - the minimum number of children in 'idle'
    ;                                    state (waiting to process). If the number
    ;                                    of 'idle' processes is less than this
    ;                                    number then some children will be created.
    ;             pm.max_spare_servers - the maximum number of children in 'idle'
    ;                                    state (waiting to process). If the number
    ;                                    of 'idle' processes is greater than this
    ;                                    number then some children will be killed.
    ; Note: This value is mandatory.
    pm = dynamic
    
    ; The number of child processes to be created when pm is set to 'static' and the
    ; maximum number of child processes to be created when pm is set to 'dynamic'.
    ; This value sets the limit on the number of simultaneous requests that will be
    ; served. Equivalent to the ApacheMaxClients directive with mpm_prefork.
    ; Equivalent to the PHP_FCGI_CHILDREN environment variable in the original PHP
    ; CGI.
    ; Note: Used when pm is set to either 'static' or 'dynamic'
    ; Note: This value is mandatory.
    pm.max_children = 7000
    
    ; The number of child processes created on startup.
    ; Note: Used only when pm is set to 'dynamic'
    ; Default Value: min_spare_servers + (max_spare_servers - min_spare_servers) / 2
    pm.start_servers = 4000
    
    ; The desired minimum number of idle server processes.
    ; Note: Used only when pm is set to 'dynamic'
    ; Note: Mandatory when pm is set to 'dynamic'
    pm.min_spare_servers = 4000
    
    ; The desired maximum number of idle server processes.
    ; Note: Used only when pm is set to 'dynamic'
    ; Note: Mandatory when pm is set to 'dynamic'
    pm.max_spare_servers = 6000
     
    ; The number of requests each child process should execute before respawning.
    ; This can be useful to work around memory leaks in 3rd party libraries. For
    ; endless request processing specify '0'. Equivalent to PHP_FCGI_MAX_REQUESTS.
    ; Default Value: 0
    pm.max_requests = 500
    
    ; The URI to view the FPM status page. If this value is not set, no URI will be
    ; recognized as a status page. By default, the status page shows the following
    ; information:
    ;   accepted conn    - the number of request accepted by the pool;
    ;   pool             - the name of the pool;
    ;   process manager  - static or dynamic;
    ;   idle processes   - the number of idle processes;
    ;   active processes - the number of active processes;
    ;   total processes  - the number of idle + active processes.
    ; The values of 'idle processes', 'active processes' and 'total processes' are
    ; updated each second. The value of 'accepted conn' is updated in real time.
    ; Example output:
    ;   accepted conn:   12073
    ;   pool:             www
    ;   process manager:  static
    ;   idle processes:   35
    ;   active processes: 65
    ;   total processes:  100
    ; By default the status page output is formatted as text/plain. Passing either
    ; 'html' or 'json' as a query string will return the corresponding output
    ; syntax. Example:
    ;   http://www.foo.bar/status
    ;   http://www.foo.bar/status?json
    ;   http://www.foo.bar/status?html
    ; Note: The value must start with a leading slash (/). The value can be
    ;       anything, but it may not be a good idea to use the .php extension or it
    ;       may conflict with a real PHP file.
    ; Default Value: not set 
    ;pm.status_path = /status
     
    ; The ping URI to call the monitoring page of FPM. If this value is not set, no
    ; URI will be recognized as a ping page. This could be used to test from outside
    ; that FPM is alive and responding, or to
    ; - create a graph of FPM availability (rrd or such);
    ; - remove a server from a group if it is not responding (load balancing);
    ; - trigger alerts for the operating team (24/7).
    ; Note: The value must start with a leading slash (/). The value can be
    ;       anything, but it may not be a good idea to use the .php extension or it
    ;       may conflict with a real PHP file.
    ; Default Value: not set
    ;ping.path = /ping
    
    ; This directive may be used to customize the response of a ping request. The
    ; response is formatted as text/plain with a 200 response code.
    ; Default Value: pong
    ;ping.response = pong
     
    ; The timeout for serving a single request after which the worker process will
    ; be killed. This option should be used when the 'max_execution_time' ini option
    ; does not stop script execution for some reason. A value of '0' means 'off'.
    ; Available units: s(econds)(default), m(inutes), h(ours), or d(ays)
    ; Default Value: 0
    request_terminate_timeout = 120s
     
    ; The timeout for serving a single request after which a PHP backtrace will be
    ; dumped to the 'slowlog' file. A value of '0s' means 'off'.
    ; Available units: s(econds)(default), m(inutes), h(ours), or d(ays)
    ; Default Value: 0
    ;request_slowlog_timeout = 0
     
    ; The log file for slow requests
    ; Default Value: not set
    ; Note: slowlog is mandatory if request_slowlog_timeout is set
    slowlog = /var/log/php-fpm/www-slow.log
     
    ; Set open file descriptor rlimit.
    ; Default Value: system defined value
    ;rlimit_files = 1024
     
    ; Set max core size rlimit.
    ; Possible Values: 'unlimited' or an integer greater or equal to 0
    ; Default Value: system defined value
    ;rlimit_core = 0
     
    ; Chroot to this directory at the start. This value must be defined as an
    ; absolute path. When this value is not set, chroot is not used.
    ; Note: chrooting is a great security feature and should be used whenever 
    ;       possible. However, all PHP paths will be relative to the chroot
    ;       (error_log, sessions.save_path, ...).
    ; Default Value: not set
    ;chroot = 
     
    ; Chdir to this directory at the start. This value must be an absolute path.
    ; Default Value: current directory or / when chroot
    ;chdir = /var/www
     
    ; Redirect worker stdout and stderr into main error log. If not set, stdout and
    ; stderr will be redirected to /dev/null according to FastCGI specs.
    ; Default Value: no
    ;catch_workers_output = yes
     
    ; Limits the extensions of the main script FPM will allow to parse. This can
    ; prevent configuration mistakes on the web server side. You should only limit
    ; FPM to .php extensions to prevent malicious users to use other extensions to
    ; exectute php code.
    ; Note: set an empty value to allow all extensions.
    ; Default Value: .php
    ;security.limit_extensions = .php .php3 .php4 .php5
    
    ; Pass environment variables like LD_LIBRARY_PATH. All $VARIABLEs are taken from
    ; the current environment.
    ; Default Value: clean env
    ;env[HOSTNAME] = $HOSTNAME
    ;env[PATH] = /usr/local/bin:/usr/bin:/bin
    ;env[TMP] = /tmp
    ;env[TMPDIR] = /tmp
    ;env[TEMP] = /tmp
    
    ; Additional php.ini defines, specific to this pool of workers. These settings
    ; overwrite the values previously defined in the php.ini. The directives are the
    ; same as the PHP SAPI:
    ;   php_value/php_flag             - you can set classic ini defines which can
    ;                                    be overwritten from PHP call 'ini_set'. 
    ;   php_admin_value/php_admin_flag - these directives won't be overwritten by
    ;                                     PHP call 'ini_set'
    ; For php_*flag, valid values are on, off, 1, 0, true, false, yes or no.
    
    ; Defining 'extension' will load the corresponding shared extension from
    ; extension_dir. Defining 'disable_functions' or 'disable_classes' will not
    ; overwrite previously defined php.ini values, but will append the new value
    ; instead.
    
    ; Default Value: nothing is defined by default except the values in php.ini and
    ;                specified at startup with the -d argument
    ;php_admin_value[sendmail_path] = /usr/sbin/sendmail -t -i -f www@my.domain.com
    ;php_flag[display_errors] = off
    php_admin_value[error_log] = /var/log/php-fpm/www-error.log
    php_admin_flag[log_errors] = on
    ;php_admin_value[memory_limit] = 128M
    
    ; Set session path to a directory owned by process user
    php_value[session.save_handler] = files
    php_value[session.save_path] = /var/lib/php/session
    
    
    here is nginx.conf
    Code:
    user              nginx;
    worker_processes  8;
    worker_rlimit_nofile 20000;
    
    error_log /var/log/nginx/error.log crit;
    
    pid        /var/run/nginx.pid;
    
    events {
        worker_connections  4000;
    	use epoll;
    	multi_accept on;
    }
    
    
    http {
    
    	pagespeed on;
    
    # needs to exist and be writable by nginx
    	pagespeed FileCachePath /var/ngx_pagespeed_cache;
    	geoip_country  /usr/share/GeoIP/GeoIPv6.dat; # the country IP database
        include       /etc/nginx/mime.types;
        default_type  application/octet-stream;
    
        log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                          '$status $body_bytes_sent "$http_referer" '
                          '"$http_user_agent" "$http_x_forwarded_for"';
    
    					  
    # added
    access_log off;
    server_tokens off;
    
    client_body_timeout 10;
    client_max_body_size 20m;
    client_body_buffer_size 128k;
    
    open_file_cache max=200000 inactive=20s;
    open_file_cache_valid 30s;
    open_file_cache_min_uses 2;
    open_file_cache_errors on;
    
    sendfile on; 
    tcp_nopush on;
    tcp_nodelay on; 
    send_timeout 2;
    keepalive_requests 100000;
    	
    
     
        # Load config files from the /etc/nginx/conf.d directory
        # The default server is in conf.d/default.conf
        include /etc/nginx/conf.d/*.conf;
    	
    	gzip on;
    	gzip_static on;
    	gzip_min_length 10240;
    	gzip_proxied expired no-cache no-store private auth;
    	gzip_types text/plain text/css text/xml text/javascript application/x-javascript application/xml;
    	gzip_disable "MSIE [1-6]\.";
    	
    	
    	## Load virtual host conf files. ##
    	include /etc/nginx/sites-enabled/*;
    
    }
    
    and in php.ini
    Code:
    ...
    max_execution_time = 30
    max_input_time = 60
    memory_limit = 512M
    ...
    
    Thank you for your help
     
  9. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Im sorry for delay, I was out for some time

    Please correct those:
    key_buffer_size = 75M

    max_connect_errors = 100



    turn on query cache:
    from this
    query_cache_type = 0
    query_cache_size = 0
    to
    query_cache_limit = 1M
    query_cache_size = 30M
    query_cache_type = 1


    max_connections = 7000
    7000 concurrent mysql connections, nice :) read below


    php-fpm, correct:

    pm = dynamic

    pm.max_children = 7000 # assuming that we keep this high number :)
    pm.start_servers = 100
    pm.min_spare_servers = 20
    pm.max_spare_servers = 100

    pm.max_requests = 1000

    request_terminate_timeout = 10s

    nginx:
    worker_processes 3; # keep it lower than max cores, you got much more proceses there, mysql and php-fpm also needs some cores :)


    worker_connections 4000; # and here is the thing, you can accept nginx 4000 connections, not 7000 :D and you got default 75 seconds keep alive time :D

    so for all mysql and php-fpm settings it would be goot at least to change max 4000 :)
    that assuming that you would need that much concurrent connections (check the end about slow queries)

    and from this
    keepalive_requests 100000;


    to this:
    keepalive_requests 100; # default
    keepalive_timeout 3;


    before you can save current result from mysqltuner and report
    then update and restart

    anyway, your problem is mostly slow, badly indexed queries, when optimizing those you wouldnt have houndreds, thounsands concurrent connections, but probably tens at best handling same traffic
    Optimizing queries properly, efficiently (especially that complecated like the first query you have there, the one that took 76% of mysql time based on pt-query-digest ) requires access to the server with the data structure, where it can be tested, it takes more time
    In some cases rewriting queries in code might be needed to get most of it
    To decide you would need more inside checks, but as I said, that takes time and requires access to the server with code/data/database you are optimizing

    after changing to innodb also it would be good if you posted updated pt-query-digest result, mysqltuner and mysqlreport, couse a lot has changed
    now there is most likely much less table locking


    edit: also hopefully you installed APC with php-fpm too, it will just speed up php a bit (bit lower cpu use)
     
    #9 thinkbot, May 22, 2013
    Last edited: May 22, 2013
  10. sc0t

    sc0t Member

    Joined:
    May 14, 2013
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi !
    Thank you for your reply.
    Here the last config used:

    my.cnf
    Code:
    [mysql]
    
    # CLIENT #
    port                           = 3306
    socket                         = /var/lib/mysql/mysql.sock
    
    [mysqld]
    
    # GENERAL #
    user                           = mysql
    default_storage_engine         = InnoDB
    socket                         = /var/lib/mysql/mysql.sock
    pid_file                       = /var/lib/mysql/mysql.pid
    
    # MyISAM #
    key_buffer_size                = 75M
    myisam_recover                 = FORCE,BACKUP
    
    # SAFETY #
    max_allowed_packet             = 8M
    max_connect_errors             = 100
    innodb                         = FORCE
    
    # DATA STORAGE #
    datadir                        = /var/lib/mysql/
    
    # BINARY LOGGING #
    log_bin                        = /var/lib/mysql/mysql-bin
    expire_logs_days               = 2
    sync_binlog                    = 1
    
    # CACHES AND LIMITS #
    tmp_table_size                 = 32M
    max_heap_table_size            = 32M
    query_cache_limit              = 1M
    query_cache_size               = 30M
    query_cache_type               = 1
    max_connections                = 4000
    thread_cache_size              = 500
    open_files_limit               = 65535
    table_definition_cache         = 4096
    table_open_cache               = 4096
    
    # INNODB #
    innodb_flush_method            = O_DIRECT
    innodb_log_files_in_group      = 2
    innodb_log_file_size           = 512M
    innodb_flush_log_at_trx_commit = 1
    innodb_file_per_table          = 1
    innodb_buffer_pool_size        = 26G
    
    # LOGGING #
    log_error                      = /var/lib/mysql/mysql-error.log
    log_queries_not_using_indexes  = 1
    slow_query_log                 = 1
    slow_query_log_file            = /var/lib/mysql/mysql-slow.log
    
    #AJOUT#
    wait_timeout=3
    log_warnings_suppress = 1592
    
    nginx.conf & fastcgi_params
    Code:
    user              nginx;
    worker_processes  3;
    worker_rlimit_nofile 200000;
    
    
    error_log /var/log/nginx/error.log crit;
    pid        /var/run/nginx.pid;
    
    events {
    worker_connections 4000;
    multi_accept on;
    use epoll;
    }
    
    
    http {
    pagespeed on;
    
    # needs to exist and be writable by nginx
    	pagespeed FileCachePath /var/ngx_pagespeed_cache;
    	geoip_country  /usr/share/GeoIP/GeoIPv6.dat; # the country IP database
       include       /etc/nginx/mime.types;
       default_type  application/octet-stream;
    
        log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                          '$status $body_bytes_sent "$http_referer" '
                          '"$http_user_agent" "$http_x_forwarded_for"';
    
    					  
    # added
    access_log off;
    server_tokens off;
    
    sendfile on;
    tcp_nopush on;
    tcp_nodelay on;
    keepalive_requests 100; # default
    keepalive_timeout 3;
    types_hash_max_size 2048;
    server_names_hash_bucket_size 64;
    server_name_in_redirect off;
    client_body_buffer_size 8K;
    client_header_buffer_size 2k;
    client_max_body_size 16k;
    client_body_timeout 10;
    large_client_header_buffers 4 2k;
     
        # Load config files from the /etc/nginx/conf.d directory
        # The default server is in conf.d/default.conf
        include /etc/nginx/conf.d/*.conf;
    	
    	gzip on;
    	gzip_static on;
    	gzip_min_length 10240;
    	gzip_proxied expired no-cache no-store private auth;
    	gzip_types text/plain text/css text/xml text/javascript application/x-javascript application/xml;
    	gzip_disable "MSIE [1-6]\.";
    	
    	
    	## Load virtual host conf files. ##
    	include /etc/nginx/sites-enabled/*;
    fastcgi_connect_timeout 15;
    fastcgi_send_timeout 60;
    fastcgi_read_timeout 60;
    fastcgi_buffer_size 32k;
    fastcgi_buffers 512 16k;
    fastcgi_busy_buffers_size 32k;
    fastcgi_temp_file_write_size 256k;
    fastcgi_max_temp_file_size 0;
    }
    
    www.conf
    Code:
    pid = /var/run/php-fpm/php-fpm.pid
    error_log = /var/log/php-fpm/error.log
    emergency_restart_threshold = 10
    emergency_restart_interval = 1m
    process_control_timeout = 5
    daemonize = yes
    listen = 127.0.0.1:9000
    ;listen = /tmp/php5-fpm.sock
    listen.backlog = 32400
    user = apache
    group = apache
    pm = dynamic
    pm.max_children = 4000
    pm.start_servers = 100
    pm.min_spare_servers = 20
    pm.max_spare_servers = 100
    pm.max_requests =1000
    request_terminate_timeout = 10s
    rlimit_files = 65536
    rlimit_core = unlimited
    catch_workers_output = no
    
    Results:

    mysqltuner.pl
    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.30-30.2-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1K (Tables: 1)
    [--] Data in InnoDB tables: 87M (Tables: 282)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 33
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 21h 21m 5s (23M q [307.386 qps], 2M conn, TX: 10B, RX: 4B)
    [--] Reads / Writes: 43% / 57%
    [--] Total buffers: 26.1G global + 2.8M per thread (4000 max threads)
    [!!] Maximum possible memory usage: 36.9G (117% of installed RAM)
    [!!] Slow queries: 14% (3M/23M)
    [OK] Highest usage of available connections: 61% (2442/4000)
    [OK] Key buffer size / total MyISAM indexes: 75.0M/104.0K
    [OK] Key buffer hit rate: 100.0% (9B cached / 17 reads)
    [OK] Query cache efficiency: 22.3% (1M cached / 7M selects)
    [!!] Query cache prunes per day: 243437
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1M sorts)
    [!!] Joins performed without indexes: 1042579
    [!!] Temporary tables created on disk: 49% (1M on disk / 2M total)
    [OK] Thread cache hit rate: 99% (10K created / 2M connections)
    [OK] Table cache hit rate: 36% (4K open / 11K opened)
    [OK] Open file limit used: 0% (3/65K)
    [OK] Table locks acquired immediately: 99% (16M immediate / 16M locks)
    [OK] InnoDB data size / buffer pool: 87.7M/26.0G
    
    -------- 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
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 30M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 32M)
    
    mysqlreport
    Code:
    MySQL 5.5.30-30.2-log    uptime 0 21:24:28      Fri May 24 02:42:24 2013
    
    __ Key _________________________________________________________________
    Buffer used   620.00k of  75.00M  %Used:   0.81
      Current      13.52M            %Usage:  18.03
    Write hit     100.00%
    Read hit      100.00%
    
    __ Questions ___________________________________________________________
    Total          23.66M   307.0/s
      DMS          12.58M   163.2/s  %Total:  53.17
      Com_          7.17M    93.0/s           30.29
      COM_QUIT      2.37M    30.7/s           10.00
      QC Hits       1.57M    20.4/s            6.63
      -Unknown     21.93k     0.3/s            0.09
    Slow 10 s       3.41M    44.3/s           14.42  %DMS:  27.12  Log:  ON
    DMS            12.58M   163.2/s           53.17
      SELECT        5.45M    70.8/s           23.05         43.36
      UPDATE        4.73M    61.3/s           19.98         37.58
      INSERT        1.49M    19.3/s            6.29         11.82
      DELETE      911.27k    11.8/s            3.85          7.24
      REPLACE           0       0/s            0.00          0.00
    Com_            7.17M    93.0/s           30.29
      set_option    4.76M    61.8/s           20.13
      change_db     2.40M    31.2/s           10.15
      show_table_     319     0.0/s            0.00
    
    __ SELECT and Sort _____________________________________________________
    Scan          922.92k    12.0/s %SELECT:  16.92
    Range         388.60k     5.0/s            7.12
    Full join       1.04M    13.5/s           19.14
    Range check         0       0/s            0.00
    Full rng join       0       0/s            0.00
    Sort scan       1.11M    14.4/s
    Sort range      1.01k     0.0/s
    Sort mrg pass       0       0/s
    
    __ Query Cache _________________________________________________________
    Memory usage   29.04M of  30.00M  %Used:  96.79
    Block Fragmnt   0.09%
    Hits            1.57M    20.4/s
    Inserts         4.01M    52.0/s
    Insrt:Prune   18.46:1    49.2/s
    Hit:Insert     0.39:1
    
    __ Table Locks _________________________________________________________
    Waited             26     0.0/s  %Total:   0.00
    Immediate      16.47M   213.7/s
    
    __ Tables ______________________________________________________________
    Open             4096 of 4096    %Cache: 100.00
    Opened         11.39k     0.1/s
    
    __ Connections _________________________________________________________
    Max used         2442 of 4000      %Max:  61.05
    Total           2.39M    31.0/s
    
    __ Created Temp ________________________________________________________
    Disk table      1.10M    14.2/s
    Table           1.10M    14.3/s    Size:  32.0M
    File              173     0.0/s
    
    __ Threads _____________________________________________________________
    Running             1 of    1
    Cached            499 of  500      %Hit:  99.54
    Created        10.89k     0.1/s
    Slow                0       0/s
    
    __ Aborted _____________________________________________________________
    Clients        43.54k     0.6/s
    Connects          167     0.0/s
    
    __ Bytes _______________________________________________________________
    Sent           10.26G  133.1k/s
    Received        4.80G   62.3k/s
    
    __ InnoDB Buffer Pool __________________________________________________
    Usage         171.33M of  26.00G  %Used:   0.64
    Read hit      100.00%
    Pages
      Free          1.69M            %Total:  99.36
      Data         10.85k                      0.64 %Drty:  11.24
      Misc            111                      0.01
      Latched                                  0.00
    Reads           5.67G   73.6k/s
      From file     6.69k     0.1/s            0.00
      Ahead Rnd         0       0/s
      Ahead Sql                 0/s
    Writes         56.58M   734.1/s
    Flushes       189.33k     2.5/s
    Wait Free           0       0/s
    
    __ InnoDB Lock _________________________________________________________
    Waits          907222    11.8/s
    Current             0
    Time acquiring
      Total     172536675 ms
      Average        1901 ms
      Max           64414 ms
    
    __ InnoDB Data, Pages, Rows ____________________________________________
    Data
      Reads         8.16k     0.1/s
      Writes       12.22M   158.6/s
      fsync        12.05M   156.3/s
      Pending
        Reads           0
        Writes          0
        fsync           0
    
    Pages
      Created       3.04k     0.0/s
      Read          7.87k     0.1/s
      Written     189.33k     2.5/s
    
    Rows
      Deleted       1.50M    19.5/s
      Inserted      1.49M    19.3/s
      Read         14.04G  182.1k/s
      Updated       4.70M    61.0/s
    


    The server is better now, but sometimes with very high load, I think php-fpm crash and I start receiving 502 bad gateway error, and some other time, unable to connect...
     
  11. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    please set join_buffer_size as 4M
    and decrease max connections in mysql to 2000 or less

    remember that after changing the vales, mysql restart is necessary

    also please post the results of pt-query-digest, couse the results should be much different after chaning to innodb
     
  12. sc0t

    sc0t Member

    Joined:
    May 14, 2013
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi,

    I applied the changes and I decreased nginx worker_connections and php-fpm pm.max_children to 2000 as well.

    here the pt-quert-digest
    Code:
    
    # 9.9s user time, 80ms system time, 25.14M rss, 176.51M vsz
    # Current date: Fri May 24 14:29:56 2013
    # Hostname: hostname
    # Files: /var/lib/mysql/mysql-slow.log
    # Overall: 35.12k total, 135 unique, 30.70 QPS, 0.58x concurrency ________
    # Time range: 2013-05-24 14:10:51 to 14:29:55
    # Attribute          total     min     max     avg     95%  stddev  median
    # ============     ======= ======= ======= ======= ======= ======= =======
    # Exec time           667s    55us      1s    19ms    65ms    32ms     2ms
    # Lock time            13s    13us   631ms   375us   159us    10ms    40us
    # Rows sent         16.35k       0   1.45k    0.48    0.99   10.45       0
    # Rows examine      13.53M       0  88.53k  404.04   1.33k   2.73k   49.17
    # Rows affecte      37.50k       0   1.44k    1.09    0.99   14.72       0
    # Rows read         13.53M       0  88.53k  404.04   1.33k   2.73k   49.17
    # Bytes sent        41.32M      11  89.58k   1.20k   3.88k   1.80k   51.63
    # Query size        16.87M      18   1.32k  503.72   1.26k  572.48  107.34
    
    # Profile
    # Rank Query ID           Response time  Calls R/Call V/M   Item
    # ==== ================== ============== ===== ====== ===== ==============
    #    1 0x5DD4D98ED12F60DA 382.8757 57.4%  8399 0.0456  0.01 SELECT xa_ads xa_c xa_ads_ip
    #    2 0x803FB8BAEB9524B3 216.7697 32.5%  3357 0.0646  0.02 SELECT xa_ads xa_c xa_ads_ip
    #    3 0x4317A1B02C705FCA  33.3250  5.0%  8307 0.0040  0.17 DELETE xa_ads_show
    #    4 0x1B1620F55D56834C  24.2520  3.6% 10503 0.0023  0.13 UPDATE xa_c
    # MISC 0xMISC               9.9600  1.5%  4555 0.0022   0.0 <131 ITEMS>
    
    # Query 1: 7.34 QPS, 0.33x concurrency, ID 0x5DD4D98ED12F60DA at byte 20382012
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.01
    # Time range: 2013-05-24 14:10:51 to 14:29:55
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         23    8399
    # Exec time     57    383s   765us   458ms    46ms    68ms    24ms    48ms
    # Lock time      9      1s   109us    82ms   152us   159us   871us   131us
    # Rows sent     42   6.98k       0       1    0.85    0.99    0.35    0.99
    # Rows examine   3 444.04k       0     120   54.14   80.10   19.77   54.21
    # Rows affecte   0       0       0       0       0       0       0       0
    # Rows read      3 444.04k       0     120   54.14   80.10   19.77   54.21
    # Bytes sent    67  27.96M   2.55k   3.94k   3.41k   3.88k  612.70   3.88k
    # Query size    63  10.67M   1.30k   1.30k   1.30k   1.26k       0   1.26k
    # String:
    # Databases    db
    # Hosts        localhost
    # Last errno   0
    # Users        user
    # Query_time distribution
    #   1us
    #  10us
    # 100us  ##
    #   1ms  ########
    #  10ms  ################################################################
    # 100ms  #
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads'\G
    #    SHOW CREATE TABLE `db`.`xa_ads`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_c'\G
    #    SHOW CREATE TABLE `db`.`xa_c`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads_ip'\G
    #    SHOW CREATE TABLE `db`.`xa_ads_ip`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    select xa_ads.ad_type,xa_ads.kind,xa_ads.n,xa_c.camp,xa_c.price_i,xa_ads.norepeat,xa_ads.banner,xa_ads.url,xa_ads.alt,xa_ads.raw,xa_ads.javascript,xa_ads.def,xa_ads.user,xa_c.i_nu,xa_c.zone,xa_c.c_nu,xa_c.unlimit,xa_c.d_valid_by,xa_c.t2,xa_c.emailed,xa_c.i_d_nu,MD5(RAND()) AS m,
    xa_ads.url1,xa_ads.url2,xa_ads.url3,
    xa_ads.text1,xa_ads.text2,xa_ads.text3,xa_ads.text4,xa_ads.text5,xa_ads.text6,xa_ads.text7,xa_ads.text8,xa_ads.text9,xa_ads.text10,
    xa_ads.picture1,xa_ads.picture2,xa_ads.picture3,xa_ads.picture4,xa_ads.picture5 
      FROM (xa_ads,xa_c ,xa_c_countries) left join xa_ads_ip 
      ON xa_c.n = xa_ads_ip.ad AND xa_ads_ip.ip = '198.228.200.166' 
      WHERE (xa_c.zone = '15') AND xa_ads.ad_complete = '1' AND xa_ads.ad_type = '20' AND xa_ads.accept = '1' AND xa_ads.en_w = '1' AND xa_c.en_u = '1' AND xa_c.en_w = '1' AND (xa_c.i_nu>'0' OR xa_c.c_nu>'0' OR xa_c.d_valid_by>'1369398354' OR xa_c.unlimit='1') AND (xa_c.i_d_max='0' OR xa_c.i_d_nu>'0') AND (xa_c.c_d_max='0' OR  xa_c.c_d_nu>'0') AND xa_c.t1<'1369398354' AND xa_c.t2>'1369398354' AND xa_c.d6 = '1' AND xa_c.tz3 = '1' AND (xa_c.some_countr = '0' OR (xa_c_countries.country = 'US' AND xa_c.n = xa_c_countries.ad AND xa_c.camp = xa_c_countries.c)) AND xa_c.n = xa_ads.n AND xa_ads.def = 0 AND xa_ads_ip.ad IS NULL 
      GROUP BY xa_c.n 
      ORDER BY xa_ads.weight desc,m LIMIT 1\G
    
    # Query 2: 2.95 QPS, 0.19x concurrency, ID 0x803FB8BAEB9524B3 at byte 26399255
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.02
    # Time range: 2013-05-24 14:10:56 to 14:29:55
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          9    3357
    # Exec time     32    217s    43ms   811ms    65ms   100ms    33ms    56ms
    # Lock time      3   484ms   110us   480us   144us   176us    18us   138us
    # Rows sent     20   3.28k       1       1       1       1       0       1
    # Rows examine   1 214.49k      49     106   65.43   84.10    9.62   62.76
    # Rows affecte   0       0       0       0       0       0       0       0
    # Rows read      1 214.49k      49     106   65.43   84.10    9.62   62.76
    # Bytes sent    27  11.25M   2.79k   3.94k   3.43k   3.88k  504.56   2.89k
    # Query size    25   4.33M   1.32k   1.32k   1.32k   1.26k       0   1.26k
    # String:
    # Databases    db
    # Hosts        localhost
    # Last errno   0
    # Users        user
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ################################################################
    # 100ms  ####
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads'\G
    #    SHOW CREATE TABLE `db`.`xa_ads`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_c'\G
    #    SHOW CREATE TABLE `db`.`xa_c`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads_ip'\G
    #    SHOW CREATE TABLE `db`.`xa_ads_ip`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    select xa_ads.ad_type,xa_ads.kind,xa_ads.n,xa_c.camp,xa_c.price_i,xa_ads.norepeat,xa_ads.banner,xa_ads.url,xa_ads.alt,xa_ads.raw,xa_ads.javascript,xa_ads.def,xa_ads.user,xa_c.i_nu,xa_c.zone,xa_c.c_nu,xa_c.unlimit,xa_c.d_valid_by,xa_c.t2,xa_c.emailed,xa_c.i_d_nu,MD5(RAND()) AS m,
    xa_ads.url1,xa_ads.url2,xa_ads.url3,
    xa_ads.text1,xa_ads.text2,xa_ads.text3,xa_ads.text4,xa_ads.text5,xa_ads.text6,xa_ads.text7,xa_ads.text8,xa_ads.text9,xa_ads.text10,
    xa_ads.picture1,xa_ads.picture2,xa_ads.picture3,xa_ads.picture4,xa_ads.picture5 
      FROM (xa_ads,xa_c ,xa_c_countries) left join xa_ads_ip 
      ON xa_c.n = xa_ads_ip.ad AND xa_ads_ip.ip = '168.61.9.122' 
      WHERE (xa_c.zone = '15' OR xa_c.zone = '47') AND xa_ads.ad_complete = '1' AND xa_ads.ad_type = '20' AND xa_ads.accept = '1' AND xa_ads.en_w = '1' AND xa_c.en_u = '1' AND xa_c.en_w = '1' AND (xa_c.i_nu>'0' OR xa_c.c_nu>'0' OR xa_c.d_valid_by>'1369398550' OR xa_c.unlimit='1') AND (xa_c.i_d_max='0' OR xa_c.i_d_nu>'0') AND (xa_c.c_d_max='0' OR  xa_c.c_d_nu>'0') AND xa_c.t1<'1369398550' AND xa_c.t2>'1369398550' AND xa_c.d6 = '1' AND xa_c.tz3 = '1' AND (xa_c.some_countr = '0' OR (xa_c_countries.country = 'US' AND xa_c.n = xa_c_countries.ad AND xa_c.camp = xa_c_countries.c)) AND xa_c.n = xa_ads.n AND xa_ads.def = 0 AND xa_ads_ip.ad IS NULL 
      GROUP BY xa_c.n 
      ORDER BY xa_ads.weight desc,m LIMIT 1\G
    
    # Query 3: 7.26 QPS, 0.03x concurrency, ID 0x4317A1B02C705FCA at byte 26392570
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.17
    # Time range: 2013-05-24 14:10:51 to 14:29:55
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         23    8307
    # Exec time      4     33s     1ms      1s     4ms     4ms    26ms     2ms
    # Lock time     52      7s    13us   631ms   830us    30us    18ms    18us
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine  61   8.30M     560   1.41k   1.02k   1.33k  292.41   1.14k
    # Rows affecte  24   9.17k       0     168    1.13    8.91    3.74       0
    # Rows read     61   8.30M     560   1.41k   1.02k   1.33k  292.41   1.14k
    # Bytes sent     0  89.24k      11      11      11      11       0      11
    # Query size     2 397.50k      49      49      49      49       0      49
    # String:
    # Databases    db
    # Hosts        localhost
    # Last errno   0
    # Users        user
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms  ################################################################
    #  10ms  #
    # 100ms  #
    #    1s  #
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads_show'\G
    #    SHOW CREATE TABLE `db`.`xa_ads_show`\G
    delete from xa_ads_show where time < '1369398429'\G
    # Converted for EXPLAIN
    # EXPLAIN /*!50100 PARTITIONS*/
    select * from  xa_ads_show where time < '1369398429'\G
    
    # Query 4: 9.18 QPS, 0.02x concurrency, ID 0x1B1620F55D56834C at byte 26392914
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.13
    # Time range: 2013-05-24 14:10:51 to 14:29:55
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         29   10503
    # Exec time      3     24s   505us      1s     2ms     3ms    17ms     1ms
    # Lock time     18      2s    21us   570ms   233us   113us     8ms    38us
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine   2 369.25k      36      36      36      36       0      36
    # Rows affecte  27  10.26k       1       1       1       1       0       1
    # Rows read      2 369.25k      36      36      36      36       0      36
    # Bytes sent     1 533.36k      52      52      52      52       0      52
    # Query size     6   1.11M     110     111  110.41  107.34       0  107.34
    # String:
    # Databases    db
    # Hosts        localhost
    # Last errno   0
    # Users        user
    # Query_time distribution
    #   1us
    #  10us
    # 100us  ##############
    #   1ms  ################################################################
    #  10ms  #
    # 100ms  #
    #    1s  #
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_c'\G
    #    SHOW CREATE TABLE `db`.`xa_c`\G
    update xa_c set last = '1369398549', i_got = i_got + 1, r = (c_got/i_got)*100 where (n = '802' AND camp = '1')\G
    # Converted for EXPLAIN
    # EXPLAIN /*!50100 PARTITIONS*/
    select  last = '1369398549', i_got = i_got + 1, r = (c_got/i_got)*100 from xa_c where  (n = '802' AND camp = '1')\G
    
    Thank you
     
  13. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    # Time range: 2013-05-24 14:10:51 to 14:29:55


    Can you update pt-query-digest a bit, so we get the results from longer period

    Based on current results, time of execution dropped a lot :) part is becouse table lock

    and please run explain for those 2 queries:

    explain select xa_ads.ad_type,xa_ads.kind,xa_ads.n,xa_c.camp,xa_c.price_i,xa_ads.norepeat,xa_ads.banner,xa_ads.url,xa_ads.alt,xa_ads.raw,xa_ads.javascript,xa_ads.def,xa_ads.user,xa_c.i_nu,xa_c.zone,xa_c.c_nu,xa_c.unlimit,xa_c.d_valid_by,xa_c.t2,xa_c.emailed,xa_c.i_d_nu,MD5(RAND()) AS m,
    xa_ads.url1,xa_ads.url2,xa_ads.url3,
    xa_ads.text1,xa_ads.text2,xa_ads.text3,xa_ads.text4,xa_ads.text5,xa_ads.text6,xa_ads.text7,xa_ads.text8,xa_ads.text9,xa_ads.text10,
    xa_ads.picture1,xa_ads.picture2,xa_ads.picture3,xa_ads.picture4,xa_ads.picture5
    FROM (xa_ads,xa_c ,xa_c_countries) left join xa_ads_ip
    ON xa_c.n = xa_ads_ip.ad AND xa_ads_ip.ip = '198.228.200.166'
    WHERE (xa_c.zone = '15') AND xa_ads.ad_complete = '1' AND xa_ads.ad_type = '20' AND xa_ads.accept = '1' AND xa_ads.en_w = '1' AND xa_c.en_u = '1' AND xa_c.en_w = '1' AND (xa_c.i_nu>'0' OR xa_c.c_nu>'0' OR xa_c.d_valid_by>'1369398354' OR xa_c.unlimit='1') AND (xa_c.i_d_max='0' OR xa_c.i_d_nu>'0') AND (xa_c.c_d_max='0' OR xa_c.c_d_nu>'0') AND xa_c.t1<'1369398354' AND xa_c.t2>'1369398354' AND xa_c.d6 = '1' AND xa_c.tz3 = '1' AND (xa_c.some_countr = '0' OR (xa_c_countries.country = 'US' AND xa_c.n = xa_c_countries.ad AND xa_c.camp = xa_c_countries.c)) AND xa_c.n = xa_ads.n AND xa_ads.def = 0 AND xa_ads_ip.ad IS NULL
    GROUP BY xa_c.n
    ORDER BY xa_ads.weight desc,m LIMIT 1



    explain select xa_ads.ad_type,xa_ads.kind,xa_ads.n,xa_c.camp,xa_c.price_i,xa_ads.norepeat,xa_ads.banner,xa_ads.url,xa_ads.alt,xa_ads.raw,xa_ads.javascript,xa_ads.def,xa_ads.user,xa_c.i_nu,xa_c.zone,xa_c.c_nu,xa_c.unlimit,xa_c.d_valid_by,xa_c.t2,xa_c.emailed,xa_c.i_d_nu,MD5(RAND()) AS m,
    xa_ads.url1,xa_ads.url2,xa_ads.url3,
    xa_ads.text1,xa_ads.text2,xa_ads.text3,xa_ads.text4,xa_ads.text5,xa_ads.text6,xa_ads.text7,xa_ads.text8,xa_ads.text9,xa_ads.text10,
    xa_ads.picture1,xa_ads.picture2,xa_ads.picture3,xa_ads.picture4,xa_ads.picture5
    FROM (xa_ads,xa_c ,xa_c_countries) left join xa_ads_ip
    ON xa_c.n = xa_ads_ip.ad AND xa_ads_ip.ip = '168.61.9.122'
    WHERE (xa_c.zone = '15' OR xa_c.zone = '47') AND xa_ads.ad_complete = '1' AND xa_ads.ad_type = '20' AND xa_ads.accept = '1' AND xa_ads.en_w = '1' AND xa_c.en_u = '1' AND xa_c.en_w = '1' AND (xa_c.i_nu>'0' OR xa_c.c_nu>'0' OR xa_c.d_valid_by>'1369398550' OR xa_c.unlimit='1') AND (xa_c.i_d_max='0' OR xa_c.i_d_nu>'0') AND (xa_c.c_d_max='0' OR xa_c.c_d_nu>'0') AND xa_c.t1<'1369398550' AND xa_c.t2>'1369398550' AND xa_c.d6 = '1' AND xa_c.tz3 = '1' AND (xa_c.some_countr = '0' OR (xa_c_countries.country = 'US' AND xa_c.n = xa_c_countries.ad AND xa_c.camp = xa_c_countries.c)) AND xa_c.n = xa_ads.n AND xa_ads.def = 0 AND xa_ads_ip.ad IS NULL
    GROUP BY xa_c.n
    ORDER BY xa_ads.weight desc,m LIMIT 1
     
  14. sc0t

    sc0t Member

    Joined:
    May 14, 2013
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi,

    The first explain:
    Code:
    +----+-------------+----------------+--------+---------------+------+---------+---------------------------+------+----------------------------------------------+
    | id | select_type | table          | type   | possible_keys | key  | key_len | ref                       | rows | Extra                                        |
    +----+-------------+----------------+--------+---------------+------+---------+---------------------------+------+----------------------------------------------+
    |  1 | SIMPLE      | xa_c           | ALL    | work          | NULL | NULL    | NULL                      |   36 | Using where; Using temporary; Using filesort |
    |  1 | SIMPLE      | xa_ads         | eq_ref | n,work        | n    | 4       | xteeendaaadverrttt.xa_c.n |    1 | Using where                                  |
    |  1 | SIMPLE      | xa_ads_ip      | ref    | ip            | ip   | 257     | const                     |    1 | Using where; Not exists                      |
    |  1 | SIMPLE      | xa_c_countries | index  | work          | work | 12      | NULL                      | 1521 | Using where; Using index; Using join buffer  |
    +----+-------------+----------------+--------+---------------+------+---------+---------------------------+------+----------------------------------------------+
    4 rows in set (0.00 sec)
    The second explain:
    Code:
    +----+-------------+----------------+--------+---------------+------+---------+---------------------------+------+----------------------------------------------+
    | id | select_type | table          | type   | possible_keys | key  | key_len | ref                       | rows | Extra                                        |
    +----+-------------+----------------+--------+---------------+------+---------+---------------------------+------+----------------------------------------------+
    |  1 | SIMPLE      | xa_c           | ALL    | work          | NULL | NULL    | NULL                      |   36 | Using where; Using temporary; Using filesort |
    |  1 | SIMPLE      | xa_ads         | eq_ref | n,work        | n    | 4       | xteeendaaadverrttt.xa_c.n |    1 | Using where                                  |
    |  1 | SIMPLE      | xa_ads_ip      | ref    | ip            | ip   | 257     | const                     |    1 | Using where; Not exists                      |
    |  1 | SIMPLE      | xa_c_countries | index  | work          | work | 12      | NULL                      | 1521 | Using where; Using index; Using join buffer  |
    +----+-------------+----------------+--------+---------------+------+---------+---------------------------+------+----------------------------------------------+
    4 rows in set (0.00 sec)
    
    the new pt-digest:

    Code:
    # 162.9s user time, 950ms system time, 41.13M rss, 192.55M vsz
    # Current date: Fri May 24 20:16:55 2013
    # Hostname: hostname
    # Files: /var/lib/mysql/mysql-slow.log
    # Overall: 599.71k total, 534 unique, 27.31 QPS, 0.51x concurrency _______
    # Time range: 2013-05-24 14:10:51 to 20:16:54
    # Attribute          total     min     max     avg     95%  stddev  median
    # ============     ======= ======= ======= ======= ======= ======= =======
    # Exec time         11188s    33us      8s    19ms    61ms    65ms     2ms
    # Lock time           555s       0      8s   924us   159us    49ms    40us
    # Rows sent        835.65k       0 163.71k    1.43    0.99  306.60       0
    # Rows examine     229.59M       0 163.71k  401.44   1.09k   3.09k   49.17
    # Rows affecte     502.77k       0   1.44k    0.86    0.99    5.97       0
    # Rows read        229.59M       0 163.71k  401.44   1.09k   3.09k   49.17
    # Bytes sent       761.64M       0   6.23M   1.30k   3.88k  15.79k   51.63
    # Query size       283.30M      18   1.32k  495.35   1.26k  570.07  107.34
    
    # Profile
    # Rank Query ID           Response time   Calls  R/Call V/M   Item
    # ==== ================== =============== ====== ====== ===== ============
    #    1 0x5DD4D98ED12F60DA 7667.6823 68.5% 163530 0.0469  0.04 SELECT xa_ads xa_c xa_ads_ip
    #    2 0x803FB8BAEB9524B3 2082.1449 18.6%  33648 0.0619  0.03 SELECT xa_ads xa_c xa_ads_ip
    #    3 0x1B1620F55D56834C  734.5092  6.6% 172953 0.0042  1.82 UPDATE xa_c
    #    4 0x4317A1B02C705FCA  512.1934  4.6% 140834 0.0036  0.82 DELETE xa_ads_show
    # MISC 0xMISC              191.6069  1.7%  88742 0.0022   0.0 <530 ITEMS>
    
    # Query 1: 7.45 QPS, 0.35x concurrency, ID 0x5DD4D98ED12F60DA at byte 46954588
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.04
    # Time range: 2013-05-24 14:10:51 to 20:16:54
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         27  163530
    # Exec time     68   7668s   757us      4s    47ms    68ms    43ms    48ms
    # Lock time      4     25s   108us   230ms   153us   159us     1ms   131us
    # Rows sent     16 136.04k       0       1    0.85    0.99    0.35    0.99
    # Rows examine   3   8.73M       0     120   56.01   84.10   19.36   56.92
    # Rows affecte   0       0       0       0       0       0       0       0
    # Rows read      3   8.73M       0     120   56.01   84.10   19.36   56.92
    # Bytes sent    72 554.89M   2.55k   3.94k   3.47k   3.88k  600.99   3.88k
    # Query size    73 207.83M   1.30k   1.30k   1.30k   1.26k       0   1.26k
    # String:
    # Databases    db
    # Hosts        localhost
    # Last errno   0
    # Users        user
    # Query_time distribution
    #   1us
    #  10us
    # 100us  #
    #   1ms  #########
    #  10ms  ################################################################
    # 100ms  #
    #    1s  #
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads'\G
    #    SHOW CREATE TABLE `db`.`xa_ads`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_c'\G
    #    SHOW CREATE TABLE `db`.`xa_c`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads_ip'\G
    #    SHOW CREATE TABLE `db`.`xa_ads_ip`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    select xa_ads.ad_type,xa_ads.kind,xa_ads.n,xa_c.camp,xa_c.price_i,xa_ads.norepeat,xa_ads.banner,xa_ads.url,xa_ads.alt,xa_ads.raw,xa_ads.javascript,xa_ads.def,xa_ads.user,xa_c.i_nu,xa_c.zone,xa_c.c_nu,xa_c.unlimit,xa_c.d_valid_by,xa_c.t2,xa_c.emailed,xa_c.i_d_nu,MD5(RAND()) AS m,
    xa_ads.url1,xa_ads.url2,xa_ads.url3,
    xa_ads.text1,xa_ads.text2,xa_ads.text3,xa_ads.text4,xa_ads.text5,xa_ads.text6,xa_ads.text7,xa_ads.text8,xa_ads.text9,xa_ads.text10,
    xa_ads.picture1,xa_ads.picture2,xa_ads.picture3,xa_ads.picture4,xa_ads.picture5 
      FROM (xa_ads,xa_c ,xa_c_countries) left join xa_ads_ip 
      ON xa_c.n = xa_ads_ip.ad AND xa_ads_ip.ip = '50.112.44.137' 
      WHERE (xa_c.zone = '15') AND xa_ads.ad_complete = '1' AND xa_ads.ad_type = '20' AND xa_ads.accept = '1' AND xa_ads.en_w = '1' AND xa_c.en_u = '1' AND xa_c.en_w = '1' AND (xa_c.i_nu>'0' OR xa_c.c_nu>'0' OR xa_c.d_valid_by>'1369399247' OR xa_c.unlimit='1') AND (xa_c.i_d_max='0' OR xa_c.i_d_nu>'0') AND (xa_c.c_d_max='0' OR  xa_c.c_d_nu>'0') AND xa_c.t1<'1369399247' AND xa_c.t2>'1369399247' AND xa_c.d6 = '1' AND xa_c.tz3 = '1' AND (xa_c.some_countr = '0' OR (xa_c_countries.country = 'US' AND xa_c.n = xa_c_countries.ad AND xa_c.camp = xa_c_countries.c)) AND xa_c.n = xa_ads.n AND xa_ads.def = 0 AND xa_ads_ip.ad IS NULL 
      GROUP BY xa_c.n 
      ORDER BY xa_ads.weight desc,m LIMIT 1\G
    
    # Query 2: 1.53 QPS, 0.09x concurrency, ID 0x803FB8BAEB9524B3 at byte 46771579
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.03
    # Time range: 2013-05-24 14:10:56 to 20:16:41
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          5   33648
    # Exec time     18   2082s    41ms      4s    62ms    95ms    44ms    53ms
    # Lock time      0      5s   109us     3ms   146us   176us    28us   138us
    # Rows sent      3  32.86k       1       1       1       1       0       1
    # Rows examine   0   2.18M      49     139   68.09   88.31   12.18   65.89
    # Rows affecte   0       0       0       0       0       0       0       0
    # Rows read      0   2.18M      49     139   68.09   88.31   12.18   65.89
    # Bytes sent    15 116.69M   2.79k   3.94k   3.55k   3.88k  496.04   3.88k
    # Query size    15  43.41M   1.32k   1.32k   1.32k   1.26k       0   1.26k
    # String:
    # Databases    db
    # Hosts        localhost
    # Last errno   0
    # Users        user
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ################################################################
    # 100ms  ###
    #    1s  #
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads'\G
    #    SHOW CREATE TABLE `db`.`xa_ads`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_c'\G
    #    SHOW CREATE TABLE `db`.`xa_c`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads_ip'\G
    #    SHOW CREATE TABLE `db`.`xa_ads_ip`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    select xa_ads.ad_type,xa_ads.kind,xa_ads.n,xa_c.camp,xa_c.price_i,xa_ads.norepeat,xa_ads.banner,xa_ads.url,xa_ads.alt,xa_ads.raw,xa_ads.javascript,xa_ads.def,xa_ads.user,xa_c.i_nu,xa_c.zone,xa_c.c_nu,xa_c.unlimit,xa_c.d_valid_by,xa_c.t2,xa_c.emailed,xa_c.i_d_nu,MD5(RAND()) AS m,
    xa_ads.url1,xa_ads.url2,xa_ads.url3,
    xa_ads.text1,xa_ads.text2,xa_ads.text3,xa_ads.text4,xa_ads.text5,xa_ads.text6,xa_ads.text7,xa_ads.text8,xa_ads.text9,xa_ads.text10,
    xa_ads.picture1,xa_ads.picture2,xa_ads.picture3,xa_ads.picture4,xa_ads.picture5 
      FROM (xa_ads,xa_c ,xa_c_countries) left join xa_ads_ip 
      ON xa_c.n = xa_ads_ip.ad AND xa_ads_ip.ip = '80.241.223.155' 
      WHERE (xa_c.zone = '1' OR xa_c.zone = '46') AND xa_ads.ad_complete = '1' AND xa_ads.ad_type = '1' AND xa_ads.accept = '1' AND xa_ads.en_w = '1' AND xa_c.en_u = '1' AND xa_c.en_w = '1' AND (xa_c.i_nu>'0' OR xa_c.c_nu>'0' OR xa_c.d_valid_by>'1369399235' OR xa_c.unlimit='1') AND (xa_c.i_d_max='0' OR xa_c.i_d_nu>'0') AND (xa_c.c_d_max='0' OR  xa_c.c_d_nu>'0') AND xa_c.t1<'1369399235' AND xa_c.t2>'1369399235' AND xa_c.d6 = '1' AND xa_c.tz3 = '1' AND (xa_c.some_countr = '0' OR (xa_c_countries.country = 'DE' AND xa_c.n = xa_c_countries.ad AND xa_c.camp = xa_c_countries.c)) AND xa_c.n = xa_ads.n AND xa_ads.def = 0 AND xa_ads_ip.ad IS NULL 
      GROUP BY xa_c.n 
      ORDER BY xa_ads.weight desc,m LIMIT 1\G
    
    # Query 3: 7.87 QPS, 0.03x concurrency, ID 0x1B1620F55D56834C at byte 46991749
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 1.82
    # Time range: 2013-05-24 14:10:51 to 20:16:54
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         28  172953
    # Exec time      6    735s   505us      8s     4ms     3ms    88ms     1ms
    # Lock time     66    367s    20us      7s     2ms   108us    79ms    40us
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine   2   5.94M      36      36      36      36       0      36
    # Rows affecte  33 168.90k       1       1       1       1       0       1
    # Rows read      2   5.94M      36      36      36      36       0      36
    # Bytes sent     1   8.58M      52      52      52      52       0      52
    # Query size     6  18.19M     110     111  110.31  107.34       0  107.34
    # String:
    # Databases    db
    # Hosts        localhost
    # Last errno   0
    # Users        user
    # Query_time distribution
    #   1us
    #  10us
    # 100us  ############
    #   1ms  ################################################################
    #  10ms  #
    # 100ms  #
    #    1s  #
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_c'\G
    #    SHOW CREATE TABLE `db`.`xa_c`\G
    update xa_c set last = '1369399243', i_got = i_got + 1, r = (c_got/i_got)*100 where (n = '799' AND camp = '1')\G
    # Converted for EXPLAIN
    # EXPLAIN /*!50100 PARTITIONS*/
    select  last = '1369399243', i_got = i_got + 1, r = (c_got/i_got)*100 from xa_c where  (n = '799' AND camp = '1')\G
    
    # Query 4: 6.41 QPS, 0.02x concurrency, ID 0x4317A1B02C705FCA at byte 47003884
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.82
    # Time range: 2013-05-24 14:10:51 to 20:16:54
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         23  140834
    # Exec time      4    512s   866us      8s     4ms     4ms    55ms     2ms
    # Lock time     17     98s    12us      8s   698us    31us    46ms    19us
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine  56 130.19M     482   1.46k  969.33   1.26k  203.12  964.41
    # Rows affecte  32 164.36k       0     168    1.20    8.91    3.26       0
    # Rows read     56 130.19M     482   1.46k  969.33   1.26k  203.12  964.41
    # Bytes sent     0   1.48M      11      11      11      11       0      11
    # Query size     2   6.58M      49      49      49      49       0      49
    # String:
    # Databases    db
    # Hosts        localhost
    # Last errno   0
    # Users        user
    # Query_time distribution
    #   1us
    #  10us
    # 100us  #
    #   1ms  ################################################################
    #  10ms  #
    # 100ms  #
    #    1s  #
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads_show'\G
    #    SHOW CREATE TABLE `db`.`xa_ads_show`\G
    delete from xa_ads_show where time < '1369399124'\G
    # Converted for EXPLAIN
    # EXPLAIN /*!50100 PARTITIONS*/
    select * from  xa_ads_show where time < '1369399124'\G
    
    Thank you
     
  15. sc0t

    sc0t Member

    Joined:
    May 14, 2013
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hello,

    I have found in mysql-error.log these:
    ...
    130526 15:54:49 [Warning] Too many connections
    130526 15:54:49 [Warning] Too many connections
    130526 15:54:49 [Warning] Too many connections
    130526 15:54:49 [Warning] Too many connections
    130526 15:54:49 [Warning] Too many connections
    130526 15:54:49 [Warning] Too many connections
    130526 15:54:49 [Warning] Too many connections
    130526 15:54:49 [Warning] Too many connections
    130526 15:54:49 [Warning] Too many connections
    130526 15:54:49 [Warning] Too many connections

    and many warning /notice in php-fpm logs:
    WARNING: [pool www] child 14608, script '.../show.php' (request: "GET /show.php") execution timed out
    ... and
    WARNING: [pool www] child 15896 exited on signal 15 (SIGTERM) after 26.137603 seconds from start
     
  16. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Add index on xa_c.n

    just click index in phpadminarea in xa_c table near n key
    then run explain again

    explain select xa_ads.ad_type,xa_ads.kind,xa_ads.n,xa_c.camp,xa_c.price_i,xa_ads.norepeat,xa_ads.banner,xa_ads.url,xa_ads.alt,xa_ads.raw,xa_ads.javascript,xa_ads.def,xa_ads.user,xa_c.i_nu,xa_c.zone,xa_c.c_nu,xa_c.unlimit,xa_c.d_valid_by,xa_c.t2,xa_c.emailed,xa_c.i_d_nu,MD5(RAND()) AS m,
    xa_ads.url1,xa_ads.url2,xa_ads.url3,
    xa_ads.text1,xa_ads.text2,xa_ads.text3,xa_ads.text4,xa_ads.text5,xa_ads.text6,xa_ads.text7,xa_ads.text8,xa_ads.text9,xa_ads.text10,
    xa_ads.picture1,xa_ads.picture2,xa_ads.picture3,xa_ads.picture4,xa_ads.picture5
    FROM (xa_ads,xa_c ,xa_c_countries) left join xa_ads_ip
    ON xa_c.n = xa_ads_ip.ad AND xa_ads_ip.ip = '50.112.44.137'
    WHERE (xa_c.zone = '15') AND xa_ads.ad_complete = '1' AND xa_ads.ad_type = '20' AND xa_ads.accept = '1' AND xa_ads.en_w = '1' AND xa_c.en_u = '1' AND xa_c.en_w = '1' AND (xa_c.i_nu>'0' OR xa_c.c_nu>'0' OR xa_c.d_valid_by>'1369399247' OR xa_c.unlimit='1') AND (xa_c.i_d_max='0' OR xa_c.i_d_nu>'0') AND (xa_c.c_d_max='0' OR xa_c.c_d_nu>'0') AND xa_c.t1<'1369399247' AND xa_c.t2>'1369399247' AND xa_c.d6 = '1' AND xa_c.tz3 = '1' AND (xa_c.some_countr = '0' OR (xa_c_countries.country = 'US' AND xa_c.n = xa_c_countries.ad AND xa_c.camp = xa_c_countries.c)) AND xa_c.n = xa_ads.n AND xa_ads.def = 0 AND xa_ads_ip.ad IS NULL
    GROUP BY xa_c.n
    ORDER BY xa_ads.weight desc,m LIMIT 1
     
  17. sc0t

    sc0t Member

    Joined:
    May 14, 2013
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi,
    Index Added: ALTER TABLE `xa_c` ADD INDEX ( `n` ) ;
    then explain:
    +----+-------------+----------------+-------+---------------+------+---------+-----------------------------+------+----------------------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+----------------+-------+---------------+------+---------+-----------------------------+------+----------------------------------------------+
    | 1 | SIMPLE | xa_ads | ref | n,work | work | 6 | const,const,const | 8 | Using where; Using temporary; Using filesort |
    | 1 | SIMPLE | xa_c | ref | work,n | n | 4 | xteeendaaadverrttt.xa_ads.n | 1 | Using where |
    | 1 | SIMPLE | xa_ads_ip | ref | ip | ip | 257 | const | 1 | Using where; Not exists |
    | 1 | SIMPLE | xa_c_countries | index | work | work | 12 | NULL | 1270 | Using where; Using index; Using join buffer |
    +----+-------------+----------------+-------+---------------+------+---------+-----------------------------+------+----------------------------------------------+
    4 rows in set (0.00 sec)


    Thank you
     
  18. sc0t

    sc0t Member

    Joined:
    May 14, 2013
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Here another digest:
    Code:
    # 18.3s user time, 100ms system time, 30.68M rss, 212.41M vsz
    # Current date: Mon May 27 03:33:54 2013
    # Hostname: hostname
    # Files: /var/lib/mysql/mysql-slow.log
    # Overall: 64.54k total, 234 unique, 11.77 QPS, 0.20x concurrency ________
    # Time range: 2013-05-27 02:02:28 to 03:33:53
    # Attribute          total     min     max     avg     95%  stddev  median
    # ============     ======= ======= ======= ======= ======= ======= =======
    # Exec time          1086s    52us      3s    17ms    53ms    26ms     1ms
    # Lock time             6s    13us    97ms    95us   159us   952us    31us
    # Rows sent         58.00k       0   1.45k    0.92    0.99    8.16    0.99
    # Rows examine      18.68M       0  91.97k  303.57  592.07   3.18k   84.10
    # Rows affecte      23.59k       0      83    0.37    2.90    1.78       0
    # Rows read         18.68M       0  91.97k  303.57  592.07   3.18k   84.10
    # Bytes sent       125.58M      11  89.54k   1.99k   3.88k   3.57k   69.19
    # Query size        35.21M      18   1.45k  572.11   1.39k  630.60   92.72
    
    # Profile
    # Rank Query ID           Response time  Calls R/Call V/M   Item
    # ==== ================== ============== ===== ====== ===== ==============
    #    1 0xE143CD37EECA220E 849.7214 78.2% 17321 0.0491  0.00 SELECT xa_ads xa_c xa_ads_ip
    #    2 0xBE560506E570C3F5 103.0933  9.5%  4038 0.0255  0.01 SELECT xa_ads xa_c xa_ads_ip
    #    3 0xD98203F9E0031F40  48.2483  4.4%   893 0.0540  0.00 SELECT xa_ads xa_c xa_ads_ip
    #    4 0x9FF7BB7E34A66360  37.7892  3.5%   765 0.0494  0.00 SELECT xa_ads xa_c xa_ads_ip
    # MISC 0xMISC              47.4693  4.4% 41522 0.0011   0.0 <230 ITEMS>
    
    # Query 1: 3.16 QPS, 0.15x concurrency, ID 0xE143CD37EECA220E at byte 0 __
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-05-27 02:02:28 to 03:33:53
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         26   17321
    # Exec time     78    850s    40ms   174ms    49ms    59ms     6ms    46ms
    # Lock time     45      3s   128us    79ms   162us   176us   595us   152us
    # Rows sent     29  16.92k       1       1       1       1       0       1
    # Rows examine   8   1.55M      86     131   93.97  107.34    7.78   92.72
    # Rows affecte   0       0       0       0       0       0       0       0
    # Rows read      8   1.55M      86     131   93.97  107.34    7.78   92.72
    # Bytes sent    51  64.98M   2.79k   3.94k   3.84k   3.88k  305.04   3.88k
    # Query size    67  23.81M   1.40k   1.41k   1.41k   1.39k       0   1.39k
    # String:
    # Databases    db
    # Hosts        localhost
    # Last errno   0
    # Users        user
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ################################################################
    # 100ms  #
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads'\G
    #    SHOW CREATE TABLE `db`.`xa_ads`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_c'\G
    #    SHOW CREATE TABLE `db`.`xa_c`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads_ip'\G
    #    SHOW CREATE TABLE `db`.`xa_ads_ip`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    select xa_ads.ad_type,xa_ads.kind,xa_ads.n,xa_c.camp,xa_c.price_i,xa_ads.norepeat,xa_ads.banner,xa_ads.url,xa_ads.alt,xa_ads.raw,xa_ads.javascript,xa_ads.def,xa_ads.user,xa_c.i_nu,xa_c.zone,xa_c.c_nu,xa_c.unlimit,xa_c.d_valid_by,xa_c.t2,xa_c.emailed,xa_c.i_d_nu,MD5(RAND()) AS m,
    xa_ads.url1,xa_ads.url2,xa_ads.url3,
    xa_ads.text1,xa_ads.text2,xa_ads.text3,xa_ads.text4,xa_ads.text5,xa_ads.text6,xa_ads.text7,xa_ads.text8,xa_ads.text9,xa_ads.text10,
    xa_ads.picture1,xa_ads.picture2,xa_ads.picture3,xa_ads.picture4,xa_ads.picture5 
      FROM (xa_ads,xa_c ,xa_c_countries ,xa_c_cats) left join xa_ads_ip 
      ON xa_c.n = xa_ads_ip.ad AND xa_ads_ip.ip = '3495707356' 
      WHERE (xa_c.zone = '16') AND xa_ads.ad_complete = '1' AND xa_ads.ad_type = '17' AND xa_ads.accept = '1' AND xa_ads.en_w = '1' AND xa_c.en_u = '1' AND xa_c.en_w = '1' AND (xa_c.i_nu>'0' OR xa_c.c_nu>'0' OR xa_c.d_valid_by>'1369612948' OR xa_c.unlimit='1') AND (xa_c.i_d_max='0' OR xa_c.i_d_nu>'0') AND (xa_c.c_d_max='0' OR  xa_c.c_d_nu>'0') AND xa_c.t1<'1369612948' AND xa_c.t2>'1369612948' AND xa_c.d2 = '1' AND xa_c.tz1 = '1' AND (xa_c.some_countr = '0' OR (xa_c_countries.country = 'US' AND xa_c.n = xa_c_countries.ad AND xa_c.camp = xa_c_countries.c)) AND (xa_c.n = xa_c_cats.ad AND xa_c.camp = xa_c_cats.c AND (xa_c_cats.c1 = 0 or xa_c_cats.c1 = 89)) AND xa_c.n = xa_ads.n AND xa_ads.def = 0 AND xa_ads_ip.ad IS NULL 
      GROUP BY xa_c.n 
      ORDER BY xa_ads.weight desc,m LIMIT 1\G
    
    # Query 2: 0.74 QPS, 0.02x concurrency, ID 0xBE560506E570C3F5 at byte 10320027
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.01
    # Time range: 2013-05-27 02:02:28 to 03:33:48
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          6    4038
    # Exec time      9    103s   763us    67ms    26ms    51ms    19ms    16ms
    # Lock time      9   596ms   120us   683us   147us   167us    14us   144us
    # Rows sent      4   2.73k       0       1    0.69    0.99    0.46    0.99
    # Rows examine   0  89.93k       0      71   22.81   46.83   15.91   26.08
    # Rows affecte   0       0       0       0       0       0       0       0
    # Rows read      0  89.93k       0      71   22.81   46.83   15.91   26.08
    # Bytes sent    10  12.61M   2.55k   3.94k   3.20k   3.88k  627.71   2.89k
    # Query size    14   5.13M   1.30k   1.30k   1.30k   1.26k       0   1.26k
    # String:
    # Databases    db
    # Hosts        localhost
    # Last errno   0
    # Users        user
    # Query_time distribution
    #   1us
    #  10us
    # 100us  ##
    #   1ms  #########################
    #  10ms  ################################################################
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads'\G
    #    SHOW CREATE TABLE `db`.`xa_ads`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_c'\G
    #    SHOW CREATE TABLE `db`.`xa_c`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads_ip'\G
    #    SHOW CREATE TABLE `db`.`xa_ads_ip`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    select xa_ads.ad_type,xa_ads.kind,xa_ads.n,xa_c.camp,xa_c.price_i,xa_ads.norepeat,xa_ads.banner,xa_ads.url,xa_ads.alt,xa_ads.raw,xa_ads.javascript,xa_ads.def,xa_ads.user,xa_c.i_nu,xa_c.zone,xa_c.c_nu,xa_c.unlimit,xa_c.d_valid_by,xa_c.t2,xa_c.emailed,xa_c.i_d_nu,MD5(RAND()) AS m,
    xa_ads.url1,xa_ads.url2,xa_ads.url3,
    xa_ads.text1,xa_ads.text2,xa_ads.text3,xa_ads.text4,xa_ads.text5,xa_ads.text6,xa_ads.text7,xa_ads.text8,xa_ads.text9,xa_ads.text10,
    xa_ads.picture1,xa_ads.picture2,xa_ads.picture3,xa_ads.picture4,xa_ads.picture5 
      FROM (xa_ads,xa_c ,xa_c_countries ) left join xa_ads_ip 
      ON xa_c.n = xa_ads_ip.ad AND xa_ads_ip.ip = '3351074052' 
      WHERE (xa_c.zone = '16') AND xa_ads.ad_complete = '1' AND xa_ads.ad_type = '17' AND xa_ads.accept = '1' AND xa_ads.en_w = '1' AND xa_c.en_u = '1' AND xa_c.en_w = '1' AND (xa_c.i_nu>'0' OR xa_c.c_nu>'0' OR xa_c.d_valid_by>'1369614030' OR xa_c.unlimit='1') AND (xa_c.i_d_max='0' OR xa_c.i_d_nu>'0') AND (xa_c.c_d_max='0' OR  xa_c.c_d_nu>'0') AND xa_c.t1<'1369614030' AND xa_c.t2>'1369614030' AND xa_c.d2 = '1' AND xa_c.tz1 = '1' AND (xa_c.some_countr = '0' OR (xa_c_countries.country = 'US' AND xa_c.n = xa_c_countries.ad AND xa_c.camp = xa_c_countries.c))  AND xa_c.n = xa_ads.n AND xa_ads.def = 0 AND xa_ads_ip.ad IS NULL 
      GROUP BY xa_c.n 
      ORDER BY xa_ads.weight desc,m LIMIT 1\G
    
    # Query 3: 0.16 QPS, 0.01x concurrency, ID 0xD98203F9E0031F40 at byte 53701595
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-05-27 02:02:29 to 03:33:51
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          1     893
    # Exec time      4     48s    48ms    92ms    54ms    61ms     5ms    51ms
    # Lock time      2   143ms   135us   260us   159us   167us     9us   152us
    # Rows sent      1     893       1       1       1       1       0       1
    # Rows examine   0  92.81k      90     133  106.43  124.25   12.86  102.22
    # Rows affecte   0       0       0       0       0       0       0       0
    # Rows read      0  92.81k      90     133  106.43  124.25   12.86  102.22
    # Bytes sent     2   3.20M   2.79k   3.94k   3.67k   3.88k  476.95   3.88k
    # Query size     3   1.26M   1.45k   1.45k   1.45k   1.39k       0   1.39k
    # String:
    # Databases    db
    # Hosts        localhost
    # Last errno   0
    # Users        user
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ################################################################
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads'\G
    #    SHOW CREATE TABLE `db`.`xa_ads`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_c'\G
    #    SHOW CREATE TABLE `db`.`xa_c`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads_ip'\G
    #    SHOW CREATE TABLE `db`.`xa_ads_ip`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    select xa_ads.ad_type,xa_ads.kind,xa_ads.n,xa_c.camp,xa_c.price_i,xa_ads.norepeat,xa_ads.banner,xa_ads.url,xa_ads.alt,xa_ads.raw,xa_ads.javascript,xa_ads.def,xa_ads.user,xa_c.i_nu,xa_c.zone,xa_c.c_nu,xa_c.unlimit,xa_c.d_valid_by,xa_c.t2,xa_c.emailed,xa_c.i_d_nu,MD5(RAND()) AS m,
    xa_ads.url1,xa_ads.url2,xa_ads.url3,
    xa_ads.text1,xa_ads.text2,xa_ads.text3,xa_ads.text4,xa_ads.text5,xa_ads.text6,xa_ads.text7,xa_ads.text8,xa_ads.text9,xa_ads.text10,
    xa_ads.picture1,xa_ads.picture2,xa_ads.picture3,xa_ads.picture4,xa_ads.picture5 
      FROM (xa_ads,xa_c ,xa_c_countries ,xa_c_cats) left join xa_ads_ip 
      ON xa_c.n = xa_ads_ip.ad AND xa_ads_ip.ip = '3305156523' 
      WHERE (xa_c.zone = '1') AND xa_ads.ad_complete = '1' AND xa_ads.ad_type = '1' AND xa_ads.accept = '1' AND xa_ads.en_w = '1' AND xa_c.en_u = '1' AND xa_c.en_w = '1' AND (xa_c.i_nu>'0' OR xa_c.c_nu>'0' OR xa_c.d_valid_by>'1369618275' OR xa_c.unlimit='1') AND (xa_c.i_d_max='0' OR xa_c.i_d_nu>'0') AND (xa_c.c_d_max='0' OR  xa_c.c_d_nu>'0') AND xa_c.t1<'1369618275' AND xa_c.t2>'1369618275' AND xa_c.d2 = '1' AND xa_c.tz1 = '1' AND (xa_c.some_countr = '0' OR (xa_c_countries.country = 'TN' AND xa_c.n = xa_c_countries.ad AND xa_c.camp = xa_c_countries.c)) AND (xa_c.n = xa_c_cats.ad AND xa_c.camp = xa_c_cats.c AND (xa_c_cats.c1 = 0 or xa_c_cats.c2 = 218 or (xa_c_cats.c1 = 216 and xa_c_cats.c2 = 0))) AND xa_c.n = xa_ads.n AND xa_ads.def = 0 AND xa_ads_ip.ad IS NULL 
      GROUP BY xa_c.n 
      ORDER BY xa_ads.weight desc,m LIMIT 1\G
    
    # Query 4: 0.14 QPS, 0.01x concurrency, ID 0x9FF7BB7E34A66360 at byte 907432
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-05-27 02:02:37 to 03:33:48
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          1     765
    # Exec time      3     38s    42ms    93ms    49ms    59ms     6ms    46ms
    # Lock time      1   116ms   122us   384us   151us   176us    16us   144us
    # Rows sent      1     765       1       1       1       1       0       1
    # Rows examine   0  28.43k      21      70   38.06   62.76   13.82   33.28
    # Rows affecte   0       0       0       0       0       0       0       0
    # Rows read      0  28.43k      21      70   38.06   62.76   13.82   33.28
    # Bytes sent     2   2.88M   2.79k   3.94k   3.85k   3.88k  293.00   3.88k
    # Query size     2 1009.62k   1.32k   1.32k   1.32k   1.26k       0   1.26k
    # String:
    # Databases    db
    # Hosts        localhost
    # Last errno   0
    # Users        user
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ################################################################
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads'\G
    #    SHOW CREATE TABLE `db`.`xa_ads`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_c'\G
    #    SHOW CREATE TABLE `db`.`xa_c`\G
    #    SHOW TABLE STATUS FROM `db` LIKE 'xa_ads_ip'\G
    #    SHOW CREATE TABLE `db`.`xa_ads_ip`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    select xa_ads.ad_type,xa_ads.kind,xa_ads.n,xa_c.camp,xa_c.price_i,xa_ads.norepeat,xa_ads.banner,xa_ads.url,xa_ads.alt,xa_ads.raw,xa_ads.javascript,xa_ads.def,xa_ads.user,xa_c.i_nu,xa_c.zone,xa_c.c_nu,xa_c.unlimit,xa_c.d_valid_by,xa_c.t2,xa_c.emailed,xa_c.i_d_nu,MD5(RAND()) AS m,
    xa_ads.url1,xa_ads.url2,xa_ads.url3,
    xa_ads.text1,xa_ads.text2,xa_ads.text3,xa_ads.text4,xa_ads.text5,xa_ads.text6,xa_ads.text7,xa_ads.text8,xa_ads.text9,xa_ads.text10,
    xa_ads.picture1,xa_ads.picture2,xa_ads.picture3,xa_ads.picture4,xa_ads.picture5 
      FROM (xa_ads,xa_c ,xa_c_countries ) left join xa_ads_ip 
      ON xa_c.n = xa_ads_ip.ad AND xa_ads_ip.ip = '2830867426' 
      WHERE (xa_c.zone = '1' OR xa_c.zone = '46') AND xa_ads.ad_complete = '1' AND xa_ads.ad_type = '1' AND xa_ads.accept = '1' AND xa_ads.en_w = '1' AND xa_c.en_u = '1' AND xa_c.en_w = '1' AND (xa_c.i_nu>'0' OR xa_c.c_nu>'0' OR xa_c.d_valid_by>'1369613035' OR xa_c.unlimit='1') AND (xa_c.i_d_max='0' OR xa_c.i_d_nu>'0') AND (xa_c.c_d_max='0' OR  xa_c.c_d_nu>'0') AND xa_c.t1<'1369613035' AND xa_c.t2>'1369613035' AND xa_c.d2 = '1' AND xa_c.tz1 = '1' AND (xa_c.some_countr = '0' OR (xa_c_countries.country = 'KW' AND xa_c.n = xa_c_countries.ad AND xa_c.camp = xa_c_countries.c))  AND xa_c.n = xa_ads.n AND xa_ads.def = 0 AND xa_ads_ip.ad IS NULL 
      GROUP BY xa_c.n 
      ORDER BY xa_ads.weight desc,m LIMIT 1\G
    
     
  19. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    before restarting mysql, please:
    Can you rerun mysqltuner, mysqlreport ? to see the sum up after all
    and attach also the result of
    mysqladmin extended-status
    and
    mysqladmin variables

    -----------------------------------
    There is an improvement, no longer ALL type scan (now all ref), less records for sorting etc.

    Further optimizations, since query is quite complicated would require access to the data, so thats all that we can do thru forums

    One more thing,
    based on the previous mysqltuner (please update the newest one)
    [--] Up for: 21h 21m 5s (23M q [307.386 qps], 2M conn, TX: 10B, RX: 4B)

    you got 307 queries per second, so you must have a lot of queries,

    please run
    SET GLOBAL general_log_file = 'general.log';
    SET GLOBAL general_log = 'ON';

    after 10 minutes or so:
    SET GLOBAL general_log = 'OFF';


    and generate pt-query-digest
    ./pt-query-digest /var/lib/mysql/general.log --type=genlog > general.txt

    and attach general.txt (first 10 queries will be ok)

    And I've noticed that in the last my.cnf that you showed you didn't set long_query_time, and in mysqlreport results you had 10s
    but your pt-query-digest returns queries faster than 10s, so what is your current long_query_time setting in my.cnf ?
     
  20. sc0t

    sc0t Member

    Joined:
    May 14, 2013
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hello,
    Thank you for your reply.

    Before Restarting mysql:
    mysqltuner :
    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.31-30.3-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1K (Tables: 1)
    [--] Data in InnoDB tables: 111M (Tables: 286)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 33
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 12h 24m 47s (19M q [148.886 qps], 1M conn, TX: 10B, RX: 4B)
    [--] Reads / Writes: 42% / 58%
    [--] Total buffers: 26.1G global + 6.6M per thread (2000 max threads)
    [!!] Maximum possible memory usage: 39.1G (124% of installed RAM)
    [!!] Slow queries: 8% (1M/19M)
    [!!] Highest connection usage: 100%  (2001/2000)
    [OK] Key buffer size / total MyISAM indexes: 75.0M/104.0K
    [OK] Key buffer hit rate: 100.0% (4B cached / 17 reads)
    [OK] Query cache efficiency: 29.4% (1M cached / 6M selects)
    [!!] Query cache prunes per day: 53312
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 993K sorts)
    [!!] Joins performed without indexes: 890169
    [!!] Temporary tables created on disk: 49% (978K on disk / 1M total)
    [OK] Thread cache hit rate: 99% (8K created / 1M connections)
    [!!] Table cache hit rate: 5% (760 open / 14K opened)
    [OK] Open file limit used: 0% (76/65K)
    [OK] Table locks acquired immediately: 99% (14M immediate / 14M locks)
    [OK] InnoDB data size / buffer pool: 111.8M/26.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Reduce or eliminate persistent connections to reduce connection usage
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        max_connections (> 2000)
        wait_timeout (< 3)
        interactive_timeout (< 28800)
        query_cache_size (> 30M)
        join_buffer_size (> 4.0M, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 32M)
        table_cache (> 4096)
    mysqreport
    Code:
    MySQL 5.5.31-30.3-log    uptime 1 12:27:12      Tue May 28 14:29:31 2013
    
    __ Key _________________________________________________________________
    Buffer used   735.00k of  75.00M  %Used:   0.96
      Current      13.54M            %Usage:  18.05
    Write hit     100.00%
    Read hit      100.00%
    
    __ Questions ___________________________________________________________
    Total          19.54M   148.9/s
      DMS          10.78M    82.2/s  %Total:  55.19
      Com_          5.20M    39.6/s           26.61
      QC Hits       1.91M    14.6/s            9.78
      COM_QUIT      1.70M    13.0/s            8.72
      -Unknown     57.67k     0.4/s            0.30
    Slow 10 s       1.67M    12.7/s            8.54  %DMS:  15.47  Log:  ON
    DMS            10.78M    82.2/s           55.19
      SELECT        4.59M    35.0/s           23.47         42.53
      UPDATE        4.42M    33.7/s           22.61         40.96
      INSERT        1.12M     8.5/s            5.74         10.40
      DELETE      658.59k     5.0/s            3.37          6.11
      REPLACE           0       0/s            0.00          0.00
    Com_            5.20M    39.6/s           26.61
      set_option    3.45M    26.3/s           17.68
      change_db     1.74M    13.3/s            8.91
      show_fields     683     0.0/s            0.00
    
    __ SELECT and Sort _____________________________________________________
    Scan          145.77k     1.1/s %SELECT:   3.18
    Range         635.21k     4.8/s           13.85
    Full join     891.18k     6.8/s           19.43
    Range check         0       0/s            0.00
    Full rng join       0       0/s            0.00
    Sort scan     989.13k     7.5/s
    Sort range      5.16k     0.0/s
    Sort mrg pass       0       0/s
    
    __ Query Cache _________________________________________________________
    Memory usage   28.52M of  30.00M  %Used:  95.07
    Block Fragmnt   0.03%
    Hits            1.91M    14.6/s
    Inserts         3.40M    25.9/s
    Insrt:Prune   42.08:1    25.3/s
    Hit:Insert     0.56:1
    
    __ Table Locks _________________________________________________________
    Waited             18     0.0/s  %Total:   0.00
    Immediate      14.97M   114.1/s
    
    __ Tables ______________________________________________________________
    Open              777 of 4096    %Cache:  18.97
    Opened         14.35k     0.1/s
    
    __ Connections _________________________________________________________
    Max used         2001 of 2000      %Max: 100.05
    Total           1.75M    13.3/s
    
    __ Created Temp ________________________________________________________
    Disk table    979.09k     7.5/s
    Table         985.29k     7.5/s    Size:  32.0M
    File              504     0.0/s
    
    __ Threads _____________________________________________________________
    Running             2 of    2
    Cached            498 of  500      %Hit:  99.54
    Created         8.08k     0.1/s
    Slow                0       0/s
    
    __ Aborted _____________________________________________________________
    Clients        86.01k     0.7/s
    Connects       14.63k     0.1/s
    
    __ Bytes _______________________________________________________________
    Sent           10.06G   76.6k/s
    Received        4.10G   31.2k/s
    
    __ InnoDB Buffer Pool __________________________________________________
    Usage         368.77M of  26.00G  %Used:   1.39
    Read hit      100.00%
    Pages
      Free          1.68M            %Total:  98.61
      Data         23.36k                      1.37 %Drty:   7.66
      Misc            239                      0.01
      Latched                                  0.00
    Reads           2.62G   20.0k/s
      From file     9.03k     0.1/s            0.00
      Ahead Rnd         0       0/s
      Ahead Sql                 0/s
    Writes         49.72M   378.9/s
    Flushes       388.04k     3.0/s
    Wait Free           0       0/s
    
    __ InnoDB Lock _________________________________________________________
    Waits         1865385    14.2/s
    Current             0
    Time acquiring
      Total     442524121 ms
      Average        2372 ms
      Max           57090 ms
    
    __ InnoDB Data, Pages, Rows ____________________________________________
    Data
      Reads        11.06k     0.1/s
      Writes       11.38M    86.7/s
      fsync        11.02M    83.9/s
      Pending
        Reads           0
        Writes          0
        fsync           0
    
    Pages
      Created      12.67k     0.1/s
      Read         10.76k     0.1/s
      Written     388.04k     3.0/s
    
    Rows
      Deleted       1.06M     8.1/s
      Inserted      1.78M    13.6/s
      Read          2.48G   18.9k/s
      Updated       4.39M    33.5/s
    
    mysqladmin extended-status
    Code:
    +------------------------------------------+-------------+
    | Variable_name                            | Value       |
    +------------------------------------------+-------------+
    | Aborted_clients                          | 86012       |
    | Aborted_connects                         | 14628       |
    | Binlog_cache_disk_use                    | 1001        |
    | Binlog_cache_use                         | 6189327     |
    | Binlog_stmt_cache_disk_use               | 0           |
    | Binlog_stmt_cache_use                    | 0           |
    | Bytes_received                           | 4105822521  |
    | Bytes_sent                               | 10069108903 |
    | Com_admin_commands                       | 2           |
    | Com_assign_to_keycache                   | 0           |
    | Com_alter_db                             | 0           |
    | Com_alter_db_upgrade                     | 0           |
    | Com_alter_event                          | 0           |
    | Com_alter_function                       | 0           |
    | Com_alter_procedure                      | 0           |
    | Com_alter_server                         | 0           |
    | Com_alter_table                          | 1           |
    | Com_alter_tablespace                     | 0           |
    | Com_analyze                              | 0           |
    | Com_begin                                | 0           |
    | Com_binlog                               | 0           |
    | Com_call_procedure                       | 0           |
    | Com_change_db                            | 1744720     |
    | Com_change_master                        | 0           |
    | Com_check                                | 0           |
    | Com_checksum                             | 0           |
    | Com_commit                               | 0           |
    | Com_create_db                            | 0           |
    | Com_create_event                         | 0           |
    | Com_create_function                      | 0           |
    | Com_create_index                         | 0           |
    | Com_create_procedure                     | 0           |
    | Com_create_server                        | 0           |
    | Com_create_table                         | 0           |
    | Com_create_trigger                       | 0           |
    | Com_create_udf                           | 0           |
    | Com_create_user                          | 0           |
    | Com_create_view                          | 0           |
    | Com_dealloc_sql                          | 0           |
    | Com_delete                               | 659719      |
    | Com_delete_multi                         | 0           |
    | Com_do                                   | 0           |
    | Com_drop_db                              | 0           |
    | Com_drop_event                           | 0           |
    | Com_drop_function                        | 0           |
    | Com_drop_index                           | 0           |
    | Com_drop_procedure                       | 0           |
    | Com_drop_server                          | 0           |
    | Com_drop_table                           | 0           |
    | Com_drop_trigger                         | 0           |
    | Com_drop_user                            | 0           |
    | Com_drop_view                            | 0           |
    | Com_empty_query                          | 0           |
    | Com_execute_sql                          | 0           |
    | Com_flush                                | 292         |
    | Com_grant                                | 0           |
    | Com_ha_close                             | 0           |
    | Com_ha_open                              | 0           |
    | Com_ha_read                              | 0           |
    | Com_help                                 | 0           |
    | Com_insert                               | 1123536     |
    | Com_insert_select                        | 0           |
    | Com_install_plugin                       | 0           |
    | Com_kill                                 | 0           |
    | Com_load                                 | 0           |
    | Com_lock_tables                          | 40          |
    | Com_optimize                             | 100         |
    | Com_preload_keys                         | 0           |
    | Com_prepare_sql                          | 0           |
    | Com_purge                                | 0           |
    | Com_purge_before_date                    | 0           |
    | Com_release_savepoint                    | 0           |
    | Com_rename_table                         | 0           |
    | Com_rename_user                          | 0           |
    | Com_repair                               | 0           |
    | Com_replace                              | 0           |
    | Com_replace_select                       | 0           |
    | Com_reset                                | 0           |
    | Com_resignal                             | 0           |
    | Com_revoke                               | 0           |
    | Com_revoke_all                           | 0           |
    | Com_rollback                             | 0           |
    | Com_rollback_to_savepoint                | 0           |
    | Com_savepoint                            | 0           |
    | Com_select                               | 4594074     |
    | Com_set_option                           | 3459256     |
    | Com_signal                               | 0           |
    | Com_show_authors                         | 0           |
    | Com_show_binlog_events                   | 0           |
    | Com_show_binlogs                         | 0           |
    | Com_show_charsets                        | 1           |
    | Com_show_client_statistics               | 0           |
    | Com_show_collations                      | 0           |
    | Com_show_contributors                    | 0           |
    | Com_show_create_db                       | 0           |
    | Com_show_create_event                    | 0           |
    | Com_show_create_func                     | 0           |
    | Com_show_create_proc                     | 0           |
    | Com_show_create_table                    | 625         |
    | Com_show_create_trigger                  | 0           |
    | Com_show_databases                       | 6           |
    | Com_show_engine_logs                     | 0           |
    | Com_show_engine_mutex                    | 0           |
    | Com_show_engine_status                   | 0           |
    | Com_show_events                          | 14          |
    | Com_show_errors                          | 0           |
    | Com_show_fields                          | 683         |
    | Com_show_function_status                 | 14          |
    | Com_show_grants                          | 0           |
    | Com_show_index_statistics                | 0           |
    | Com_show_keys                            | 3           |
    | Com_show_master_status                   | 1           |
    | Com_show_open_tables                     | 0           |
    | Com_show_plugins                         | 0           |
    | Com_show_privileges                      | 0           |
    | Com_show_procedure_status                | 14          |
    | Com_show_processlist                     | 0           |
    | Com_show_profile                         | 0           |
    | Com_show_profiles                        | 0           |
    | Com_show_relaylog_events                 | 0           |
    | Com_show_slave_hosts                     | 0           |
    | Com_show_slave_status                    | 1           |
    | Com_show_slave_status_nolock             | 0           |
    | Com_show_status                          | 5           |
    | Com_show_storage_engines                 | 2           |
    | Com_show_table_statistics                | 0           |
    | Com_show_table_status                    | 627         |
    | Com_show_tables                          | 32          |
    | Com_show_temporary_tables                | 0           |
    | Com_show_thread_statistics               | 0           |
    | Com_show_triggers                        | 618         |
    | Com_show_user_statistics                 | 0           |
    | Com_show_variables                       | 6           |
    | Com_show_warnings                        | 0           |
    | Com_slave_start                          | 0           |
    | Com_slave_stop                           | 0           |
    | Com_stmt_close                           | 0           |
    | Com_stmt_execute                         | 0           |
    | Com_stmt_fetch                           | 0           |
    | Com_stmt_prepare                         | 0           |
    | Com_stmt_reprepare                       | 0           |
    | Com_stmt_reset                           | 0           |
    | Com_stmt_send_long_data                  | 0           |
    | Com_truncate                             | 17          |
    | Com_uninstall_plugin                     | 0           |
    | Com_unlock_tables                        | 42          |
    | Com_update                               | 4425156     |
    | Com_update_multi                         | 0           |
    | Com_xa_commit                            | 0           |
    | Com_xa_end                               | 0           |
    | Com_xa_prepare                           | 0           |
    | Com_xa_recover                           | 0           |
    | Com_xa_rollback                          | 0           |
    | Com_xa_start                             | 0           |
    | Compression                              | OFF         |
    | Connections                              | 1749386     |
    | Created_tmp_disk_tables                  | 980636      |
    | Created_tmp_files                        | 504         |
    | Created_tmp_tables                       | 986840      |
    | Delayed_errors                           | 0           |
    | Delayed_insert_threads                   | 0           |
    | Delayed_writes                           | 0           |
    | Flashcache_enabled                       | OFF         |
    | Flush_commands                           | 1           |
    | Handler_commit                           | 16968570    |
    | Handler_delete                           | 1061321     |
    | Handler_discover                         | 0           |
    | Handler_prepare                          | 11298696    |
    | Handler_read_first                       | 1660339     |
    | Handler_read_key                         | 78965562    |
    | Handler_read_last                        | 496         |
    | Handler_read_next                        | 59731694    |
    | Handler_read_prev                        | 282723      |
    | Handler_read_rnd                         | 1031577     |
    | Handler_read_rnd_next                    | 2436698787  |
    | Handler_rollback                         | 22138       |
    | Handler_savepoint                        | 0           |
    | Handler_savepoint_rollback               | 0           |
    | Handler_update                           | 37186117    |
    | Handler_write                            | 4657324541  |
    | Innodb_adaptive_hash_cells               | 55249463    |
    | Innodb_adaptive_hash_heap_buffers        | 239         |
    | Innodb_adaptive_hash_hash_searches       | 66190445    |
    | Innodb_adaptive_hash_non_hash_searches   | 64377169    |
    | Innodb_background_log_sync               | 129827      |
    | Innodb_buffer_pool_pages_data            | 23363       |
    | Innodb_buffer_pool_bytes_data            | 382779392   |
    | Innodb_buffer_pool_pages_dirty           | 1720        |
    | Innodb_buffer_pool_bytes_dirty           | 28180480    |
    | Innodb_buffer_pool_pages_flushed         | 388429      |
    | Innodb_buffer_pool_pages_LRU_flushed     | 0           |
    | Innodb_buffer_pool_pages_free            | 1680332     |
    | Innodb_buffer_pool_pages_made_not_young  | 0           |
    | Innodb_buffer_pool_pages_made_young      | 330         |
    | Innodb_buffer_pool_pages_misc            | 240         |
    | Innodb_buffer_pool_pages_old             | 8622        |
    | Innodb_buffer_pool_pages_total           | 1703935     |
    | Innodb_buffer_pool_read_ahead_rnd        | 0           |
    | Innodb_buffer_pool_read_ahead            | 1593        |
    | Innodb_buffer_pool_read_ahead_evicted    | 0           |
    | Innodb_buffer_pool_read_requests         | 2624118847  |
    | Innodb_buffer_pool_reads                 | 9033        |
    | Innodb_buffer_pool_wait_free             | 0           |
    | Innodb_buffer_pool_write_requests        | 49806886    |
    | Innodb_checkpoint_age                    | 87031550    |
    | Innodb_checkpoint_max_age                | 869019772   |
    | Innodb_checkpoint_target_age             | 841862905   |
    | Innodb_data_fsyncs                       | 11034969    |
    | Innodb_data_pending_fsyncs               | 0           |
    | Innodb_data_pending_reads                | 0           |
    | Innodb_data_pending_writes               | 0           |
    | Innodb_data_read                         | 178491392   |
    | Innodb_data_reads                        | 11058       |
    | Innodb_data_writes                       | 11402760    |
    | Innodb_data_written                      | 22611620352 |
    | Innodb_dblwr_pages_written               | 388429      |
    | Innodb_dblwr_writes                      | 14345       |
    | Innodb_deadlocks                         | 0           |
    | Innodb_dict_tables                       | 294         |
    | Innodb_have_atomic_builtins              | ON          |
    | Innodb_history_list_length               | 3377        |
    | Innodb_ibuf_discarded_delete_marks       | 0           |
    | Innodb_ibuf_discarded_deletes            | 0           |
    | Innodb_ibuf_discarded_inserts            | 0           |
    | Innodb_ibuf_free_list                    | 8           |
    | Innodb_ibuf_merged_delete_marks          | 0           |
    | Innodb_ibuf_merged_deletes               | 0           |
    | Innodb_ibuf_merged_inserts               | 286         |
    | Innodb_ibuf_merges                       | 137         |
    | Innodb_ibuf_segment_size                 | 10          |
    | Innodb_ibuf_size                         | 1           |
    | Innodb_log_waits                         | 0           |
    | Innodb_log_write_requests                | 9487332     |
    | Innodb_log_writes                        | 10984738    |
    | Innodb_lsn_current                       | 41680591436 |
    | Innodb_lsn_flushed                       | 41680591436 |
    | Innodb_lsn_last_checkpoint               | 41593559886 |
    | Innodb_master_thread_1_second_loops      | 129414      |
    | Innodb_master_thread_10_second_loops     | 12938       |
    | Innodb_master_thread_background_loops    | 144         |
    | Innodb_master_thread_main_flush_loops    | 140         |
    | Innodb_master_thread_sleeps              | 129409      |
    | Innodb_max_trx_id                        | 148651856   |
    | Innodb_mem_adaptive_hash                 | 445932080   |
    | Innodb_mem_dictionary                    | 112219216   |
    | Innodb_mem_total                         | 28667019264 |
    | Innodb_mutex_os_waits                    | 232859625   |
    | Innodb_mutex_spin_rounds                 | 7012352782  |
    | Innodb_mutex_spin_waits                  | 13638845    |
    | Innodb_oldest_view_low_limit_trx_id      | 148651838   |
    | Innodb_os_log_fsyncs                     | 10997957    |
    | Innodb_os_log_pending_fsyncs             | 0           |
    | Innodb_os_log_pending_writes             | 0           |
    | Innodb_os_log_written                    | 9877028352  |
    | Innodb_page_size                         | 16384       |
    | Innodb_pages_created                     | 12671       |
    | Innodb_pages_read                        | 10760       |
    | Innodb_pages_written                     | 388429      |
    | Innodb_purge_trx_id                      | 148651837   |
    | Innodb_purge_undo_no                     | 0           |
    | Innodb_row_lock_current_waits            | 0           |
    | Innodb_current_row_locks                 | 0           |
    | Innodb_row_lock_time                     | 4425241248  |
    | Innodb_row_lock_time_avg                 | 2372        |
    | Innodb_row_lock_time_max                 | 57090       |
    | Innodb_row_lock_waits                    | 1865445     |
    | Innodb_rows_deleted                      | 1061320     |
    | Innodb_rows_inserted                     | 1781081     |
    | Innodb_rows_read                         | 2483360635  |
    | Innodb_rows_updated                      | 4399521     |
    | Innodb_read_views_memory                 | 17184       |
    | Innodb_descriptors_memory                | 16000       |
    | Innodb_s_lock_os_waits                   | 3696754     |
    | Innodb_s_lock_spin_rounds                | 111685530   |
    | Innodb_s_lock_spin_waits                 | 2302411     |
    | Innodb_truncated_status_writes           | 0           |
    | Innodb_x_lock_os_waits                   | 88214       |
    | Innodb_x_lock_spin_rounds                | 3036075     |
    | Innodb_x_lock_spin_waits                 | 38185       |
    | Key_blocks_not_flushed                   | 0           |
    | Key_blocks_unused                        | 62937       |
    | Key_blocks_used                          | 735         |
    | Key_read_requests                        | 4653082967  |
    | Key_reads                                | 17          |
    | Key_write_requests                       | 2746929     |
    | Key_writes                               | 0           |
    | Last_query_cost                          | 0.000000    |
    | Max_used_connections                     | 2001        |
    | Not_flushed_delayed_rows                 | 0           |
    | Open_files                               | 76          |
    | Open_streams                             | 0           |
    | Open_table_definitions                   | 328         |
    | Open_tables                              | 777         |
    | Opened_files                             | 3924184     |
    | Opened_table_definitions                 | 746         |
    | Opened_tables                            | 14348       |
    | Performance_schema_cond_classes_lost     | 0           |
    | Performance_schema_cond_instances_lost   | 0           |
    | Performance_schema_file_classes_lost     | 0           |
    | Performance_schema_file_handles_lost     | 0           |
    | Performance_schema_file_instances_lost   | 0           |
    | Performance_schema_locker_lost           | 0           |
    | Performance_schema_mutex_classes_lost    | 0           |
    | Performance_schema_mutex_instances_lost  | 0           |
    | Performance_schema_rwlock_classes_lost   | 0           |
    | Performance_schema_rwlock_instances_lost | 0           |
    | Performance_schema_table_handles_lost    | 0           |
    | Performance_schema_table_instances_lost  | 0           |
    | Performance_schema_thread_classes_lost   | 0           |
    | Performance_schema_thread_instances_lost | 0           |
    | Prepared_stmt_count                      | 0           |
    | Qcache_free_blocks                       | 9           |
    | Qcache_free_memory                       | 1535672     |
    | Qcache_hits                              | 1913261     |
    | Qcache_inserts                           | 3409210     |
    | Qcache_lowmem_prunes                     | 80886       |
    | Qcache_not_cached                        | 1150573     |
    | Qcache_queries_in_cache                  | 15730       |
    | Qcache_total_blocks                      | 31691       |
    | Queries                                  | 19571580    |
    | Questions                                | 19571574    |
    | Rpl_status                               | AUTH_MASTER |
    | Select_full_join                         | 892598      |
    | Select_full_range_join                   | 0           |
    | Select_range                             | 636268      |
    | Select_range_check                       | 0           |
    | Select_scan                              | 145883      |
    | Slave_heartbeat_period                   | 0.000       |
    | Slave_open_temp_tables                   | 0           |
    | Slave_received_heartbeats                | 0           |
    | Slave_retried_transactions               | 0           |
    | Slave_running                            | OFF         |
    | Slow_launch_threads                      | 0           |
    | Slow_queries                             | 1671348     |
    | Sort_merge_passes                        | 0           |
    | Sort_range                               | 5166        |
    | Sort_rows                                | 993576      |
    | Sort_scan                                | 990688      |
    | Ssl_accept_renegotiates                  | 0           |
    | Ssl_accepts                              | 0           |
    | Ssl_callback_cache_hits                  | 0           |
    | Ssl_cipher                               |             |
    | Ssl_cipher_list                          |             |
    | Ssl_client_connects                      | 0           |
    | Ssl_connect_renegotiates                 | 0           |
    | Ssl_ctx_verify_depth                     | 0           |
    | Ssl_ctx_verify_mode                      | 0           |
    | Ssl_default_timeout                      | 0           |
    | Ssl_finished_accepts                     | 0           |
    | Ssl_finished_connects                    | 0           |
    | Ssl_session_cache_hits                   | 0           |
    | Ssl_session_cache_misses                 | 0           |
    | Ssl_session_cache_mode                   | NONE        |
    | Ssl_session_cache_overflows              | 0           |
    | Ssl_session_cache_size                   | 0           |
    | Ssl_session_cache_timeouts               | 0           |
    | Ssl_sessions_reused                      | 0           |
    | Ssl_used_session_cache_entries           | 0           |
    | Ssl_verify_depth                         | 0           |
    | Ssl_verify_mode                          | 0           |
    | Ssl_version                              |             |
    | Table_locks_immediate                    | 14995037    |
    | Table_locks_waited                       | 18          |
    | Tc_log_max_pages_used                    | 0           |
    | Tc_log_page_size                         | 0           |
    | Tc_log_page_waits                        | 1           |
    | Threadpool_idle_threads                  | 0           |
    | Threadpool_threads                       | 0           |
    | Threads_cached                           | 499         |
    | Threads_connected                        | 1           |
    | Threads_created                          | 8081        |
    | Threads_running                          | 1           |
    | Uptime                                   | 131422      |
    | Uptime_since_flush_status                | 131422      |
    | binlog_commits                           | 6189517     |
    | binlog_group_commits                     | 5968515     |
    +------------------------------------------+-------------+
    
    mysqladmin variables
    Code:
    +---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
    | Variable_name                                     | Value                                                                                                                  |
    +---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
    | auto_increment_increment                          | 1                                                                                                                      |
    | auto_increment_offset                             | 1                                                                                                                      |
    | autocommit                                        | ON                                                                                                                     |
    | automatic_sp_privileges                           | ON                                                                                                                     |
    | back_log                                          | 50                                                                                                                     |
    | basedir                                           | /usr                                                                                                                   |
    | big_tables                                        | OFF                                                                                                                    |
    | binlog_cache_size                                 | 32768                                                                                                                  |
    | binlog_direct_non_transactional_updates           | OFF                                                                                                                    |
    | binlog_format                                     | STATEMENT                                                                                                              |
    | binlog_stmt_cache_size                            | 32768                                                                                                                  |
    | bulk_insert_buffer_size                           | 8388608                                                                                                                |
    | character_set_client                              | latin1                                                                                                                 |
    | character_set_connection                          | latin1                                                                                                                 |
    | character_set_database                            | latin1                                                                                                                 |
    | character_set_filesystem                          | binary                                                                                                                 |
    | character_set_results                             | latin1                                                                                                                 |
    | character_set_server                              | latin1                                                                                                                 |
    | character_set_system                              | utf8                                                                                                                   |
    | character_sets_dir                                | /usr/share/mysql/charsets/                                                                                             |
    | collation_connection                              | latin1_swedish_ci                                                                                                      |
    | collation_database                                | latin1_swedish_ci                                                                                                      |
    | collation_server                                  | latin1_swedish_ci                                                                                                      |
    | completion_type                                   | NO_CHAIN                                                                                                               |
    | concurrent_insert                                 | AUTO                                                                                                                   |
    | connect_timeout                                   | 10                                                                                                                     |
    | datadir                                           | /var/lib/mysql/                                                                                                        |
    | date_format                                       | %Y-%m-%d                                                                                                               |
    | datetime_format                                   | %Y-%m-%d %H:%i:%s                                                                                                      |
    | default_storage_engine                            | InnoDB                                                                                                                 |
    | default_week_format                               | 0                                                                                                                      |
    | delay_key_write                                   | ON                                                                                                                     |
    | delayed_insert_limit                              | 100                                                                                                                    |
    | delayed_insert_timeout                            | 300                                                                                                                    |
    | delayed_queue_size                                | 1000                                                                                                                   |
    | div_precision_increment                           | 4                                                                                                                      |
    | enforce_storage_engine                            |                                                                                                                        |
    | engine_condition_pushdown                         | ON                                                                                                                     |
    | event_scheduler                                   | OFF                                                                                                                    |
    | expand_fast_index_creation                        | OFF                                                                                                                    |
    | expire_logs_days                                  | 2                                                                                                                      |
    | extra_max_connections                             | 1                                                                                                                      |
    | extra_port                                        | 0                                                                                                                      |
    | fast_index_creation                               | ON                                                                                                                     |
    | flush                                             | OFF                                                                                                                    |
    | flush_time                                        | 0                                                                                                                      |
    | foreign_key_checks                                | ON                                                                                                                     |
    | ft_boolean_syntax                                 | + -><()~*:""&|                                                                                                         |
    | ft_max_word_len                                   | 84                                                                                                                     |
    | ft_min_word_len                                   | 4                                                                                                                      |
    | ft_query_expansion_limit                          | 20                                                                                                                     |
    | ft_stopword_file                                  | (built-in)                                                                                                             |
    | general_log                                       | OFF                                                                                                                    |
    | general_log_file                                  | /var/lib/mysql/xtend.log                                                                                               |
    | group_concat_max_len                              | 1024                                                                                                                   |
    | have_compress                                     | YES                                                                                                                    |
    | have_crypt                                        | YES                                                                                                                    |
    | have_csv                                          | YES                                                                                                                    |
    | have_dynamic_loading                              | YES                                                                                                                    |
    | have_flashcache                                   | YES                                                                                                                    |
    | have_geometry                                     | YES                                                                                                                    |
    | have_innodb                                       | YES                                                                                                                    |
    | have_ndbcluster                                   | NO                                                                                                                     |
    | have_openssl                                      | DISABLED                                                                                                               |
    | have_partitioning                                 | YES                                                                                                                    |
    | have_profiling                                    | YES                                                                                                                    |
    | have_query_cache                                  | YES                                                                                                                    |
    | have_response_time_distribution                   | YES                                                                                                                    |
    | have_rtree_keys                                   | YES                                                                                                                    |
    | have_ssl                                          | DISABLED                                                                                                               |
    | have_symlink                                      | YES                                                                                                                    |
    | hostname                                          | xtend.network                                                                                                          |
    | ignore_builtin_innodb                             | OFF                                                                                                                    |
    | init_connect                                      |                                                                                                                        |
    | init_file                                         |                                                                                                                        |
    | init_slave                                        |                                                                                                                        |
    | innodb_adaptive_flushing                          | ON                                                                                                                     |
    | innodb_adaptive_flushing_method                   | estimate                                                                                                               |
    | innodb_adaptive_hash_index                        | ON                                                                                                                     |
    | innodb_adaptive_hash_index_partitions             | 1                                                                                                                      |
    | innodb_additional_mem_pool_size                   | 8388608                                                                                                                |
    | innodb_autoextend_increment                       | 8                                                                                                                      |
    | innodb_autoinc_lock_mode                          | 1                                                                                                                      |
    | innodb_blocking_buffer_pool_restore               | OFF                                                                                                                    |
    | innodb_buffer_pool_instances                      | 1                                                                                                                      |
    | innodb_buffer_pool_populate                       | OFF                                                                                                                    |
    | innodb_buffer_pool_restore_at_startup             | 0                                                                                                                      |
    | innodb_buffer_pool_shm_checksum                   | ON                                                                                                                     |
    | innodb_buffer_pool_shm_key                        | 0                                                                                                                      |
    | innodb_buffer_pool_size                           | 27917287424                                                                                                            |
    | innodb_change_buffering                           | all                                                                                                                    |
    | innodb_checkpoint_age_target                      | 0                                                                                                                      |
    | innodb_checksums                                  | ON                                                                                                                     |
    | innodb_commit_concurrency                         | 0                                                                                                                      |
    | innodb_concurrency_tickets                        | 500                                                                                                                    |
    | innodb_corrupt_table_action                       | assert                                                                                                                 |
    | innodb_data_file_path                             | ibdata1:10M:autoextend                                                                                                 |
    | innodb_data_home_dir                              |                                                                                                                        |
    | innodb_dict_size_limit                            | 0                                                                                                                      |
    | innodb_doublewrite                                | ON                                                                                                                     |
    | innodb_doublewrite_file                           |                                                                                                                        |
    | innodb_fake_changes                               | OFF                                                                                                                    |
    | innodb_fast_checksum                              | OFF                                                                                                                    |
    | innodb_fast_shutdown                              | 1                                                                                                                      |
    | innodb_file_format                                | Antelope                                                                                                               |
    | innodb_file_format_check                          | ON                                                                                                                     |
    | innodb_file_format_max                            | Antelope                                                                                                               |
    | innodb_file_per_table                             | ON                                                                                                                     |
    | innodb_flush_log_at_trx_commit                    | 1                                                                                                                      |
    | innodb_flush_method                               | O_DIRECT                                                                                                               |
    | innodb_flush_neighbor_pages                       | area                                                                                                                   |
    | innodb_force_load_corrupted                       | OFF                                                                                                                    |
    | innodb_force_recovery                             | 0                                                                                                                      |
    | innodb_ibuf_accel_rate                            | 100                                                                                                                    |
    | innodb_ibuf_active_contract                       | 1                                                                                                                      |
    | innodb_ibuf_max_size                              | 13958627328                                                                                                            |
    | innodb_import_table_from_xtrabackup               | 0                                                                                                                      |
    | innodb_io_capacity                                | 200                                                                                                                    |
    | innodb_kill_idle_transaction                      | 0                                                                                                                      |
    | innodb_large_prefix                               | OFF                                                                                                                    |
    | innodb_lazy_drop_table                            | 0                                                                                                                      |
    | innodb_lock_wait_timeout                          | 50                                                                                                                     |
    | innodb_locking_fake_changes                       | ON                                                                                                                     |
    | innodb_locks_unsafe_for_binlog                    | OFF                                                                                                                    |
    | innodb_log_block_size                             | 512                                                                                                                    |
    | innodb_log_buffer_size                            | 8388608                                                                                                                |
    | innodb_log_file_size                              | 536870912                                                                                                              |
    | innodb_log_files_in_group                         | 2                                                                                                                      |
    | innodb_log_group_home_dir                         | ./                                                                                                                     |
    | innodb_max_bitmap_file_size                       | 104857600                                                                                                              |
    | innodb_max_changed_pages                          | 1000000                                                                                                                |
    | innodb_max_dirty_pages_pct                        | 75                                                                                                                     |
    | innodb_max_purge_lag                              | 0                                                                                                                      |
    | innodb_merge_sort_block_size                      | 1048576                                                                                                                |
    | innodb_mirrored_log_groups                        | 1                                                                                                                      |
    | innodb_old_blocks_pct                             | 37                                                                                                                     |
    | innodb_old_blocks_time                            | 0                                                                                                                      |
    | innodb_open_files                                 | 300                                                                                                                    |
    | innodb_page_size                                  | 16384                                                                                                                  |
    | innodb_print_all_deadlocks                        | OFF                                                                                                                    |
    | innodb_purge_batch_size                           | 20                                                                                                                     |
    | innodb_purge_threads                              | 1                                                                                                                      |
    | innodb_random_read_ahead                          | OFF                                                                                                                    |
    | innodb_read_ahead                                 | linear                                                                                                                 |
    | innodb_read_ahead_threshold                       | 56                                                                                                                     |
    | innodb_read_io_threads                            | 4                                                                                                                      |
    | innodb_recovery_stats                             | OFF                                                                                                                    |
    | innodb_recovery_update_relay_log                  | OFF                                                                                                                    |
    | innodb_replication_delay                          | 0                                                                                                                      |
    | innodb_rollback_on_timeout                        | OFF                                                                                                                    |
    | innodb_rollback_segments                          | 128                                                                                                                    |
    | innodb_show_locks_held                            | 10                                                                                                                     |
    | innodb_show_verbose_locks                         | 0                                                                                                                      |
    | innodb_spin_wait_delay                            | 6                                                                                                                      |
    | innodb_stats_auto_update                          | 1                                                                                                                      |
    | innodb_stats_method                               | nulls_equal                                                                                                            |
    | innodb_stats_on_metadata                          | ON                                                                                                                     |
    | innodb_stats_sample_pages                         | 8                                                                                                                      |
    | innodb_stats_update_need_lock                     | 1                                                                                                                      |
    | innodb_strict_mode                                | OFF                                                                                                                    |
    | innodb_support_xa                                 | ON                                                                                                                     |
    | innodb_sync_spin_loops                            | 30                                                                                                                     |
    | innodb_table_locks                                | ON                                                                                                                     |
    | innodb_thread_concurrency                         | 0                                                                                                                      |
    | innodb_thread_concurrency_timer_based             | OFF                                                                                                                    |
    | innodb_thread_sleep_delay                         | 10000                                                                                                                  |
    | innodb_track_changed_pages                        | OFF                                                                                                                    |
    | innodb_use_atomic_writes                          | OFF                                                                                                                    |
    | innodb_use_global_flush_log_at_trx_commit         | ON                                                                                                                     |
    | innodb_use_native_aio                             | ON                                                                                                                     |
    | innodb_use_sys_malloc                             | ON                                                                                                                     |
    | innodb_use_sys_stats_table                        | OFF                                                                                                                    |
    | innodb_version                                    | 5.5.31-rel30.3                                                                                                         |
    | innodb_write_io_threads                           | 4                                                                                                                      |
    | interactive_timeout                               | 28800                                                                                                                  |
    | join_buffer_size                                  | 4194304                                                                                                                |
    | keep_files_on_create                              | OFF                                                                                                                    |
    | key_buffer_size                                   | 78643200                                                                                                               |
    | key_cache_age_threshold                           | 300                                                                                                                    |
    | key_cache_block_size                              | 1024                                                                                                                   |
    | key_cache_division_limit                          | 100                                                                                                                    |
    | large_files_support                               | ON                                                                                                                     |
    | large_page_size                                   | 0                                                                                                                      |
    | large_pages                                       | OFF                                                                                                                    |
    | lc_messages                                       | en_US                                                                                                                  |
    | lc_messages_dir                                   | /usr/share/mysql/                                                                                                      |
    | lc_time_names                                     | en_US                                                                                                                  |
    | license                                           | GPL                                                                                                                    |
    | local_infile                                      | OFF                                                                                                                    |
    | lock_wait_timeout                                 | 31536000                                                                                                               |
    | locked_in_memory                                  | OFF                                                                                                                    |
    | log                                               | OFF                                                                                                                    |
    | log_bin                                           | ON                                                                                                                     |
    | log_bin_trust_function_creators                   | OFF                                                                                                                    |
    | log_error                                         | /var/lib/mysql/mysql-error.log                                                                                         |
    | log_output                                        | FILE                                                                                                                   |
    | log_queries_not_using_indexes                     | ON                                                                                                                     |
    | log_slave_updates                                 | OFF                                                                                                                    |
    | log_slow_admin_statements                         | OFF                                                                                                                    |
    | log_slow_filter                                   |                                                                                                                        |
    | log_slow_queries                                  | ON                                                                                                                     |
    | log_slow_rate_limit                               | 1                                                                                                                      |
    | log_slow_rate_type                                | session                                                                                                                |
    | log_slow_slave_statements                         | OFF                                                                                                                    |
    | log_slow_sp_statements                            | ON                                                                                                                     |
    | log_slow_verbosity                                |                                                                                                                        |
    | log_warnings                                      | 1                                                                                                                      |
    | log_warnings_suppress                             | 1592                                                                                                                   |
    | long_query_time                                   | 10.000000                                                                                                              |
    | low_priority_updates                              | OFF                                                                                                                    |
    | lower_case_file_system                            | OFF                                                                                                                    |
    | lower_case_table_names                            | 0                                                                                                                      |
    | max_allowed_packet                                | 8388608                                                                                                                |
    | max_binlog_cache_size                             | 18446744073709547520                                                                                                   |
    | max_binlog_files                                  | 0                                                                                                                      |
    | max_binlog_size                                   | 1073741824                                                                                                             |
    | max_binlog_stmt_cache_size                        | 18446744073709547520                                                                                                   |
    | max_connect_errors                                | 100                                                                                                                    |
    | max_connections                                   | 2000                                                                                                                   |
    | max_delayed_threads                               | 20                                                                                                                     |
    | max_error_count                                   | 64                                                                                                                     |
    | max_heap_table_size                               | 33554432                                                                                                               |
    | max_insert_delayed_threads                        | 20                                                                                                                     |
    | max_join_size                                     | 18446744073709551615                                                                                                   |
    | max_length_for_sort_data                          | 1024                                                                                                                   |
    | max_long_data_size                                | 8388608                                                                                                                |
    | max_prepared_stmt_count                           | 16382                                                                                                                  |
    | max_relay_log_size                                | 0                                                                                                                      |
    | max_seeks_for_key                                 | 18446744073709551615                                                                                                   |
    | max_sort_length                                   | 1024                                                                                                                   |
    | max_sp_recursion_depth                            | 0                                                                                                                      |
    | max_tmp_tables                                    | 32                                                                                                                     |
    | max_user_connections                              | 0                                                                                                                      |
    | max_write_lock_count                              | 18446744073709551615                                                                                                   |
    | metadata_locks_cache_size                         | 1024                                                                                                                   |
    | min_examined_row_limit                            | 0                                                                                                                      |
    | multi_range_count                                 | 256                                                                                                                    |
    | myisam_data_pointer_size                          | 6                                                                                                                      |
    | myisam_max_sort_file_size                         | 9223372036853727232                                                                                                    |
    | myisam_mmap_size                                  | 18446744073709551615                                                                                                   |
    | myisam_recover_options                            | BACKUP,FORCE                                                                                                           |
    | myisam_repair_threads                             | 1                                                                                                                      |
    | myisam_sort_buffer_size                           | 8388608                                                                                                                |
    | myisam_stats_method                               | nulls_unequal                                                                                                          |
    | myisam_use_mmap                                   | OFF                                                                                                                    |
    | net_buffer_length                                 | 16384                                                                                                                  |
    | net_read_timeout                                  | 30                                                                                                                     |
    | net_retry_count                                   | 10                                                                                                                     |
    | net_write_timeout                                 | 60                                                                                                                     |
    | new                                               | OFF                                                                                                                    |
    | old                                               | OFF                                                                                                                    |
    | old_alter_table                                   | OFF                                                                                                                    |
    | old_passwords                                     | OFF                                                                                                                    |
    | open_files_limit                                  | 65535                                                                                                                  |
    | optimizer_fix                                     | ON                                                                                                                     |
    | optimizer_prune_level                             | 1                                                                                                                      |
    | optimizer_search_depth                            | 62                                                                                                                     |
    | optimizer_switch                                  | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on |
    | performance_schema                                | OFF                                                                                                                    |
    | performance_schema_events_waits_history_long_size | 10000                                                                                                                  |
    | performance_schema_events_waits_history_size      | 10                                                                                                                     |
    | performance_schema_max_cond_classes               | 80                                                                                                                     |
    | performance_schema_max_cond_instances             | 1000                                                                                                                   |
    | performance_schema_max_file_classes               | 50                                                                                                                     |
    | performance_schema_max_file_handles               | 32768                                                                                                                  |
    | performance_schema_max_file_instances             | 10000                                                                                                                  |
    | performance_schema_max_mutex_classes              | 200                                                                                                                    |
    | performance_schema_max_mutex_instances            | 1000000                                                                                                                |
    | performance_schema_max_rwlock_classes             | 30                                                                                                                     |
    | performance_schema_max_rwlock_instances           | 1000000                                                                                                                |
    | performance_schema_max_table_handles              | 100000                                                                                                                 |
    | performance_schema_max_table_instances            | 50000                                                                                                                  |
    | performance_schema_max_thread_classes             | 50                                                                                                                     |
    | performance_schema_max_thread_instances           | 1000                                                                                                                   |
    | pid_file                                          | /var/lib/mysql//xtend.network.pid                                                                                      |
    | plugin_dir                                        | /usr/lib64/mysql/plugin/                                                                                               |
    | port                                              | 3306                                                                                                                   |
    | preload_buffer_size                               | 32768                                                                                                                  |
    | profiling                                         | OFF                                                                                                                    |
    | profiling_history_size                            | 15                                                                                                                     |
    | protocol_version                                  | 10                                                                                                                     |
    | query_alloc_block_size                            | 8192                                                                                                                   |
    | query_cache_limit                                 | 1048576                                                                                                                |
    | query_cache_min_res_unit                          | 4096                                                                                                                   |
    | query_cache_size                                  | 31457280                                                                                                               |
    | query_cache_strip_comments                        | OFF                                                                                                                    |
    | query_cache_type                                  | ON                                                                                                                     |
    | query_cache_wlock_invalidate                      | OFF                                                                                                                    |
    | query_prealloc_size                               | 8192                                                                                                                   |
    | query_response_time_range_base                    | 10                                                                                                                     |
    | query_response_time_stats                         | OFF                                                                                                                    |
    | range_alloc_block_size                            | 4096                                                                                                                   |
    | read_buffer_size                                  | 131072                                                                                                                 |
    | read_only                                         | OFF                                                                                                                    |
    | read_rnd_buffer_size                              | 262144                                                                                                                 |
    | relay_log                                         |                                                                                                                        |
    | relay_log_index                                   |                                                                                                                        |
    | relay_log_info_file                               | relay-log.info                                                                                                         |
    | relay_log_purge                                   | ON                                                                                                                     |
    | relay_log_recovery                                | OFF                                                                                                                    |
    | relay_log_space_limit                             | 0                                                                                                                      |
    | report_host                                       |                                                                                                                        |
    | report_password                                   |                                                                                                                        |
    | report_port                                       | 3306                                                                                                                   |
    | report_user                                       |                                                                                                                        |
    | rpl_recovery_rank                                 | 0                                                                                                                      |
    | secure_auth                                       | OFF                                                                                                                    |
    | secure_file_priv                                  |                                                                                                                        |
    | server_id                                         | 1                                                                                                                      |
    | skip_external_locking                             | ON                                                                                                                     |
    | skip_name_resolve                                 | OFF                                                                                                                    |
    | skip_networking                                   | OFF                                                                                                                    |
    | skip_show_database                                | OFF                                                                                                                    |
    | slave_compressed_protocol                         | OFF                                                                                                                    |
    | slave_exec_mode                                   | STRICT                                                                                                                 |
    | slave_load_tmpdir                                 | /tmp                                                                                                                   |
    | slave_max_allowed_packet                          | 1073741824                                                                                                             |
    | slave_net_timeout                                 | 3600                                                                                                                   |
    | slave_skip_errors                                 | OFF                                                                                                                    |
    | slave_transaction_retries                         | 10                                                                                                                     |
    | slave_type_conversions                            |                                                                                                                        |
    | slow_launch_time                                  | 2                                                                                                                      |
    | slow_query_log                                    | ON                                                                                                                     |
    | slow_query_log_file                               | /var/lib/mysql/mysql-slow.log                                                                                          |
    | slow_query_log_timestamp_always                   | OFF                                                                                                                    |
    | slow_query_log_timestamp_precision                | second                                                                                                                 |
    | slow_query_log_use_global_control                 |                                                                                                                        |
    | socket                                            | /var/lib/mysql/mysql.sock                                                                                              |
    | sort_buffer_size                                  | 2097152                                                                                                                |
    | sql_auto_is_null                                  | OFF                                                                                                                    |
    | sql_big_selects                                   | ON                                                                                                                     |
    | sql_big_tables                                    | OFF                                                                                                                    |
    | sql_buffer_result                                 | OFF                                                                                                                    |
    | sql_log_bin                                       | ON                                                                                                                     |
    | sql_log_off                                       | OFF                                                                                                                    |
    | sql_low_priority_updates                          | OFF                                                                                                                    |
    | sql_max_join_size                                 | 18446744073709551615                                                                                                   |
    | sql_mode                                          |                                                                                                                        |
    | sql_notes                                         | ON                                                                                                                     |
    | sql_quote_show_create                             | ON                                                                                                                     |
    | sql_safe_updates                                  | OFF                                                                                                                    |
    | sql_select_limit                                  | 18446744073709551615                                                                                                   |
    | sql_slave_skip_counter                            | 0                                                                                                                      |
    | sql_warnings                                      | OFF                                                                                                                    |
    | ssl_ca                                            |                                                                                                                        |
    | ssl_capath                                        |                                                                                                                        |
    | ssl_cert                                          |                                                                                                                        |
    | ssl_cipher                                        |                                                                                                                        |
    | ssl_key                                           |                                                                                                                        |
    | storage_engine                                    | InnoDB                                                                                                                 |
    | stored_program_cache                              | 256                                                                                                                    |
    | sync_binlog                                       | 1                                                                                                                      |
    | sync_frm                                          | ON                                                                                                                     |
    | sync_master_info                                  | 0                                                                                                                      |
    | sync_relay_log                                    | 0                                                                                                                      |
    | sync_relay_log_info                               | 0                                                                                                                      |
    | system_time_zone                                  | CEST                                                                                                                   |
    | table_definition_cache                            | 4096                                                                                                                   |
    | table_open_cache                                  | 4096                                                                                                                   |
    | thread_cache_size                                 | 500                                                                                                                    |
    | thread_concurrency                                | 10                                                                                                                     |
    | thread_handling                                   | one-thread-per-connection                                                                                              |
    | thread_pool_high_prio_tickets                     | 0                                                                                                                      |
    | thread_pool_idle_timeout                          | 60                                                                                                                     |
    | thread_pool_max_threads                           | 500                                                                                                                    |
    | thread_pool_oversubscribe                         | 3                                                                                                                      |
    | thread_pool_size                                  | 8                                                                                                                      |
    | thread_pool_stall_limit                           | 500                                                                                                                    |
    | thread_stack                                      | 262144                                                                                                                 |
    | thread_statistics                                 | OFF                                                                                                                    |
    | time_format                                       | %H:%i:%s                                                                                                               |
    | time_zone                                         | SYSTEM                                                                                                                 |
    | timed_mutexes                                     | OFF                                                                                                                    |
    | tmp_table_size                                    | 33554432                                                                                                               |
    | tmpdir                                            | /tmp                                                                                                                   |
    | transaction_alloc_block_size                      | 8192                                                                                                                   |
    | transaction_prealloc_size                         | 4096                                                                                                                   |
    | tx_isolation                                      | REPEATABLE-READ                                                                                                        |
    | unique_checks                                     | ON                                                                                                                     |
    | updatable_views_with_limit                        | YES                                                                                                                    |
    | userstat                                          | OFF                                                                                                                    |
    | version                                           | 5.5.31-30.3-log                                                                                                        |
    | version_comment                                   | Percona Server (GPL), Release rel30.3, Revision 520                                                                    |
    | version_compile_machine                           | x86_64                                                                                                                 |
    | version_compile_os                                | Linux                                                                                                                  |
    | wait_timeout                                      | 3                                                                                                                      |
    +---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
    
    next post
     
Loading...

Share This Page