Skip to main content

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 >> 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 'xxx@xxx' ignored in --skip-name-resolve mode. 140830 2:16:53 [Warning] 'user' entry 'xxx@xxx' ignored in --skip-name-resolve mode. 140830 2:16:53 [Warning] 'user' entry 'xxx@xxx' ignored in --skip-name-resolve mode.

Comments

8 comments

Date Votes
  • cPanelMichael
    Hello :) You may find the following thread helpful based on the output of your MySQL error log: InnoDB Corruption Repair Guide Thank you.
    0
  • 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...
    0
  • cPanelMichael
    Do you have innodb_force_recovery enabled in your /etc/my.cnf file? Thank you.
    0
  • 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
    0
  • 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.
    0
  • abdelhost77
    Dear Sir, please help :( Mysql crashed again and wont be started : root@ [~]# /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. root@r [~]# 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 InnoDB: File operation call: 'read'. InnoDB: Cannot continue operation. 141023 17:42:18 mysqld_safe mysqld from pid file /var/lib/mysql/.pid ended
    i try with innodb_force_recovery=4 in /etc/my.cnf But it is not help .
    0
  • 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: [url=http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html]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 ?
    0
  • 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.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post