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 my.cnf modifications - Assistance

Discussion in 'Workarounds and Optimization' started by nourjabi, Sep 4, 2013.

  1. nourjabi

    nourjabi Active Member

    Joined:
    Jun 24, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    Hi everyone,

    I pledge your assistance in configuring MySQL my.cnf file on my server to best use my resources:

    (I have tried many optimization tools and methods, and at times I found it frustrating to attain any better result).





    My current setup is:

    Code:
    [COLOR="#0000FF"]Dedicated Server  
    Intel(R) Xeon(R) CPU E3-1230 V2 @ 3.30GHz ( 8 CPUs ) 
    Cache 8192 KB
    8GB RAM
    100Mbps port
    1,000GB SATA2 x1 (No RAID)
    Enterprise Linux - CentOS 6.4 - 64 bit - WHM Cpanel 11.38.2 (Build 6)[/COLOR]
    
    


    My.cnf (currently in use):



    Code:
    [mysqld]
    
    max_connections = 500
    
    key_buffer = 220M
    
    myisam_sort_buffer_size = 32M
    
    join_buffer_size = 1M
    
    read_buffer_size = 256K
    
    sort_buffer_size = 256K
    
    table_cache = 128M
    
    thread_cache_size = 16M
    
    interactive_timeout = 25
    
    wait_timeout = 10
    
    connect_timeout = 10
    
    max_allowed_packet = 16M
    
    max_connect_errors = 10
    
    query_cache_limit = 8M
    
    query_cache_size = 64M
    
    query_cache_type = 1
    
    tmp_table_size = 16M
    
    max_heap_table_size = 64M
    
    open_files_limit=13850
    
    [mysqld_safe]
    
    [mysqldump]
    
    quick
    
    max_allowed_packet = 16M
    
    [myisamchk]
    
    key_buffer = 220M
    
    sort_buffer = 256K
    
    read_buffer = 256K
    
    write_buffer = 256K
    
    [mysqlhotcopy]
    
    interactive-timeout

    My current load:

    I have three Huge websites.

    1. E-Commerce website with Stock market and news.
    2. a child clinic website with visitors up to 1 mil a month.
    3. a sports website, which always busy with visitors for sport news.


    Current Server's mysqltuner.pl statues:

    Code:
    -------- Storage Engine Statistics -------------------------------------------
    
    [--] Status: [COLOR="#00FF00"]+Archive[/COLOR][COLOR="#FF0000"] -BDB -Federated [/COLOR][COLOR="#00FF00"]+InnoDB[/COLOR] [COLOR="#FF0000"]-ISAM -NDBCluster[/COLOR]
    [--] Data in MyISAM tables: 8G (Tables: 5255)
    [--] Data in InnoDB tables: 4M (Tables: 160)
    [--] Data in MEMORY tables: 1M (Tables: 22)
    [!!] Total fragmented tables: 216
    
    
    -------- Performance Metrics -------------------------------------------------
    
    [--] Up for: 1d 23h 0m 19s (9M q [56.387 qps], 175K conn, TX: 66B, RX: 2B)
    [--] Reads / Writes: 38% / 62%
    [--] Total buffers: 1.0G global + 2.7M per thread (300 max threads)
    [OK] Maximum possible memory usage: 1.8G (5% of installed RAM)
    [OK] Slow queries: 0% (69/9M)
    [OK] Highest usage of available connections: 4% (12/300)
    [OK] Key buffer size / total MyISAM indexes: 1000.0M/1.4G
    [OK] Key buffer hit rate: 99.8% (198M cached / 312K reads)
    [OK] Query cache efficiency: 89.5% (6M cached / 6M selects)
    [[COLOR="#FF0000"]!![/COLOR]] Query cache prunes per day: 115508
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 238K sorts)
    [[COLOR="#FF0000"]!![/COLOR]] Joins performed without indexes: 3734
    [[COLOR="#FF0000"]!![/COLOR]] Temporary tables created on disk: 29% (123K on disk / 419K total)
    [OK] Thread cache hit rate: 98% (1K created / 175K connections)
    [[COLOR="#FF0000"]!![/COLOR]] Table cache hit rate: 0% (10K open / 1M opened)
    [OK] Open file limit used: 5% (15K/262K)
    [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
    [OK] InnoDB data size / buffer pool: 4.7M/8.0M
    
    -------- Recommendations -----------------------------------------------------
    
    General recommendations:
    
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        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:
    
        query_cache_size (> 16M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        table_cache (> 131072)
    My CPU / MEM load on server (almost all time):

    Code:
    mysql	  -  CPU 55% ~ 107%	  Mem 1.7% ~ 5%

    Can you tell me what i have wrong in My.cnf and what I should change/remove ?

    I really appreciate your assistance.

    thanks everyone
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    648
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    I moved this thread over to the "Optimization" forum. You will likely get more user feedback on MySQL configurations here.

    Thank you.
     
  3. nourjabi

    nourjabi Active Member

    Joined:
    Jun 24, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    Hi, thank you very much, I was actually looking for this section earlier, but didn't find it.
     
  4. thinkbot

    thinkbot Well-Known Member

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

    key_buffer = 2000M

    table_cache = 5000
    thread_cache_size = 50

    and add slow queries tracking

    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes


    and 50-107% its frop top ?
    couse there it means, in very simplistic description: 50% - 100% of 1 core + 7% of next core
    you got 8 cores (inc virtual), so 1/8 is used
    so it's quite good actually

    anyways please correcy my.cnf with values above and restart
    then please provide mysqltuner.pl after few hours
     
  5. nourjabi

    nourjabi Active Member

    Joined:
    Jun 24, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    Thank you very much, I will edit now, and let you know the result after 24 HR, to make sure sqltuner get's a good result log.



     
  6. nourjabi

    nourjabi Active Member

    Joined:
    Jun 24, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    Hi, Just a question, did you mean thread_cache_size = 50 or thread_cache_size = 50M ?
     
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    thread_cache_size = 50

    without M

    same for table_cache = 5000
    without M :)
     
  8. nourjabi

    nourjabi Active Member

    Joined:
    Jun 24, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    Thank you, i will hopefully post results later. thanks again.

     
  9. nourjabi

    nourjabi Active Member

    Joined:
    Jun 24, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    Here's 24 Hour SQLTuner readings:


     
  10. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    ok you need to review slow queries now, there are a lot of them
    is this custom script ?

    cd /root
    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest
    ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt

    and copy slow.txt here in
    Code:
     brackets
     
  11. nourjabi

    nourjabi Active Member

    Joined:
    Jun 24, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    Slow.txt

    Here's the slow.txt :)

    So, do you believe the whole issue is from slow queries (bad written sql queries) and I should let the client fix them ?


    Code:
    # 600.2s user time, 580ms system time, 106.82M rss, 260.20M vsz
    # Current date: Sun Sep 15 23:42:52 2013
    # Hostname: server.noblesintl.com
    # Files: /var/lib/mysql/mysql-slow.log
    # Overall: 4.21M total, 572 unique, 0.58 QPS, 0.01x concurrency __________
    # Time range: 2013-06-24 13:34:00 to 2013-09-15 23:42:50
    # Attribute          total     min     max     avg     95%  stddev  median
    # ============     ======= ======= ======= ======= ======= ======= =======
    # Exec time        102131s    29us    186s    24ms   100ms   243ms     4ms
    # Lock time           973s       0     96s   231us    52us    96ms    38us
    # Rows sent        910.56M       0   9.30M  227.05 1012.63   4.83k    0.99
    # Rows examine      16.78G       0 381.26M   4.18k   3.35k 692.75k   1.39k
    # Query size       244.40M      14  27.06k   60.94  118.34   43.86   51.63
    
    # Profile
    # Rank Query ID           Response time    Calls   R/Call   V/M   Item
    # ==== ================== ================ ======= ======== ===== ========
    #    1 0xD5DBA4FF14F318CD 85407.9630 83.6%  833023   0.1025  0.00 SELECT pages
    #    2 0xBF1709884D047149  5547.6923  5.4%  833023   0.0067  0.00 SELECT videocontent
    #    3 0xBCE087184A1D06D9  2052.1737  2.0%  833023   0.0025  0.00 SELECT signatures
    #    4 0x7C4EDE3BEFD0010D  1828.9393  1.8%      15 121.9293 11.53 SELECT t_news news_cats
    #    5 0x24E8F807B04FC9A7  1697.9560  1.7%   60001   0.0283  0.00 SELECT pages
    #    6 0x801BBD3669FBF8BF  1184.1595  1.2%   68463   0.0173  0.00 SELECT ips_countries
    #   31 0xD7BF21F1A60B501B    25.2908  0.0%      15   1.6861  2.51 UPDATE t_news
    # MISC 0xMISC              4386.9140  4.3% 1577703   0.0028   0.0 <565 ITEMS>
    
    # Query 1: 0.12 QPS, 0.01x concurrency, ID 0xD5DBA4FF14F318CD at byte 919210452
    # Scores: V/M = 0.00
    # Time range: 2013-06-24 13:34:00 to 2013-09-15 23:42:48
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         19  833023
    # Exec time     83  85408s    95ms      2s   103ms   105ms    17ms   100ms
    # Lock time     20    197s    12us   652ms   236us    36us     3ms    23us
    # Rows sent     89 819.06M   1.01k   1.01k   1.01k   1.01k       0   1.01k
    # Rows examine   4 819.06M   1.01k   1.01k   1.01k   1.01k       0   1.01k
    # Query size    11  27.01M      34      34      34      34       0      34
    # String:
    # Databases    childcli_ccs
    # Hosts        localhost
    # Users        childcli_db
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  #
    # 100ms  ################################################################
    #    1s  #
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'pages'\G
    #    SHOW CREATE TABLE `childcli_ccs`.`pages`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT * FROM pages where active=1\G
    
    # Query 2: 0.12 QPS, 0.00x concurrency, ID 0xBF1709884D047149 at byte 919209135
    # Scores: V/M = 0.00
    # Time range: 2013-06-24 13:34:00 to 2013-09-15 23:42:48
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         19  833023
    # Exec time      5   5548s     5ms      2s     7ms     8ms     5ms     6ms
    # Lock time      4     40s    19us    58ms    47us    54us    84us    47us
    # Rows sent      0 813.50k       1       1       1       1       0       1
    # Rows examine   6   1.13G   1.42k   1.42k   1.42k   1.42k       0   1.42k
    # Query size    21  53.23M      67      67      67      67       0      67
    # String:
    # Databases    childcli_ccs
    # Hosts        localhost
    # Users        childcli_db
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms  ################################################################
    #  10ms  #
    # 100ms  #
    #    1s  #
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'videocontent'\G
    #    SHOW CREATE TABLE `childcli_ccs`.`videocontent`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT * FROM videocontent where active=1 order by RAND(15) limit 1\G
    
    # Query 3: 0.12 QPS, 0.00x concurrency, ID 0xBCE087184A1D06D9 at byte 275949673
    # Scores: V/M = 0.00
    # Time range: 2013-06-24 13:34:00 to 2013-09-15 23:42:48
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         19  833023
    # Exec time      2   2052s     2ms   242ms     2ms     3ms   598us     2ms
    # Lock time      3     37s    17us    15ms    44us    52us    41us    42us
    # Rows sent      0 813.50k       1       1       1       1       0       1
    # Rows examine   7   1.19G   1.50k   1.50k   1.50k   1.50k       0   1.50k
    # Query size    16  39.72M      50      50      50      50       0      50
    # String:
    # Databases    childcli_ccs
    # Hosts        localhost
    # Users        childcli_db
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms  ################################################################
    #  10ms  #
    # 100ms  #
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'signatures'\G
    #    SHOW CREATE TABLE `childcli_ccs`.`signatures`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT * FROM signatures order by RAND(14) limit 1\G
    
    # Query 4: 0.00 QPS, 0.08x concurrency, ID 0x7C4EDE3BEFD0010D at byte 367802407
    # Scores: V/M = 11.53
    # Time range: 2013-09-14 23:55:12 to 2013-09-15 05:59:14
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0      15
    # Exec time      1   1829s     82s    186s    122s    184s     37s     97s
    # Lock time     46    451s    38us     96s     30s     93s     39s      6s
    # Rows sent      0     653      14      50   43.53   49.17    9.85   49.17
    # Rows examine  31   5.27G 319.18M 381.26M 359.98M 379.16M  31.04M 379.16M
    # Query size     0   2.78k     189     190  189.67  183.58       0  183.58
    # String:
    # Databases    b2bsy_b2b
    # Hosts        localhost
    # Users        b2bsy_root
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms
    #    1s
    #  10s+  ################################################################
    # Tables
    #    SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 't_news'\G
    #    SHOW CREATE TABLE `b2bsy_b2b`.`t_news`\G
    #    SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 'news_cats'\G
    #    SHOW CREATE TABLE `b2bsy_b2b`.`news_cats`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT * FROM `t_news` WHERE approved = 1 and deleted = 0 and id in (select DISTINCT n_id from news_cats where c_id = 652 order by n_id desc)  ORDER BY `sort_date` DESC,`id` DESC limit 0, 50\G
    
    # Query 5: 0.01 QPS, 0.00x concurrency, ID 0x24E8F807B04FC9A7 at byte 278543846
    # Scores: V/M = 0.00
    # Time range: 2013-06-24 13:34:00 to 2013-09-15 23:42:46
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          1   60001
    # Exec time      1   1698s    22ms      2s    28ms    34ms    10ms    26ms
    # Lock time      1     15s    22us   178ms   257us    47us     4ms    25us
    # Rows sent      0 292.97k       5       5       5       5       0       5
    # Rows examine   0 113.01M   1.93k   1.93k   1.93k   1.93k       0   1.93k
    # Query size     3   8.41M     147     147     147     147       0     147
    # String:
    # Databases    childcli_ccs
    # Hosts        localhost
    # Users        childcli_db
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ################################################################
    # 100ms  #
    #    1s  #
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'pages'\G
    #    SHOW CREATE TABLE `childcli_ccs`.`pages`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT * FROM pages WHERE catgoryid <> '11' AND catgoryid <> '25' AND catgoryid <> '26' AND catgoryid <> '29' AND  active=1 order by rand() limit 5\G
    
    # Query 6: 0.01 QPS, 0.00x concurrency, ID 0x801BBD3669FBF8BF at byte 904376402
    # Scores: V/M = 0.00
    # Time range: 2013-06-24 13:34:40 to 2013-09-15 23:42:41
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          1   68463
    # Exec time      1   1184s    15ms   105ms    17ms    19ms     3ms    16ms
    # Lock time      0      3s    16us   719us    40us    54us    10us    40us
    # Rows sent      0  31.42k       0       1    0.47    0.99    0.50       0
    # Rows examine  24   4.08G  62.56k  62.56k  62.56k  62.56k       0  62.56k
    # Query size     2   6.77M     100     104  103.74  102.22    1.45  102.22
    # String:
    # Databases    soccer_dbnew
    # Hosts        localhost
    # Users        soccer_deqaq
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ################################################################
    # 100ms  #
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `soccer_dbnew` LIKE 'ips_countries'\G
    #    SHOW CREATE TABLE `soccer_dbnew`.`ips_countries`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT * FROM ips_countries  WHERE 1603907757 >= Beginning_IP_Number  AND 1603907757 <= Ending_IP_Number\G
    
    # Query 31: 0.00 QPS, 0.00x concurrency, ID 0xD7BF21F1A60B501B at byte 268339734
    # Scores: V/M = 2.51
    # Time range: 2013-09-14 22:34:24 to 2013-09-15 10:22:46
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0      15
    # Exec time      0     25s   104ms      6s      2s      6s      2s   993ms
    # Lock time      0      2s    18us   701ms   126ms   672ms   233ms    40us
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine   0      15       1       1       1       1       0       1
    # Query size     0     888      58      60   59.20   59.77       1   56.92
    # String:
    # Databases    b2bsy_b2b
    # Hosts        localhost
    # Users        b2bsy_root
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms  ################################################################
    #    1s  ########################################################
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 't_news'\G
    #    SHOW CREATE TABLE `b2bsy_b2b`.`t_news`\G
    update `t_news` set
    		`counter` = '144'
    		where id = 904\G
    # Converted for EXPLAIN
    # EXPLAIN /*!50100 PARTITIONS*/
    select 
    		`counter` = '144' from `t_news` where  id = 904\G
    
     
  12. thinkbot

    thinkbot Well-Known Member

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

    Yes, your problems are becouse :
    Query 1:
    SELECT * FROM pages where active=1\G

    it takes most of the time in mysql 83.6% of all queries
    what is this query trying to do ? select all elements, or only count ?

    if count use
    SELECT COUNT(*) as total FROM pages where active=1\G

    if you really want to select all elements, you should select only columns you need
    example SELECT id, user, content FROM pages where active=1
    not SELECT * FROM

    also make sure, you got index on active column in pages


    Query 2:
    second one, queries with rand() (especially on bigger tables):

    SELECT * FROM videocontent where active=1 order by RAND(15) limit 1\G
    with RAND()

    ~jk - ORDER BY RAND()
     
  13. nourjabi

    nourjabi Active Member

    Joined:
    Jun 24, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    I see,

    Seems my suspicious were true.

    That domain is giving me a headache, i will let my programmer check this topic and see if she can
    correct those queries to solve the issue, thanks .



     
  14. nourjabi

    nourjabi Active Member

    Joined:
    Jun 24, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    hi,

    I have made the changes to SQL Queries as you mentioned, and BAM!

    Down from 89% + to 8~10% , I mean you are the man.


    Do you think I should fine tune anything else now?


    Here's plsqltuner report:


    Code:
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 4G (Tables: 2157)
    [--] Data in InnoDB tables: 17M (Tables: 168)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 26)
    [!!] Total fragmented tables: 196
    
    -------- Security Recommendations  -------------------------------------------
    [!!] User 'link2ad_new@localhost' has no password set.
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 23m 6s (460K q [332.227 qps], 1K conn, TX: 9B, RX: 27M)
    [--] Reads / Writes: 91% / 9%
    [--] Total buffers: 2.4G global + 8.2M per thread (500 max threads)
    [!!] Maximum possible memory usage: 6.4G (86% of installed RAM)
    [!!] Slow queries: 10% (49K/460K)
    [OK] Highest usage of available connections: 1% (5/500)
    [OK] Key buffer size / total MyISAM indexes: 2.0G/1.1G
    [OK] Key buffer hit rate: 98.6% (565K cached / 7K reads)
    [OK] Query cache efficiency: 86.1% (387K cached / 449K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 36K sorts)
    [!!] Joins performed without indexes: 197
    [!!] Temporary tables created on disk: 35% (19K on disk / 53K total)
    [OK] Thread cache hit rate: 99% (5 created / 1K connections)
    [OK] Table cache hit rate: 66% (193 open / 289 opened)
    [OK] Open file limit used: 2% (334/13K)
    [OK] Table locks acquired immediately: 99% (68K immediate / 68K locks)
    [OK] InnoDB data size / buffer pool: 17.8M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Adjust your join queries to always utilize indexes
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        join_buffer_size (> 2.0M, or always use indexes with joins)
    
     
  15. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    can you generate slow.txt again with pt-query-digest, with new stats ?
    and can you tell how did you correct your queries, how does it look now ?
     
  16. nourjabi

    nourjabi Active Member

    Joined:
    Jun 24, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    Ok, But it will take time untill tomorrow, as I removed old slow log to make sure i get new readings, i will post new results tomorrow after 24 HR.

    Also, I didn't do the changes my self, I handed your Notes to the programmer of the website and he checked them out and made the necessary changes.


     
  17. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    you dont have to wait 24h to generate slowlog, can be only few hours
     
  18. nourjabi

    nourjabi Active Member

    Joined:
    Jun 24, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    Code:
    # 129.5s user time, 130ms system time, 43.58M rss, 193.03M vsz
    # Current date: Sat Sep 21 03:04:44 2013
    # Hostname: server.noblesintl.com
    # Files: /var/lib/mysql/mysql-slow.log
    # Overall: 911.63k total, 406 unique, 41.03 QPS, 0.12x concurrency _______
    # Time range: 2013-09-20 20:54:22 to 2013-09-21 03:04:42
    # Attribute          total     min     max     avg     95%  stddev  median
    # ============     ======= ======= ======= ======= ======= ======= =======
    # Exec time          2759s    29us    100s     3ms     5ms   246ms     1ms
    # Lock time            65s       0      6s    71us    49us    12ms    22us
    # Rows sent         41.95M       0   9.30M   48.25    9.83  10.34k    0.99
    # Rows examine       4.43G       0 396.46M   5.09k   3.35k 974.87k   1.39k
    # Query size        69.31M      14 106.31k   79.72  118.34  125.81   69.19
    
    # Profile
    # Rank Query ID           Response time  Calls  R/Call  V/M   Item
    # ==== ================== ============== ====== ======= ===== ============
    #    1 0x7C4EDE3BEFD0010D 581.2365 21.1%      6 96.8727  0.25 SELECT t_news news_cats
    #    2 0xCEFAD6A90BFF75CD 436.5478 15.8% 237952  0.0018  0.00 SELECT videocontent
    #    3 0x253746F4D0305402 358.7444 13.0%  15879  0.0226  0.00 SELECT pages
    #    4 0x36ED1CBF7ACA2E3B 243.0325  8.8% 237952  0.0010  0.00 SELECT signatures
    #    5 0x95A32AF22D1B8058 202.1475  7.3%  49707  0.0041  0.00 SELECT pages
    #    6 0x801BBD3669FBF8BF 166.7257  6.0%   9939  0.0168  0.00 SELECT ips_countries
    #    7 0x67A347A2812914DF 103.5395  3.8%   1914  0.0541  7.64 SELECT post
    #    8 0x86251B43B9DDF65A  60.5789  2.2%  15879  0.0038  0.00 SELECT pages
    #    9 0x043FF6692C1BC226  44.5401  1.6%  86429  0.0005  0.34 SELECT ads
    #   10 0x62F0C89E29502FE3  40.5223  1.5%   1116  0.0363  0.00 SELECT add_comment
    #   11 0xBD961FCD0E8FE879  34.3455  1.2%  16274  0.0021  3.08 SELECT online
    #   12 0xC32B386D3651DF02  28.2047  1.0%   8462  0.0033  0.00 SELECT news
    #   13 0xA04424D8B00ECAC4  22.9826  0.8%  18757  0.0012  0.00 SELECT comp_per
    #   14 0xBCD934C7A7981E7E  21.9524  0.8%   1357  0.0162  0.00 SELECT t_news
    #   15 0xD7BF21F1A60B501B  21.6663  0.8%      2 10.8331  4.16 UPDATE t_news
    #   16 0x5E0E2E4D89C8B96D  21.5775  0.8%   1357  0.0159  0.00 SELECT t_news
    #   17 0x49649CDCE6DA22B3  20.5867  0.7%   5292  0.0039  0.00 SELECT pages
    #   18 0x046457F0CE3E8295  20.0790  0.7%   5300  0.0038  0.00 SELECT pages
    #   19 0x04D731CE69EB754C  19.8876  0.7%   4254  0.0047  0.00 SELECT pages
    #   20 0xCF8D24A96B4A4B96  18.7486  0.7%      7  2.6784  4.33 UPDATE smtp sends
    #   30 0x55F1CC51FAEEAE8D  11.0347  0.4%     29  0.3805  0.33 INSERT av_cat
    # MISC 0xMISC             280.3069 10.2% 193766  0.0014   0.0 <385 ITEMS>
    
    # Query 1: 0.00 QPS, 0.04x concurrency, ID 0x7C4EDE3BEFD0010D at byte 206163548
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.25
    # Time range: 2013-09-20 23:05:31 to 2013-09-21 02:53:00
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0       6
    # Exec time     21    581s     87s    100s     97s     97s      5s     97s
    # Lock time      0   333us    42us    66us    55us    63us     8us    60us
    # Rows sent      0     228       6      50      38   49.17   16.47   49.17
    # Rows examine  50   2.24G 331.86M 396.46M 382.48M 379.16M  19.24M 379.16M
    # Query size     0   1.11k     189     190  189.67  183.58       0  183.58
    # String:
    # Databases    b2bsy_b2b
    # Hosts        localhost
    # Users        b2bsy_root
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms
    #    1s
    #  10s+  ################################################################
    # Tables
    #    SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 't_news'\G
    #    SHOW CREATE TABLE `b2bsy_b2b`.`t_news`\G
    #    SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 'news_cats'\G
    #    SHOW CREATE TABLE `b2bsy_b2b`.`news_cats`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT * FROM `t_news` WHERE approved = 1 and deleted = 0 and id in (select DISTINCT n_id from news_cats where c_id = 626 order by n_id desc)  ORDER BY `sort_date` DESC,`id` DESC limit 0, 50\G
    
    # Query 2: 10.71 QPS, 0.02x concurrency, ID 0xCEFAD6A90BFF75CD at byte 190392332
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-09-20 20:54:23 to 2013-09-21 03:04:42
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         26  237952
    # Exec time     15    437s     1ms   341ms     2ms     2ms     1ms     2ms
    # Lock time      9      6s    19us   196us    26us    38us     6us    23us
    # Rows sent      0 232.38k       1       1       1       1       0       1
    # Rows examine   7 330.18M   1.42k   1.42k   1.42k   1.42k       0   1.42k
    # Query size    29  20.42M      90      90      90      90       0      90
    # String:
    # Databases    childcli_ccs
    # Hosts        localhost
    # Users        childcli_db
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms  ################################################################
    #  10ms  #
    # 100ms  #
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'videocontent'\G
    #    SHOW CREATE TABLE `childcli_ccs`.`videocontent`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT id,active,btextcode,name FROM videocontent where active=1 order by RAND(20) limit 1\G
    
    # Query 3: 0.71 QPS, 0.02x concurrency, ID 0x253746F4D0305402 at byte 185890808
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-09-20 20:54:23 to 2013-09-21 03:04:41
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          1   15879
    # Exec time     13    359s    19ms   213ms    23ms    24ms     3ms    22ms
    # Lock time      0   580ms    27us    18ms    36us    36us   249us    30us
    # Rows sent      0  77.53k       5       5       5       5       0       5
    # Rows examine   0  29.97M   1.93k   1.93k   1.93k   1.93k       0   1.93k
    # Query size     4   2.89M     191     191     191     191       0     191
    # String:
    # Databases    childcli_ccs
    # Hosts        localhost
    # Users        childcli_db
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ################################################################
    # 100ms  #
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'pages'\G
    #    SHOW CREATE TABLE `childcli_ccs`.`pages`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT id,title,shortdesc,thumbs,date,text,catgoryid FROM pages WHERE catgoryid <> '11' AND catgoryid <> '25' AND catgoryid <> '26' AND catgoryid <> '29' AND  active=1 order by rand() limit 5\G
    
    # Query 4: 10.71 QPS, 0.01x concurrency, ID 0x36ED1CBF7ACA2E3B at byte 93772805
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-09-20 20:54:23 to 2013-09-21 03:04:42
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         26  237952
    # Exec time      8    243s   744us    18ms     1ms     1ms   174us     1ms
    # Lock time      8      6s    17us   489us    23us    28us     4us    21us
    # Rows sent      0 232.38k       1       1       1       1       0       1
    # Rows examine   7 349.24M   1.50k   1.50k   1.50k   1.50k       0   1.50k
    # Query size    23  16.34M      72      72      72      72       0      72
    # String:
    # Databases    childcli_ccs
    # Hosts        localhost
    # Users        childcli_db
    # Query_time distribution
    #   1us
    #  10us
    # 100us  ######################
    #   1ms  ################################################################
    #  10ms  #
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'signatures'\G
    #    SHOW CREATE TABLE `childcli_ccs`.`signatures`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT id,name_image,url_image FROM signatures order by RAND(20) limit 1\G
    
    # Query 5: 2.24 QPS, 0.01x concurrency, ID 0x95A32AF22D1B8058 at byte 93779330
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-09-20 20:54:23 to 2013-09-21 03:04:41
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          5   49707
    # Exec time      7    202s     3ms    47ms     4ms     6ms     1ms     4ms
    # Lock time      3      2s    15us    23ms    45us    49us   155us    42us
    # Rows sent      0 258.42k       0      57    5.32   19.46    6.76    2.90
    # Rows examine   1  48.97M   1.01k   1.01k   1.01k   1.01k       0   1.01k
    # Query size     3   2.51M      52      54   52.90   51.63       0   51.63
    # String:
    # Databases    childcli_ccs
    # Hosts        localhost
    # Users        childcli_db
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms  ################################################################
    #  10ms  #
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'pages'\G
    #    SHOW CREATE TABLE `childcli_ccs`.`pages`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT * FROM pages where catgoryid='13' AND active=1\G
    
    # Query 6: 0.45 QPS, 0.01x concurrency, ID 0x801BBD3669FBF8BF at byte 192524714
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-09-20 20:54:35 to 2013-09-21 03:04:35
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          1    9939
    # Exec time      6    167s    15ms    36ms    17ms    19ms     1ms    16ms
    # Lock time      0   331ms    18us   135us    33us    47us     7us    30us
    # Rows sent      0   4.95k       0       1    0.51    0.99    0.50    0.99
    # Rows examine  13 607.21M  62.56k  62.56k  62.56k  62.56k       0  62.56k
    # Query size     1 1007.27k     100     104  103.78  102.22    1.36  102.22
    # String:
    # Databases    soccer_dbnew
    # Hosts        localhost
    # Users        soccer_deqaq
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ################################################################
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `soccer_dbnew` LIKE 'ips_countries'\G
    #    SHOW CREATE TABLE `soccer_dbnew`.`ips_countries`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT * FROM ips_countries  WHERE 1593212649 >= Beginning_IP_Number  AND 1593212649 <= Ending_IP_Number\G
    
    # Query 7: 0.71 QPS, 0.04x concurrency, ID 0x67A347A2812914DF at byte 176036063
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 7.64
    # Time range: 2013-09-21 01:01:04 to 01:46:08
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0    1914
    # Exec time      3    104s    29us     20s    54ms    87ms   643ms   167us
    # Lock time      0       0       0       0       0       0       0       0
    # Rows sent     52  21.90M       0   9.30M  11.72k  13.13k 225.41k    4.96
    # Rows examine   0  21.97M       0   9.30M  11.75k  13.13k 225.41k   13.83
    # Query size     0 149.76k      43   4.53k   80.12  124.25  110.07   56.92
    # String:
    # Databases    horde (617/32%), shabable_2... (128/6%)... 60 more
    # Hosts        localhost
    # Users        root
    # Query_time distribution
    #   1us
    #  10us  #####################################
    # 100us  ################################################################
    #   1ms  ##################
    #  10ms  ############
    # 100ms  #####
    #    1s  #
    #  10s+  #
    # Tables
    #    SHOW TABLE STATUS FROM `shabable_vbnew1` LIKE 'post'\G
    #    SHOW CREATE TABLE `shabable_vbnew1`.`post`\G
    SELECT /*!40001 SQL_NO_CACHE */ * FROM `post`\G
    # Converted for EXPLAIN
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT /*!40001 SQL_NO_CACHE */ * FROM `post`\G
    
    # Query 8: 0.71 QPS, 0.00x concurrency, ID 0x86251B43B9DDF65A at byte 93728433
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-09-20 20:54:23 to 2013-09-21 03:04:41
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          1   15879
    # Exec time      2     61s     3ms    21ms     4ms     4ms   532us     4ms
    # Lock time      1   827ms    20us     9ms    52us    54us   124us    49us
    # Rows sent      0  46.52k       3       3       3       3       0       3
    # Rows examine   0  18.41M   1.19k   1.19k   1.19k   1.19k       0   1.19k
    # Query size     1   1.33M      88      88      88      88       0      88
    # String:
    # Databases    childcli_ccs
    # Hosts        localhost
    # Users        childcli_db
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms  ################################################################
    #  10ms  #
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'pages'\G
    #    SHOW CREATE TABLE `childcli_ccs`.`pages`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT id,title,bimage FROM pages WHERE bimage !='' AND active=1 order by RAND() limit 3\G
    
    # Query 9: 3.89 QPS, 0.00x concurrency, ID 0x043FF6692C1BC226 at byte 167526754
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.34
    # Time range: 2013-09-20 20:54:22 to 2013-09-21 03:04:42
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          9   86429
    # Exec time      1     45s   182us      2s   515us   799us    13ms   260us
    # Lock time      6      4s    18us      2s    47us    44us     6ms    22us
    # Rows sent      0  77.32k       0       1    0.92    0.99    0.28    0.99
    # Rows examine   0   1.43M      15      20   17.38   19.46    1.28   16.81
    # Query size     8   5.77M      70      70      70      70       0      70
    # String:
    # Databases    childcli_ccs
    # Hosts        localhost
    # Users        childcli_db
    # Query_time distribution
    #   1us
    #  10us
    # 100us  ################################################################
    #   1ms  #
    #  10ms  #
    # 100ms  #
    #    1s  #
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'ads'\G
    #    SHOW CREATE TABLE `childcli_ccs`.`ads`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT * FROM ads WHERE cat='0' AND active='1' ORDER BY rand() LIMIT 1\G
    
    # Query 10: 0.05 QPS, 0.00x concurrency, ID 0x62F0C89E29502FE3 at byte 83679032
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-09-20 20:54:33 to 2013-09-21 03:04:33
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0    1116
    # Exec time      1     41s    35ms    55ms    36ms    38ms     1ms    36ms
    # Lock time      0    35ms    18us    82us    31us    42us     8us    33us
    # Rows sent      0  19.50k       0     895   17.89   65.89   44.68    5.75
    # Rows examine   2 128.61M 118.01k 118.01k 118.01k 118.01k       0 118.01k
    # Query size     0  69.70k      61      64   63.96   62.76    0.25   62.76
    # String:
    # Databases    soccer_dbnew
    # Hosts        localhost
    # Users        soccer_deqaq
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ################################################################
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `soccer_dbnew` LIKE 'add_comment'\G
    #    SHOW CREATE TABLE `soccer_dbnew`.`add_comment`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    select * from add_comment where `id_rel`='6456' and `appear`='1'\G
    
    # Query 11: 0.73 QPS, 0.00x concurrency, ID 0xBD961FCD0E8FE879 at byte 176178434
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 3.08
    # Time range: 2013-09-20 20:54:22 to 2013-09-21 03:04:41
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          1   16274
    # Exec time      1     34s    71us      6s     2ms   176us    81ms    98us
    # Lock time     50     33s    17us      6s     2ms    31us    81ms    21us
    # Rows sent      0  10.50k       0       2    0.66    0.99    0.47    0.99
    # Rows examine   0   2.90M      86     249  186.62  234.30   44.76  192.76
    # Query size     1 792.40k      46      52   49.86   51.63    1.43   49.17
    # String:
    # Databases    childcli_ccs
    # Hosts        localhost
    # Users        childcli_db
    # Query_time distribution
    #   1us
    #  10us  #################################################
    # 100us  ################################################################
    #   1ms  #
    #  10ms  #
    # 100ms  #
    #    1s  #
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'online'\G
    #    SHOW CREATE TABLE `childcli_ccs`.`online`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    select xtime from online where xip='90.148.19.3'\G
    
    # Query 12: 0.38 QPS, 0.00x concurrency, ID 0xC32B386D3651DF02 at byte 183579739
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-09-20 20:54:33 to 2013-09-21 03:04:36
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0    8462
    # Exec time      1     28s     3ms    21ms     3ms     4ms   529us     3ms
    # Lock time      0   372ms    21us     1ms    43us    54us    24us    44us
    # Rows sent      0   9.45k       1       2    1.14    1.96    0.34    0.99
    # Rows examine   0  28.02M   3.39k   3.39k   3.39k   3.35k       0   3.35k
    # Query size     1   1.06M     130     133  131.14  130.47    1.50  130.47
    # String:
    # Databases    soccer_dbnew
    # Hosts        localhost
    # Users        soccer_deqaq
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms  ################################################################
    #  10ms  #
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `soccer_dbnew` LIKE 'news'\G
    #    SHOW CREATE TABLE `soccer_dbnew`.`news`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT title,image1,page,id FROM news WHERE Rel_Window = '1' AND issue_id='1' AND page = '6' order by issue_id desc , details_date\G
    
    # Query 13: 0.85 QPS, 0.00x concurrency, ID 0xA04424D8B00ECAC4 at byte 203189436
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-09-20 20:54:27 to 2013-09-21 03:03:44
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          2   18757
    # Exec time      0     23s   866us     4ms     1ms     2ms   262us     1ms
    # Lock time      0   386ms    12us   523us    20us    31us     7us    18us
    # Rows sent      0  18.31k       0       1    1.00    0.99    0.02    0.99
    # Rows examine   5 263.43M  14.20k  16.62k  14.38k  15.96k  580.27  13.78k
    # Query size     1 788.61k      39      44   43.05   42.48    0.68   42.48
    # String:
    # Databases    industry_a... (17380/92%)... 1 more
    # Hosts        localhost
    # Users        industry_a... (17380/92%)... 1 more
    # Query_time distribution
    #   1us
    #  10us
    # 100us  ##########################
    #   1ms  ################################################################
    #  10ms
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `medicalg_medical` LIKE 'comp_per'\G
    #    SHOW CREATE TABLE `medicalg_medical`.`comp_per`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    select * from comp_per where comp_id="5880"\G
    
    # Query 14: 0.06 QPS, 0.00x concurrency, ID 0xBCD934C7A7981E7E at byte 146775366
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-09-20 20:54:48 to 2013-09-21 03:04:10
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0    1357
    # Exec time      0     22s    15ms    71ms    16ms    17ms     2ms    16ms
    # Lock time      0    49ms    22us     3ms    36us    44us    84us    30us
    # Rows sent      0  13.25k      10      10      10      10       0      10
    # Rows examine   0  18.47M  13.94k  13.94k  13.94k  13.94k       0  13.94k
    # Query size     0 161.67k     122     122     122     122       0     122
    # String:
    # Databases    b2bsy_b2b
    # Hosts        localhost
    # Users        b2bsy_root
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ################################################################
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 't_news'\G
    #    SHOW CREATE TABLE `b2bsy_b2b`.`t_news`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT id, title, text, img FROM t_news where approved = '1' and deleted = '0' order by sort_date desc, id desc limit 0,10\G
    
    # Query 15: 0.18 QPS, 1.97x concurrency, ID 0xD7BF21F1A60B501B at byte 167749136
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 4.16
    # Time range: 2013-09-21 01:02:55 to 01:03:06
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0       2
    # Exec time      0     22s      6s     16s     11s     16s      7s     11s
    # Lock time      0    87us    43us    44us    43us    44us       0    43us
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine   0       2       1       1       1       1       0       1
    # Query size     0     119      59      60   59.50      60    0.71   59.50
    # String:
    # Databases    b2bsy_b2b
    # Hosts        localhost
    # Users        b2bsy_root
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms
    #    1s  ################################################################
    #  10s+  ################################################################
    # Tables
    #    SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 't_news'\G
    #    SHOW CREATE TABLE `b2bsy_b2b`.`t_news`\G
    update `t_news` set
    		`counter` = '184'
    		where id = 7160\G
    # Converted for EXPLAIN
    # EXPLAIN /*!50100 PARTITIONS*/
    select 
    		`counter` = '184' from `t_news` where  id = 7160\G
    
    # Query 16: 0.06 QPS, 0.00x concurrency, ID 0x5E0E2E4D89C8B96D at byte 74994399
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-09-20 20:54:48 to 2013-09-21 03:04:10
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0    1357
    # Exec time      0     22s    15ms    25ms    16ms    17ms     1ms    16ms
    # Lock time      0    31ms    16us    54us    22us    27us     3us    21us
    # Rows sent      0   6.63k       5       5       5       5       0       5
    # Rows examine   0  18.47M  13.93k  13.93k  13.93k  13.93k       0  13.93k
    # Query size     0 117.94k      89      89      89      89       0      89
    # String:
    # Databases    b2bsy_b2b
    # Hosts        localhost
    # Users        b2bsy_root
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ################################################################
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 't_news'\G
    #    SHOW CREATE TABLE `b2bsy_b2b`.`t_news`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT * FROM t_news where approved = 1 and deleted = 0 ORDER BY counter DESC LIMIT 0 , 5\G
    
    # Query 17: 0.24 QPS, 0.00x concurrency, ID 0x49649CDCE6DA22B3 at byte 93769387
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-09-20 20:54:23 to 2013-09-21 03:04:41
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0    5292
    # Exec time      0     21s     3ms    44ms     4ms     4ms     1ms     4ms
    # Lock time      0   265ms    19us     4ms    49us    54us   101us    44us
    # Rows sent      0  51.68k      10      10      10      10       0      10
    # Rows examine   0   5.26M   1.02k   1.02k   1.02k   1.02k       0   1.02k
    # Query size     0 439.28k      85      85      85      85       0      85
    # String:
    # Databases    childcli_ccs
    # Hosts        localhost
    # Users        childcli_db
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms  ################################################################
    #  10ms  #
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'pages'\G
    #    SHOW CREATE TABLE `childcli_ccs`.`pages`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT id,title,date,visitor FROM pages where active=1 order by visitor DESC LIMIT 10\G
    
    # Query 18: 0.24 QPS, 0.00x concurrency, ID 0x046457F0CE3E8295 at byte 93752012
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-09-20 20:54:23 to 2013-09-21 03:04:41
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0    5300
    # Exec time      0     20s     3ms    50ms     4ms     4ms   978us     4ms
    # Lock time      0   152ms    18us   175us    28us    42us     8us    26us
    # Rows sent      0  25.88k       5       5       5       5       0       5
    # Rows examine   0   5.25M   1.01k   1.01k   1.01k   1.01k       0   1.01k
    # Query size     0 496.88k      96      96      96      96       0      96
    # String:
    # Databases    childcli_ccs
    # Hosts        localhost
    # Users        childcli_db
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms  ################################################################
    #  10ms  #
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'pages'\G
    #    SHOW CREATE TABLE `childcli_ccs`.`pages`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT id,title,rate,votes FROM pages where active=1 and votes != '1' order by rate DESC LIMIT 5\G
    
    # Query 19: 0.19 QPS, 0.00x concurrency, ID 0x04D731CE69EB754C at byte 93821858
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-09-20 20:54:27 to 2013-09-21 03:04:41
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0    4254
    # Exec time      0     20s     4ms    14ms     5ms     5ms   395us     5ms
    # Lock time      0   135ms    27us    85us    31us    36us     2us    30us
    # Rows sent      0  20.00k       0       5    4.81    4.96    0.79    4.96
    # Rows examine   0   4.21M   1.01k   1.01k   1.01k 1012.63    0.00 1012.63
    # Query size     1   1.01M     228     293  248.67  271.23   11.68  234.30
    # String:
    # Databases    childcli_ccs
    # Hosts        localhost
    # Users        childcli_db
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms  ################################################################
    #  10ms  #
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'pages'\G
    #    SHOW CREATE TABLE `childcli_ccs`.`pages`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT id,title,active FROM pages WHERE catgoryid <> '11' AND catgoryid <> '25' AND catgoryid <> '26' AND catgoryid <> '29' AND  id != 1052 AND active=1 AND MATCH (title) AGAINST ('سرطان الدماغ عند الأطفال :اسباب ,اعراض,تشخيص, علاج' IN BOOLEAN MODE) order by title LIMIT 0,5\G
    
    # Query 20: 0.21 QPS, 0.55x concurrency, ID 0xCF8D24A96B4A4B96 at byte 193126382
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 4.33
    # Time range: 2013-09-21 02:00:43 to 02:01:17
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0       7
    # Exec time      0     19s   153ms     10s      3s     10s      3s   900ms
    # Lock time      0       0       0       0       0       0       0       0
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine   0  31.59k      11  26.75k   4.51k  25.99k   8.88k  652.75
    # Query size     0   1.07k     154     157  156.29  151.03       0  151.03
    # String:
    # Databases    eximstats
    # Hosts        localhost
    # Users        eximstats
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms  ################################################################
    #    1s  ################################
    #  10s+  ################
    # Tables
    #    SHOW TABLE STATUS FROM `eximstats` LIKE 'smtp'\G
    #    SHOW CREATE TABLE `eximstats`.`smtp`\G
    #    SHOW TABLE STATUS FROM `eximstats` LIKE 'sends'\G
    #    SHOW CREATE TABLE `eximstats`.`sends`\G
    update smtp INNER JOIN sends ON (sends.msgid=smtp.msgid) set smtp.processed=1 where smtp.transport_is_remote=1 and sends.user='childcli' and smtp.processed=0\G
    # Converted for EXPLAIN
    # EXPLAIN /*!50100 PARTITIONS*/
    select  smtp.processed=1 from smtp INNER JOIN sends ON (sends.msgid=smtp.msgid) where  smtp.transport_is_remote=1 and sends.user='childcli' and smtp.processed=0\G
    
    # Query 30: 0.00 QPS, 0.00x concurrency, ID 0x55F1CC51FAEEAE8D at byte 175977888
    # This item is included in the report because it matches --outliers.
    # Scores: V/M = 0.33
    # Time range: 2013-09-20 22:01:01 to 2013-09-21 02:03:51
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0      29
    # Exec time      0     11s   102ms      1s   381ms      1s   354ms   155ms
    # Lock time      0     9ms    65us     2ms   317us   490us   322us   185us
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine   0       0       0       0       0       0       0       0
    # Query size     0 212.24k     139 106.31k   7.32k  27.29k  19.63k  284.79
    # String:
    # Databases    b2bsy_b2b
    # Hosts        localhost
    # Users        b2bsy_root
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms  ################################################################
    #    1s  ##########
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 'av_cat'\G
    #    SHOW CREATE TABLE `b2bsy_b2b`.`av_cat`\G
    INSERT INTO `av_cat` (
    								 `id`
    								)
    								VALUES (
    							'14312'
    							),(
    							'14030'
    							),(
    							'13578'
    							),(
    							'11630'
    							),(
    							'11535'
    							),(
    							'11350'
    							),(
    							'11165'
    							),(
    							'10937'
    							)\G
    
     
  19. thinkbot

    thinkbot Well-Known Member

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

    SELECT * FROM `t_news` WHERE approved = 1 and deleted = 0 and id in (select DISTINCT n_id from news_cats where c_id = 626 order by n_id desc) ORDER BY `sort_date` DESC,`id` DESC limit 0, 50\G


    97 seconds, each execution

    check if you have indexes
    (id) or/and (sort_date) on t_news table
    and
    (n_id, c_id) on news_cats table


    the rest is fine, altough its good to replace rand() with something else
    there are many solutions for faster rand results, not using temporary table, you can check the link that I send in the first page of the topic
     
  20. nourjabi

    nourjabi Active Member

    Joined:
    Jun 24, 2013
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    Thanks, I have also sent this query modification to the programmer.

    But I have a question, even thought we have tweaked the settings and the CPU usage went down a lot,
    what can we do to improve speed? some sites still feel sluggish while loading .

    thanks a head for your help
     
Loading...

Share This Page