jair101 Posted January 21, 2018 Posted January 21, 2018 Something weird is going on with my DB. Today I have noticed that the index size of core_notifications is few times as large as the other tables: I tried optimizing the table, which as I understand, will recreate the index, but it didn't help. I also checked the size of my daily mysql dumps and it seems this thing started on 06.01. I cannot remember making changes to my settings on 06.01, no new apps installed, no new plugins. As you can see my zipped db size is growing like 100mb per day, which is A LOT. On some days it is reduced, on some day it increases again: The structure and content of the table look fine to me: Any idea how to troubleshoot this? I guess I can truncate the table, killing all notifications in the forum, but I doubt it will solve my problem, becides 70k notifications look fine to me. The index size is out of proportion. My notification settings are to clear them after 1 month.
jair101 Posted January 21, 2018 Author Posted January 21, 2018 Additionally, the problematic index is member_read.
jair101 Posted January 22, 2018 Author Posted January 22, 2018 member_read consists of indexing the following columns - member and read_time. The cardinality of member was equal to the number of rows, i.e. like there are as many member as there are notifications. The cardinality of read_time was 2, i.e. like only two unix timestamps were used to read the notifications - obviously messed up. So I removed this index and recreated it. One additional ANALYZE of the table, now it looks good. That was weird and I have no idea how it happened, hopefully it won't happen again.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.