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

Thread: My MySQL server optimization progress, history

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

    My MySQL server optimization progress, history



    As a MySQL newbie i would like to share how my mysql server optimization goes thru the history:

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 23d 2h 29m 34s (193M q [97.175 qps], 9M conn, TX: 8405B, RX: 37B)
    [--] Reads / Writes: 74% / 26%
    [--] Total buffers: 2.3G global + 34.5M per thread (200 max threads)
    [OK] Maximum possible memory usage: 9.0G (52% of installed RAM)
    [OK] Slow queries: 0% (105K/193M)
    [OK] Highest usage of available connections: 19% (39/200)
    [!!] Key buffer size / total MyISAM indexes: 0B/1.1G
    [!!] Key buffer hit rate: 0.0% (2B cached / 2B reads)
    [OK] Query cache efficiency: 70.2% (94M cached / 133M selects)
    [!!] Query cache prunes per day: 558587
    [OK] Sorts requiring temporary tables: 1% (108K temp sorts / 9M sorts)
    [!!] Joins performed without indexes: 1214820
    [!!] Temporary tables created on disk: 32% (4M on disk / 15M total)
    [OK] Thread cache hit rate: 99% (11K created / 9M connections)
    [!!] Table cache hit rate: 0% (1K open / 75M opened)
    [OK] Open file limit used: 3% (1K/43K)
    [OK] Table locks acquired immediately: 99% (73M immediate / 73M locks)
    [OK] InnoDB buffer pool / data size: 760.0M/714.0M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increasing the query_cache size over 128M may reduce performance
    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
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
    key_buffer_size (> 1.1G)
    query_cache_size (> 512M) [see warning above]
    join_buffer_size (> 16.0M, or always use indexes with joins)
    table_cache (> 1024)


    Report Complete:
    Wed Apr 13 09:02:15 UTC 2016
    Adjustments i made after above report:
    table_open_cache=1024 -> 512M (lowered)
    tmp_table_size=1024M -> 512M (lowered, average maximum size of mysql table is 45M)
    join_buffer_size=16M -> 64M (increase)
    read_buffer_size=16M -> 64M (increase)
    sort_buffer_size=64M <- NEW variable

    Decision based on this article
    Lets see next run if tmp_tables % reduce

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


    Is this useful / helpfull? Yes | No
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 2h 3m 34s (8M q [94.798 qps], 453K conn, TX: 337B, RX: 6B)
    [--] Reads / Writes: 69% / 31%
    [--] Total buffers: 1.8G global + 192.5M per thread (200 max threads)
    [!!] Maximum possible memory usage: 39.4G (168% of installed RAM)
    [OK] Slow queries: 0% (9K/8M)
    [OK] Highest usage of available connections: 27% (54/200)
    [!!] Key buffer size / total MyISAM indexes: 0B/1.2G
    [!!] Key buffer hit rate: 0.0% (68M cached / 68M reads)
    [OK] Query cache efficiency: 63.6% (3M cached / 5M selects)
    [!!] Query cache prunes per day: 455500
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 379K sorts)
    [!!] Joins performed without indexes: 57515
    [!!] Temporary tables created on disk: 30% (204K on disk / 661K total)
    [OK] Thread cache hit rate: 99% (475 created / 453K connections)
    [!!] Table cache hit rate: 0% (1K open / 4M opened)
    [OK] Open file limit used: 3% (1K/43K)
    [OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
    [!!] Connections aborted: 16%
    [!!] InnoDB buffer pool / data size: 760.0M/832.4M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Increasing the query_cache size over 128M may reduce performance
    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
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
    Your applications are not closing MySQL connections properly
    Variables to adjust:
    *** MySQL's maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
    key_buffer_size (> 1.2G)
    query_cache_size (> 512M) [see warning above]
    join_buffer_size (> 64.0M, or always use indexes with joins)
    table_cache (> 1024)
    innodb_buffer_pool_size (>= 832M)
    Changes made after this report:
    key_buffer_size=1400 -> #key_buffer_size=1400
    query_cache_size=512M -> query_cache_size=96M
    query_cache_limit=512M -> query_cache_limit=96M

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


    Is this useful / helpfull? Yes | No
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 26d 20h 44m 31s (214M q [92.516 qps], 8M conn, TX: 7566B, RX: 92B)
    [--] Reads / Writes: 74% / 26%
    [--] Total buffers: 1.4G global + 192.5M per thread (200 max threads)
    [!!] Maximum possible memory usage: 39.0G (166% of installed RAM)
    [OK] Slow queries: 0% (165K/214M)
    [OK] Highest usage of available connections: 28% (58/200)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/1.2G
    [OK] Key buffer hit rate: 97.1% (2B cached / 69M reads)
    [OK] Query cache efficiency: 62.8% (94M cached / 151M selects)
    [!!] Query cache prunes per day: 902001
    [OK] Sorts requiring temporary tables: 0% (3 temp sorts / 78M sorts)
    [!!] Joins performed without indexes: 1597832
    [!!] Temporary tables created on disk: 31% (5M on disk / 16M total)
    [OK] Thread cache hit rate: 99% (3K created / 8M connections)
    [!!] Table cache hit rate: 0% (1K open / 109M opened)
    [OK] Open file limit used: 4% (1K/43K)
    [OK] Table locks acquired immediately: 99% (103M immediate / 103M locks)
    [!!] InnoDB buffer pool / data size: 760.0M/810.5M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    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
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
    *** MySQL's maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 96M)
    join_buffer_size (> 64.0M, or always use indexes with joins)
    table_cache (> 1024)
    innodb_buffer_pool_size (>= 810M)
    changes made after this report:

    sort_buffer_size=64M -> sort_buffer_size=4M
    read_buffer_size=64M -> read_buffer_size=2M
    (above changes per advice on https://dev.mysql.com/doc/refman/5.5/en/server-parameters.html)
    table_cache=1024 -> (removed, is named table_open_cache now)
    table_open_cache=1024 -> table_open_cache=2048 (https://mariadb.com/kb/en/mariadb/optimizing-table_open_cache/)
    "The overall goal should be to prevent temp table creation as much as possible." (tmp_table_size increase will not help) watch join and sort buffer

    # free -mt|grep buffers
    -/+ buffers/cache: 3167 20794

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


    Is this useful / helpfull? Yes | No
    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 12d 17h 51m 28s (87M q [79.294 qps], 3M conn, TX: 3611G, RX: 21G)
    [--] Reads / Writes: 77% / 23%
    [--] Binary logging is disabled
    [--] Physical Memory : 18.0G
    [--] Max MySQL memory : 15.1G
    [--] Other process memory: 1.9G
    [--] Total buffers: 1.4G global + 70.5M per thread (200 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 13.9G (77.16% of installed RAM)
    [OK] Maximum possible memory usage: 15.1G (84.05% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [OK] Slow queries: 0% (26K/87M)
    [!!] Highest connection usage: 91% (182/200)
    [OK] Aborted connections: 0.15% (5761/3957389)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [!!] Query cache may be disabled by default due to mutex contention.
    [OK] Sorts requiring temporary tables: 0% (3K temp sorts / 4M sorts)
    [!!] Joins performed without indexes: 734841
    [!!] Temporary tables created on disk: 44% (2M on disk / 5M total)
    [!!] Table cache hit rate: 0% (2K open / 48M opened)
    [OK] Open file limit used: 7% (3K/43K)
    [OK] Table locks acquired immediately: 99% (42M immediate / 42M locks)

    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 79.6% (6M used / 8M cache)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/1.3G
    [OK] Read Key buffer hit rate: 97.3% (982M cached / 26M reads)
    [!!] Write Key buffer hit rate: 56.1% (15M cached / 6M writes)

    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is disabled.

    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [!!] InnoDB buffer pool / data size: 760.0M/1.1G
    [OK] InnoDB buffer pool instances: 1
    [OK] InnoDB Used buffer: 100.00% (48639 used/ 48640 total)
    [OK] InnoDB Read buffer efficiency: 100.00% (10499341540 hits/ 10499681327 total)
    [OK] InnoDB Write log efficiency: 92.21% (11632093 hits/ 12614218 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 982125 writes)


    -------- Recommendations ---------------------------------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    OPTIMIZE TABLE mpellzmp_fh.sessions; -- can free 51 MB
    Total freed space after theses OPTIMIZE TABLE : 51 Mb
    Set up a Secure Password for [email protected] ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Restrict Host for [email protected]% to [email protected]
    Reduce or eliminate persistent connections to reduce connection usage
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    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
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (43762) variable
    should be greater than table_open_cache ( 2048)
    Variables to adjust:
    max_connections (> 200)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_type (=0)
    join_buffer_size (> 64.0M, or always use indexes with joins)
    table_open_cache (> 2048)
    innodb_buffer_pool_size (>= 1G) if possible.
    There was problems with high MySQL memory usage (this and last run), i think next run might be solved thanks to following adjustments

    OLD:
    [!!] Temporary tables created on disk: 31% (5M on disk / 16M total)

    NEW:
    [!!] Temporary tables created on disk: 44% (2M on disk / 5M total)

    Changes i made after this report:
    innodb_buffer_pool_size=760M -> innodb_buffer_pool_size=2G
    -> wait_timeout=300
    -> interactive_timeout=300
    -> query_cache_type=0 (Don't cache results or retrieve from query cache)
    tmp_table_size=512M -> tmp_table_size=192M
    max_heap_table_size=512M -> max_heap_table_size=292M

    Later maybe increase table_open_cache (not sure if safe, im low on RAM)

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


    Is this useful / helpfull? Yes | No
    There are no longer memory issues "Unable to fork". Following is the last 30 days 26.7.2016 - 26.8.2016

    1. privvmpages openvz was set to unlimited and physpages was activated and set to 18GB. vSWAP was set to 2GB
    2. i made MySQL adjustments mentioned in above post in this thread and restarted MySQL
    3. im temporarily running certain bash scripts that is consuming more and more RAM.

    Attachment 283

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


    Is this useful / helpfull? Yes | No
    Still no issues.

    # mysqltunerenhanced_outdated_but_whm_focus
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 48d 19h 5m 42s (371M q [88.084 qps], 21M conn, TX: 13075G, RX: 61G)
    [--] Reads / Writes: 88% / 12%
    [--] Binary logging is disabled
    [--] Total buffers: 2.3G global + 70.5M per thread (200 max threads)
    [OK] Maximum reached memory usage: 9.5G (52.96% of installed RAM)
    [!!] Maximum possible memory usage: 16.1G (89.30% of installed RAM)
    [OK] Slow queries: 0% (35K/371M)
    [OK] Highest usage of available connections: 52% (105/200)
    [!!] Aborted connections: 9.17% (1942459/21190294)
    [OK] Query cache is disabled by default due to mutex contention.
    [OK] Sorts requiring temporary tables: 0% (889 temp sorts / 55M sorts)
    [!!] Joins performed without indexes: 7015399
    [!!] Temporary tables created on disk: 42% (20M on disk / 47M total)
    [OK] Thread cache hit rate: 99% (28K created / 21M connections)
    [!!] Table cache hit rate: 0% (2K open / 177M opened)
    [OK] Open file limit used: 8% (3K/43K)
    [OK] Table locks acquired immediately: 99% (375M immediate / 375M locks)

    -------- MyISAM Metrics ------------------------------------------------------
    [!!] Key buffer used: 89.0% (7M used / 8M cache)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/1.2G
    [OK] Read Key buffer hit rate: 98.5% (7B cached / 106M reads)
    [!!] Write Key buffer hit rate: 70.9% (118M cached / 34M writes)

    -------- InnoDB Metrics ------------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 2.0G/856.2M
    [!!] InnoDB buffer pool instances: 1
    [!!] InnoDB Used buffer: 73.22% (95972 used/ 131072 total)
    [OK] InnoDB Read buffer efficiency: 100.00% (2143288492 hits/ 2143348268 total)
    [!!] InnoDB Write Log efficiency: 78.63% (1858146 hits/ 2363135 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 504989 writes)

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Consider dedicating a server for your database installation with less services running on !
    Consider dedicating a server for Web server in production !
    Run OPTIMIZE TABLE to defragment tables for better performance
    Set up a Secure Password for [email protected] ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Reduce your overall MySQL memory footprint for system stability
    See notes on accuracy of this recommendation below
    Reduce or eliminate unclosed connections and network issues
    Adjust your join queries to always utilize indexes. Please note this
    calculation is made by adding Select_full_join + Select_range_check
    status values and triggered when the total >250
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    (exception MariaDB 10.1.x mariadb.com/kb/en/mariadb/optimizing-table_open_cache/)
    Beware that open_files_limit (43762) variable
    should be greater than table_open_cache ( 2048)
    Variables to adjust:
    join_buffer_size (> 64.0M, or always use indexes with joins)
    tmp_table_size (> 192M)
    max_heap_table_size (> 192M)
    table_open_cache (> 2048)
    innodb_buffer_pool_instances(=2)

    Report Complete:
    Wed Dec 21 10:31:39 UTC 2016
    ----------------------------
    # cat /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=150
    max_user_connections=18
    wait_timeout=300
    interactive_timeout=300

    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=2G
    join_buffer_size=64M
    sort_buffer_size=4M

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

    # was 512M
    query_cache_size=96M
    query_cache_limit=96M
    query_cache_type=0
    table_cache=1024
    table_open_cache=2048
    tmp_table_size=192M
    max_heap_table_size=192M

    default-storage-engine=MyISAM
    [myisamchk]
    ft_min_word_len=3
    Possible issues: [!!] Aborted connections: 9.17% (1942459/21190294)
    Legend: Too many connections are aborted. - Connections are usually aborted when they cannot be authorized.
    My opinion: I am limiting concurrent mysql connections on several sites and one of them is getting flooded by the robots, unsure if this counts towards aborted connections. example: mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: User *_* already has more than 'max_user_connections' active connections
    /home/*/public_html/*.php on line *

    Key buffer used: 89.0% , previously was 79%
    Write Key buffer hit rate: 70.9%, previously 56%

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


    Is this useful / helpfull? Yes | No
    Because of some unknown issue with server overloading (seems to be RAM/HDD issue and the Mysql is TOP process in this matter) i am trying to reduce my.cnf variables. Changes:
    read_buffer_size=128K (was 2M)
    sort_buffer_size=256K (was 4M)
    join_buffer_size=256K (was 64M)
    (values recommended at: https://haydenjames.io/my-cnf-tuning-avoid-this-common-pitfall/)

    - based on advice: "For more than a decade the default remains at 128K. Increasing the default is not only a waste of server memory, but often does not help performance"

    added following below [mysqld]
    # Skip MYSQL Reverse DNS lookup
    skip-name-resolve

    UPDATE: skip-name-resolve caused an issue (i do not remember which, i had to disable it)

    UPDATE2: i got some advices regarding my config and this is what i followed/changed:

    InnoDB buffer pool size to 1G (from 2G)
    Dropped max_connections to 150, max_user_connections to 30
    Increased join_buffer_size to 1M
    Set query_cache_size=32M

+ 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