Every day at 18:00 mysqlserver became very slow
How do I apply the suggestions by mysqltuner for optimize mysqlserver
CentOS release 6.6 (Final)
MySQL version 5.6.28
64-bit architecture
32GB RAM
[mysqltuner]
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.28
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 47M (Tables: 175)
[--] Data in InnoDB tables: 96K (Tables: 2)
[!!] Total fragmented tables: 2
-------- Security Recommendations -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 605 basic passwords in the list.
-------- CVE Security Recommendations ---------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -------------------------------------------------
[--] Up for: 38m 25s (410K q [178.166 qps], 25K conn, TX: 3B, RX: 37M)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is disabled
[--] Total buffers: 572.0M global + 16.2M per thread (300 max threads)
[OK] Maximum reached memory usage: 4.6G (14.83% of installed RAM)
[OK] Maximum possible memory usage: 5.3G (17.08% of installed RAM)
[OK] Slow queries: 0% (0/410K)
[OK] Highest usage of available connections: 85% (256/300)
[OK] Aborted connections: 0.01% (2/25571)
[OK] Query cache efficiency: 90.6% (252K cached / 278K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 199 sorts)
[!!] Temporary tables created on disk: 97% (2K on disk / 2K total)
[OK] Thread cache hit rate: 88% (3K created / 25K connections)
[OK] Table cache hit rate: 92% (89 open / 96 opened)
[OK] Open file limit used: 1% (70/5K)
[OK] Table locks acquired immediately: 98% (3K immediate / 3K locks)
-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 19.0% (19M used / 104M cache)
[OK] Key buffer size / total MyISAM indexes: 100.0M/7.8M
[OK] Read Key buffer hit rate: 98.7% (29K cached / 370 reads)
[!!] Write Key buffer hit rate: 0.0% (60 cached / 60 writes)
-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 256.0M/96.0K
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 1.18% (194 used/ 16383 total)
[!!] InnoDB Read buffer efficiency: 85.69% (1168 hits/ 1363 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)
-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.
-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.
-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
tmp_table_size (> 100M)
max_heap_table_size (> 100M)
[my.cnf]
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-name-resolve
user=mysql
symbolic-links=0
character-set-server=utf8
key_buffer_size=100M
read_buffer_size=4M
read_rnd_buffer_size=4M
join_buffer_size=4M
sort_buffer_size=4M
innodb_buffer_pool_instances=1
innodb_buffer_pool_size=256M
query_cache_type=1
query_cache_limit=100M
query_cache_size=100M
interactive_timeout=180
wait_timeout=180
max_connections=300
tmp_table_size=100M
max_heap_table_size=100M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql processlist]
mysql> show full processlist;
+-------+--------+-----------+--------------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+--------+-----------+--------------+---------+------+-------+-----------------------+
| 24129 | dbuser | localhost | datadb_store | Sleep | 10 | | NULL |
| 24131 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24134 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24139 | dbuser | localhost | datadb_store | Sleep | 8 | | NULL |
| 24141 | dbuser | localhost | datadb_store | Sleep | 7 | | NULL |
| 24146 | dbuser | localhost | datadb_store | Sleep | 7 | | NULL |
| 24149 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24150 | dbuser | localhost | datadb_store | Sleep | 3 | | NULL |
| 24151 | dbuser | localhost | datadb_store | Sleep | 4 | | NULL |
| 24152 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24153 | dbuser | localhost | datadb_store | Sleep | 3 | | NULL |
| 24154 | dbuser | localhost | datadb_store | Sleep | 8 | | NULL |
| 24155 | dbuser | localhost | datadb_store | Sleep | 3 | | NULL |
| 24157 | dbuser | localhost | datadb_store | Sleep | 3 | | NULL |
| 24158 | dbuser | localhost | datadb_store | Sleep | 9 | | NULL |
| 24159 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24160 | dbuser | localhost | datadb_store | Sleep | 9 | | NULL |
| 24161 | dbuser | localhost | datadb_store | Sleep | 3 | | NULL |
| 24162 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24163 | dbuser | localhost | datadb_store | Sleep | 3 | | NULL |
| 24164 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24165 | dbuser | localhost | datadb_store | Sleep | 7 | | NULL |
| 24166 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24167 | dbuser | localhost | datadb_store | Sleep | 3 | | NULL |
| 24168 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24169 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24170 | dbuser | localhost | datadb_store | Sleep | 9 | | NULL |
| 24171 | dbuser | localhost | datadb_store | Sleep | 3 | | NULL |
| 24172 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24174 | dbuser | localhost | datadb_store | Sleep | 3 | | NULL |
| 24175 | dbuser | localhost | datadb_store | Sleep | 8 | | NULL |
| 24176 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24177 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24178 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24179 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24180 | dbuser | localhost | datadb_store | Sleep | 5 | | NULL |
| 24181 | dbuser | localhost | datadb_store | Sleep | 8 | | NULL |
| 24182 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24183 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24184 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24185 | dbuser | localhost | datadb_store | Sleep | 7 | | NULL |
| 24186 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24187 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24188 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24189 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24190 | dbuser | localhost | datadb_store | Sleep | 7 | | NULL |
| 24191 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24192 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24193 | dbuser | localhost | datadb_store | Sleep | 5 | | NULL |
| 24194 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24195 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24196 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24197 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24198 | dbuser | localhost | datadb_store | Sleep | 7 | | NULL |
| 24199 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24200 | dbuser | localhost | datadb_store | Sleep | 5 | | NULL |
| 24201 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24202 | dbuser | localhost | datadb_store | Sleep | 3 | | NULL |
| 24203 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24204 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24205 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24206 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24207 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24208 | dbuser | localhost | datadb_store | Sleep | 33 | | NULL |
| 24209 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24210 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24211 | dbuser | localhost | datadb_store | Sleep | 33 | | NULL |
| 24212 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24213 | dbuser | localhost | datadb_store | Sleep | 34 | | NULL |
| 24214 | dbuser | localhost | datadb_store | Sleep | 34 | | NULL |
| 24215 | dbuser | localhost | datadb_store | Sleep | 31 | | NULL |
| 24216 | dbuser | localhost | datadb_store | Sleep | 31 | | NULL |
| 24217 | dbuser | localhost | datadb_store | Sleep | 34 | | NULL |
| 24218 | dbuser | localhost | datadb_store | Sleep | 31 | | NULL |
| 24219 | dbuser | localhost | datadb_store | Sleep | 30 | | NULL |
| 24220 | dbuser | localhost | datadb_store | Sleep | 30 | | NULL |
| 24221 | dbuser | localhost | datadb_store | Sleep | 32 | | NULL |
| 24222 | dbuser | localhost | datadb_store | Sleep | 24 | | NULL |
| 24223 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24224 | dbuser | localhost | datadb_store | Sleep | 22 | | NULL |
| 24225 | dbuser | localhost | datadb_store | Sleep | 31 | | NULL |
| 24226 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24227 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24228 | dbuser | localhost | datadb_store | Sleep | 28 | | NULL |
| 24229 | dbuser | localhost | datadb_store | Sleep | 25 | | NULL |
| 24230 | dbuser | localhost | datadb_store | Sleep | 25 | | NULL |
| 24231 | dbuser | localhost | datadb_store | Sleep | 25 | | NULL |
| 24232 | dbuser | localhost | datadb_store | Sleep | 26 | | NULL |
| 24233 | dbuser | localhost | datadb_store | Sleep | 22 | | NULL |
| 24234 | dbuser | localhost | datadb_store | Sleep | 10 | | NULL |
| 24235 | dbuser | localhost | datadb_store | Sleep | 22 | | NULL |
| 24236 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24237 | dbuser | localhost | datadb_store | Sleep | 24 | | NULL |
| 24238 | dbuser | localhost | datadb_store | Sleep | 26 | | NULL |
| 24239 | dbuser | localhost | datadb_store | Sleep | 21 | | NULL |
| 24240 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24241 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24242 | dbuser | localhost | datadb_store | Sleep | 22 | | NULL |
| 24243 | dbuser | localhost | datadb_store | Sleep | 22 | | NULL |
| 24244 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24246 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24247 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24248 | dbuser | localhost | datadb_blog | Sleep | 0 | | NULL |
| 24249 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24250 | dbuser | localhost | datadb_store | Sleep | 24 | | NULL |
| 24251 | dbuser | localhost | datadb_store | Sleep | 21 | | NULL |
| 24252 | dbuser | localhost | datadb_store | Sleep | 21 | | NULL |
| 24253 | dbuser | localhost | datadb_store | Sleep | 24 | | NULL |
| 24254 | dbuser | localhost | datadb_store | Sleep | 22 | | NULL |
| 24255 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24256 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24257 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24258 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24259 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24260 | dbuser | localhost | datadb_store | Sleep | 22 | | NULL |
| 24261 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24262 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24263 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24264 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24265 | dbuser | localhost | datadb_store | Sleep | 23 | | NULL |
| 24266 | dbuser | localhost | datadb_store | Sleep | 26 | | NULL |
| 24267 | dbuser | localhost | datadb_store | Sleep | 13 | | NULL |
| 24268 | dbuser | localhost | datadb_store | Sleep | 22 | | NULL |
| 24269 | dbuser | localhost | datadb_store | Sleep | 21 | | NULL |
| 24270 | dbuser | localhost | datadb_store | Sleep | 12 | | NULL |
| 24271 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24272 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24273 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24274 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24275 | dbuser | localhost | datadb_store | Sleep | 12 | | NULL |
| 24276 | dbuser | localhost | datadb_store | Sleep | 12 | | NULL |
| 24277 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24278 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24279 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24280 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24281 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24282 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24283 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24284 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24285 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24286 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24287 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24288 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24289 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24290 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24291 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24292 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24293 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24294 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24295 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24296 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24297 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24298 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24299 | dbuser | localhost | datadb_store | Sleep | 11 | | NULL |
| 24300 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24301 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24302 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24303 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24304 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24305 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24306 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24307 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24308 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24309 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24310 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24311 | dbuser | localhost | datadb_store | Sleep | 9 | | NULL |
| 24312 | dbuser | localhost | datadb_store | Sleep | 8 | | NULL |
| 24313 | dbuser | localhost | datadb_store | Sleep | 8 | | NULL |
| 24314 | dbuser | localhost | datadb_store | Sleep | 7 | | NULL |
| 24315 | dbuser | localhost | datadb_store | Sleep | 7 | | NULL |
| 24316 | dbuser | localhost | datadb_store | Sleep | 7 | | NULL |
| 24317 | dbuser | localhost | datadb_store | Sleep | 7 | | NULL |
| 24318 | dbuser | localhost | datadb_store | Sleep | 7 | | NULL |
| 24319 | dbuser | localhost | datadb_store | Sleep | 7 | | NULL |
| 24320 | dbuser | localhost | datadb_store | Sleep | 7 | | NULL |
| 24321 | dbuser | localhost | datadb_store | Sleep | 7 | | NULL |
| 24322 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24323 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24324 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24325 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24326 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24327 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24328 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24329 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24330 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24331 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24332 | dbuser | localhost | datadb_store | Sleep | 5 | | NULL |
| 24333 | dbuser | localhost | datadb_store | Sleep | 5 | | NULL |
| 24334 | dbuser | localhost | datadb_store | Sleep | 4 | | NULL |
| 24335 | dbuser | localhost | datadb_store | Sleep | 4 | | NULL |
| 24336 | dbuser | localhost | datadb_store | Sleep | 4 | | NULL |
| 24337 | dbuser | localhost | datadb_store | Sleep | 4 | | NULL |
| 24338 | dbuser | localhost | datadb_store | Sleep | 4 | | NULL |
| 24339 | dbuser | localhost | datadb_store | Sleep | 4 | | NULL |
| 24340 | dbuser | localhost | datadb_store | Sleep | 4 | | NULL |
| 24341 | dbuser | localhost | datadb_store | Sleep | 4 | | NULL |
| 24342 | dbuser | localhost | datadb_store | Sleep | 4 | | NULL |
| 24343 | dbuser | localhost | datadb_store | Sleep | 4 | | NULL |
| 24344 | dbuser | localhost | datadb_store | Sleep | 3 | | NULL |
| 24345 | dbuser | localhost | datadb_store | Sleep | 3 | | NULL |
| 24346 | dbuser | localhost | datadb_store | Sleep | 21 | | NULL |
| 24347 | dbuser | localhost | datadb_store | Sleep | 21 | | NULL |
| 24348 | dbuser | localhost | datadb_store | Sleep | 21 | | NULL |
| 24349 | dbuser | localhost | datadb_store | Sleep | 21 | | NULL |
| 24350 | dbuser | localhost | datadb_store | Sleep | 19 | | NULL |
| 24351 | dbuser | localhost | datadb_store | Sleep | 18 | | NULL |
| 24352 | dbuser | localhost | datadb_store | Sleep | 16 | | NULL |
| 24353 | dbuser | localhost | datadb_store | Sleep | 16 | | NULL |
| 24354 | dbuser | localhost | datadb_store | Sleep | 16 | | NULL |
| 24355 | dbuser | localhost | datadb_store | Sleep | 16 | | NULL |
| 24356 | dbuser | localhost | datadb_store | Sleep | 16 | | NULL |
| 24357 | dbuser | localhost | datadb_store | Sleep | 16 | | NULL |
| 24358 | dbuser | localhost | datadb_store | Sleep | 16 | | NULL |
| 24359 | dbuser | localhost | datadb_store | Sleep | 15 | | NULL |
| 24360 | root | localhost | NULL | Query | -1 | init | show full processlist |
| 24361 | dbuser | localhost | datadb_store | Sleep | 7 | | NULL |
| 24362 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24363 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24364 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24365 | dbuser | localhost | datadb_store | Sleep | 6 | | NULL |
| 24366 | dbuser | localhost | datadb_store | Sleep | 5 | | NULL |
| 24367 | dbuser | localhost | datadb_store | Sleep | 5 | | NULL |
| 24368 | dbuser | localhost | datadb_store | Sleep | 5 | | NULL |
| 24369 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24370 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24371 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24372 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24373 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24374 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24375 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24376 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24377 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24378 | dbuser | localhost | datadb_store | Sleep | 2 | | NULL |
| 24379 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24380 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24381 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24382 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24383 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24384 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24385 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24386 | dbuser | localhost | datadb_store | Sleep | 1 | | NULL |
| 24387 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24388 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24389 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24390 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
| 24391 | dbuser | localhost | datadb_store | Sleep | 0 | | NULL |
+-------+-------+------------+--------------+---------+------+-------+-----------------------+
246 rows in set (0.00 sec)
Update : 20/02/2016
the problem is from some worng query in database im fixed by remove it and chnage back default settings for my.cnf
[my.cnf]
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-name-resolve
user=mysql
symbolic-links=0
character-set-server=utf8
max_connections=300
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Thanks in advance
Best Answer
The recommendation about optimizing fragmented tables is bogus; ignore it.
You ran for only 38 minutes before getting the analysis; wait a day.
Do not raise
max_heap_table_sizeandtmp_table_size. Instead, figure out which queries are spilling to disk and fix them. Do this by settinglong_query_time = 1, turning on the slowlog, wait a day, then summarize the slowlog withmysqldumpslow -s torpt-query-digest. The show us the worst couple of queries for further discussion. IncludeSHOW CREATE TABLEandEXPLAIN SELECT ....