Optimise my.cnf

[abdelhost77]

Hello Sir ,

the SQL worked like a charm during months but recently i found that Mysql start crashing several time each day , you will find below : mysqltuner.pl result , etc/my.cnf ; and Logs showing crash from /var/lib/mysql/*err
Please advice :

./mysqltuner.pl

>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at MySQLTuner-perl by major
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.73-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
[!!] InnoDB is enabled but isn't being used
Argument "" isn't numeric in numeric gt (>) at ./mysqltuner.pl line 564 (#1)
(W numeric) The indicated string was fed as an argument to an operator
that expected a numeric value instead. If you're fortunate the message
will identify which operator was so unfortunate.

[OK] Total fragmented tables:

-------- Security Recommendations -------------------------------------------
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
[OK] All database users have passwords assigned
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
Argument "" isn't numeric in numeric eq (==) at ./mysqltuner.pl line 623 (#1)

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8m 47s (39K q [74.679 qps], 831 conn, TX: 135M, RX: 6M)
[--] Reads / Writes: 87% / 13%
[--] Total buffers: 1.6G global + 4.4M per thread (100 max threads)
[OK] Maximum possible memory usage: 2.0G (26% of installed RAM)
[OK] Slow queries: 0% (0/39K)
[OK] Highest usage of available connections: 10% (10/100)
[!!] Cannot calculate MyISAM index size - re-run script as root user
[OK] Query cache efficiency: 56.3% (18K cached / 33K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5K sorts)
[!!] Joins performed without indexes: 595
[!!] Temporary tables created on disk: 29% (1K on disk / 3K total)
[OK] Thread cache hit rate: 98% (10 created / 831 connections)
[OK] Table cache hit rate: 99% (706 open / 713 opened)
[OK] Open file limit used: 1% (955/50K)
[OK] Table locks acquired immediately: 99% (21K immediate / 21K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
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 without LIMIT clauses
Variables to adjust:
join_buffer_size (> 1.0M, or always use indexes with joins)
tmp_table_size (> 25M)
max_heap_table_size (> 25M)










##################################
############ /etc/my.cnf #############
##################################






[mysqld]
skip-name-resolve
myisam_use_mmap=1

max_connections=100
max_user_connections = 50

log-slow-queries=mysql-slow.log
long_query_time = 1
safe-show-database

query_cache_type = 1
query_cache_size = 75M
query_cache_limit = 2M

join_buffer_size=1M
read_rnd_buffer_size=1M
max_allowed_packet=268435456

tmp_table_size=25M
max_heap_table_size=25M

table_open_cache = 7500
thread_cache_size = 25

open_files_limit=50000

key_buffer_size = 500M
myisam_sort_buffer_size = 256M

innodb_file_per_table=1
innodb_buffer_pool_size = 1G
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M









##############################
########## LOGS#################
##############################






140830 2:02:23 [Warning] 'db' entry 'xxx\_ecole ' ignored in --skip-name-resolve mode.
140830 2:02:23 [Warning] 'db' entry 'xxx\_wp469 ' ignored in --skip-name-resolve mode.
140830 2:02:23 [Warning] 'db' entry 'xxx\_wp622 ' ignored in --skip-name-resolve mode.
InnoDB: Error: tried to read 16384 bytes at offset 0 32768.
InnoDB: Was only able to read 8192.
140830 2:14:41 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: File operation call: 'read'.
InnoDB: Cannot continue operation.
140830 02:14:43 mysqld_safe Number of processes running now: 0
140830 02:14:43 mysqld_safe mysqld restarted
140830 2:14:44 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
140830 2:14:44 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead.
140830 2:14:44 [Note] Plugin 'FEDERATED' is disabled.
140830 2:14:44 InnoDB: Initializing buffer pool, size = 1.0G
140830 2:14:44 InnoDB: Completed initialization of buffer pool
InnoDB: Log scan progressed past the checkpoint lsn 7 1329764116
140830 2:14:44 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 7 1329764445
140830 2:16:52 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
140830 2:16:53 InnoDB: Started; log sequence number 7 1329764445
140830 2:16:53 [Warning] 'user' entry '[email protected]' ignored in --skip-name-resolve mode.
140830 2:16:53 [Warning] 'user' entry '[email protected]' ignored in --skip-name-resolve mode.
140830 2:16:53 [Warning] 'user' entry '[email protected]' ignored in --skip-name-resolve mode.

 

[cPanelMichael]

Hello

You may find the following thread helpful based on the output of your MySQL error log:

InnoDB Corruption Repair Guide

Thank you.

 

[abdelhost77]

Hello Sir ,

i identify the corrupted table by running mysqlcheck , i delete the database containing this table from cpanel => delete database , but after that Mysql wont be started , please it is very urgent as it is production server with many sites , your help is much appreciated .

141022 17:51:02 [Note] Event Scheduler: Loaded 1 event
141022 17:51:02 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.73-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPLv2)
InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.
InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.
InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.
InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.
141022 17:51:07 [Note] /usr/sbin/mysqld: Normal shutdown

141022 17:51:07 [Note] Event Scheduler: Purging the queue. 1 events
141022 17:51:09  InnoDB: Starting shutdown...

 

[cPanelMichael]

Do you have innodb_force_recovery enabled in your /etc/my.cnf file?

Thank you.

 

[abdelhost77]

yes it is innodb_force_recovery=4

then i remove the innodb_force_recovery=4 and actually the logs shows that INNODB try to recover the table that has been deleted but fails and keep retrying wich is increasing highly the CPU , the logs shows :

141022 18:53:00 [Note] Event Scheduler: Loaded 1 event
141022 18:53:00 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.73-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPLv2)
InnoDB: Dropping table with id 0 11395 in recovery if it exists
InnoDB: Table found: dropping table "badDb"."badTable" in recovery
InnoDB: Error: tried to read 16384 bytes at offset 0 32768.
InnoDB: Was only able to read 8192.

knowin also that the table still exist in /var/lib/mysql even if deleted from Cpanel user account .

/var/lib/mysql/badDb]# ls -lart ps_product_attachment.*
-rw-rw---- 1 mysql mysql 98304 May  1  2013 badTable.ibd
-rw-rw---- 1 mysql mysql  8620 Aug 13 23:07 badTable.frm

 

[abdelhost77]

i remove badTable.ibd and badTable.frm then restart mysql and then INNODB seems been able to remove data related to this table from internal data dictionnary and start succesfully , the CPU go back to normal , hope it is fixed now.

 

[abdelhost77]

Dear Sir , please help

Mysql crashed again and wont be started :

[email protected] [~]# /etc/init.d/mysql status
MySQL is not running, but lock exists [FAILED]

/etc/init.d/mysql start
Starting MySQL..........Manager of pid-file quit without up[FAILED]ile.
[email protected] [~]#


Logs : /var/lib/mysql/*err

141023 17:42:05 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
141023 17:42:05 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
141023 17:42:05 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead.
141023 17:42:05 [Note] Plugin 'FEDERATED' is disabled.
141023 17:42:05  InnoDB: Initializing buffer pool, size = 1.0G
141023 17:42:05  InnoDB: Completed initialization of buffer pool
InnoDB: Log scan progressed past the checkpoint lsn 8 767061244
141023 17:42:05  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Error: tried to read 16384 bytes at offset 0 0.
InnoDB: Was only able to read -1.
141023 17:42:18  InnoDB: Operating system error number 5 in a file operation.
InnoDB: Error number 5 means 'Input/output error'.
InnoDB: Some operating system error numbers are described at
InnoDB: [url=http://dev.mysql.com/doc/refman/5.1/en/operating-system-error-codes.html]MySQL :: MySQL 5.1 Reference Manual :: 14.6.12.6 Operating System Error Codes[/url]
InnoDB: File operation call: 'read'.
InnoDB: Cannot continue operation.
141023 17:42:18 mysqld_safe mysqld from pid file /var/lib/mysql/<hostname>.pid ended

i try with innodb_force_recovery=4 in /etc/my.cnf

But it is not help .

 

[abdelhost77]

I try with innodb_force_recovery=5 in /etc/my.cnf
then execute as root :
mysqlcheck --all-databases -r

than remove innodb_force_recovery=5 from /etc/my.cnf
then /etc/init.d/mysql start

and mysql Restarted


in logs /var/lib/mysql/*err i found :




141023 18:05:05 InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './badDB/badTable.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: MySQL :: MySQL 5.1 Reference Manual :: 14.6.12.3 Troubleshooting InnoDB Data Dictionary Operations
InnoDB: for how to resolve the issue.


The logs are true , yesterday i removed manually /badDB/badTable.ibd .


So let's now describe the scenario from begining to help if someone encounter the same problem .

1 - For some reason one cpanel table is corrumpted ( let say : badDB/badTable)
2 - the cpanel user remove the table from the cpanel account ( so the table is not deleted as root user so not well removed)
3 - Mysql start crashing
4 - After investigation you found that badDB/badTable.ibd still in /var/lib/mysql , and logs says that Mysql try to recover the data from this table but fails + high CPU
5 - You remove manually badDB/badTable.ibd from /var/lib/mysql , then mysql restart sucessfully indicating that data of badDB/badTable are removed from INNODB data dictionnary .
6 - if you try to restart Mysql , logs shows there still a corruption and still crash an still refering to /badDB/badTable.ibd in logs
7 - mysqlcheck --all-databases -r is executed
8- Mysql restart


So the question now how to tell to INNODB MYSQL that i do not need anymore badDB/badTable and clean the cache and anything in INNODB engine refering to this table to avoid any possible furtur corruption/crash ?

 

[cPanelMichael]

It's likely a good idea to consult with a qualified system administrator that specializes in MySQL repair. Removing data is not always recommended for everyone as some may prefer to avoid any data loss.

Thank you.