Timeline for Occasionally simple queries are slow with InnoDB caused by slow query end
Current License: CC BY-SA 3.0
19 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 12, 2017 at 23:35 | comment | added | travisbotello | @WilsonHauck Here is all: my.cnf, SHOW GLOBAL STATUS, SHOW GLOBAL VARIABLES, SHOW ENGINE INNODB STATUS, SELECT name, count FROM information_schema.innodb_metrics ORDER BY name, MySQLTUNER.com, ulimit -a | |
| Dec 12, 2017 at 22:37 | comment | added | travisbotello | @WilsonHauck Okay, thanks for taking this one step further. I will deliver requested logs tomorrow eod. | |
| Dec 12, 2017 at 22:35 | comment | added | Evan Carroll | Yes, I was thinking nasty locking too, but I was thinking it was the excessive amount of io threads causing it, all the same I was way wrong and my experience with fixing these problems leaves me to believe it would be a lot of no-fun which sadly exceeds my free-threshold. | |
| Dec 12, 2017 at 22:34 | comment | added | travisbotello |
@WilsonHauck This make sense in terms of fine tuning performance. But can this be the reason for simple queries taking up to 15s occasionally? I know that 216 is the same but why is it performing 4x slower occ? I can post alternatives suffering from these symptoms. @EvanCarroll maybe InnoDB nasty locking? Mentioned here and here. Maybe my.cnf InnoDB settings are not working with the rest of my setup leading to excessive flushing/locking?
|
|
| Dec 12, 2017 at 20:31 | comment | added | Evan Carroll | @WilsonHauck I'm probably not the right guy to answer. My expertise is moving people off of MySQL onto PostgreSQL. ;) | |
| Dec 12, 2017 at 20:26 | comment | added | Wilson Hauck | For more complete analysis, please post current complete my.cnf/ini Text results of: SHOW GLOBAL STATUS; SHOW GLOBAL VARIABLES; SHOW ENGINE INNODB STATUS; SELECT name, count FROM information_schema.innodb_metrics ORDER BY name; Optional information, ulimit -a for a linux/unix list of limits, MySQLTuner.com report if readily available, because the report includes RAM, per Engine analysis of data space/table counts and additional very helpful information. All suggestions for configuation changes should be carefully evaluated before taking action. | |
| Dec 12, 2017 at 14:11 | comment | added | Wilson Hauck | @travisbotello 216 is no different than any of the other updates in the list. They all operate on ID=1. According to SHOW CREATE, you next ID per auto increment is 69. SHOW INDEX has cardinality of 7 indicating the need to ANALYZE TABLE users. Any table that is updated should be considered for ANALYZE TABLE tbl_name periodically. Weekly has worked well for systems I have supported. Because id is defined as int(10) unsigned and your where is simply 1 for the match to update, there is type conversion/matching going on behind the scenes. If you find alternative, please post here. | |
| Dec 11, 2017 at 15:27 | comment | added | travisbotello |
@WilsonHauck The problematic query from my profiling is not 204 which was fast, it is 216 | 4.06162153 with over 4 seconds: Here is SHOW INDEX, SHOW CREATE and EXPLAIN. Just to make things clear, this issue does not only affect users tables, I have slow logs for other tables as well. This is just an example....
|
|
| Dec 11, 2017 at 14:56 | comment | added | Wilson Hauck |
@travisbotello If you would like the focus to be on this content from profiling | 204 | 0.01350122 | update users set remember_token = 'PpIxVPJE4Hkc981B6C6dT0aeCaExgjmR2GOydFqDZpU36O6z0mOsGic1DYIO' where id = 1 | please provide SHOW INDEX FROM users; and SHOW CREATE TABLE users; and EXPLAIN update users set `remember ........ for analysis.
|
|
| Dec 11, 2017 at 10:39 | comment | added | travisbotello |
@WilsonHauck Spend some more time on debugging. It is definitely related to query end. See my profiling
|
|
| Dec 11, 2017 at 10:01 | comment | added | travisbotello | @WilsonHauck There you go: Output from SHOW INDEX FROM user; and Output from SHOW CREATE TABLE user; | |
| Dec 9, 2017 at 15:36 | comment | added | Wilson Hauck | @travisbotello Could you post complete test results of SHOW INDEX FROM users; and SHOW CREATE TABLE users; please. | |
| Nov 30, 2017 at 10:39 | comment | added | travisbotello | Problem still persists. After about 3 hours in production, slowlog is full again: pastebin of > 10 secs example | |
| Nov 30, 2017 at 6:57 | comment | added | travisbotello | Thanks for the deep inside. Let's give this a try. I will change those values to 4 and reset my slowlogs. I will let you know if this helps. | |
| Nov 30, 2017 at 4:14 | history | undeleted | Evan Carroll | ||
| Nov 30, 2017 at 4:14 | history | edited | Evan Carroll | CC BY-SA 3.0 |
deleted 588 characters in body
|
| Nov 30, 2017 at 4:14 | history | deleted | Evan Carroll | via Vote | |
| Nov 30, 2017 at 4:08 | history | edited | Evan Carroll | CC BY-SA 3.0 |
added 30 characters in body
|
| Nov 30, 2017 at 3:42 | history | answered | Evan Carroll | CC BY-SA 3.0 |