Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

Abnormal MySQL Behavior : Unresolved Errors

Discussion in 'Workarounds and Optimization' started by MekGro, Jan 6, 2016.

  1. MekGro

    MekGro Member

    Joined:
    Jan 6, 2016
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    India
    cPanel Access Level:
    Website Owner
    Hello,

    We have 4 GB RAM & 4.8 GHz Total CPU based Linux VPS. Since last 1 month, we have encountered "No Such directory" , "Mysql gone away" , "InnoDB tables not found" errors just about everyday. It seems that MySQL is consuming lot of resources and hence our website is going down.

    We have done the mysqltuner.pl, results are:-
    Code:
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 215M (Tables: 268)
[--] Data in InnoDB tables: 4G (Tables: 3338)
[--] Data in MEMORY tables: 0B (Tables: 153)
[!!] Total fragmented tables: 3379

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1m 41s (58K q [577.267 qps], 168 conn, TX: 191M, RX: 14M)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is disabled
[--] Total buffers: 2.2G global + 6.4M per thread (500 max threads)
[OK] Maximum reached memory usage: 2.5G (66.85% of installed RAM)
[!!] Maximum possible memory usage: 5.3G (143.21% of installed RAM)
[OK] Slow queries: 0% (516/58K)
[OK] Highest usage of available connections: 8% (42/500)
[OK] Aborted connections: 0.00%  (0/168)
[OK] Query cache efficiency: 90.9% (51K cached / 57K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Joins performed without indexes: 2
[!!] Temporary tables created on disk: 39% (423 on disk / 1K total)
[OK] Thread cache hit rate: 75% (42 created / 168 connections)
[OK] Table cache hit rate: 97% (261 open / 268 opened)
[OK] Open file limit used: 0% (59/10K)
[OK] Table locks acquired immediately: 100% (11K immediate / 11K locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.8% (6M used / 33M cache)
[!!] Key buffer size / total MyISAM indexes: 32.0M/45.5M
[!!] Read Key buffer hit rate: 92.1% (2K cached / 190 reads)
[OK] Write Key buffer hit rate: 95.8% (142 cached / 6 writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 2.0G/4.1G
[!!] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 3.32% (4350 used/ 131071 total)
[OK] InnoDB Read buffer efficiency: 99.06% (438813 hits/ 442966 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 175 writes)

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

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    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
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)
    key_buffer_size (> 45.5M)
    innodb_buffer_pool_size (>= 4G) if possible.
    innodb_buffer_pool_instances(=2)

Our my.cnf is:-

max_connections=500
max_user_connections=250
default-storage-engine=MyISAM
innodb_thread_concurrency=2
innodb_file_per_table=0
innodb_buffer_pool_size=2GB
wait_timeout            = 28800
connect_timeout=120
max_allowed_packet=268435456
thread_cache_size       = 1024
sort_buffer_size        = 2M
bulk_insert_buffer_size = 4M
tmp_table_size          = 128M
table_cache=1800
max_heap_table_size     = 128M
key_buffer_size         = 32M
myisam_sort_buffer_size = 128M
read_buffer_size        = 2M
read_rnd_buffer_size    = 2M
query_cache_limit       = 80M
query_cache_size        = 64M
#query_cache_type        = 2M
table_open_cache=4000
#open_files_limit=10000
open_files_limit=10000
slow_query_log=1
slow_query_log_file= /var/log/mysql_slow_queries.log
long_query_time = 1
log-queries-not-using-indexes=1
    Please let me know, what can we do further to reduce the issues.
     
    #1 MekGro, Jan 6, 2016
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    43,711
    Likes Received:
    1,794
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello :)

    I suggest letting MySQL run for at least 24 hours before running the tuner to obtain accurate results.

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #2 cPanelMichael, Jan 6, 2016
  3. MekGro

    MekGro Member

    Joined:
    Jan 6, 2016
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    India
    cPanel Access Level:
    Website Owner
    Thanks for the response but that seems unrealistic as in between timespan of 24 hours my server will need restart because of one or the other reason. Sometimes i am some editing is being done from backend and server gives up resulting in mysql error.

    BTW: is it required to disable cache when we are working on magento admin.

    Regards
     
    #3 MekGro, Jan 6, 2016
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    43,711
    Likes Received:
    1,794
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Could you let us know some of the specific output to /var/lib/mysql/$hostname.err when these issues occur?

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #4 cPanelMichael, Jan 6, 2016
  5. MekGro

    MekGro Member

    Joined:
    Jan 6, 2016
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    India
    cPanel Access Level:
    Website Owner
    how can check the error logs of a specific date with "/var/lib/mysql/$hostname.err"
     
    #5 MekGro, Jan 7, 2016
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    43,711
    Likes Received:
    1,794
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    You can search the log file with the "grep" command. EX:

    Code:
    grep $date /var/lib/mysql/$hostname.err
    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #6 cPanelMichael, Jan 7, 2016
  7. MekGro

    MekGro Member

    Joined:
    Jan 6, 2016
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    India
    cPanel Access Level:
    Website Owner
    hi,

    thanks for reverting back, as suggest the error log(when the issue occurred) is follow:-

    160101 23:33:02 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
    160101 23:33:02 [ERROR] /usr/sbin/mysqld: Table './eximstats/failures' is marked as crashed and should be repaired
    160101 23:34:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
    160101 23:34:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
    160101 23:34:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
    160101 23:34:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/defers' is marked as crashed and should be repaired
    160101 23:34:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/failures' is marked as crashed and should be repaired
    160101 23:34:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/defers' is marked as crashed and should be repaired

    The error log is filled with eximstats issue, is this the culprit of my issues. what should i do next.

    Regards
     
    #7 MekGro, Jan 7, 2016
  8. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    43,711
    Likes Received:
    1,794
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    You can repair the "eximstats" database via a command such as:

    Code:
    mysqlcheck --repair eximstats
    After that, I recommend modifying the following option under the "Stats and Logs" tab in "WHM Home » Server Configuration » Tweak Settings":

    "The interval, in days, to retain Exim stats in the database"

    You can reduce the number of days if you find there is too much disk space utilized by the eximstats database.

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #8 cPanelMichael, Jan 7, 2016
  9. MekGro

    MekGro Member

    Joined:
    Jan 6, 2016
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    India
    cPanel Access Level:
    Website Owner
    Is this issue normal thing that happens or is it unusual. So, according to you "eximstats" is the reason behind high usage. If in future any thing with resources go wrong i should see "/var/lib/mysql/$hostname.err" ?

    BTW i also notice some leechprotect using high amount of resources. What should i do for "leechprotect"

    Thanks + Regards
     
    #9 MekGro, Jan 7, 2016
  10. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    43,711
    Likes Received:
    1,794
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    The log does not indicate the "eximstats" database is the culprit, but rather it's filling up the logs so it's a good idea to correct the issue. The following thread will help if your goal is to determine the source of the load issue:

    Troubleshooting high server loads on Linux servers

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #10 cPanelMichael, Jan 8, 2016
  11. MekGro

    MekGro Member

    Joined:
    Jan 6, 2016
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    India
    cPanel Access Level:
    Website Owner
    Sorry for replying soo late & thanks alot for the above link and support.
     
    #11 MekGro, Jan 20, 2016
(You must log in or sign up to post here.)
Loading...
Similar Threads - Abnormal MySQL Behavior
  1. upsforum

    abnormal email sent

    upsforum, Jan 8, 2017, in forum: E-mail Discussion
    Replies:
    2
    Views:
    804
    cPanelMichael
    Jan 10, 2017
  2. RobNH

    MySQLi - Too Many Connections Error due to Amazon AWS

    RobNH, Jun 21, 2018 at 7:47 AM, in forum: Database Discussion
    Replies:
    5
    Views:
    40
    Infopro
    Jun 21, 2018 at 10:03 AM
  3. geekbaaz

    Multiple cPanel servers with a common MySQL server?

    geekbaaz, Jun 21, 2018 at 3:24 AM, in forum: Database Discussion
    Replies:
    2
    Views:
    56
    cPanelMichael
    Jun 21, 2018 at 11:32 AM
  4. cosmin

    MySQL InnoDB errors after upgrade to 5.7

    cosmin, Jun 20, 2018 at 11:46 PM, in forum: Database Discussion
    Replies:
    2
    Views:
    46
    cPanelMichael
    Jun 21, 2018 at 10:31 AM
  5. tyuuu

    mysql_pconnect can not work after change password/username

    tyuuu, Jun 19, 2018 at 9:14 AM, in forum: Database Discussion
    Replies:
    10
    Views:
    62
    cPanelLauren
    Jun 20, 2018 at 9:38 AM

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Accept Learn More...
    Dismiss Notice