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.

Optimize PHP and MySQL Request

Discussion in 'Workarounds and Optimization' started by Thorsten H., Sep 13, 2016.

Tags:
  1. Thorsten H.

    Thorsten H. Active Member

    Joined:
    Jul 13, 2016
    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Germany
    cPanel Access Level:
    Root Administrator
    Hello,
    I have a big performanceproblem with my kvm-server. It is running cPanel 11.58 - ea-apache24-mod_mpm_worker - MariaDB 10.0
    I have some tables in one database with more than 1.000.000 enties. This database is very large; the engines are mixed - innoDB and MyISAM.

    When I ran HTOP all 4 cores are running at 100% all the time with php-cgi command using the CPU.

    When I run mysqltuner.pl this is the output:
    Code:
    >>  MySQLTuner 1.6.18 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    
    [--] Skipped version check for MySQLTuner script
    [!!] Currently running unsupported MySQL version 10.0.27-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -----------------------------------------------------------------
    [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA
    [--] Data in Aria tables: 1M (Tables: 2)
    [--] Data in MyISAM tables: 776M (Tables: 249)
    [--] Data in InnoDB tables: 938M (Tables: 1464)
    [--] Data in MEMORY tables: 0B (Tables: 2)
    [OK] Total fragmented tables: 0
    
    -------- Security Recommendations ------------------------------------------------------------------
    [OK] There are no anonymous accounts for any database users
    [OK] All database users have passwords assigned
    [!!] User 'munin@localhost' has user name as password.
    [--] There are 612 basic passwords in the list.
    
    -------- CVE Security Recommendations --------------------------------------------------------------
    [OK] NO SECURITY CVE FOUND FOR YOUR VERSION
    
    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 27s (246 q [9.111 qps], 24 conn, TX: 1M, RX: 38K)
    [--] Reads / Writes: 96% / 4%
    [--] Binary logging is disabled
    [--] Physical Memory     : 7.7G
    [--] Max MySQL memory    : 7.1G
    [--] Other process memory: 1.5G
    [--] Total buffers: 1.1G global + 40.8M per thread (151 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 1.2G (15.02% of installed RAM)
    [!!] Maximum possible memory usage: 7.1G (92.21% of installed RAM)
    [!!] Overall possible memory usage with other process exceeded memory
    [OK] Slow queries: 0% (0/246)
    [OK] Highest usage of available connections: 1% (2/151)
    [!!] Aborted connections: 4.17%  (1/24)
    [!!] Query cache may be disabled by default due to mutex contention.
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 31 sorts)
    [!!] Joins performed without indexes: 2
    [OK] Temporary tables created on disk: 18% (5 on disk / 27 total)
    [OK] Thread cache hit rate: 91% (2 created / 24 connections)
    [OK] Table cache hit rate: 208% (121 open / 58 opened)
    [OK] Open file limit used: 0% (24/10K)
    [OK] Table locks acquired immediately: 100% (244 immediate / 244 locks)
    
    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance schema is disabled.
    
    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is enabled.
    [--] Thread Pool Size: 4 thread(s).
    [--] Using default value is good enough for your version (10.0.27-MariaDB)
    
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 18.2% (12M used / 67M cache)
    [!!] Key buffer size / total MyISAM indexes: 64.0M/269.1M
    
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/160.0K
    [OK] Aria pagecache hit rate: 96.2% (52 cached / 2 reads)
    
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [!!] InnoDB buffer pool / data size: 768.0M/938.5M
    [OK] InnoDB buffer pool instances: 1
    [--] InnoDB Buffer Pool Chunk Size not used or defined in your version
    [OK] InnoDB Read buffer efficiency: 97.94% (80459 hits/ 82151 total)
    [!!] InnoDB Write Log efficiency: 76.47% (26 hits/ 34 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 8 writes)
    
    -------- TokuDB Metrics ----------------------------------------------------------------------------
    [--] TokuDB is disabled.
    
    -------- Galera Metrics ----------------------------------------------------------------------------
    [--] Galera is disabled.
    
    -------- Replication Metrics -----------------------------------------------------------------------
    [--] Galera Synchronous replication: NO
    [--] No replication slave(s) for this server.
    [--] This is a standalone server.
    
    -------- Recommendations ---------------------------------------------------------------------------
    General recommendations:
        Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Dedicate this server to your database for highest performance.
        Reduce or eliminate unclosed connections and network issues
        Adjust your join queries to always utilize indexes
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_type (=0)
        join_buffer_size (> 32.0M, or always use indexes with joins)
        key_buffer_size (> 269.1M)
        innodb_buffer_pool_size (>= 938M) if possible.
    
    my my.cnf
    Code:
    [mysqld]
    performance-schema=0
    bind-address=127.0.0.1
    skip_name_resolve
    
    local-infile=0
    
    # MyISAM-Einträge
    tmp_table_size=75M
    max_heap_table_size=75M # beide Einträge verdoppelt - 20.07.2016 - 64M pro GB
    query_cache_limit = 50M
    query_cache_size = 50M #512MB - 28.07.2016 768M -> 128M
    key_buffer_size=64M #48MB - 17.01.2014 128M - 05.02.2016 96MB - 28.07.2016 64M - 08.09.2016 48MB - 64M 13.09.2016
    join_buffer_size=32M # erhöht 512 - 768 - 11.08.2016 - 8M 05.09.2016 - 32M 13.09.2016
    query_cache_type = 0
    
    # InnoDB-Einträge
    default-storage-engine = InnoDB
    innodb_file_per_table=1
    query_cache_type = 1 # query_cache aus für innoDB
    innodb_buffer_pool_size = 768M #Eintrag lt. mysqltuner - 11.08.2016 1G - 05.09.2016 1G - 768M 13.09.2016
    innodb_buffer_pool_instances = 1 # Anzahl der Cores
    
    wait_timeout=60 # löscht Sleep-Connections - default 28800
    
    table_cache=4096 # das Gleiche wie table_open_cache - nur eins verwenden!
    sort_buffer_size=256k
    read_buffer_size=256k
    read_rnd_buffer_size=8M
    myisam_sort_buffer_size=128M
    
    
    thread_cache_size=16
    table_definition_cache=3500
    
    
    # Logs bremsen den Server aus und produzieren viele Daten
    log-queries-not-using-indexes=0
    
    log-error = "/var/log/mysql/server.ws-s.de.err"
    
    # engine-condition-pushdown
    open_files_limit=10000
    
    log-output=FILE
    max_allowed_packet=268435456
    
    query_cache_min_res_unit = 4096
    So my question is, where to start searching for the problem.

    Can anyone help me and give me tips to optimize PHP and mysql?
     
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,450
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    Some key details that you've already got:

    Code:
    -------- Recommendations ---------------------------------------------------------------------------
    General recommendations:
        Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Dedicate this server to your database for highest performance.
        Reduce or eliminate unclosed connections and network issues
        Adjust your join queries to always utilize indexes
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_type (=0)
        join_buffer_size (> 32.0M, or always use indexes with joins)
        key_buffer_size (> 269.1M)
        innodb_buffer_pool_size (>= 938M) if possible.
    .
     
  3. Thorsten H.

    Thorsten H. Active Member

    Joined:
    Jul 13, 2016
    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Germany
    cPanel Access Level:
    Root Administrator
    Hello InfoPro,
    you are right, but the hardfacts (RAM, RAM used by MySQL) will not change over the runtime I think. But now I will/have to wait until 1 day or so is over.

    But I'm also glad to get some tipps to optimize PHP and MySQL.

    Regards
    Thorsten
     
  4. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,450
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    It looks by your my.cnf that you already got some tips. If it was me I think I would remove all of that from the my.cnf, restart MySQL, and then let it run 24 hours.
     
  5. Thorsten H.

    Thorsten H. Active Member

    Joined:
    Jul 13, 2016
    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Germany
    cPanel Access Level:
    Root Administrator
    Hello,
    was that a joke or do you mean it regular? I'm afraid, that the MariaDB-Server will crash - and it is a productive server, so I don't want to play around with it.
     
  6. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,450
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
  7. Thorsten H.

    Thorsten H. Active Member

    Joined:
    Jul 13, 2016
    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Germany
    cPanel Access Level:
    Root Administrator
    Thanks for the links.
     
  8. Thorsten H.

    Thorsten H. Active Member

    Joined:
    Jul 13, 2016
    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Germany
    cPanel Access Level:
    Root Administrator
    Hello,
    it sounds a bit strange, but I tried it - renaming the my.cnf and restarting mysql/MariaDB.

    The first start without confi-file was a bit slow but not so bad. the messuring with mysqltuner.pl gave some recommendations the I included into a new my.cnf. Now I will wait 1 or 2 days to see what will happen. What I have seen by now is, that the memory used is going dowm from 5.7 GB to just 1,8 GB.

    Here are the outputs and the, realy small, my.cnf

    Code:
     >>  MySQLTuner 1.6.18 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    
    [[0;34m--[0m] Skipped version check for MySQLTuner script
    [[0;31m!![0m] Currently running unsupported MySQL version 10.0.27-MariaDB
    [[0;32mOK[0m] Operating on 64-bit architecture
    -------- Storage Engine Statistics -----------------------------------------------------------------
    [[0;34m--[0m] Status: [0;32m+ARCHIVE [0m[0;32m+Aria [0m[0;32m+BLACKHOLE [0m[0;32m+CSV [0m[0;32m+FEDERATED [0m[0;32m+InnoDB [0m[0;32m+MEMORY [0m[0;32m+MRG_MyISAM [0m[0;32m+MyISAM [0m[0;32m+PERFORMANCE_SCHEMA [0m
    [[0;34m--[0m] Data in Aria tables: 1M (Tables: 2)
    [[0;34m--[0m] Data in MyISAM tables: 768M (Tables: 249)
    [[0;34m--[0m] Data in InnoDB tables: 937M (Tables: 1464)
    [[0;34m--[0m] Data in MEMORY tables: 0B (Tables: 2)
    [[0;32mOK[0m] Total fragmented tables: 0
    -------- Security Recommendations ------------------------------------------------------------------
    [[0;32mOK[0m] There are no anonymous accounts for any database users
    [[0;32mOK[0m] All database users have passwords assigned
    [[0;31m!![0m] User 'munin@localhost' has user name as password.
    [[0;31m!![0m] There is no basic password file list!
    -------- CVE Security Recommendations --------------------------------------------------------------
    [[0;32mOK[0m] NO SECURITY CVE FOUND FOR YOUR VERSION
    -------- Performance Metrics -----------------------------------------------------------------------
    [[0;34m--[0m] Up for: 11m 0s (13K q [19.735 qps], 342 conn, TX: 67M, RX: 1M)
    [[0;34m--[0m] Reads / Writes: 97% / 3%
    [[0;34m--[0m] Binary logging is disabled
    [[0;34m--[0m] Physical Memory     : 7.7G
    [[0;34m--[0m] Max MySQL memory    : 836.0M
    [[0;34m--[0m] Other process memory: 1.1G
    [[0;34m--[0m] Total buffers: 416.0M global + 2.8M per thread (151 max threads)
    [[0;34m--[0m] P_S Max memory usage: 0B
    [[0;34m--[0m] Galera GCache Max memory usage: 0B
    [[0;32mOK[0m] Maximum reached memory usage: 429.9M (5.46% of installed RAM)
    [[0;32mOK[0m] Maximum possible memory usage: 836.0M (10.62% of installed RAM)
    [[0;32mOK[0m] Overall possible memory usage with other process is compatible with memory available
    [[0;32mOK[0m] Slow queries: 0% (1/13K)
    [[0;32mOK[0m] Highest usage of available connections: 3% (5/151)
    [[0;32mOK[0m] Aborted connections: 0.58%  (2/342)
    [[0;31m!![0m] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [[0;31m!![0m] Query cache may be disabled by default due to mutex contention.
    [[0;32mOK[0m] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
    [[0;31m!![0m] Joins performed without indexes: 61
    [[0;32mOK[0m] Temporary tables created on disk: 23% (189 on disk / 807 total)
    [[0;31m!![0m] Thread cache is disabled
    [[0;31m!![0m] Table cache hit rate: 4% (400 open / 9K opened)
    [[0;32mOK[0m] Open file limit used: 4% (198/4K)
    [[0;32mOK[0m] Table locks acquired immediately: 100% (13K immediate / 13K locks)
    -------- Performance schema ------------------------------------------------------------------------
    [[0;34m--[0m] Performance schema is disabled.
    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [[0;34m--[0m] ThreadPool stat is enabled.
    [[0;34m--[0m] Thread Pool Size: 4 thread(s).
    [[0;34m--[0m] Using default value is good enough for your version (10.0.27-MariaDB)
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [[0;31m!![0m] Key buffer used: 18.4% (24M used / 134M cache)
    [[0;31m!![0m] Key buffer size / total MyISAM indexes: 128.0M/267.6M
    [[0;31m!![0m] Read Key buffer hit rate: 94.5% (6K cached / 341 reads)
    [[0;31m!![0m] Write Key buffer hit rate: 0.0% (187 cached / 187 writes)
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [[0;34m--[0m] AriaDB is enabled.
    [[0;32mOK[0m] Aria pagecache size / total Aria indexes: 128.0M/160.0K
    [[0;32mOK[0m] Aria pagecache hit rate: 99.5% (22K cached / 113 reads)
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [[0;34m--[0m] InnoDB is enabled.
    [[0;31m!![0m] InnoDB buffer pool / data size: 128.0M/937.7M
    [[0;31m!![0m] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
    [[0;34m--[0m] InnoDB Buffer Pool Chunk Size not used or defined in your version
    [[0;32mOK[0m] InnoDB Read buffer efficiency: 99.82% (13144001 hits/ 13167875 total)
    [[0;31m!![0m] InnoDB Write Log efficiency: 51.87% (319 hits/ 615 total)
    [[0;32mOK[0m] InnoDB log waits: 0.00% (0 waits / 296 writes)
    -------- TokuDB Metrics ----------------------------------------------------------------------------
    [[0;34m--[0m] TokuDB is disabled.
    -------- Galera Metrics ----------------------------------------------------------------------------
    [[0;34m--[0m] Galera is disabled.
    -------- Replication Metrics -----------------------------------------------------------------------
    [[0;34m--[0m] Galera Synchronous replication: NO
    [[0;34m--[0m] No replication slave(s) for this server.
    [[0;34m--[0m] This is a standalone server.
    -------- Recommendations ---------------------------------------------------------------------------
    General recommendations:
        Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
        MySQL started within last 24 hours - recommendations may be inaccurate
        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
        Set thread_cache_size to 4 as a starting value
        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 (4096) variable
        should be greater than table_open_cache ( 400)
    Variables to adjust:
        query_cache_type (=0)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        thread_cache_size (start at 4)
        table_open_cache (> 400)
        key_buffer_size (> 267.6M)
        innodb_buffer_pool_size (>= 937M) if possible.
        innodb_buffer_pool_instances (=1)
    
    Code:
     >>  MySQLTuner 1.6.18 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    
    [[0;34m--[0m] Skipped version check for MySQLTuner script
    [[0;31m!![0m] Currently running unsupported MySQL version 10.0.27-MariaDB
    [[0;32mOK[0m] Operating on 64-bit architecture
    -------- Storage Engine Statistics -----------------------------------------------------------------
    [[0;34m--[0m] Status: [0;32m+ARCHIVE [0m[0;32m+Aria [0m[0;32m+BLACKHOLE [0m[0;32m+CSV [0m[0;32m+FEDERATED [0m[0;32m+InnoDB [0m[0;32m+MEMORY [0m[0;32m+MRG_MyISAM [0m[0;32m+MyISAM [0m[0;32m+PERFORMANCE_SCHEMA [0m
    [[0;34m--[0m] Data in Aria tables: 1M (Tables: 2)
    [[0;34m--[0m] Data in MyISAM tables: 768M (Tables: 249)
    [[0;34m--[0m] Data in InnoDB tables: 946M (Tables: 1525)
    [[0;34m--[0m] Data in MEMORY tables: 0B (Tables: 2)
    [[0;32mOK[0m] Total fragmented tables: 0
    -------- Security Recommendations ------------------------------------------------------------------
    [[0;32mOK[0m] There are no anonymous accounts for any database users
    [[0;32mOK[0m] All database users have passwords assigned
    [[0;31m!![0m] User 'munin@localhost' has user name as password.
    [[0;31m!![0m] There is no basic password file list!
    -------- CVE Security Recommendations --------------------------------------------------------------
    [[0;32mOK[0m] NO SECURITY CVE FOUND FOR YOUR VERSION
    -------- Performance Metrics -----------------------------------------------------------------------
    [[0;34m--[0m] Up for: 8m 42s (12K q [24.379 qps], 477 conn, TX: 69M, RX: 1M)
    [[0;34m--[0m] Reads / Writes: 97% / 3%
    [[0;34m--[0m] Binary logging is disabled
    [[0;34m--[0m] Physical Memory     : 7.7G
    [[0;34m--[0m] Max MySQL memory    : 1.8G
    [[0;34m--[0m] Other process memory: 1.1G
    [[0;34m--[0m] Total buffers: 1.3G global + 2.8M per thread (151 max threads)
    [[0;34m--[0m] P_S Max memory usage: 0B
    [[0;34m--[0m] Galera GCache Max memory usage: 0B
    [[0;32mOK[0m] Maximum reached memory usage: 1.4G (17.68% of installed RAM)
    [[0;32mOK[0m] Maximum possible memory usage: 1.8G (22.84% of installed RAM)
    [[0;32mOK[0m] Overall possible memory usage with other process is compatible with memory available
    [[0;32mOK[0m] Slow queries: 0% (0/12K)
    [[0;32mOK[0m] Highest usage of available connections: 3% (5/151)
    [[0;32mOK[0m] Aborted connections: 0.42%  (2/477)
    [[0;32mOK[0m] Query cache is disabled by default due to mutex contention on multiprocessor machines.
    [[0;32mOK[0m] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
    [[0;31m!![0m] Joins performed without indexes: 75
    [[0;31m!![0m] Temporary tables created on disk: 31% (332 on disk / 1K total)
    [[0;32mOK[0m] Thread cache hit rate: 98% (5 created / 477 connections)
    [[0;31m!![0m] Table cache hit rate: 4% (400 open / 9K opened)
    [[0;32mOK[0m] Open file limit used: 3% (131/4K)
    [[0;32mOK[0m] Table locks acquired immediately: 100% (12K immediate / 12K locks)
    -------- Performance schema ------------------------------------------------------------------------
    [[0;34m--[0m] Performance schema is disabled.
    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [[0;34m--[0m] ThreadPool stat is enabled.
    [[0;34m--[0m] Thread Pool Size: 4 thread(s).
    [[0;34m--[0m] Using default value is good enough for your version (10.0.27-MariaDB)
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [[0;31m!![0m] Key buffer used: 18.2% (51M used / 281M cache)
    [[0;32mOK[0m] Key buffer size / total MyISAM indexes: 268.0M/267.6M
    [[0;31m!![0m] Read Key buffer hit rate: 94.1% (3K cached / 203 reads)
    [[0;31m!![0m] Write Key buffer hit rate: 4.2% (120 cached / 115 writes)
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [[0;34m--[0m] AriaDB is enabled.
    [[0;32mOK[0m] Aria pagecache size / total Aria indexes: 128.0M/160.0K
    [[0;32mOK[0m] Aria pagecache hit rate: 99.7% (46K cached / 120 reads)
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [[0;34m--[0m] InnoDB is enabled.
    [[0;32mOK[0m] InnoDB buffer pool / data size: 950.0M/946.5M
    [[0;32mOK[0m] InnoDB buffer pool instances: 1
    [[0;34m--[0m] InnoDB Buffer Pool Chunk Size not used or defined in your version
    [[0;32mOK[0m] InnoDB Read buffer efficiency: 99.89% (6772910 hits/ 6780192 total)
    [[0;31m!![0m] InnoDB Write Log efficiency: 54.07% (339 hits/ 627 total)
    [[0;32mOK[0m] InnoDB log waits: 0.00% (0 waits / 288 writes)
    -------- TokuDB Metrics ----------------------------------------------------------------------------
    [[0;34m--[0m] TokuDB is disabled.
    -------- Galera Metrics ----------------------------------------------------------------------------
    [[0;34m--[0m] Galera is disabled.
    -------- Replication Metrics -----------------------------------------------------------------------
    [[0;34m--[0m] Galera Synchronous replication: NO
    [[0;34m--[0m] No replication slave(s) for this server.
    [[0;34m--[0m] This is a standalone server.
    -------- Recommendations ---------------------------------------------------------------------------
    General recommendations:
        Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries 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
        Beware that open_files_limit (4096) variable
        should be greater than table_open_cache ( 400)
    Variables to adjust:
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        table_open_cache (> 400)
    
    and that is the acual my.cnf
    Code:
    [mysqld]
    bind-address = 127.0.0.1 
    skip-name-resolve = 1
    thread_cache_size = 4
    table_open_cache = 400
    join_buffer_size = 128K
    
    query_cache_type = 0
    tmp_table_size = 16M
    max_heap_table_size = 16M
    key_buffer_size = 268M
    innodb_buffer_pool_size = 950M
    innodb_buffer_pool_instances = 1 
    
    The next days I will post a new one.
     
  9. pacificnet.lk

    pacificnet.lk Registered

    Joined:
    Sep 15, 2016
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Melbourne
    cPanel Access Level:
    DataCenter Provider
    Hi Thorsten H.

    Just a thort. Did you check the IOPS the server can do and if it is not overloading the Disk IOPS

    Thanks
     
  10. Thorsten H.

    Thorsten H. Active Member

    Joined:
    Jul 13, 2016
    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Germany
    cPanel Access Level:
    Root Administrator
    Hello Pacific...
    I have the parameters but have not checked it by now, because I don't know good or bad values for that.

    Here are the values from yesterday:
    Disk IO (read/write per sec.) between 100 and 350 with one spike up to 900

    Last month all between 100 and spikes to 350/400.

    So I think they are OK.

    And I changed/added some parameters to my.cnf

    This is the actual one:
    Code:
    [mysqld]
    bind-address = 127.0.0.1             # 15.09.2016
    skip-name-resolve = 1                 # 15.09.2016
    thread_cache_size = 4                 # 15.09.2016
    table_open_cache = 1000             # 400 15.09.2016 - 1000 16.09.2016
    join_buffer_size = 256K             # 128k 15.09.2016 - 256k 16.09.2016
    
    query_cache_type = 0                 # 0 15.09.2016
    tmp_table_size = 24M                 # 16M 15.09.2016 - 24M 16.09.2016
    max_heap_table_size = 24M             # 16M 15.09.2016 - 24M 16.09.2016
    key_buffer_size = 268M                 # 268M 15.09.2016
    innodb_buffer_pool_size = 950M         # 950M 15.09.2016
    innodb_buffer_pool_instances = 1     # 1 15.09.2016
    
    max_connections = 200                # 200 16.09.2016
    wait_timeout = 30                    # 30 16.09.2016
    interactive_timeout = 30            # 30 16.09.2016
    
    
    Any sugestions?
     
Loading...

Share This Page