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.

Optimise my.cnf

Discussion in 'Workarounds and Optimization' started by abdelhost77, Oct 22, 2014.

  1. abdelhost77

    abdelhost77 Well-Known Member

    Joined:
    Apr 25, 2012
    Messages:
    81
    Likes Received:
    1
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    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 :
    Code:
    
    
    
    
    
    ./mysqltuner.pl
    
    >> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
    >> 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.
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,724
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. abdelhost77

    abdelhost77 Well-Known Member

    Joined:
    Apr 25, 2012
    Messages:
    81
    Likes Received:
    1
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    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 .


    Code:
    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...
     
    #3 abdelhost77, Oct 22, 2014
    Last edited: Oct 22, 2014
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,724
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  5. abdelhost77

    abdelhost77 Well-Known Member

    Joined:
    Apr 25, 2012
    Messages:
    81
    Likes Received:
    1
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    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 :



    Code:
    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 .


    Code:
    /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
     
    #5 abdelhost77, Oct 22, 2014
    Last edited: Oct 22, 2014
  6. abdelhost77

    abdelhost77 Well-Known Member

    Joined:
    Apr 25, 2012
    Messages:
    81
    Likes Received:
    1
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    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.
     
  7. abdelhost77

    abdelhost77 Well-Known Member

    Joined:
    Apr 25, 2012
    Messages:
    81
    Likes Received:
    1
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    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



    Code:
    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 .
     
  8. abdelhost77

    abdelhost77 Well-Known Member

    Joined:
    Apr 25, 2012
    Messages:
    81
    Likes Received:
    1
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    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 ?
     
    #8 abdelhost77, Oct 23, 2014
    Last edited: Oct 23, 2014
  9. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,724
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
Loading...

Share This Page