SQL tuning help

Gauravk · Jun 1, 2013

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 <[email protected]>
 >>  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

ES - George · Jun 1, 2013

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.

Gauravk · Jun 1, 2013

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.

ES - George · Jun 1, 2013

Gauravk said:
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.

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

Gauravk · Jun 1, 2013

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.

Gauravk · Jun 1, 2013

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.......!

thinkbot · Jun 2, 2013

- 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

Gauravk · Jun 2, 2013

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

thinkbot · Jun 3, 2013

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."

ES - George · Jun 3, 2013

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.

Gauravk · Jun 3, 2013

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)

cPanelMichael · Jun 3, 2013

Gauravk said:
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.

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.

Gauravk · Jun 3, 2013

Thanks Michael, Same i did yesterday and it helped a lot.

Gauravk · Jun 8, 2013

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
# [email protected]: 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
# [email protected]: 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';
# [email protected]: 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';
# [email protected]: 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$
# [email protected]: 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';
# [email protected]: 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
# [email protected]: 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$
# [email protected]: 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
# [email protected]: XXXXXXXX_XXXX[XXXXXXXX_XXXX] @ localhost []

# Time: 130608 17:26:50
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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';
# [email protected]: 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';
# [email protected]: 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
# [email protected]: 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$

thinkbot · Jun 8, 2013

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

Gauravk · Jun 8, 2013

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

Gauravk · Jun 8, 2013

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.

Gauravk · Jun 8, 2013

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").

Gauravk · Jun 8, 2013

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.

Gauravk · Jun 8, 2013

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???

Thread starter	Similar threads	Forum	Replies	Date
F	Server Tuning Help (Apache and MySQL)	Workarounds and Optimization	5	Nov 26, 2016
S	Help with tuning mySQL	Workarounds and Optimization	4	Sep 25, 2014
D	Help with optimzation : mysqltuner, tuning-primer	Workarounds and Optimization	10	May 23, 2013
A	Mysql tuning help	Workarounds and Optimization	1	Feb 19, 2013
K	Help with MySql tuning conf.	Workarounds and Optimization	0	Jul 15, 2012

SQL tuning help

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Administrator

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member