cache performance issues

soaringeagle
@soaringeagle
7 years ago
3,304 posts
enabled full page cache
but now when i post..anything at all the spinner keeps spinning and mysql processlist shows
MariaDB [(none)]> SHOW PROCESSLIST;
+---------+--------------+-----------+--------------+---------+------+----------                                                                             +-------------------------------------------------------------------------------                                                                             -----------------------+----------+
| Id      | User         | Host      | db           | Command | Time | State                                                                                 | Info                                                                                                                                                                              | Progress |
+---------+--------------+-----------+--------------+---------+------+----------                                                                             +-------------------------------------------------------------------------------                                                                             -----------------------+----------+
| 1139415 | greentec_jam | localhost | greentec_jam | Query   |   89 | updating                                                                              | DELETE FROM jr_jrcore_cache WHERE (cache_item_id LIKE '%,85,%')                                                                                                                   |    0.000 |
| 1139432 | greentec_jam | localhost | greentec_jam | Query   |   22 | update                                                                                | INSERT INTO jr_jrcore_cache (cache_key,cache_expires,cache_module,cache_profil                                                                             e_id,cache_user_id,cac |    0.000 |
| 1139433 | greentec_jam | localhost | greentec_jam | Query   |   16 | update                                                                                | INSERT INTO jr_jrcore_cache (cache_key,cache_expires,cache_module,cache_profil                                                                             e_id,cache_user_id,cac |    0.000 |
| 1139444 | greentec_jam | localhost | greentec_jam | Query   |    5 | update                                                                                | INSERT INTO jr_jrcore_cache (cache_key,cache_expires,cache_module,cache_profil                                                                             e_id,cache_user_id,cac |    0.000 |
| 1139447 | greentec_jam | localhost | greentec_jam | Query   |   49 | update                                                                                | INSERT INTO jr_jrcore_cache (cache_key,cache_expires,cache_module,cache_profil                                                                             e_id,cache_user_id,cac |    0.000 |
| 1139450 | greentec_jam | localhost | greentec_jam | Query   |   49 | update                                                                                | INSERT INTO jr_jrcore_cache (cache_key,cache_expires,cache_module,cache_profil                                                                             e_id,cache_user_id,cac |    0.000 |
| 1139456 | greentec_jam | localhost | greentec_jam | Query   |   39 | updating                                                                              | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_ke                                                                             y = '31197d658d4b14d2b |    0.000 |
| 1139461 | greentec_jam | localhost | greentec_jam | Query   |   33 | updating                                                                              | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_ke                                                                             y = '1070b93117c90eede |    0.000 |
| 1139462 | greentec_jam | localhost | greentec_jam | Query   |   31 | update                                                                                | INSERT INTO jr_jrcore_cache (cache_key,cache_expires,cache_module,cache_profil                                                                             e_id,cache_user_id,cac |    0.000 |
| 1139463 | greentec_jam | localhost | greentec_jam | Query   |   30 | updating                                                                              | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_ke                                                                             y = '1c3e4ccca32695f0f |    0.000 |
| 1139465 | greentec_jam | localhost | greentec_jam | Query   |   29 | update                                                                                | INSERT INTO jr_jrcore_cache (cache_key,cache_expires,cache_module,cache_profil                                                                             e_id,cache_user_id,cac |    0.000 |
| 1139467 | greentec_jam | localhost | greentec_jam | Query   |   29 | update                                                                                | INSERT INTO jr_jrcore_cache (cache_key,cache_expires,cache_module,cache_profil                                                                             e_id,cache_user_id,cac |    0.000 |
| 1139468 | greentec_jam | localhost | greentec_jam | Query   |   28 | update                                                                                | INSERT INTO jr_jrcore_cache (cache_key,cache_expires,cache_module,cache_profil                                                                             e_id,cache_user_id,cac |    0.000 |
| 1139469 | greentec_jam | localhost | greentec_jam | Query   |   27 | updating                                                                              | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_ke                                                                             y = '26338f17a054da759 |    0.000 |
| 1139472 | greentec_jam | localhost | greentec_jam | Query   |   26 | updating                                                                              | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_ke                                                                             y = '638e799f8262b6f04 |    0.000 |
| 1139475 | greentec_jam | localhost | greentec_jam | Query   |   25 | update                                                                                | INSERT INTO jr_jrcore_cache (cache_key,cache_expires,cache_module,cache_profil                                                                             e_id,cache_user_id,cac |    0.000 |
| 1139476 | greentec_jam | localhost | greentec_jam | Query   |   25 | update                                                                                | INSERT INTO jr_jrcore_cache (cache_key,cache_expires,cache_module,cache_profil                                                                             e_id,cache_user_id,cac |    0.000 |
| 1139477 | greentec_jam | localhost | greentec_jam | Query   |   24 | update                                                                                | INSERT INTO jr_jrcore_cache (cache_key,cache_expires,cache_module,cache_profil                                                                             e_id,cache_user_id,cac |    0.000 |
| 1139480 | greentec_jam | localhost | greentec_jam | Query   |   15 | update                                                                                | INSERT INTO jr_jrcore_cache (cache_key,cache_expires,cache_module,cache_profil                                                                             e_id,cache_user_id,cac |    0.000 |
| 1139487 | greentec_jam | localhost | greentec_jam | Query   |   11 | updating                                                                              | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_ke                                                                             y = '62f9dabb4705ddac4 |    0.000 |
| 1139488 | greentec_jam | localhost | greentec_jam | Query   |    9 | updating                                                                              | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_ke                                                                             y = '56824e470ffbda06b |    0.000 |
| 1139491 | greentec_jam | localhost | greentec_jam | Query   |    9 | update                                                                                | INSERT INTO jr_jrcore_cache (cache_key,cache_expires,cache_module,cache_profil                                                                             e_id,cache_user_id,cac |    0.000 |
| 1139494 | greentec_jam | localhost | greentec_jam | Query   |    7 | updating                                                                              | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_ke                                                                             y = '26338f17a054da759 |    0.000 |
| 1139495 | greentec_jam | localhost | greentec_jam | Query   |    5 | update                                                                                | INSERT INTO jr_jrcore_cache (cache_key,cache_expires,cache_module,cache_profil                                                                             e_id,cache_user_id,cac |    0.000 |
| 1139498 | greentec_jam | localhost | NULL         | Query   |    0 | init                                                                                  | SHOW PROCESSLIST                                                                                                                                                                  |    0.000 |
+---------+--------------+-----------+--------------+---------+------+----------                                                                             +-------------------------------------------------------------------------------                                                                             -----------------------+----------+
25 rows in set (0.00 sec)

MariaDB [(none)]>
i refresh the page and the post is there (but refresh is sloooow too)
it seems the db insert is held up by a whole lot of cache updating

this is all the full page caching right?



--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

updated by @soaringeagle: 12/09/17 04:27:13PM
brian
@brian
7 years ago
10,136 posts
Based on listings in your other posts, I suspect you have some low level database issues - either that or your server is overloaded. There's just no way that first query should still be running after 89 seconds.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
soaringeagle
@soaringeagle
7 years ago
3,304 posts
after page refresh db processes were empty
i know my db performance test results arent good
problem seems to be with tuning it more the guy that reconfigured it must have done something weird to my.cnf cause edits in it dont seem to reflect in the mysqltuners next run it still says to make the changes i already made

but this only got like this after enabling full page cahe
chat sites db is pretty big


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
brian
@brian
7 years ago
10,136 posts
The full page cache only affects logged out users - it would not affect you as a logged in user.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
soaringeagle
@soaringeagle
7 years ago
3,304 posts
but wouldnt all thoise tables updating cache be a result of when you post you changfe the page contrent so its updating cache expires i know it only affects logged out users who view the page they get a cached copy
but do you see the logic in what i am saying
when the page is updated..as in new comment or post, the new cache expiriations are written for all the page files...right?
i think that is whats causing it to delay the posting while its updating all the cache expirations


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
soaringeagle
@soaringeagle
7 years ago
3,304 posts
hmm pausing queues does not pause active workers!


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
brian
@brian
7 years ago
10,136 posts
soaringeagle:
hmm pausing queues does not pause active workers!

Correct - it will not "kill" any in progress workers (we do not want to do that). It will prevent NEW workers fro spinning up.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
soaringeagle
@soaringeagle
7 years ago
3,304 posts
ok thought it would pause them much like reset workerr


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
brian
@brian
7 years ago
10,136 posts
soaringeagle:
ok thought it would pause them much like reset workerr

No - there's no way to pause an in-progress worker - you can only tell NEW workers to not turn up.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net

Tags