Advertisement: Linux VPS for $4/month - based on OpenVZ, credit card accepted, automatic setup.
+ Post New Thread
Results 1 to 2 of 2

Thread: Tuning primer shell script results - ICPH Webserver

  1. #1
    Administrator Fli's Avatar
    Join Date
    Mar 2013
    Posts
    2,421
    Post Thanks / Like
    Blog Entries
    1

    Tuning primer shell script results - ICPH Webserver



    March 2nd result of mysql tuning primer Linux shell script on ICPH shared hosting server

    KEY BUFFER
    Current MyISAM index space = 1.17 G
    Current key_buffer_size = 8 M
    Key cache miss rate is 1 : 39
    Key buffer free ratio = 8 %
    You could increase key_buffer_size
    It is safe to raise this up to 1/4 of total system memory;
    assuming this is a dedicated database server.


    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 512 M
    Current query_cache_used = 422 M
    Current query_cache_limit = 512 M
    Current Query cache Memory fill ratio = 82.59 %
    Current query_cache_min_res_unit = 4 K
    However, 5451274 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 = 16.00 M
    You have had 106037 queries where a join could not use an index properly
    You have had 309619 joins without keys that check for key usage after each row
    join_buffer_size >= 4 M
    This is not advised
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.


    OPEN FILES LIMIT
    Current open_files_limit = 43762 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 = 400 tables
    You have a total of 9452 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 = 1.00 G
    Current tmp_table_size = 1.00 G
    Of 3694414 temp tables, 35% were created on disk
    Perhaps you should increase your tmp_table_size and/or max_heap_table_size
    to reduce the number of disk-based temporary tables
    Note! BLOB and TEXT columns are not allow in memory tables.
    If you are using these columns raising these values might not impact your
    ratio of on disk temp tables.


    TABLE SCANS
    Current read_buffer_size = 128 K
    Current table scan ratio = 7045 : 1
    You have a high ratio of sequential access requests to SELECTs
    You may benefit from raising read_buffer_size and/or improving your use of indexes.


    TABLE LOCKING
    Current Lock Wait ratio = 1 : 1753
    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'.
    Previous /etc/my.cnf
    [mysqld]

    max_connections=200
    max_user_connections=18

    innodb_file_per_table=1
    local-infile=0
    max_allowed_packet=268435456
    open_files_limit=43762
    ft_min_word_len=3

    thread_cache_size=12
    max_connections=200
    innodb_buffer_pool_size=400M
    join_buffer_size=16M
    query_cache_size=512M
    query_cache_limit=512M
    table_cache=6024
    table_open_cache=512
    max_heap_table_size=1024M

    [myisamchk]
    ft_min_word_len=3
    I modiffied like this:
    [mysqld]

    max_connections=200
    max_user_connections=18

    innodb_file_per_table=1
    local-infile=0
    max_allowed_packet=268435456
    open_files_limit=43762
    ft_min_word_len=3

    # was not there, was default 8M
    key_buffer_size=1024

    thread_cache_size=12
    max_connections=200
    innodb_buffer_pool_size=400M
    join_buffer_size=16M

    #was not there, was default 128k
    read_buffer_size=16M

    # was 512M
    query_cache_size=768M
    query_cache_limit=768M
    table_cache=6024
    table_open_cache=512
    tmp_table_size=1024M
    max_heap_table_size=1024M

    [myisamchk]
    ft_min_word_len=3
    and restarted mysql

    The PHPMyAdmin Server variables:

  2. #2
    Administrator Fli's Avatar
    Join Date
    Mar 2013
    Posts
    2,421
    Post Thanks / Like
    Blog Entries
    1


    Is this useful / helpfull? Yes | No
    September 28, 2015

    # mysql report

    http://pastebin.com/Xj0AZ0bT

    # /root/mysqltunerenhanced

    http://pastebin.com/kWjheA6a

    /etc/my.cnf
    [mysqld]

    #tmpdir=/dev/shm
    tmpdir=/tmp/mysqltmp/

    slow_query_log=1
    slow_query_log_file=/var/log/mysql/slow.log
    long_query_time=1
    #log_queries_not_using_indexes=1

    max_connections=200
    max_user_connections=18

    innodb_file_per_table=1
    local-infile=0
    max_allowed_packet=268435456
    open_files_limit=43762
    ft_min_word_len=3

    # was not there, was default 8M
    key_buffer_size=1400

    thread_cache_size=12
    max_connections=200
    innodb_buffer_pool_size=760M
    join_buffer_size=16M

    #was not there, was default 128k
    read_buffer_size=16M

    # was 512M
    query_cache_size=768M
    query_cache_limit=768M
    table_cache=6024
    table_open_cache=728
    tmp_table_size=1024M
    max_heap_table_size=1024M

    default-storage-engine=MyISAM
    [myisamchk]
    ft_min_word_len=3
    # /root/mysql-tuning-primer.sh

    # ./mysql-tuning-primer.sh

    -- MYSQL PERFORMANCE TUNING PRIMER --
    - By: Matthew Montgomery -

    MySQL Version 5.5.42-cll x86_64

    Uptime = 0 days 21 hrs 26 min 52 sec
    Avg. qps = 80
    Total Questions = 6202385
    Threads Connected = 5

    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 = 1.000000 sec.
    You have 10578 out of 6202406 that take longer than 1.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 = 12
    Current threads_cached = 10
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine

    MAX CONNECTIONS
    Current max_connections = 200
    Current threads_connected = 5
    Historic max_used_connections = 64
    The number of used connections is 32% of the configured maximum.
    Your max_connections variable seems to be fine.

    INNODB STATUS
    Current InnoDB index space = 315 M
    Current InnoDB data space = 675 M
    Current InnoDB buffer pool free = 0 %
    Current innodb_buffer_pool_size = 760 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 : 3.66 G
    Configured Max Per-thread Buffers : 6.73 G
    Configured Max Global Buffers : 1.50 G
    Configured Max Memory Limit : 8.24 G
    Physical Memory : 17.08 G
    Max memory limit seem to be within acceptable norms

    KEY BUFFER
    Runtime error (func=(main), adr=10): Divide by zero
    (standard_in) 1: syntax error
    Current MyISAM index space = 1.22 G
    Current key_buffer_size = 0 bytes
    Key cache miss rate is 1 : 1
    Key buffer free ratio = %
    ./mysql-tuning-primer.sh: line 795: [: -le: unary operator expected
    ./mysql-tuning-primer.sh: line 799: [: -le: unary operator expected
    ./mysql-tuning-primer.sh: line 803: [: -le: unary operator expected
    Your key_buffer_size seems to be fine

    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 768 M
    Current query_cache_used = 442 M
    Current query_cache_limit = 768 M
    Current Query cache Memory fill ratio = 57.67 %
    Current query_cache_min_res_unit = 4 K
    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 = 16.00 M
    You have had 9513 queries where a join could not use an index properly
    You have had 30087 joins without keys that check for key usage after each row
    join_buffer_size >= 4 M
    This is not advised
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.

    OPEN FILES LIMIT
    Current open_files_limit = 43762 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 = 728 tables
    Current table_definition_cache = 400 tables
    You have a total of 13669 tables
    You have 728 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 = 1.00 G
    Current tmp_table_size = 1.00 G
    Of 380288 temp tables, 31% were created on disk
    Perhaps you should increase your tmp_table_size and/or max_heap_table_size
    to reduce the number of disk-based temporary tables
    Note! BLOB and TEXT columns are not allow in memory tables.
    If you are using these columns raising these values might not impact your
    ratio of on disk temp tables.

    TABLE SCANS
    Current read_buffer_size = 16 M
    Current table scan ratio = 12531 : 1
    read_buffer_size is over 8 MB there is probably no need for such a large read_buffer

    TABLE LOCKING
    Current Lock Wait ratio = 1 : 887
    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'.

    ---
    after this, i modified config this way:

    - query_cache_size and query_cache_limit to be: 512M (old 768)

    - table_open_cache to be 5000 (old: 728)

+ Post New Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
 Protected by : ZB BLOCK  &  StopForumSpam