2

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

  • First thing - check what happens at 18:00 - check mysql processlist, OS processes, cronjobs etc. Maybe backups? – jkavalik Feb 17 '16 at 19:48
  • The stats from tuner do not match with the my.cnf values (for example innodb_buffer_pool_size 128MB vs. 2.5GB. I think you need to move those variables under the [mysqld] section instead of [mysqld_safe]. But the buffer pool is too big if you have only few KB of InnoDB tables. tmp_table_size should be 100MB max, not 5GB. All the *_buffer_size seem too big too. – jkavalik Feb 17 '16 at 19:58
  • @jkavalik backup is set at 07:00 in cronjob – Mwordpress Feb 17 '16 at 20:05
  • Then prepare some script/cronjob to save the processlist and other status info each minute (or each 10s) between 17:50 and 18:10 (or longer interval if the situation is not as precise) ad see exactly what happens and what is slow. – jkavalik Feb 17 '16 at 20:13
  • @jkavalik can you take look now in post – Mwordpress Feb 17 '16 at 20:24
1

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_size and tmp_table_size. Instead, figure out which queries are spilling to disk and fix them. Do this by setting long_query_time = 1, turning on the slowlog, wait a day, then summarize the slowlog with mysqldumpslow -s t or pt-query-digest. The show us the worst couple of queries for further discussion. Include SHOW CREATE TABLE and EXPLAIN SELECT ....

0

You can add sysschema in MySQL 5.6.28: https://github.com/mysql/mysql-sys

Then used last version of mysqltuner https://github.com/major/MySQLTuner-perl

When running add --verbose option to get most of performance schema.

Be sure, you get the following config for PFS:

#***********************************************************************
#*                 PERFORMANCE SCHEMA
#***********************************************************************
# 
# Indicate whether Performance Schema is enabled
performance_schema=ON

#
# Specifies the maximum number of each instrument
performance_schema_max_cond_classes=80
performance_schema_max_file_classes=50
performance_schema_max_mutex_classes=200
performance_schema_max_rwlock_classes=40
performance_schema_max_socket_classes=10
performance_schema_max_stage_classes=150
performance_schema_max_statement_classes=168
performance_schema_max_thread_classes=50

#
# indicates how many different users, hosts, or accounts (combinations of 'user_name'@'host_name') 
# are expected to connect to the server.
performance_schema_accounts_size=100
performance_schema_hosts_size=100
performance_schema_users_size=100

#
# affect directly how much memory is used to keep historical data, but have no impact on CPU. 
# Keeping the default value is perfectly valid and will not have negative effects. 
# Setting a size to 0 to not spend the memory is also perfectly fine, 
# at the cost of some flexibility (no history without a server restart then).
#
performance_schema_events_stages_history_long_size=1000
performance_schema_events_stages_history_size=10
performance_schema_events_statements_history_long_size=1000
performance_schema_events_statements_history_size=10
performance_schema_events_waits_history_long_size=10000
performance_schema_events_waits_history_size=10

#
# The bad news is that tuning these 9 parameters is critical, as this directly impacts 
# how the Performance Schema functions internally. Parameters that are severely undersized 
# will cause internal contentions in the Performance Schema implementation, which in turns 
# is a known cause of overhead.
# The good news is that the server can also "auto tune" itself, by computing a value for 
# each internal instrumentation buffer (this is what these parameters are for), 
# based on yet other configuration items.
#
performance_schema_max_cond_instances=1258
performance_schema_max_file_handles=32768
performance_schema_max_file_instances=6250
performance_schema_max_mutex_instances=5133 
performance_schema_max_rwlock_instances=2765
performance_schema_max_table_handles=366
performance_schema_max_table_instances=587
performance_schema_max_socket_instances=230
performance_schema_max_thread_instances=288

#
# do need to change only if a lot of singularities 
# (turn instrumentation on for this user, turn it off for this table) 
# are defined with explicit INSERT in tables setup_actors or setup_objects. 
# Unless you use these tables, in which case you know how many rows are inserted there, 
# these parameters need no change.
#
performance_schema_setup_actors_size=100
performance_schema_setup_objects_size=100

performance_schema_session_connect_attrs_size=512
# Red Hat 7.3/Warning
performance_schema_digests_size=200
#performance_schema_digests_size=5000

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.