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.

SQL tuning help

Discussion in 'Workarounds and Optimization' started by Gauravk, Jun 1, 2013.

  1. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi Guys,

    Learning great info from you guys in past i manage to run server independently. Now after year or so server is kind of slowing down sometimes, may be its spiking the max connection of 50 users or exceeding innodb buffer, i am not sure. Please advise what next tweak is needed based on below. Any help is highly appreciated. Thanks in advance.

    Tuning report:

    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  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.68-cll
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 340M (Tables: 207)
    [--] Data in InnoDB tables: 330M (Tables: 2815)
    [--] Data in MEMORY tables: 0B (Tables: 4)
    [!!] Total fragmented tables: 2851
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 33h 19m 5s (506K q [10.559 qps], 18K conn, TX: 988M, RX: 63M)
    [--] Reads / Writes: 69% / 31%
    [--] Total buffers: 540.0M global + 4.6M per thread (50 max threads)
    [OK] Maximum possible memory usage: 768.1M (37% of installed RAM)
    [OK] Slow queries: 0% (2K/506K)
    [OK] Highest usage of available connections: 30% (15/50)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/97.2M
    [OK] Key buffer hit rate: 98.6% (285K cached / 3K reads)
    [OK] Query cache efficiency: 63.7% (204K cached / 320K selects)
    [!!] Query cache prunes per day: 18532
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 9K sorts)
    [!!] Joins performed without indexes: 3110
    [!!] Temporary tables created on disk: 27% (16K on disk / 61K total)
    [OK] Thread cache hit rate: 99% (16 created / 18K connections)
    [!!] Table cache hit rate: 0% (4K open / 496K opened)
    [OK] Open file limit used: 5% (495/8K)
    [OK] Table locks acquired immediately: 99% (184K immediate / 184K locks)
    [!!] InnoDB data size / buffer pool: 330.9M/200.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 64M)
        join_buffer_size (> 2.0M, or always use indexes with joins)
        table_cache (> 4096)
        innodb_buffer_pool_size (>= 330M)
    
    


    Current my.cnf

    Code:
    [mysqld]
    
    
    plugin_dir=/usr/lib/mysql/plugin
    
    ####  InnoDB-Plugin SETTINGS NEED REMOVED BEFORE MySQL 5.5 UPGRADE!!!! ###
    
    ignore_builtin_innodb
    plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_lo$
    
    innodb_strict_mode      = 1
    ####  END InnoDB-Plugin #####
    
    
    max_connections=50
    slow_query_log
    max_allowed_packet=32M
    query_cache_size=64M
    query_cache_limit=2M
    tmp_table_size=256M
    max_heap_table_size=256M
    long-query-time = 1
    thread_cache_size=12
    table_open_cache=4096
    
    #### Per connection configuration ####
    sort_buffer_size=2M
    join_buffer_size=2M
    thread_stack=192K
    
    
    ##### INNODB Specific Options ######
    
    default_table_type=InnoDB
    innodb_buffer_pool_size=200M
    innodb_flush_log_at_trx_commit=0
    innodb_log_buffer_size=4M
    innodb_flush_method=O_DSYNC
    open_files_limit=7916
     
  2. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    The first thing I'd do is optimise your tables. I've never seen that many fragmented tables.
    To do this on your version of mysql, head to a command line and type: mysqlcheck -Aor
    That that will optimise and repair all database tables.
     
  3. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Thanks George, for the advise. Just wanted to double check that it wont create any disaster? As i am on live server and have no means to undo this command. I am no way challenging your knowledge, i just want make sure that running this command is absolutely safe. Please confirm.
     
  4. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    It won't do any damage or cause a disaster. The command in question (mysqlcheck) is a built in mysql tool. I've used it many, many times in lots of servers and I've never seen any errors in the log files or such.

    If you wish to just optimise (and not repair), you could use: mysqlcheck -Ao

    You could also use something like this instead: Optimize only fragmented tables in MySQL | Justin.my
     
  5. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Thanks George, to start with (to gain confidence) Ao instead of repair i tried and found plenty of tables showing this remark

    note : Table does not support optimize, doing recreate + analyze instead
    status : OK

    My main PHP forum is running on InnoDB that is i am more concerned of being slow at times, like dead slow showing the browser grey circle circling for 10 second before loading the forums.

    In this server couple of other sites are also there (i guess) those uses myisam, to be honest for those im not concerned at the moment. Sharing this so that i can pin point the real pain point and get some good solution.

    After this optimize how do i check if any difference happen or from which part of mysqltuner showed you the need for Aor?

    Thanks a lot for sharing your knowledge.
     
  6. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Just took a note of db on server = 40
    38 = InnoDB (phpbb and drupal) = 301 MB
    2 = My Isam (Joomla an dother) = 1 MB

    Why after Aor even now im seeing

    [--] Data in MyISAM tables: 336M (Tables: 146)
    [--] Data in InnoDB tables: 301M (Tables: 2750)


    1 MB Myisam become 336MB, is really crazy.......!
     
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    - before updating my.cnf, download pt-query-digest and generate info on slow queries like that:

    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest

    generate slow log
    ./pt-query-digest /var/lib/mysql/*-slow.log > slow.txt

    and copy here slow.txt

    - then remove old slow log
    rm -/var/lib/mysql/*-slow.log

    - update my.cnf :

    [mysqld]

    #### InnoDB-Plugin SETTINGS NEED REMOVED BEFORE MySQL 5.5 UPGRADE!!!! ###
    plugin_dir=/usr/lib/mysql/plugin

    ignore_builtin_innodb
    plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_lo$

    innodb_strict_mode = 1
    #### END InnoDB-Plugin #####


    max_connections=75

    max_allowed_packet=32M
    query_cache_size=30M
    query_cache_limit=1M
    tmp_table_size=256M
    max_heap_table_size=256M
    thread_cache_size=30

    table_open_cache = 3000
    table_definition_cache = 2000

    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes

    #### Per connection configuration ####
    sort_buffer_size=256K
    join_buffer_size=4M
    thread_stack=192K

    ##### INNODB Specific Options ######

    default_table_type=InnoDB
    innodb_buffer_pool_size=750M
    innodb_log_file_size = 50M
    innodb_flush_log_at_trx_commit=2
    innodb_log_buffer_size=8M
    innodb_flush_method=O_DIRECT
    innodb_old_blocks_time=1000
    innodb_purge_threads=1
    innodb_concurrency_tickets=5000
    innodb_open_files=2000


    first upgrade mysql to 5.5 or 5.6, or better Percona 5.5

    if you update mysql, you can remove this code
    #### InnoDB-Plugin SETTINGS NEED REMOVED BEFORE MySQL 5.5 UPGRADE!!!! ###
    plugin_dir=/usr/lib/mysql/plugin

    ignore_builtin_innodb
    plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_lo$

    innodb_strict_mode = 1
    #### END InnoDB-Plugin #####

    if you dont upgrade, leave it

    - also since innodb log file size changes, delete old log buffers before restarting mysql
    rm -rf /var/lib/mysql/ib_logfile*

    - then restart mysql

    after few hours of running, or best 24h, generate new mysqltuner and new pt-query-digest result and post here
     
  8. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi Thinkbot,

    Appreciate your help and suggestion. While waiting here for anyone to give advise i started exploring my server more and more and realize that 384MB myisam is not from my 2 db but the eximstats of cpanel has eaten that space due to recording 90 days history. Cleared all of the crap and 30 days eximstats trim down to 40 megs. Then run Aor and server is in much better and faster shape now.

    While at it, i definitely want to optimize the my.cnf as its been an year old and its good time to update it. Though now its not that critical so I like to wait for a day or two to see most max connections and then update the my.cnf.

    Could you please explain what are the new items you added for, what does it mean and do (I am noobie at MySQL):
    ---table_definition_cache = 2000
    ---innodb_flush_method=O_DIRECT
    ---innodb_old_blocks_time=1000
    ---innodb_purge_threads=1
    ---innodb_concurrency_tickets=5000
    ---innodb_open_files=2000
     
  9. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Here you can find all the variables description for your mysql version 5.1
    MySQL :: MySQL 5.1 Reference Manual :: 5.1.4 Server System Variables


    [--] Data in MyISAM tables: 340M (Tables: 207)
    [--] Data in InnoDB tables: 330M (Tables: 2815)

    InnoDB (phpbb and drupal)
    My Isam (Joomla an dother)

    [OK] Key buffer size / total MyISAM indexes: 8.0M/97.2M

    "realize that 384MB myisam is not from my 2 db but the eximstats of cpanel has eaten that space due to recording 90 days history. Cleared all of the crap and 30 days eximstats trim down to 40 megs. Then run Aor and server is in much better and faster shape now."

    You already had enough key_buffer_size for MyISAM, so removing some myisam data (and thus indexes) won't change much,
    Especially when you remove eximstats (mail app), it has no affect to your current MyISAM apps like Joomla and other

    edit: actually myisam index size is higher than your set key_buffer_size, but you would have to check utilization, not all indexes are used at once, so you got there OK sign anyways
    you can use mysqlreport or tuningprimer to see key_buffer utilization to give you 100% info
    this key_buffer_size is used to store most used indexes only, no need to store all of them

    you can update key_buffer_size = 100M to my previous my.cnf
    end edit

    And since phpbb and drupal is on InnoDB as you wrote, key_buffer_size is not even used there
    They use innodb_buffer_pool_size, which I wrote to increase, since
    [!!] InnoDB data size / buffer pool: 330.9M/200.0M


    So don't know how did you make this assuption
    "Then run Aor and server is in much better and faster shape now."
     
    #9 thinkbot, Jun 3, 2013
    Last edited: Jun 3, 2013
  10. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    I would also suggest looking at your MySQL log file located /var/lib/mysql/$hostname.err
    Replace $hostname.err with your server's hostname (found by running 'hostname')
    I..e /var/lib/mysql/vps.myserver.com.err

    You might see errors and such in that file and if so, those errors need to be fixed.
     
  11. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi Thinkbot,

    I thank and value your every advise, and apologies if i sound too blunt with my assumptions based on my very limited knowledge.

    I actually deleted couple of innodb database and trimmed down the eximstats and by these two changes i have actually seen some noticeable difference in server / mysql response time. My all 20 phpbb forums loading pretty fast like before.

    Your link was very educating and self explanatory. I will also do the slow log shortly in a while and update here.

    I am copying current tuner below, and like i said i wish to run for a day more to see what max connections is actually needed in my usage.




    HVH - George: Thanks for the /var/lib/mysql/$hostname.err heads up, luckily its rightly name in my server. I am reading it now and lets see what i actually understand after reading 1700 lines.....! lol.



    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.68-cll
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 50M (Tables: 146)
    [--] Data in InnoDB tables: 239M (Tables: 2709)
    [--] Data in MEMORY tables: 0B (Tables: 2)
    [!!] Total fragmented tables: 2725
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 17h 33m 31s (2M q [9.370 qps], 89K conn, TX: 4B, RX: 277M)
    [--] Reads / Writes: 64% / 36%
    [--] Total buffers: 540.0M global + 4.6M per thread (50 max threads)
    [OK] Maximum possible memory usage: 768.1M (37% of installed RAM)
    [OK] Slow queries: 0% (5K/2M)
    [OK] Highest usage of available connections: 62% (31/50)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/11.5M
    [OK] Key buffer hit rate: 98.5% (12M cached / 189K reads)
    [OK] Query cache efficiency: 62.0% (844K cached / 1M selects)
    [!!] Query cache prunes per day: 12242
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 45K sorts)
    [!!] Joins performed without indexes: 13888
    [OK] Temporary tables created on disk: 24% (58K on disk / 238K total)
    [OK] Thread cache hit rate: 99% (152 created / 89K connections)
    [!!] Table cache hit rate: 0% (4K open / 2M opened)
    [OK] Open file limit used: 5% (428/8K)
    [OK] Table locks acquired immediately: 99% (911K immediate / 911K locks)
    [!!] InnoDB data size / buffer pool: 239.8M/200.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 64M)
        join_buffer_size (> 2.0M, or always use indexes with joins)
        table_cache (> 4096)
        innodb_buffer_pool_size (>= 239M)
    
     
  12. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    To note, you can modify the following option in "WHM Home » Server Configuration » Tweak Settings" under the "Stats and Logs" tab:

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

    Reducing this value will decrease the amount of data that is stored in the Eximstats database.

    Thank you.
     
  13. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Thanks Michael, Same i did yesterday and it helped a lot.
     
  14. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi All Experts,

    Please find the latest SQL Tuner report below and advise next plan of action to optimize the best way.

    I have checked the error log and there were no error found, only few minor warnings were there in past.

    I am also copying the slow log query below for reference.


    My SQL Tuner

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.68-cll
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 66M (Tables: 146)
    [--] Data in InnoDB tables: 249M (Tables: 2709)
    [--] Data in MEMORY tables: 0B (Tables: 2)
    [!!] Total fragmented tables: 2730
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 7d 17h 8m 52s (6M q [9.206 qps], 261K conn, TX: 10B, RX: 741M)
    [--] Reads / Writes: 66% / 34%
    [--] Total buffers: 540.0M global + 4.6M per thread (50 max threads)
    [OK] Maximum possible memory usage: 768.1M (37% of installed RAM)
    [OK] Slow queries: 0% (12K/6M)
    [OK] Highest usage of available connections: 62% (31/50)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/15.6M
    [OK] Key buffer hit rate: 98.6% (14M cached / 201K reads)
    [OK] Query cache efficiency: 61.5% (2M cached / 3M selects)
    [!!] Query cache prunes per day: 20614
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 119K sorts)
    [!!] Joins performed without indexes: 44840
    [OK] Temporary tables created on disk: 23% (150K on disk / 646K total)
    [OK] Thread cache hit rate: 99% (299 created / 261K connections)
    [!!] Table cache hit rate: 0% (4K open / 6M opened)
    [OK] Open file limit used: 4% (368/8K)
    [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
    [!!] InnoDB data size / buffer pool: 249.1M/200.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 64M)
        join_buffer_size (> 2.0M, or always use indexes with joins)
        table_cache (> 4096)
        innodb_buffer_pool_size (>= 249M)
    



    SLOW LOG QUERY

    Code:
    # Time: 130608 17:24:54
    # User@Host: XXXXXX_XXXXXX[XXXXXX_XXXXXX] @ localhost []
    # Query_time: 3.044552  Lock_time: 0.000089 Rows_sent: 0  Rows_examined: 1
    use XXXXXX_XXXXXX;
    SET timestamp=1370697894;
    UPDATE phpbb_config
                    SET config_value = '1370697891'
                    WHERE config_name = 'rand_seed_last_update';
    # Time: 130608 17:25:21
    # User@Host: XXXXXX_XXXXXX[XXXXXX_XXXXXX] @ localhost []
    # Query_time: 2.625152  Lock_time: 0.000070 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370697921;
    UPDATE phpbb_config
                    SET config_value = '1370697918'
                    WHERE config_name = 'rand_seed_last_update';
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 1.693371  Lock_time: 0.000088 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370697921;
    UPDATE phpbb_config
                    SET config_value = '1370697919'
                    WHERE config_name = 'rand_seed_last_update';
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 5.193340  Lock_time: 0.000043 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370697921;
    UPDATE phpbb_sessions SET session_time = 1370697916, session_page = 'ucp.php?i=172', se$
                                                                    WHERE session_id = '446$
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 1.532546  Lock_time: 0.000049 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370697921;
    UPDATE phpbb_config
                    SET config_value = '1370697920'
                    WHERE config_name = 'rand_seed_last_update';
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 1.694401  Lock_time: 0.000063 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370697921;
    UPDATE phpbb_config
                    SET config_value = '1370697920'
                    WHERE config_name = 'rand_seed_last_update';
    # Time: 130608 17:25:37
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 4.598781  Lock_time: 0.000050 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370697937;
    UPDATE phpbb_sessions SET session_time = 1370697932, session_last_visit = 1370697932, s$
                                                    WHERE session_id = '04df1f1b416c3efc487$
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 2.927842  Lock_time: 0.000099 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370697937;
    UPDATE phpbb_sessions SET session_time = 1370697934, session_page = 'ucp.php?i=profile&$
                                                                    WHERE session_id = '446$
    # Time: 130608 17:26:50
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    
    # Time: 130608 17:26:50
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 2.908848  Lock_time: 0.000070 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370698010;
    UPDATE phpbb_config
                    SET config_value = '1370698007'
                    WHERE config_name = 'rand_seed_last_update';
    # Time: 130608 17:27:28
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 4.001407  Lock_time: 0.000057 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370698048;
    UPDATE phpbb_sessions SET session_time = 1370698044, session_page = 'viewtopic.php?foru$
                                                                    WHERE session_id = 'bdc$
    # Time: 130608 17:30:59
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 2.363839  Lock_time: 0.000043 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370698259;
    UPDATE phpbb_config
                    SET config_value = '1370698256'
                    WHERE config_name = 'rand_seed_last_update';
    # Time: 130608 17:31:49
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 1.138845  Lock_time: 0.000095 Rows_sent: 0  Rows_examined: 1
    SET timestamp=1370698309;
    UPDATE phpbb_sessions SET session_time = 1370698308, session_page = 'ucp.php?mode=regis$
                                                                    WHERE session_id = '951$
    # Time: 130608 17:39:57
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 3.081691  Lock_time: 0.000063 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370698797;
    UPDATE phpbb_config
                    SET config_value = '1370698794'
                    WHERE config_name = 'rand_seed_last_update';
    # Time: 130608 18:06:38
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 1.016553  Lock_time: 0.000054 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370700398;
    UPDATE phpbb_config
                    SET config_value = '1370700397'
                    WHERE config_name = 'rand_seed_last_update';
    # Time: 130608 18:13:02
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 1.127437  Lock_time: 0.000041 Rows_sent: 0  Rows_examined: 1
    SET timestamp=1370700782;
    UPDATE phpbb_config
                    SET config_value = '1370700781'
                    WHERE config_name = 'rand_seed_last_update';
    # Time: 130608 18:19:44
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 5.075410  Lock_time: 0.000047 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370701184;
    UPDATE phpbb_config
    
    SET timestamp=1370701184;
    UPDATE phpbb_config
                    SET config_value = '1370701179'
                    WHERE config_name = 'rand_seed_last_update';
    # Time: 130608 19:43:24
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 1.482132  Lock_time: 0.000106 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370706204;
    UPDATE phpbb_config
                    SET config_value = '1370706203'
                    WHERE config_name = 'rand_seed_last_update';
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 1.486032  Lock_time: 0.000088 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370706204;
    UPDATE phpbb_config
            SET config_value = '1370706203 0ab6ea17e4267069'
            WHERE config_name = 'cron_lock' AND config_value = '0';
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 1.474115  Lock_time: 0.000078 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370706204;
    UPDATE phpbb_config
            SET config_value = '1370706203 247226aaf38462f0'
            WHERE config_name = 'cron_lock' AND config_value = '0';
    # Time: 130608 19:43:42
    # User@Host: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []
    # Query_time: 2.312193  Lock_time: 0.000053 Rows_sent: 0  Rows_examined: 1
    use XXXXXXXX_XXXX;
    SET timestamp=1370706222;
    UPDATE phpbb_sessions SET session_time = 1370706220, session_page = 'viewtopic.php?foru$
                                                                    WHERE session_id = '359$
    
    
    
     
  15. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Update the my.cnf with the one I wrote earlier (especially innodb buffer, since "[!!] InnoDB data size / buffer pool: 249.1M/200.0M
    ") + key_buffer_size = 100M


    and generate slow query log like this

    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest

    ./pt-query-digest /var/lib/mysql/mysq-slow.log > slow.txt
    and copy slow.txt here to check
     
  16. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Thanks Thinkbot,

    For quick response, please advise where should i add key_buffer_size = 100M?

    Please find my updated my.cnf below. Thanks.

    Code:
    
    [mysqld]
    
    
    plugin_dir=/usr/lib/mysql/plugin
    
    ####  InnoDB-Plugin SETTINGS NEED REMOVED BEFORE MySQL 5.5 UPGRADE!!!! ###
    
    ignore_builtin_innodb
    plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plug$
    
    innodb_strict_mode      = 1
    ####  END InnoDB-Plugin #####
    
    
    max_connections=50
    slow_query_log
    max_allowed_packet=32M
    query_cache_size=64M
    query_cache_limit=2M
    tmp_table_size=256M
    max_heap_table_size=256M
    long-query-time = 1
    thread_cache_size=12
    table_open_cache=4096
    
    #### Per connection configuration ####
    sort_buffer_size=2M
    join_buffer_size=2M
    thread_stack=192K
    
    
    ##### INNODB Specific Options ######
    
    default_table_type=InnoDB
    innodb_buffer_pool_size=300M
    innodb_flush_log_at_trx_commit=0
    innodb_log_buffer_size=4M
    innodb_flush_method=O_DSYNC
    open_files_limit=7916
     
  17. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    I have tried copying your earlier my.cnf and sql is not restarting.

    Remove yours and copied mine and still its not working.

    Can i restore those log files, if coz of that its creating issues??? Please help.
     
  18. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Please help guys, i am getting below message. I am not sure why. Should i upgrade mysql from 5.1 to 5.5 expecting that if something went wrong in log files or my.cnf would be fixed?????

    Any way in cPanel or WHM or SSH to rollback MySQL to 2 hours back state?


    Code:
    Restarting MySQL Server
    
    Waiting for mysql to restart....................................................................finished.
    
    
    mysql has failed, please contact the sysadmin (result was "mysql is not running").
     
  19. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    While restarting thru SSH getting below error:

    /etc/init.d/mysql restart
    ERROR! MySQL manager or server PID file could not be found!
    Starting MySQL. ERROR! Manager of pid-file quit without updating file.
     
  20. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Now trying to upgrade the MySQL to 5.5 (as available). During the installation seen below messages

    [20130608.231723] Starting MySQL... ERROR! The server quit without updating PID file (/var/lib/mysql/servername.pid).
    [20130608.231723] error: %post(MySQL55-server-5.5.30-4.cp1136.i386) scriptlet failed, exit status 1
    [20130608.231723] MySQL55-shared-5.5.30-4.cp1136
    [20130608.231724] MySQL55-devel-5.5.30-4.cp1136
    [20130608.231724] W Error Code: 5
    [20130608.231724] E The following possible errors were detected while installing RPMs:
    [20130608.231724] E 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)' %post(MySQL55-server-5.5.30-4.cp1136.i386) scriptlet failed, exit status 1
    [20130608.231724] The Administrator will be notified to review this output when this script completes
    [20130608.231724] Checking for and running RPM::Versions 'post' hooks for any RPMs just installed


    And in end installation failed.

    How can i re create the .pid file???
     
Loading...

Share This Page