Question Slow Queries

[Home Alone]

This particular query started running really slow today:

# User@Host: root[root] @ [10.0.0.3]
# Thread_id: 443035 Schema: lipstick_forum QC_hit: No
# Query_time: 979.511385 Lock_time: 781.017818 Rows_sent: 0 Rows_examined: 0
SET timestamp=1398816291;
DELETE FROM dbtech_thanks_recententry WHERE dateline < '1396223311';

# User@Host: root[root] @ [10.0.0.3]
# Thread_id: 452578 Schema: lipstick_forum QC_hit: No
# Query_time: 642.666215 Lock_time: 471.119190 Rows_sent: 0 Rows_examined: 0
SET timestamp=1398816291;
DELETE FROM dbtech_thanks_recententry WHERE dateline < '1396223559';

# User@Host: root[root] @ [10.0.0.2]
# Thread_id: 449746 Schema: lipstick_forum QC_hit: No
# Query_time: 811.138351 Lock_time: 608.486907 Rows_sent: 0 Rows_examined: 0
SET timestamp=1398816291;
DELETE FROM dbtech_thanks_recententry WHERE dateline < '1396223479';

# User@Host: root[root] @ [10.0.0.3]
# Thread_id: 449114 Schema: lipstick_forum QC_hit: No
# Query_time: 825.136647 Lock_time: 634.886077 Rows_sent: 0 Rows_examined: 0
SET timestamp=1398816291;
DELETE FROM dbtech_thanks_recententry WHERE dateline < '1396223464';

# User@Host: root[root] @ [10.0.0.3]
# Thread_id: 440410 Schema: lipstick_forum QC_hit: No
# Query_time: 1042.434253 Lock_time: 817.176852 Rows_sent: 0 Rows_examined: 0
SET timestamp=1398816291;
DELETE FROM dbtech_thanks_recententry WHERE dateline < '1396223248';

# User@Host: root[root] @ [10.0.0.3]
# Thread_id: 442232 Schema: lipstick_forum QC_hit: No
# Query_time: 997.417482 Lock_time: 764.026719 Rows_sent: 0 Rows_examined: 0
SET timestamp=1398816291;
DELETE FROM dbtech_thanks_recententry WHERE dateline < '1396223292';

Any idea what would cause this?

 

[Fillip H.]

The only thing I can think of is if you have configured the Recent Entry Cache setting to be too high.

For instance, if you've set it to 365 (1 year) and you have amassed, say 5 million button clicks in the past year, this would effectively undo the performance enhancements of the Recent Entry Cache.

Can you confirm that is not the case?

 

[Home Alone]

I had it set at 30 days -- the recent entry cache had nearly 2,000,000 entries. I lowered it to 14 days, increased the Innodb Buffer and set the MySQL /tmp directory to /dev/shm. That has helped. The recent entry cache now has about 800,000 entries. The entry cache has 5.2M entries and the thanks_entry table has over 30M entries.

 

[Fillip H.]

2 million button clicks in 30 days? Daymn...

If the queries are still slow, try adding an index (not unique, just normal index) on the dateline column on the recententry table.

 

[Home Alone]

I had to add the index on the recent entries table. The improvements you made have helped tremendously on my big board. I had to either shut down the shop or the thanks mod from about 7:00 p.m. until 1:00 a.m. Now I'm able to keep both mods active.

 

[Fillip H.]

I'll make sure to get that index added to future versions so it helps other big board owners, thanks