Marty thank you for having a look --
This is a wordpress site - hosted on Digitalocean. I have 14 more like it and up to 50 at one time. I believe it is a plugin handling query strings, but I am not able to find how to determine what function calls the queries that paralyzed the machine.
More --info--
I was wrong there are hours that we are seeing more hits -- I am showing two sets of data below. The first is when we post to facebook and people come back to the site with ?fbclid= in the query string. The second is during the same time period when people hit the site with ?fbclid= in the query OR in the referrer_string indicating hits to wp-admin/admin-ajax.php
The screen shot above was from the 08 time period below.
First:
01 24
02 25
03 20
04 15
05 308
06 1010
07 717
08 390
09 252
10 199
11 175
12 207
13 220
14 209
15 177
16 144
17 126
18 108
19 94
20 75
21 85
22 76
Second:
01 50
02 48
03 41
04 30
05 400
06 1192
07 786
08 468
09 493
10 382
11 338
12 406
13 439
14 442
15 363
16 328
17 257
18 218
19 191
20 159
21 159
22 164
total number of unique ips of those hours 3618
total number of ips over the same time period 8958
Regarding Caching -- since these are query string hits, no caching is employed. However when the site is visited by those not carrying a query string -- the cache produces instant responses show HIT in the response header.
-- slow queries--
I cannot so far tell what application or function calls the slow queries but i have tuned the database adding index files to all tables requiring them. It reduced the number of slow queries (One for example completely gone) -- but others remain -- example below ---
A related block -- if I new what was calling this (looks like Wordpress to me) I might be able to make some headway.
Time: 240109 15:05:34
User@Host: truthco3v94[truthco3v94] @ localhost []
Thread_id: 27854 Schema: truth_com_EnfJ52WV QC_hit: No
Query_time: 13.200313 Lock_time: 0.000105 Rows_sent: 5 Rows_examined: 14068
Rows_affected: 0 Bytes_sent: 200111
Tmp_tables: 1 Tmp_disk_tables: 1 Tmp_table_sizes: 36978688
Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: Yes
Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: No
SET timestamp=1704812734;
SELECT DISTINCT wp_posts.*, SUM( COALESCE( pvc.count, 0 ) ) AS post_views
FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_post_views pvc ON pvc.id = wp_posts.ID AND pvc.type = 4
WHERE 1=1 AND wp_posts.ID NOT IN (1402,1691,4351,28684,28692,30874,36540,36579,36581,36583,36589,37011,37014,37015,37017,38591,38599,38601,38604,38646,38648,38649,38651,38652,38655,38657,38800,38944,38945,38946,38947,39181,39642,39650,39661,39664,39678,39713,39714,39715,39716,39717,39718,39719,39720,39801,39827,39846,39848,39851,40358,40364,40366,40367,40369,40372,40390,40391,40436,40927,41096,41106,41108,41110,41111,41116,41117,41118,41222,41268,41699,41703,41710,41722,42392,42398,42399,42401,42402,42404,42405,43096,44489,44503,46503,46523,46568) AND (
wp_term_relationships.term_taxonomy_id IN (1,3,4,42,92,99,120,161,164,167,201,213,227,231,268,283,297,316,321,341,345,370,494,572,746,1031,6252,7224,7583,7719,8723,9424,9960,11147,11251,11252,11253,12122,12187,12533,12636,12637,12692,12693,12694,12695,12868,12933,12934,12935,13416,13802,13803,13856,13987,13988,13989,14057,14134,14135,14136,14137,14223,14224,14307,14318,14319,14405,14493,14494,14495,14496,14497,14773,14778,14779,15150,15151,15152,15153,15167,15517,15636,15640,15641,15942,16030,16287,16310,17175,17201,17212,17269,18589,18590,18591,18592,19048,20165,20317,20320,20942,21330,21409,21410)
) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID, wp_term_relationships.term_taxonomy_id HAVING post_views > 0
ORDER BY post_views DESC, wp_posts.ID DESC
LIMIT 0, 5;
User@Host: truthco3v94[truthco3v94] @ localhost []
Thread_id: 27858 Schema: truth_com_EnfJ52WV QC_hit: No
Query_time: 13.501053 Lock_time: 0.000102 Rows_sent: 5 Rows_examined: 14068
Rows_affected: 0 Bytes_sent: 200111
Tmp_tables: 1 Tmp_disk_tables: 1 Tmp_table_sizes: 36978688
Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: Yes
Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: No
SET timestamp=1704812734;
SELECT DISTINCT wp_posts.*, SUM( COALESCE( pvc.count, 0 ) ) AS post_views
FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_post_views pvc ON pvc.id = wp_posts.ID AND pvc.type = 4
WHERE 1=1 AND wp_posts.ID NOT IN (1402,1691,4351,28684,28692,30874,36540,36579,36581,36583,36589,37011,37014,37015,37017,38591,38599,38601,38604,38646,38648,38649,38651,38652,38655,38657,38800,38944,38945,38946,38947,39181,39642,39650,39661,39664,39678,39713,39714,39715,39716,39717,39718,39719,39720,39801,39827,39846,39848,39851,40358,40364,40366,40367,40369,40372,40390,40391,40436,40927,41096,41106,41108,41110,41111,41116,41117,41118,41222,41268,41699,41703,41710,41722,42392,42398,42399,42401,42402,42404,42405,43096,44489,44503,46503,46523,46568) AND (
wp_term_relationships.term_taxonomy_id IN (1,3,4,42,92,99,120,161,164,167,201,213,227,231,268,283,297,316,321,341,345,370,494,572,746,1031,6252,7224,7583,7719,8723,9424,9960,11147,11251,11252,11253,12122,12187,12533,12636,12637,12692,12693,12694,12695,12868,12933,12934,12935,13416,13802,13803,13856,13987,13988,13989,14057,14134,14135,14136,14137,14223,14224,14307,14318,14319,14405,14493,14494,14495,14496,14497,14773,14778,14779,15150,15151,15152,15153,15167,15517,15636,15640,15641,15942,16030,16287,16310,17175,17201,17212,17269,18589,18590,18591,18592,19048,20165,20317,20320,20942,21330,21409,21410)
) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID, wp_term_relationships.term_taxonomy_id HAVING post_views > 0
ORDER BY post_views DESC, wp_posts.ID DESC
LIMIT 0, 5;
User@Host: truthco3v94[truthco3v94] @ localhost []
Thread_id: 27862 Schema: truth_com_EnfJ52WV QC_hit: No
Query_time: 13.523744 Lock_time: 0.000103 Rows_sent: 5 Rows_examined: 14068
Rows_affected: 0 Bytes_sent: 200111
Tmp_tables: 1 Tmp_disk_tables: 1 Tmp_table_sizes: 36978688
Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: Yes
Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: No
SET timestamp=1704812734;
SELECT DISTINCT wp_posts.*, SUM( COALESCE( pvc.count, 0 ) ) AS post_views
FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_post_views pvc ON pvc.id = wp_posts.ID AND pvc.type = 4
WHERE 1=1 AND wp_posts.ID NOT IN (1402,1691,4351,28684,28692,30874,36540,36579,36581,36583,36589,37011,37014,37015,37017,38591,38599,38601,38604,38646,38648,38649,38651,38652,38655,38657,38800,38944,38945,38946,38947,39181,39642,39650,39661,39664,39678,39713,39714,39715,39716,39717,39718,39719,39720,39801,39827,39846,39848,39851,40358,40364,40366,40367,40369,40372,40390,40391,40436,40927,41096,41106,41108,41110,41111,41116,41117,41118,41222,41268,41699,41703,41710,41722,42392,42398,42399,42401,42402,42404,42405,43096,44489,44503,46503,46523,46568) AND (
wp_term_relationships.term_taxonomy_id IN (1,3,4,42,92,99,120,161,164,167,201,213,227,231,268,283,297,316,321,341,345,370,494,572,746,1031,6252,7224,7583,7719,8723,9424,9960,11147,11251,11252,11253,12122,12187,12533,12636,12637,12692,12693,12694,12695,12868,12933,12934,12935,13416,13802,13803,13856,13987,13988,13989,14057,14134,14135,14136,14137,14223,14224,14307,14318,14319,14405,14493,14494,14495,14496,14497,14773,14778,14779,15150,15151,15152,15153,15167,15517,15636,15640,15641,15942,16030,16287,16310,17175,17201,17212,17269,18589,18590,18591,18592,19048,20165,20317,20320,20942,21330,21409,21410)
) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID, wp_term_relationships.term_taxonomy_id HAVING post_views > 0
ORDER BY post_views DESC, wp_posts.ID DESC
LIMIT 0, 5;