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.

Help with optimzation : mysqltuner, tuning-primer

Discussion in 'Workarounds and Optimization' started by dos_santos_rj, May 23, 2013.

  1. dos_santos_rj

    dos_santos_rj Member

    Joined:
    Apr 12, 2012
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello,

    I'm trying to get the best performance in my vps!
    My vps has:
    RAM guaranteed: 512MB
    RAM burstable: 1024MB

    I run the ./mysqltuner.pl

    Code:
     >>  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
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.30-cll
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 330M (Tables: 483)
    [--] Data in InnoDB tables: 1008K (Tables: 54)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [OK] Total fragmented tables: 0
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 22h 59m 29s (3M q [18.048 qps], 48K conn, TX: 21B, RX: 394M)
    [--] Reads / Writes: 88% / 12%
    [--] Total buffers: 248.0M global + 4.6M per thread (60 max threads)
    [OK] Maximum possible memory usage: 521.8M (50% of installed RAM)
    [OK] Slow queries: 0% (6K/3M)
    [OK] Highest usage of available connections: 18% (11/60)
    [OK] Key buffer size / total MyISAM indexes: 64.0M/47.5M
    [OK] Key buffer hit rate: 98.1% (6M cached / 124K reads)
    [OK] Query cache efficiency: 71.5% (1M cached / 2M selects)
    [!!] Query cache prunes per day: 23492
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 60K sorts)
    [!!] Joins performed without indexes: 1677
    [OK] Temporary tables created on disk: 7% (7K on disk / 97K total)
    [OK] Thread cache hit rate: 98% (587 created / 48K connections)
    [!!] Table cache hit rate: 1% (71 open / 5K opened)
    [OK] Open file limit used: 0% (1/40K)
    [OK] Table locks acquired immediately: 99% (900K immediate / 900K locks)
    [OK] InnoDB data size / buffer pool: 1008.0K/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 24M)
        join_buffer_size (> 2.0M, or always use indexes with joins)
        table_cache (> 20000)
    and I run the ./tuning-primer.sh

    Code:
      -- MYSQL PERFORMANCE TUNING PRIMER --
                 - By: Matthew Montgomery -
    
    MySQL Version 5.5.30-cll i686
    
    Uptime = 1 days 23 hrs 0 min 28 sec
    Avg. qps = 18
    Total Questions = 3053423
    Threads Connected = 3
    
    Warning: Server has not been running for at least 48hrs.
    It may not be safe to use these recommendations
    
    To find out more information on how each of these
    runtime variables effects performance visit:
    http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
    Visit http://www.mysql.com/products/enterprise/advisors.html
    for info about MySQL's Enterprise Monitoring and Advisory Service
    
    SLOW QUERIES
    The slow query log is enabled.
    Current long_query_time = 4.000000 sec.
    You have 6665 out of 3053444 that take longer than 4.000000 sec. to complete
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
    
    WORKER THREADS
    Current thread_cache_size = 4
    Current threads_cached = 3
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 60
    Current threads_connected = 3
    Historic max_used_connections = 11
    The number of used connections is 18% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    INNODB STATUS
    Current InnoDB index space = 1 M
    Current InnoDB data space = 1008 K
    Current InnoDB buffer pool free = 79 %
    Current innodb_buffer_pool_size = 128 M
    Depending on how much space your innodb indexes take up it may be safe
    to increase this value to up to 2 / 3 of total system memory
    
    MEMORY USAGE
    Max Memory Ever Allocated : 282 M
    Configured Max Per-thread Buffers : 273 M
    Configured Max Global Buffers : 232 M
    Configured Max Memory Limit : 505 M
    Physical Memory : 1.00 G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    Current MyISAM index space = 47 M
    Current key_buffer_size = 64 M
    Key cache miss rate is 1 : 51
    Key buffer free ratio = 88 %
    Your key_buffer_size seems to be fine
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 24 M
    Current query_cache_used = 597 K
    Current query_cache_limit = 8 M
    Current Query cache Memory fill ratio = 2.43 %
    Current query_cache_min_res_unit = 4 K
    Your query_cache_size seems to be too high.
    Perhaps you can use these resources elsewhere
    MySQL won't cache query results that are larger than query_cache_limit in size
    
    SORT OPERATIONS
    Current sort_buffer_size = 2 M
    Current read_rnd_buffer_size = 256 K
    Sort buffer seems to be fine
    
    JOINS
    Current join_buffer_size = 2.00 M
    You have had 1677 queries where a join could not use an index properly
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    If you are unable to optimize your queries you may want to increase your
    join_buffer_size to accommodate larger joins in one pass.
    
    Note! This script will still suggest raising the join_buffer_size when
    ANY joins not using indexes are found.
    
    OPEN FILES LIMIT
    Current open_files_limit = 40070 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    
    TABLE CACHE
    Current table_open_cache = 20000 tables
    Current table_definition_cache = 1024 tables
    You have a total of 578 tables
    You have 579 open tables.
    The table_cache value seems to be fine
    
    TEMP TABLES
    Current max_heap_table_size = 16 M
    Current tmp_table_size = 16 M
    Of 90595 temp tables, 7% were created on disk
    Created disk tmp tables ratio seems fine
    
    TABLE SCANS
    Current read_buffer_size = 128 K
    Current table scan ratio = 6 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 4437
    You may benefit from selective use of InnoDB.
    If you have long running SELECT's against MyISAM tables and perform
    frequent updates consider setting 'low_priority_updates=1'
    If you have a high concurrency of inserts on Dynamic row-length tables
    consider setting 'concurrent_insert=ALWAYS'.

    vi /etc/my.cnf

    Code:
    [mysqld]
    innodb_file_per_table=1
    open_files_limit=3376
    
    max_connections = 60
    query_cache_size=24M
    query_cache_limit=8M
    join_buffer_size=2M
    tmp_table_size=16M
    max_heap_table_size=16M
    thread_cache_size=4
    
    table_open_cache=1024
    table_definition_cache=1024
    
    log_slow_queries=/var/log/mysql/mysql-slow.log
    long_query_time=4
    log-queries-not-using-indexes=1
    
    table_cache=20000
    
    key_buffer_size=64M

    Can any help?!

    Thank you!
     
  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, you should always optimize for
    "RAM guaranteed: 512MB"

    Guaranteed RAM, becouse, when you exceed it, and dedicated server where VPS is located will be out of RAM, they will kill the processes on the VPSes which exceed RAM (depending on virtualization technology)

    I suggest like this:

    my.cnf:
    [mysqld]
    #skip-innodb, if you are not using innodb, uncomment it
    open_files_limit=3376

    max_connections = 60
    query_cache_size=15M
    query_cache_limit=1M
    join_buffer_size=512K
    tmp_table_size=16M
    max_heap_table_size=16M
    thread_cache_size=4

    table_open_cache=512
    table_definition_cache=512

    log_slow_queries=/var/log/mysql/mysql-slow.log
    long_query_time=0.2
    log-queries-not-using-indexes=1

    key_buffer_size=64M

    # innodb
    innodb_file_per_table=1
    innodb_buffer_pool_size = 20M


    Just with disabling innodb (of course when you not use it in your scripts) will give you around 100mb savings, or if you use it, decrease innodb_buffer_pool_size buffer as I wrote
     
    #2 thinkbot, May 23, 2013
    Last edited: May 23, 2013
  3. dos_santos_rj

    dos_santos_rj Member

    Joined:
    Apr 12, 2012
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I did the changes..

    After the changes:


    Code:
     >>  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
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.30-cll
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 338M (Tables: 480)
    [--] Data in InnoDB tables: 1008K (Tables: 54)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 5
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 9h 12m 58s (923K q [7.726 qps], 25K conn, TX: 7B, RX: 120M)
    [--] Reads / Writes: 81% / 19%
    [--] Total buffers: 131.0M global + 3.1M per thread (60 max threads)
    [OK] Maximum possible memory usage: 314.8M (30% of installed RAM)
    [OK] Slow queries: 0% (3K/923K)
    [OK] Highest usage of available connections: 15% (9/60)
    [OK] Key buffer size / total MyISAM indexes: 64.0M/49.0M
    [!!] Key buffer hit rate: 94.8% (2M cached / 138K reads)
    [OK] Query cache efficiency: 85.2% (662K cached / 778K selects)
    [!!] Query cache prunes per day: 18503
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 25K sorts)
    [!!] Joins performed without indexes: 810
    [OK] Temporary tables created on disk: 12% (4K on disk / 37K total)
    [OK] Thread cache hit rate: 99% (67 created / 25K connections)
    [!!] Table cache hit rate: 1% (512 open / 31K opened)
    [OK] Open file limit used: 26% (895/3K)
    [OK] Table locks acquired immediately: 99% (162K immediate / 162K locks)
    [OK] InnoDB data size / buffer pool: 1008.0K/20.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 15M)
        join_buffer_size (> 512.0K, or always use indexes with joins)
        table_cache (> 512)
    

    Code:
    	-- MYSQL PERFORMANCE TUNING PRIMER --
    	     - By: Matthew Montgomery -
    
    MySQL Version 5.5.30-cll i686
    
    Uptime = 1 days 9 hrs 13 min 38 sec
    Avg. qps = 7
    Total Questions = 924332
    Threads Connected = 3
    
    Warning: Server has not been running for at least 48hrs.
    It may not be safe to use these recommendations
    
    To find out more information on how each of these
    runtime variables effects performance visit:
    http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
    Visit http://www.mysql.com/products/enterprise/advisors.html
    for info about MySQL's Enterprise Monitoring and Advisory Service
    
    SLOW QUERIES
    The slow query log is enabled.
    Current long_query_time = 0.200000 sec.
    You have 3844 out of 924353 that take longer than 0.200000 sec. to complete
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
    
    WORKER THREADS
    Current thread_cache_size = 4
    Current threads_cached = 3
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 60
    Current threads_connected = 3
    Historic max_used_connections = 9
    The number of used connections is 15% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    INNODB STATUS
    Current InnoDB index space = 1 M
    Current InnoDB data space = 1008 K
    Current InnoDB buffer pool free = 0 %
    Current innodb_buffer_pool_size = 20 M
    Depending on how much space your innodb indexes take up it may be safe
    to increase this value to up to 2 / 3 of total system memory
    
    MEMORY USAGE
    Max Memory Ever Allocated : 142 M
    Configured Max Per-thread Buffers : 183 M
    Configured Max Global Buffers : 115 M
    Configured Max Memory Limit : 298 M
    Physical Memory : 1.00 G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    Current MyISAM index space = 48 M
    Current key_buffer_size = 64 M
    Key cache miss rate is 1 : 19
    Key buffer free ratio = 86 %
    Your key_buffer_size seems to be fine
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 15 M
    Current query_cache_used = 13 M
    Current query_cache_limit = 1 M
    Current Query cache Memory fill ratio = 90.48 %
    Current query_cache_min_res_unit = 4 K
    However, 25609 queries have been removed from the query cache due to lack of memory
    Perhaps you should raise query_cache_size
    MySQL won't cache query results that are larger than query_cache_limit in size
    
    SORT OPERATIONS
    Current sort_buffer_size = 2 M
    Current read_rnd_buffer_size = 256 K
    Sort buffer seems to be fine
    
    JOINS
    Current join_buffer_size = 516.00 K
    You have had 811 queries where a join could not use an index properly
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    If you are unable to optimize your queries you may want to increase your
    join_buffer_size to accommodate larger joins in one pass.
    
    Note! This script will still suggest raising the join_buffer_size when
    ANY joins not using indexes are found.
    
    OPEN FILES LIMIT
    Current open_files_limit = 3376 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    
    TABLE CACHE
    Current table_open_cache = 512 tables
    Current table_definition_cache = 512 tables
    You have a total of 575 tables
    You have 512 open tables.
    Current table_cache hit rate is 1%
    , while 100% of your table cache is in use
    You should probably increase your table_cache
    You should probably increase your table_definition_cache value.
    
    TEMP TABLES
    Current max_heap_table_size = 16 M
    Current tmp_table_size = 16 M
    Of 32989 temp tables, 12% were created on disk
    Created disk tmp tables ratio seems fine
    
    TABLE SCANS
    Current read_buffer_size = 128 K
    Current table scan ratio = 20 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 162530
    Your table locking seems to be fine
    
     
  4. dos_santos_rj

    dos_santos_rj Member

    Joined:
    Apr 12, 2012
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Need to do make changes at query_cache_size ? join_buffer_size ? table_cache?

    Code:
     
    	-- MYSQL PERFORMANCE TUNING PRIMER --
    	     - By: Matthew Montgomery -
    
    MySQL Version 5.5.30-cll i686
    
    Uptime = 2 days 2 hrs 42 min 0 sec
    Avg. qps = 6
    Total Questions = 1275696
    Threads Connected = 2
    
    Server has been running for over 48hrs.
    It should be safe to follow these recommendations
    
    To find out more information on how each of these
    runtime variables effects performance visit:
    http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
    Visit http://www.mysql.com/products/enterprise/advisors.html
    for info about MySQL's Enterprise Monitoring and Advisory Service
    
    SLOW QUERIES
    The slow query log is enabled.
    Current long_query_time = 0.200000 sec.
    You have 6324 out of 1275717 that take longer than 0.200000 sec. to complete
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
    
    WORKER THREADS
    Current thread_cache_size = 4
    Current threads_cached = 3
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 60
    Current threads_connected = 2
    Historic max_used_connections = 9
    The number of used connections is 15% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    INNODB STATUS
    Current InnoDB index space = 1 M
    Current InnoDB data space = 1008 K
    Current InnoDB buffer pool free = 0 %
    Current innodb_buffer_pool_size = 20 M
    Depending on how much space your innodb indexes take up it may be safe
    to increase this value to up to 2 / 3 of total system memory
    
    MEMORY USAGE
    Max Memory Ever Allocated : 142 M
    Configured Max Per-thread Buffers : 183 M
    Configured Max Global Buffers : 115 M
    Configured Max Memory Limit : 298 M
    Physical Memory : 1.00 G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    Current MyISAM index space = 48 M
    Current key_buffer_size = 64 M
    Key cache miss rate is 1 : 19
    Key buffer free ratio = 88 %
    Your key_buffer_size seems to be fine
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 15 M
    Current query_cache_used = 1 M
    Current query_cache_limit = 1 M
    Current Query cache Memory fill ratio = 11.29 %
    Current query_cache_min_res_unit = 4 K
    Your query_cache_size seems to be too high.
    Perhaps you can use these resources elsewhere
    MySQL won't cache query results that are larger than query_cache_limit in size
    
    SORT OPERATIONS
    Current sort_buffer_size = 2 M
    Current read_rnd_buffer_size = 256 K
    Sort buffer seems to be fine
    
    JOINS
    Current join_buffer_size = 516.00 K
    You have had 1315 queries where a join could not use an index properly
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    If you are unable to optimize your queries you may want to increase your
    join_buffer_size to accommodate larger joins in one pass.
    
    Note! This script will still suggest raising the join_buffer_size when
    ANY joins not using indexes are found.
    
    OPEN FILES LIMIT
    Current open_files_limit = 3376 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    
    TABLE CACHE
    Current table_open_cache = 512 tables
    Current table_definition_cache = 512 tables
    You have a total of 575 tables
    You have 512 open tables.
    Current table_cache hit rate is 0%
    , while 100% of your table cache is in use
    You should probably increase your table_cache
    You should probably increase your table_definition_cache value.
    
    TEMP TABLES
    Current max_heap_table_size = 16 M
    Current tmp_table_size = 16 M
    Of 45525 temp tables, 13% were created on disk
    Created disk tmp tables ratio seems fine
    
    TABLE SCANS
    Current read_buffer_size = 128 K
    Current table scan ratio = 23 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 61547
    Your table locking seems to be fine
    
     
    #4 dos_santos_rj, May 26, 2013
    Last edited: May 26, 2013
  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    I would suggest lowering sort_buffer_size from 2M to 256K
    And query_cache_size to 10M

    The rest looks very good, you can review slow queries now:

    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 you can see slow.txt for results

    You got some inefficient queries creating temporary tables, anyway, besides that, all is very good
    and as I wrote before, if you don't use InnoDB engine for your scripts, you can disable it (using skip-innodb), and save even more RAM
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Feel free to update the thread after a few days and let us know how MySQL is running overall on your server. We appreciate the user-based advice and feedback.

    Thank you.
     
  7. dos_santos_rj

    dos_santos_rj Member

    Joined:
    Apr 12, 2012
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi.

    I did the changes...

    so, my.cnf


    Code:
    [mysqld]
    #skip-innodb, if you are not using innodb, uncomment it
    open_files_limit=3376
    
    max_connections = 60
    query_cache_size=10M
    query_cache_limit=1M
    join_buffer_size=512K
    tmp_table_size=16M
    max_heap_table_size=16M
    thread_cache_size=4
    
    table_open_cache=512
    table_definition_cache=512
    
    log_slow_queries=/var/log/mysql/mysql-slow.log
    long_query_time=0.3
    log-queries-not-using-indexes=1
    
    key_buffer_size=64M
    
    # innodb
    innodb_file_per_table=1
    innodb_buffer_pool_size = 20M
    
    sort_buffer_size=256K
    

    Code:
     >>  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
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.30-cll
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 348M (Tables: 480)
    [--] Data in InnoDB tables: 1008K (Tables: 54)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 11
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 7h 34m 4s (721K q [6.345 qps], 24K conn, TX: 6B, RX: 92M)
    [--] Reads / Writes: 79% / 21%
    [--] Total buffers: 126.0M global + 1.3M per thread (60 max threads)
    [OK] Maximum possible memory usage: 204.8M (19% of installed RAM)
    [OK] Slow queries: 0% (2K/721K)
    [OK] Highest usage of available connections: 20% (12/60)
    [OK] Key buffer size / total MyISAM indexes: 64.0M/51.1M
    [OK] Key buffer hit rate: 99.6% (2M cached / 8K reads)
    [OK] Query cache efficiency: 85.5% (512K cached / 599K selects)
    [!!] Query cache prunes per day: 26333
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 19K sorts)
    [!!] Joins performed without indexes: 760
    [OK] Temporary tables created on disk: 18% (3K on disk / 18K total)
    [OK] Thread cache hit rate: 99% (52 created / 24K connections)
    [!!] Table cache hit rate: 10% (511 open / 5K opened)
    [OK] Open file limit used: 26% (892/3K)
    [OK] Table locks acquired immediately: 99% (128K immediate / 128K locks)
    [OK] InnoDB data size / buffer pool: 1008.0K/20.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 10M)
        join_buffer_size (> 512.0K, or always use indexes with joins)
        table_cache (> 512)

    Code:
    	-- MYSQL PERFORMANCE TUNING PRIMER --
    	     - By: Matthew Montgomery -
    
    MySQL Version 5.5.30-cll i686
    
    Uptime = 1 days 7 hrs 37 min 18 sec
    Avg. qps = 6
    Total Questions = 722603
    Threads Connected = 2
    
    Warning: Server has not been running for at least 48hrs.
    It may not be safe to use these recommendations
    
    To find out more information on how each of these
    runtime variables effects performance visit:
    http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
    Visit http://www.mysql.com/products/enterprise/advisors.html
    for info about MySQL's Enterprise Monitoring and Advisory Service
    
    SLOW QUERIES
    The slow query log is enabled.
    Current long_query_time = 0.300000 sec.
    You have 2891 out of 722624 that take longer than 0.300000 sec. to complete
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
    
    WORKER THREADS
    Current thread_cache_size = 4
    Current threads_cached = 3
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 60
    Current threads_connected = 2
    Historic max_used_connections = 12
    The number of used connections is 20% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    INNODB STATUS
    Current InnoDB index space = 1 M
    Current InnoDB data space = 1008 K
    Current InnoDB buffer pool free = 45 %
    Current innodb_buffer_pool_size = 20 M
    Depending on how much space your innodb indexes take up it may be safe
    to increase this value to up to 2 / 3 of total system memory
    
    MEMORY USAGE
    Max Memory Ever Allocated : 125 M
    Configured Max Per-thread Buffers : 78 M
    Configured Max Global Buffers : 110 M
    Configured Max Memory Limit : 188 M
    Physical Memory : 1.00 G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    Current MyISAM index space = 51 M
    Current key_buffer_size = 64 M
    Key cache miss rate is 1 : 249
    Key buffer free ratio = 85 %
    Your key_buffer_size seems to be fine
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 10 M
    Current query_cache_used = 4 M
    Current query_cache_limit = 1 M
    Current Query cache Memory fill ratio = 48.37 %
    Current query_cache_min_res_unit = 4 K
    Query Cache is 23 % fragmented
    Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
    If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
    MySQL won't cache query results that are larger than query_cache_limit in size
    
    SORT OPERATIONS
    Current sort_buffer_size = 256 K
    Current read_rnd_buffer_size = 256 K
    Sort buffer seems to be fine
    
    JOINS
    Current join_buffer_size = 516.00 K
    You have had 760 queries where a join could not use an index properly
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    If you are unable to optimize your queries you may want to increase your
    join_buffer_size to accommodate larger joins in one pass.
    
    Note! This script will still suggest raising the join_buffer_size when
    ANY joins not using indexes are found.
    
    OPEN FILES LIMIT
    Current open_files_limit = 3376 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    
    TABLE CACHE
    Current table_open_cache = 512 tables
    Current table_definition_cache = 512 tables
    You have a total of 575 tables
    You have 512 open tables.
    Current table_cache hit rate is 5%
    , while 100% of your table cache is in use
    You should probably increase your table_cache
    You should probably increase your table_definition_cache value.
    
    TEMP TABLES
    Current max_heap_table_size = 16 M
    Current tmp_table_size = 16 M
    Of 15231 temp tables, 18% were created on disk
    Created disk tmp tables ratio seems fine
    
    TABLE SCANS
    Current read_buffer_size = 128 K
    Current table scan ratio = 21 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 128759
    Your table locking seems to be fine
    Thanks
     
  8. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    RAM memory usage is good, you will easily fit in 512MB

    Since there are some queries creating temp tables ( 18% on disk )
    you can review slow queries

    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest

    ./pt-query-digest /var/log/mysql/mysql-slow.log > slow.txt

    and you can post here the results of slow.txt
     
  9. dos_santos_rj

    dos_santos_rj Member

    Joined:
    Apr 12, 2012
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    slow.txt

    Code:
    # 6.6s user time, 50ms system time, 22.07M rss, 32.90M vsz
    # Current date: Wed May 29 14:50:04 2013
    # Hostname: vps.##host##.info
    # Files: /var/log/mysql/mysql-slow.log
    # Overall: 29.29k total, 384 unique, 0.04 QPS, 0.00x concurrency _________
    # Time range: 2013-05-21 00:10:26 to 2013-05-29 14:47:07
    # Attribute          total     min     max     avg     95%  stddev  median
    # ============     ======= ======= ======= ======= ======= ======= =======
    # Exec time           232s    34us      7s     8ms     9ms   102ms   626us
    # Lock time             5s       0   248ms   155us    76us     3ms    26us
    # Rows sent          1.04M       0  12.69k   37.10   97.36  208.90    0.99
    # Rows examine      24.62M       0 136.85k  881.45   2.89k   3.35k  258.32
    # Query size         4.13M      11  14.15k  147.83  313.99  198.56  107.34
    
    # Profile
    # Rank Query ID           Response time Calls R/Call V/M   Item
    # ==== ================== ============= ===== ====== ===== ===============
    #    1 0x7194135125B38CDE 46.9281 20.2%    51 0.9202  0.60 SELECT information_schema.tables
    #    2 0xE6F05D29E7F679E6 18.9356  8.2%    53 0.3573  0.68 SELECT information_schema.TABLES
    #    3 0x92F3B1B361FB0E5B 18.0749  7.8%   291 0.0621  1.95 SELECT wp_options
    #    4 0x7AEDF19FDD3A33F1 13.6893  5.9%    15 0.9126  1.08 SELECT wp_options
    #    5 0x3E84BF7C0C2A3005 10.9345  4.7%   118 0.0927  4.54 SELECT wp_postmeta
    #    6 0x521E9CAE913BED7F 10.3705  4.5%  3237 0.0032  0.04 SELECT re?_listings re?_categories re?_agents
    #    7 0xB665AC4E8B7E5B47  8.1297  3.5%     5 1.6259  3.08 SELECT wp_terms wp_term_taxonomy
    #    8 0xCF9A2CB516C43E53  8.0459  3.5%     7 1.1494  1.69 INSERT sends
    #    9 0xBA3E4AF68659BF0E  6.6571  2.9%    14 0.4755  0.21 SHOW TABLES
    #   10 0x19452FECFDC5E794  6.6311  2.9%     9 0.7368  0.17 INSERT failures
    #   11 0xE3DC3FDDBCD0CC98  4.9828  2.1%    10 0.4983  0.04 DROP TABLE Bairros
    #   12 0xC7B0D57BA54014E4  4.9752  2.1%  4138 0.0012  0.04 SELECT wp_postmeta
    #   13 0xD65D7926B1674FD0  4.1864  1.8%    53 0.0790  0.12 SELECT information_schema.TABLES
    #   14 0xB0F9022862C6AE4A  4.1844  1.8%   429 0.0098  0.00 SELECT re?_listings re?_agents
    #   15 0x790F5D77800CA0CD  3.1447  1.4%     4 0.7862  0.35 SELECT wp_term_relationships wp_term_taxonomy
    #   16 0x5CBA2034458B5BC9  3.0662  1.3%     2 1.5331  1.39 SHOW DATABASES
    #   17 0x94CD2A193BB22E1F  3.0417  1.3%   265 0.0115  0.94 SELECT re?_listings re?_agents re?_priority re?_categories
    #   18 0xA766EE8F7AB39063  2.6257  1.1%    99 0.0265  0.67 SELECT wp_terms wp_term_taxonomy wp_term_relationships
    #   19 0xED38D14682C3BE22  2.3604  1.0%     3 0.7868  0.22 CREATE TABLE wp_option_tree IF
    #   20 0x5CBC6BF26D494691  2.2752  1.0%    53 0.0429  0.02 SELECT information_schema.TABLES
    # MISC 0xMISC             48.6171 21.0% 20432 0.0024   0.0 <364 ITEMS>
    
    # Query 1: 0.00 QPS, 0.00x concurrency, ID 0x7194135125B38CDE at byte 4960596
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.60
    # Time range: 2013-05-21 00:30:01 to 2013-05-29 12:30:03
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0      51
    # Exec time     20     47s    13ms      3s   920ms      2s   744ms   609ms
    # Lock time      0     4ms    62us    86us    70us    80us     7us    63us
    # Rows sent      0   1.59k      32      32      32      32       0      32
    # Rows examine   0  34.32k     688     691  689.12  685.39       0  685.39
    # Query size     0   6.28k     126     126     126     126       0     126
    # String:
    # Databases    mysql
    # Hosts        localhost
    # Users        root
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ################
    # 100ms  ########################################################
    #    1s  ################################################################
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `information_schema` LIKE 'tables'\G
    #    SHOW CREATE TABLE `information_schema`.`tables`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA\G
    
    # Query 2: 0.00 QPS, 0.00x concurrency, ID 0xE6F05D29E7F679E6 at byte 880323
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.68
    # Time range: 2013-05-21 00:17:24 to 2013-05-29 00:29:02
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0      53
    # Exec time      8     19s    11ms      2s   357ms      1s   492ms    56ms
    # Lock time      0     3ms    56us    90us    62us    76us     7us    57us
    # Rows sent      0     159       3       3       3       3       0       3
    # Rows examine   0  29.11k     561     564  562.42  563.87    1.50  537.02
    # Query size     0   9.89k     191     191     191     191       0     191
    # String:
    # Databases    rrrrr_wp... (17/32%), rrrrr_fo... (15/28%)... 6 more
    # Hosts        localhost
    # Users        root
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ################################################################
    # 100ms  ##########################
    #    1s  ##################
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `information_schema` LIKE 'TABLES'\G
    #    SHOW CREATE TABLE `information_schema`.`TABLES`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC\G
    
    # Query 3: 0.00 QPS, 0.00x concurrency, ID 0x92F3B1B361FB0E5B at byte 3018920
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 1.95
    # Time range: 2013-05-21 00:10:26 to 2013-05-29 14:43:26
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0     291
    # Exec time      7     18s   423us      5s    62ms   293ms   348ms   515us
    # Lock time      3   161ms       0    60ms   552us    66us     4ms    44us
    # Rows sent      7  80.80k       0     621  284.33  284.79   99.11  284.79
    # Rows examine   0  93.79k       0     731  330.03  329.68  118.82  329.68
    # Query size     0  20.18k      71      71      71      71       0      71
    # String:
    # Databases    souza_wp41... (258/88%), rrrrr_wp... (33/11%)
    # Hosts        localhost
    # Users        souza_wp41... (258/88%), rrrrr_u1... (33/11%)
    # Query_time distribution
    #   1us
    #  10us
    # 100us  ################################################################
    #   1ms  #######
    #  10ms  ########
    # 100ms  ######
    #    1s  #
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `rrrrr_wp657` LIKE 'wp_options'\G
    #    SHOW CREATE TABLE `rrrrr_wp657`.`wp_options`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'\G
    
    # Query 4: 0.00 QPS, 0.00x concurrency, ID 0x7AEDF19FDD3A33F1 at byte 2185831
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 1.08
    # Time range: 2013-05-22 13:20:45 to 2013-05-29 09:58:41
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0      15
    # Exec time      5     14s   216ms      4s   913ms      2s   993ms   539ms
    # Lock time      0       0       0       0       0       0       0       0
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine   0       0       0       0       0       0       0       0
    # Query size     0   1.22k      83      87   83.27   80.10    1.00   80.10
    # String:
    # Databases    rrrrr_wp657
    # Hosts        localhost
    # Users        rrrrr_u1wp657
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms  ################################################################
    #    1s  ################
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `rrrrr_wp657` LIKE 'wp_options'\G
    #    SHOW CREATE TABLE `rrrrr_wp657`.`wp_options`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT option_value FROM wp_options WHERE option_name = 'uninstall_plugins' LIMIT 1\G
    
    # Query 5: 0.00 QPS, 0.00x concurrency, ID 0x3E84BF7C0C2A3005 at byte 1368238
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 4.54
    # Time range: 2013-05-21 03:45:44 to 2013-05-29 12:03:34
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0     118
    # Exec time      4     11s    97us      7s    93ms   839us   648ms   119us
    # Lock time      0     3ms       0    53us    25us    40us     7us    23us
    # Rows sent      0   2.32k       0      27   20.14   26.08    6.45   17.65
    # Rows examine   0   7.46k       0      67   64.73   65.89   11.92   65.89
    # Query size     0  11.43k      77     142   99.16  102.22    8.55   92.72
    # String:
    # Databases    souza_wp41... (114/96%), rrrrr_wp... (4/3%)
    # Hosts        localhost
    # Users        souza_wp41... (114/96%), rrrrr_u1... (4/3%)
    # Query_time distribution
    #   1us
    #  10us  #
    # 100us  ################################################################
    #   1ms  #
    #  10ms
    # 100ms  #
    #    1s  #
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `rrrrr_wp657` LIKE 'wp_postmeta'\G
    #    SHOW CREATE TABLE `rrrrr_wp657`.`wp_postmeta`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (982,1165,1170,1192,1213,1218,1241,1243,1245,1599)\G
    
    # Query 6: 0.00 QPS, 0.00x concurrency, ID 0x521E9CAE913BED7F at byte 2570321
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.04
    # Time range: 2013-05-21 00:21:48 to 2013-05-29 14:47:07
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         11    3237
    # Exec time      4     10s     1ms   523ms     3ms    10ms    11ms     2ms
    # Lock time     23      1s    37us    77ms   330us    93us     4ms    73us
    # Rows sent      0   3.16k       1       1       1       1       0       1
    # Rows examine   4   1.22M     371   1.11k  395.27  363.48  131.54  363.48
    # Query size    23 1008.40k     319     319     319     319       0     319
    # String:
    # Databases    mongamar_monga
    # Hosts        localhost
    # Users        mongamar_monga
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms  ################################################################
    #  10ms  ###
    # 100ms  #
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `mongamar_monga` LIKE 're2_listings'\G
    #    SHOW CREATE TABLE `mongamar_monga`.`re2_listings`\G
    #    SHOW TABLE STATUS FROM `mongamar_monga` LIKE 're2_categories'\G
    #    SHOW CREATE TABLE `mongamar_monga`.`re2_categories`\G
    #    SHOW TABLE STATUS FROM `mongamar_monga` LIKE 're2_agents'\G
    #    SHOW CREATE TABLE `mongamar_monga`.`re2_agents`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT * FROM re2_listings, re2_categories, re2_agents WHERE re2_listings.CategoryID = re2_categories.CategoryID AND re2_listings.AnunAtivo = 'y' AND re2_listings.semanal = 'y' AND re2_listings.AgentID = re2_agents.AgentID AND re2_listings.numimages > 0 AND re2_agents.AccountStatus = 'active' ORDER BY rand() limit 0,1\G
    
    # Query 7: 0.00 QPS, 0.00x concurrency, ID 0xB665AC4E8B7E5B47 at byte 1161040
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 3.08
    # Time range: 2013-05-21 16:26:49 to 2013-05-29 00:25:10
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0       5
    # Exec time      3      8s   381ms      6s      2s      6s      2s   477ms
    # Lock time      0       0       0       0       0       0       0       0
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine   0       0       0       0       0       0       0       0
    # Query size     0     890     178     178     178     178       0     178
    # String:
    # Databases    rrrrr_wp657
    # Hosts        localhost
    # Users        rrrrr_u1wp657
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms  ################################################################
    #    1s  ################
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `rrrrr_wp657` LIKE 'wp_terms'\G
    #    SHOW CREATE TABLE `rrrrr_wp657`.`wp_terms`\G
    #    SHOW TABLE STATUS FROM `rrrrr_wp657` LIKE 'wp_term_taxonomy'\G
    #    SHOW CREATE TABLE `rrrrr_wp657`.`wp_term_taxonomy`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('post_tag') AND tt.count > 0 ORDER BY tt.count DESC LIMIT 45\G
    
    # Query 8: 0.00 QPS, 0.00x concurrency, ID 0xCF9A2CB516C43E53 at byte 2238626
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 1.69
    # Time range: 2013-05-22 15:10:09 to 2013-05-29 09:25:35
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0       7
    # Exec time      3      8s   382ms      5s      1s      5s      1s   477ms
    # Lock time      0       0       0       0       0       0       0       0
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine   0       0       0       0       0       0       0       0
    # Query size     0   1.89k     257     289  276.14  284.79   12.84  271.23
    # 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 'sends'\G
    #    SHOW CREATE TABLE `eximstats`.`sends`\G
    INSERT DELAYED INTO sends (mailtime,msgid,processed,domain,email,user,size,host,ip,auth,localsender) VALUES(FROM_UNIXTIME('1369246203'),'1UfDUB-00097r-ei','0','','www-data@mail.servidor.dedicado.nom.br','-remote-','0','server04.servidor.dedicado.nom.br','189.##.##.###','unauthorized','0')\G
    
    # Query 9: 0.00 QPS, 0.00x concurrency, ID 0xBA3E4AF68659BF0E at byte 4624502
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.21
    # Time range: 2013-05-24 01:00:01 to 2013-05-26 08:00:02
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0      14
    # Exec time      2      7s   205ms      1s   476ms   857ms   312ms   356ms
    # Lock time      0   622us    22us    71us    44us    66us    13us    44us
    # Rows sent      0     340       7     126   24.29   34.95   28.70   12.54
    # Rows examine   0     340       7     126   24.29   34.95   28.70   12.54
    # Query size     0     378      27      27      27      27       0      27
    # String:
    # Databases    boleto_bol... (6/42%), cphulkd (1/7%)... 7 more
    # Hosts        localhost
    # Users        root
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms  ################################################################
    #    1s  ####
    #  10s+
    SHOW /*!50002 FULL*/ TABLES\G
    
    # Query 10: 0.00 QPS, 0.00x concurrency, ID 0x19452FECFDC5E794 at byte 6618015
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.17
    # Time range: 2013-05-24 21:10:14 to 2013-05-29 09:55:30
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0       9
    # Exec time      2      7s   342ms      1s   737ms      1s   358ms   705ms
    # Lock time      0       0       0       0       0       0       0       0
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine   0       0       0       0       0       0       0       0
    # Query size     0   5.22k     337     776  593.44  755.64  145.26  652.75
    # 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 'failures'\G
    #    SHOW CREATE TABLE `eximstats`.`failures`\G
    INSERT DELAYED INTO failures (mailtime,msgid,email,transport_method,message,host,ip,router,deliveryuser,deliverydomain) VALUES(FROM_UNIXTIME('1369613487'),'1Ugl24-0001LS-L1','renault19brasil@hotmail.com','remote_smtp','SMTP error from remote mail server after MAIL FROM:<@vps.##host##.info> SIZE=2264: host mx2.hotmail.com [65.55.92.136]: 550 OU-001 (SNT0-MC1-F7) Unfortunately, messages from 173.237.###.### weren\'t sent. Please contact your Internet service provider since part of their network is on our block list. You can also refer your provider to http://mail.live.com/mail/troubleshooting.aspx#errors.','mx2.hotmail.com','65.55.92.136','lookuphost','','')\G
    
    # Query 11: 0.00 QPS, 0.00x concurrency, ID 0xE3DC3FDDBCD0CC98 at byte 5110667
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.04
    # Time range: 2013-05-24 09:00:04 to 2013-05-26 18:00:02
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0      10
    # Exec time      2      5s   201ms   723ms   498ms   672ms   145ms   501ms
    # Lock time      0       0       0       0       0       0       0       0
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine   0       0       0       0       0       0       0       0
    # Query size     0   1.56k     160     160     160     160       0     160
    # String:
    # Databases    dddddi_demo
    # Hosts        localhost
    # Users        dddddi_demo
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms  ################################################################
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `dddddi_demo` LIKE 'Bairros'\G
    #    SHOW CREATE TABLE `dddddi_demo`.`Bairros`\G
    DROP TABLE Bairros,Caracteristicas,Cidades,Configuracoes,Corretores,Fotos,Imoveis,ImovelCaracteristica,Proprietarios,TiposImoveis,banners,re2_contador,re2_stats\G
    
    # Query 12: 0.01 QPS, 0.00x concurrency, ID 0xC7B0D57BA54014E4 at byte 4453221
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.04
    # Time range: 2013-05-21 00:10:27 to 2013-05-27 21:04:25
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         14    4138
    # Exec time      2      5s   424us   454ms     1ms     2ms     7ms   972us
    # Lock time      2   107ms       0   134us    25us    35us     7us    22us
    # Rows sent      0     591       0       1    0.14    0.99    0.35       0
    # Rows examine  43  10.69M       0   2.97k   2.65k   2.89k  595.11   2.76k
    # Query size    10 439.70k     108     109  108.81  107.34       0  107.34
    # String:
    # Databases    rrrrr_wp657
    # Hosts        localhost
    # Users        rrrrr_u1wp657
    # Query_time distribution
    #   1us
    #  10us
    # 100us  #########################
    #   1ms  ################################################################
    #  10ms  #
    # 100ms  #
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `rrrrr_wp657` LIKE 'wp_postmeta'\G
    #    SHOW CREATE TABLE `rrrrr_wp657`.`wp_postmeta`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT post_id FROM wp_postmeta WHERE meta_key like "_menu_item_menu_item_parent" AND meta_value=1319 LIMIT 1\G
    
    # Query 13: 0.00 QPS, 0.00x concurrency, ID 0xD65D7926B1674FD0 at byte 4222528
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.12
    # Time range: 2013-05-21 00:17:24 to 2013-05-29 00:29:02
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0      53
    # Exec time      1      4s    10ms   358ms    79ms   279ms    97ms    44ms
    # Lock time      0     3ms    53us    87us    64us    80us     6us    60us
    # Rows sent      0      53       1       1       1       1       0       1
    # Rows examine   0  28.80k     555     558  556.42  537.02       0  537.02
    # Query size     0   7.97k     154     154     154     154       0     154
    # String:
    # Databases    rrrrr_wp... (17/32%), rrrrr_fo... (15/28%)... 6 more
    # Hosts        localhost
    # Users        root
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ################################################################
    # 100ms  ####################
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `information_schema` LIKE 'TABLES'\G
    #    SHOW CREATE TABLE `information_schema`.`TABLES`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT COUNT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0 AND NOT ENGINE='MEMORY'\G
    
    # Query 14: 0.00 QPS, 0.00x concurrency, ID 0xB0F9022862C6AE4A at byte 9110198
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-05-21 01:14:52 to 2013-05-29 14:03:16
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          1     429
    # Exec time      1      4s     1ms   101ms    10ms    16ms     7ms    10ms
    # Lock time      0    15ms    20us     4ms    35us    33us   211us    22us
    # Rows sent     37 396.02k     169   1.11k  945.27   1.09k  376.94   1.09k
    # Rows examine   1 384.08k       3   1.11k  916.79   1.09k  441.87   1.09k
    # Query size     0  36.03k      86      86      86      86       0      86
    # String:
    # Databases    mongamar_m... (345/80%), styloimo_i... (84/19%)
    # Hosts        localhost
    # Users        mongamar_m... (345/80%), styloimo_u... (84/19%)
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms  ###############
    #  10ms  ################################################################
    # 100ms  #
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `mongamar_monga` LIKE 're2_listings'\G
    #    SHOW CREATE TABLE `mongamar_monga`.`re2_listings`\G
    #    SHOW TABLE STATUS FROM `mongamar_monga` LIKE 're2_agents'\G
    #    SHOW CREATE TABLE `mongamar_monga`.`re2_agents`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    select * from re2_listings, re2_agents where re2_listings.AgentID = re2_agents.AgentID\G
    
    # Query 15: 0.00 QPS, 0.00x concurrency, ID 0x790F5D77800CA0CD at byte 9366228
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.35
    # Time range: 2013-05-25 18:16:08 to 2013-05-29 09:25:45
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0       4
    # Exec time      1      3s   249ms      2s   786ms      2s   522ms      1s
    # Lock time      0       0       0       0       0       0       0       0
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine   0       0       0       0       0       0       0       0
    # Query size     0     926     231     232  231.50  223.14       0  223.14
    # String:
    # Databases    rrrrr_wp657
    # Hosts        localhost
    # Users        rrrrr_u1wp657
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms  ################################################################
    #    1s  ################################################################
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `rrrrr_wp657` LIKE 'wp_term_relationships'\G
    #    SHOW CREATE TABLE `rrrrr_wp657`.`wp_term_relationships`\G
    #    SHOW TABLE STATUS FROM `rrrrr_wp657` LIKE 'wp_term_taxonomy'\G
    #    SHOW CREATE TABLE `rrrrr_wp657`.`wp_term_taxonomy`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT tr.term_taxonomy_id FROM wp_term_relationships AS tr INNER JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tr.object_id IN (748) AND tt.taxonomy IN ('category') ORDER BY tr.term_taxonomy_id ASC\G
    
    # Query 16: 0.00 QPS, 0.00x concurrency, ID 0x5CBA2034458B5BC9 at byte 6176530
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 1.39
    # Time range: 2013-05-26 00:00:02 to 12:00:05
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0       2
    # Exec time      1      3s   500ms      3s      2s      3s      1s      2s
    # Lock time      0   109us    52us    57us    54us    57us     3us    54us
    # Rows sent      0      64      32      32      32      32       0      32
    # Rows examine   0      64      32      32      32      32       0      32
    # Query size     0      28      14      14      14      14       0      14
    # String:
    # Databases    dddddi_demo
    # Hosts        localhost
    # Users        root
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms  ################################################################
    #    1s  ################################################################
    #  10s+
    SHOW DATABASES\G
    
    # Query 17: 0.00 QPS, 0.00x concurrency, ID 0x94CD2A193BB22E1F at byte 4597092
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.94
    # Time range: 2013-05-21 05:24:11 to 2013-05-29 14:15:23
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0     265
    # Exec time      1      3s   985us      2s    11ms    18ms   104ms     4ms
    # Lock time      2    94ms    42us    64ms   354us    93us     4ms    49us
    # Rows sent      0   2.40k       0      30    9.29   28.75    9.09    9.83
    # Rows examine   0 113.60k      40   1.08k  438.97  755.64  304.43  563.87
    # Query size     2 119.05k     459     461  460.01  441.81       0  441.81
    # String:
    # Databases    mongamar_m... (215/81%), styloimo_i... (50/18%)
    # Hosts        localhost
    # Users        mongamar_m... (215/81%), styloimo_u... (50/18%)
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms  ################################################################
    #  10ms  ####
    # 100ms
    #    1s  #
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `mongamar_monga` LIKE 're2_listings'\G
    #    SHOW CREATE TABLE `mongamar_monga`.`re2_listings`\G
    #    SHOW TABLE STATUS FROM `mongamar_monga` LIKE 're2_agents'\G
    #    SHOW CREATE TABLE `mongamar_monga`.`re2_agents`\G
    #    SHOW TABLE STATUS FROM `mongamar_monga` LIKE 're2_priority'\G
    #    SHOW CREATE TABLE `mongamar_monga`.`re2_priority`\G
    #    SHOW TABLE STATUS FROM `mongamar_monga` LIKE 're2_categories'\G
    #    SHOW CREATE TABLE `mongamar_monga`.`re2_categories`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    select * from re2_listings, re2_agents, re2_priority, re2_categories where re2_listings.CategoryID = re2_categories.CategoryID and re2_listings.AgentID = re2_agents.AgentID and re2_agents.PriorityLevel = re2_priority.PriorityLevel and re2_listings.AnunAtivo = 'y' and re2_agents.AccountStatus = 'active' and re2_listings.CategoryID = '28'  and re2_listings.SubcategoryID = '49'   order by re2_agents.PriorityLevel desc, re2_listings.DateAdded desc limit 80, 10\G
    
    # Query 18: 0.00 QPS, 0.00x concurrency, ID 0xA766EE8F7AB39063 at byte 8431169
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.67
    # Time range: 2013-05-21 03:45:44 to 2013-05-29 12:03:34
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0      99
    # Exec time      1      3s   405us   890ms    27ms     2ms   133ms   490us
    # Lock time      0     5ms    45us   113us    53us    76us    10us    47us
    # Rows sent      0     921       6      75    9.30    8.91    6.99    7.70
    # Rows examine   0   3.72k      30     375   38.46   34.95   35.87   31.70
    # Query size     0  32.29k     300     338     334  329.68    5.46  329.68
    # String:
    # Databases    souza_wp41... (95/95%), rrrrr_wp... (4/4%)
    # Hosts        localhost
    # Users        souza_wp41... (95/95%), rrrrr_u1... (4/4%)
    # Query_time distribution
    #   1us
    #  10us
    # 100us  ################################################################
    #   1ms  #
    #  10ms
    # 100ms  ##
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `rrrrr_wp657` LIKE 'wp_terms'\G
    #    SHOW CREATE TABLE `rrrrr_wp657`.`wp_terms`\G
    #    SHOW TABLE STATUS FROM `rrrrr_wp657` LIKE 'wp_term_taxonomy'\G
    #    SHOW CREATE TABLE `rrrrr_wp657`.`wp_term_taxonomy`\G
    #    SHOW TABLE STATUS FROM `rrrrr_wp657` LIKE 'wp_term_relationships'\G
    #    SHOW CREATE TABLE `rrrrr_wp657`.`wp_term_relationships`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (472) ORDER BY t.name ASC\G
    
    # Query 19: 0.00 QPS, 0.00x concurrency, ID 0xED38D14682C3BE22 at byte 4123645
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.22
    # Time range: 2013-05-24 08:52:51 to 2013-05-25 14:22:20
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0       3
    # Exec time      1      2s   330ms      1s   787ms      1s   420ms   640ms
    # Lock time      0   276us    78us   115us    92us   113us    16us    80us
    # Rows sent      0       0       0       0       0       0       0       0
    # Rows examine   0       0       0       0       0       0       0       0
    # Query size     0   1.27k     432     432     432     432       0     432
    # String:
    # Databases    souza_wp410
    # Hosts        localhost
    # Users        souza_wp410
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms  ################################################################
    #    1s  ################################
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `souza_wp410` LIKE 'wp_option_tree'\G
    #    SHOW CREATE TABLE `souza_wp410`.`wp_option_tree`\G
    CREATE TABLE IF NOT EXISTS wp_option_tree (
    			  id mediumint(9) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    			  item_id VARCHAR(50) NOT NULL,
    			  item_title VARCHAR(100) NOT NULL,
    			  item_desc LONGTEXT,
    			  item_type VARCHAR(30) NOT NULL,
    			  item_options VARCHAR(250) DEFAULT NULL,
    			  item_sort mediumint(9) DEFAULT '0' NOT NULL,
    			  UNIQUE KEY (item_id)
    		  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci\G
    
    # Query 20: 0.00 QPS, 0.00x concurrency, ID 0x5CBC6BF26D494691 at byte 8269894
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.02
    # Time range: 2013-05-21 00:17:24 to 2013-05-29 00:29:02
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count          0      53
    # Exec time      0      2s    10ms   124ms    43ms    95ms    31ms    46ms
    # Lock time      0     3ms    47us    84us    56us    69us     8us    52us
    # Rows sent      0      53       1       1       1       1       0       1
    # Rows examine   0  30.04k     579     582  580.42  563.87    0.00  563.87
    # Query size     0   7.04k     136     136     136     136       0     136
    # String:
    # Databases    rrrrr_wp... (17/32%), rrrrr_fo... (15/28%)... 6 more
    # Hosts        localhost
    # Users        root
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms  ################################################################
    # 100ms  #####
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `information_schema` LIKE 'TABLES'\G
    #    SHOW CREATE TABLE `information_schema`.`TABLES`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT IFNULL(SUM(INDEX_LENGTH),0) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema') AND ENGINE = 'MyISAM'\G
     
  10. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    It's good, no very slow queries

    Well, you don't have much data in database, and not much traffic :)


    Since your current mysql setup will use max 205 MB according to mysqltuner, and yu got 512MB guaranteed
    you can increase key_buffer_size to 150M
    so in the future, when myisam index grows, you wouldn't have to increase this value
     
  11. dos_santos_rj

    dos_santos_rj Member

    Joined:
    Apr 12, 2012
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I did the change!!

    thank you!
     
Loading...

Share This Page