MySQL Optimize Help

saamxvr · Jan 18, 2014

Hello ,

i need help to oprimiz my MySQL

this is my server info :

Intel(R) Xeon(R) CPU E3-1240 V2 @ 3.40GHz
16GB RAM
CentOS 6.4 - 64-Bit

this is my currant my.cnf

[mysqld]
innodb_file_per_table=1

this is my tuner report

Code:

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]major(7) - a social nerd - Linux man page[/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.5.34-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 588M (Tables: 91)
[--] Data in InnoDB tables: 1M (Tables: 89)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 2

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15m 15s (19K q [21.802 qps], 319 conn, TX: 979M, RX: 4M)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 583.2M (3% of installed RAM)
[OK] Slow queries: 0% (0/19K)
[OK] Highest usage of available connections: 2% (4/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/102.3M
[OK] Key buffer hit rate: 99.5% (1M cached / 8K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 4% (136 temp sorts / 2K sorts)
[!!] Temporary tables created on disk: 30% (425 on disk / 1K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 96% (226 open / 233 opened)
[OK] Open file limit used: 22% (235/1K)
[OK] Table locks acquired immediately: 99% (21K immediate / 21K locks)
[OK] InnoDB data size / buffer pool: 1.8M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)

Waiting for the suggest :

thinkbot · Jan 18, 2014

add

key_buffer_size = 150M
query_cache_limit = 1M
query_cache_size = 30M
query_cache_type = 1
table_open_cache = 1000
thread_cache_size = 16
tmp_table_size = 50M
max_heap_table_size = 50M

slow_query_log_file=mysql-slow.log
long_query_time=0.1

and later restart

after several hours, or best 24h, run mysqltuner.pl again and post your result

saamxvr · Jan 20, 2014

Thanks you .. and this is result

Code:

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]major(7) - a social nerd - Linux man page[/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.5.34-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 351)
[--] Data in InnoDB tables: 2M (Tables: 86)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 60

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 3h 27m 33s (9M q [95.266 qps], 97K conn, TX: 129B, RX: 3B)
[--] Reads / Writes: 89% / 11%
[--] Total buffers: 374.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 789.2M (4% of installed RAM)
[OK] Slow queries: 0% (4K/9M)
[OK] Highest usage of available connections: 78% (119/151)
[OK] Key buffer size / total MyISAM indexes: 150.0M/387.7M
[OK] Key buffer hit rate: 100.0% (1B cached / 478K reads)
[OK] Query cache efficiency: 21.3% (1M cached / 8M selects)
[!!] Query cache prunes per day: 1052942
[OK] Sorts requiring temporary tables: 2% (17K temp sorts / 870K sorts)
[!!] Temporary tables created on disk: 39% (297K on disk / 751K total)
[OK] Thread cache hit rate: 99% (137 created / 97K connections)
[OK] Table cache hit rate: 48% (667 open / 1K opened)
[OK] Open file limit used: 43% (935/2K)
[OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)
[OK] InnoDB data size / buffer pool: 2.5M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_size (> 30M)
    tmp_table_size (> 50M)
    max_heap_table_size (> 50M)

thinkbot · Jan 20, 2014

Much better, but since

[!!] Temporary tables created on disk: 39% (297K on disk / 751K total)

you should optimize your queries (especially the ones using temporary tables on disk)
query can can be also increased
query_cache_size = 70M

generate review of current slow queries
mkdir -p /root/install
cd /root/install
wget http://percona.com/get/pt-query-digest
chmod +x pt-query-digest
./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt
and upload slow.txt here in code brackets

cPanelMichael · Jan 22, 2014

saamxvr said:
Thanks you .. and this is result

I am happy to see the optimization recommendations you received on this thread have been helpful so far. Feel free to let us know the outcome after the most recent suggestions.

Thank you.

saamxvr · Jan 22, 2014

Hello ,

Thanks you Michael , thinkbot , i have fallow last suggest , and its take me forever to load slow.txt .. its not come out put ... any idea ?

Thank you

thinkbot · Jan 22, 2014

the generation of slow.txt can take a while, but during that you will receive percentage info of how much is finished
just slow.txt file is not big, so it should not be a problem uploading it here

saamxvr · Jan 22, 2014

This is my screen still like this last 1hours ... see the attachment .

thinkbot · Jan 22, 2014

Can you run du -sh /var/lib/mysql/mysql-slow.log

saamxvr · Jan 22, 2014

I getting this error :

[email protected] [~]# du -sh /var/lib/mysql/mysql-slow.log
du: cannot access `/var/lib/mysql/mysql-slow.log': No such file or directory
[email protected] [~]#

saamxvr · Feb 2, 2014

Hello i didnt get any reply latly - however i get new result

Code:

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]major(7) - a social nerd - Linux man page[/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.5.34-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3G (Tables: 694)
[--] Data in InnoDB tables: 4M (Tables: 103)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 115

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15m 38s (332K q [354.747 qps], 3K conn, TX: 1B, RX: 103M)
[--] Reads / Writes: 86% / 14%
[--] Total buffers: 414.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 829.2M (5% of installed RAM)
[OK] Slow queries: 0% (421/332K)
[OK] Highest usage of available connections: 8% (13/151)
[OK] Key buffer size / total MyISAM indexes: 150.0M/1.0G
[OK] Key buffer hit rate: 99.9% (155M cached / 128K reads)
[!!] Query cache efficiency: 18.6% (52K cached / 281K selects)
[!!] Query cache prunes per day: 862249
[OK] Sorts requiring temporary tables: 0% (164 temp sorts / 21K sorts)
[!!] Temporary tables created on disk: 36% (4K on disk / 13K total)
[OK] Thread cache hit rate: 99% (13 created / 3K connections)
[OK] Table cache hit rate: 98% (402 open / 409 opened)
[OK] Open file limit used: 14% (699/4K)
[OK] Table locks acquired immediately: 99% (284K immediate / 284K locks)
[OK] InnoDB data size / buffer pool: 4.5M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_limit (> 1M, or use smaller result sets)
    query_cache_size (> 70M)
    tmp_table_size (> 50M)
    max_heap_table_size (> 50M)

Any suggest ?

thinkbot · Feb 3, 2014

Its very good, but please generate slow log again

./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt
before your slow log didnt exist

saamxvr · Feb 5, 2014

im still getting

[email protected] [~]# ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt
-bash: ./pt-query-digest: No such file or directory

any idea ?

thinkbot · Feb 5, 2014

slow_query_log_file=mysql-slow.log
long_query_time=0.1

ohh forgot this one
slow_query_log=1

please add that and restart

saamxvr · Feb 9, 2014

OK when im type this command i get this result

[email protected] [~]# du -sh /var/lib/mysql/mysql-slow.log
21M /var/lib/mysql/mysql-slow.log

but when im run this command

[email protected] [~]# ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt
-bash: ./pt-query-digest: No such file or directory

any idea ?

thinkbot · Feb 10, 2014

you previosuly installed pt-query-digest in /root/install

cou first
cd /root/install
then
./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt

saamxvr · Feb 10, 2014

its not come out put , its just like below screen

http://i.imgur.com/fYtcca7.jpg

any idea ?

Thank you

thinkbot · Feb 10, 2014

yep, now open /root/install/slow.txt and copy content here

saamxvr · Feb 10, 2014

Humm ok but its big list .. how i copy all from ssh ?

thinkbot · Feb 10, 2014

use sftp or scp to copy the file to PC,
filezilla supports it

Thread starter	Similar threads	Forum	Replies	Date
S	How to optimize mysql - help	Workarounds and Optimization	2	Mar 11, 2020
U	Help - optimize mysqltunner to reduce high cpu usage	Workarounds and Optimization	5	Nov 14, 2016
F	Optimize MySQL Help	Workarounds and Optimization	2	Jan 14, 2015
	MySQL 5.6 Optimize help	Workarounds and Optimization	4	Jul 31, 2014
T	Please help me to optimize mysql (my.cnf)	Workarounds and Optimization	2	Jul 23, 2014

MySQL Optimize Help

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Administrator

Well-Known Member

Well-Known Member

Well-Known Member

Attachments

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

Well-Known Member