The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySql Tuning Optimization Help

Discussion in 'Database Discussions' started by tui, Mar 17, 2014.

  1. tui

    tui Active Member

    Joined:
    Jun 15, 2007
    Messages:
    38
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Mexico
    cPanel Access Level:
    Root Administrator
    Hello all, could somebody help me to tuning mysql? What i have:

    Vps with CentOS release 5.10 (Final) i686, 6GB Ram and 4 vCores of Intel(R) Xeon(R) CPU E31270 @ 3.40GHz

    My tuning prime:

    Code:
            -- MYSQL PERFORMANCE TUNING PRIMER --
                 - By: Matthew Montgomery -
    MySQL Version 5.5.36-cll i686
    Uptime = 9 days 0 hrs 48 min 56 sec
    Avg. qps = 10
    Total Questions = 8039793
    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 NOT enabled.
    Current long_query_time = 10.000000 sec.
    You have 1134 out of 8039814 that take longer than 10.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 = 8
    Current threads_cached = 6
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 250
    Current threads_connected = 2
    Historic max_used_connections = 45
    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 = 36 M
    Current InnoDB data space = 37 M
    Current InnoDB buffer pool free = 0 %
    Current innodb_buffer_pool_size = 8 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 : 547 M
    Configured Max Per-thread Buffers : 2.57 G
    Configured Max Global Buffers : 72 M
    Configured Max Memory Limit : 2.64 G
    Physical Memory : 6.00 G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    Current MyISAM index space = 40 M
    Current key_buffer_size = 16 M
    Key cache miss rate is 1 : 22
    Key buffer free ratio = 84 %
    Your key_buffer_size seems to be fine
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 32 M
    Current query_cache_used = 21 M
    Current query_cache_limit = 1 M
    Current Query cache Memory fill ratio = 67.36 %
    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 = 256 K
    Current read_rnd_buffer_size = 8 M
    Sort buffer seems to be fine
    
    JOINS
    Current join_buffer_size = 132.00 K
    You have had 5358 queries where a join could not use an index properly
    You have had 4903 joins without keys that check for key usage after each row
    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 = 13670 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 = 256 tables
    Current table_definition_cache = 400 tables
    You have a total of 2821 tables
    You have 256 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 486591 temp tables, 30% 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 = 2 M
    Current table scan ratio = 172 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 352
    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'.
    My my.cnf:

    Code:
    [myisamchk]
    write_buffer=2M
    key_buffer=128M
    sort_buffer_size=128M
    read_buffer=2M
    [mysqld]
    myisam_sort_buffer_size=16M
    max_allowed_packet=1M
    table_cache=256
    local-infile=0
    key_buffer=16M
    thread_cache_size=8
    query_cache_size=32M
    innodb_buffer_pool_size=8M
    skip-external-locking
    max_user_connections=30
    read_rnd_buffer_size=8M
    wait_timeout=40
    read_buffer_size=2M
    server-id=1
    sort_buffer_size=256K
    open_files_limit=13670
    max_connections=250
    thread_concurrency=8
    connect_timeout=10
    innodb_file_per_table=1
    default-storage-engine=MyISAM
    [mysqldump]
    max_allowed_packet=16M
    quick
    [mysql]
    no-auto-rehash
    [mysqlhotcopy]
    interactive-timeout
    Thanks
     
  2. markvegas

    markvegas Member

    Joined:
    Mar 18, 2014
    Messages:
    14
    Likes Received:
    1
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    Not sure if this will help you, this is how we have our my.cnf file setup our machines are very similar spec


    Code:
    [mysqld]
        
        local-infile=0
        query_cache_size=256M
        query_cache_limit=8M
        max_connections = 50
        thread_cache_size=16
        join_buffer_size=3M
        key_buffer_size=100M
        sort_buffer_size=4M
    	read_buffer_size=4M
    	read_rnd_buffer_size=8M
    	myisam_sort_buffer_size=64M
        table_cache=2048
        table_definition_cache=800
        tmp_table_size = 256M
    	max_heap_table_size = 256M
    	
    	low_priority_updates=1
    	concurrent_insert=ALWAYS
    	slow_query_log = 1
    	slow_query_log_file=/var/log/my-slow.log
    	long_query_time=3
    	
    	
    open_files_limit=3628
    [isamchk]
    key_buffer_size = 256M
    sort_buffer_size = 256M
    read_buffer_size = 2M
    write_buffer_size = 2M
    
    [myisamchk]
    key_buffer_size = 256M
    sort_buffer_size = 256M
    read_buffer_size = 2M
    write_buffer_size = 2M
    
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  4. tui

    tui Active Member

    Joined:
    Jun 15, 2007
    Messages:
    38
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Mexico
    cPanel Access Level:
    Root Administrator
    Hello again,

    @markvegas, thanks for your suggested values :) i liked it, @cpanelmichael.

    Im upgrading to mysql 5.6 but it is taking too much time :/ the upgrade has been running for the last 7 hours and still, i think it is at 35%... when the update completes i will apply the suggested values and see how it works and update with my results.

    Btw, i always see too much values of this on my server with top or with htop, is this normal?

    Code:
     2609 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.27 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     2734 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.17 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14706 mysql      20   0  869M  630M  8796 D  0.0 10.3  0:06.44 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14741 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:06.36 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     2601 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.28 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    15452 mysql      20   0  869M  630M  8796 D  0.0 10.3  0:05.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14754 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:05.84 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14673 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.54 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    30585 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.77 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    30757 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:01.12 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14675 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:01.88 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     1575 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.39 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
      407 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.53 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14708 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.16 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14674 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:02.06 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     2647 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.39 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14696 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.91 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14704 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.34 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14676 mysql      20   0  869M  630M  8796 S  0.6 10.3  0:02.54 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14695 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.46 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14671 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.60 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14670 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.61 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14677 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:01.19 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14672 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.55 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14669 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.84 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14668 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.53 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14705 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.46 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14726 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.39 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14697 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14709 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14707 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    14717 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     3724 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     3977 mysql      20   0  869M  630M  8796 S  0.0 10.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Do you receive the same result after restarting MySQL? Are those processes listed when using "ps"? You can run a command such as "mysqladmin processlist" to see which databases are actively using MySQL.

    Thank you.
     
  6. tui

    tui Active Member

    Joined:
    Jun 15, 2007
    Messages:
    38
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Mexico
    cPanel Access Level:
    Root Administrator
    The update just finished but now i could not start mysql with my old my.cnf values or @markvegas suggested values, im getting this errror:

    Starting MySQL...................... ERROR! The server quit without updating PID file.

    I deleted my.cnf and mysql can start, but i dont have any values now :/

    - - - Updated - - -

    After restarting mysql (with no my.cnf) i see this processes again:

    Code:
    5946 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:01.25 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     6050 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.17 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     6001 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.23 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     6011 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5948 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5949 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5950 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5951 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5952 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5953 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 
     5954 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5955 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5956 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5957 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5985 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5986 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5987 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5990 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5991 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5992 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5993 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5994 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5995 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     5996 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     6015 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     6029 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     6224 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.04 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     6301 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     6308 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     6310 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
     6448 mysql      20   0  770M  444M  8184 S  0.0  7.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    And mysqladmin processlist:
    Code:
    +-----+-----------+-----------+-----------+----------------+------+--------------------+------------------+
    | Id  | User      | Host      | db        | Command        | Time | State              | Info             |
    +-----+-----------+-----------+-----------+----------------+------+--------------------+------------------+
    | 10  | eximstats | localhost | eximstats | Sleep          | 8    |                    |                  |
    | 12  | DELAYED   | localhost | eximstats | Delayed insert | 8    | Waiting for INSERT |                  |
    | 162 | DELAYED   | localhost | eximstats | Delayed insert | 17   | Waiting for INSERT |                  |
    | 200 | root      | localhost |           | Query          | 0    | init               | show processlist |
    +-----+-----------+-----------+-----------+----------------+------+--------------------+------------------+
     
    #6 tui, Mar 19, 2014
    Last edited: Mar 19, 2014
  7. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    You can review the MySQL error log (/var/lib/mysql/$hostname.err) when you attempt to start MySQL with your existing /etc/my.cnf configuration to see what specific value is preventing it from starting.

    Thank you.
     
  8. tui

    tui Active Member

    Joined:
    Jun 15, 2007
    Messages:
    38
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Mexico
    cPanel Access Level:
    Root Administrator
    Is this value:

    2014-03-19 12:43:21 5787 [ERROR] /usr/sbin/mysqld: unknown variable 'table_cache=2048'

    - - - Updated - - -

    Already commented that value and mysql start without problems;

    But i still see this:

    Code:
    10146 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10225 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10224 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.04 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10094 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10095 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10096 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10097 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10098 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10099 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10100 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10101 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10102 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10103 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10120 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10121 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10122 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10132 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10133 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10134 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10135 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10136 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10137 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10138 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10172 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10187 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10208 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10223 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    10248 mysql      20   0  801M  138M  7980 S  0.0  2.3  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
    and mysqladmin processlist:

    Code:
    +----+-----------+-----------+-----------+----------------+------+--------------------+------------------+
    | Id | User      | Host      | db        | Command        | Time | State              | Info             |
    +----+-----------+-----------+-----------+----------------+------+--------------------+------------------+
    | 15 | eximstats | localhost | eximstats | Sleep          | 7    |                    |                  |
    | 16 | DELAYED   | localhost | eximstats | Delayed insert | 7    | Waiting for INSERT |                  |
    | 35 | DELAYED   | localhost | eximstats | Delayed insert | 105  | Waiting for INSERT |                  |
    | 70 | root      | localhost |           | Query          | 0    | init               | show processlist |
    +----+-----------+-----------+-----------+----------------+------+--------------------+------------------+
     
  9. tui

    tui Active Member

    Joined:
    Jun 15, 2007
    Messages:
    38
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Mexico
    cPanel Access Level:
    Root Administrator
    I have this "strange" problem, thats why i started to check mysql and my.cnf

    This problem started like 1 month ago when i noticed that one of our servers starting to have high load for no reason, we have some desks at my support office where i have a lot of displays and i have htop,top, mytop, whm and other tools open almost all time in all my servers so we can detect any problem before my clients complains or before they realized that something is wrong with the service (in case something happen), so we anticipate problems and we can face a lot of problems almost instantly, we like to give a premium service, if a bad script runs or a site is compromised we can identify and kill the script and fix the site almost instantly.

    So, like 1 month ago one of our servers starting to have high load for no reason, no bad scripts, no compromised sites and no mail spam or big querys on mysql, the server just start rising its load for no reason. we have like 70 small sites on the server, only one of them was having like 1000 visits per day and the other sites are really small, some of them uses wordpress but are really small sites.

    We started to use nginxcp and the server was stable for 1 week and then the high load start again, so we change to litespeed and move 4 sites (the most visited sites) to a new server and the server becomes stable a little high load but something within normal, 10 days passed without problems and yesterday at off peak hours the server started to have high load for almost 10 hours until i reboot it, then it becomes stable again and today i upgraded it to mysql 5.6 and applied the suggested values of @markvegas, everything was fine but a few minutes ago my server start rising its load apparently for no reason, at the moment of the high load i check the mysqladmin processlist and nothing strange in it:

    Code:
    MySQL on localhost (5.6.16-log)                                                                                                                                                                                                                                                                                                                                      up 0+05:57:27 [19:11:01]
     Queries: 14.0   qps:    0 Slow:     1.0         Se/In/Up/De(%):    00/00/00/00 
                 qps now:    0 Slow qps: 0.0  Threads:    4 (   3/   5) 00/00/00/00 
     Key Efficiency: 99.2%  Bps in/out:   0.0/  3.5   Now in/out:   8.4/ 2.0k
          Id      User         Host/IP         DB      Time    Cmd Query or State                                                                                                                                                                                                                                                                                                                
          --      ----         -------         --      ----    --- ----------                                                                                                                                                                                                                                                                                                                    
       10538      root       localhost      mysql         0  Query show full processlist                                                                                                                                                                                                                                                                                                         
       10546 roundcube       localhost  roundcube         3  Query UPDATE session SET changed='2014-03-19 20:10:58' WHERE sess_id='qlmbdfhovcnqrq4mej7nlh6pr2'                                                                                                                                                                                                                                   
       10430 eximstats       localhost  eximstats         8  Query update smtp INNER JOIN sends ON (sends.msgid=smtp.msgid) set smtp.processed=1 where smtp.transport_is_remote=1 and sends.user='cuplaper' and smtp.processed=0; /* Cpanel::Logd::geteximstats - bandwidth processing */                                                                                                        
          15 eximstats       localhost  eximstats        10  Sleep                                                                                                                                                                                                                                                                                                                               
       10371   DELAYED       localhost  eximstats        10 Delaye Waiting for INSERT                                                                                                                                                                                                                                                                                                            
       10536   DELAYED       localhost  eximstats        45 Delaye Waiting for INSERT
    I killed all processes by user mysql and the server immediately becomes stable again...

    ATM of writing this i have this mysql processes running on the server:

    Code:
    7501 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:10.59 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7562 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:02.20 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7592 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:02.12 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7777 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:02.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7503 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7568 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:02.10 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7537 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.14 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7548 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.04 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7505 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.10 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7504 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7508 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7506 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7532 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.07 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7535 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7553 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.05 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7510 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7507 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7511 mysql      20   0  805M  146M  8324 S  0.7  2.4  0:00.11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7533 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7512 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.10 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7509 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7536 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7534 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7547 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7549 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
     7550 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
    11067 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
    11576 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
    11630 mysql      20   0  805M  146M  8324 S  0.0  2.4  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
    I think that thats is the origin of my load issues, there is something strange on it.
     
Loading...

Share This Page