Performance optimizations in database
we have a big networks which keeps growing and growing – currently 20k users and still going strong. We want to try to tweak and improve every tiny screw / setting and we are just optimizing the database. Because: The DB is already 1.5GB and it’s growing fast.
We identified a LOT of “default” metadata with every update / activity / post, hence this questions arise, I dig deep already 🙂
1. Is it safe to delete the activity data where _link_embed=0 and _link_preview_data=”” (empty)? It comes for EVERY activity. 60% of ALL 100k activity rows are those “default” settings.
2. Is it safe to delete the meta-data for sent messages which do NOT have a media, e.g. gif_raw_data or _gif_data or bp_media_ids empty values? In wp_bp_messages_meta
3. Wouldnt it be possible to convert ALL userid fields (in messages, activity, users, friendship etc tables) to INT instead of BIGINT? This would reduce storage, index sizes, index scan time etc … We will never have 4 million of users…
4. Would it do any harm when deleting “deleted” messages? I.e. messages from wp_bp_messages_messages where Subject==”Deleted” and message empty?
5. Is it really necessary to store the subject of a message thread redundently? So for one thread within wp_bp_messages_messages we sometimes have 50 entries, all with the same subject.. (varchar…)
6. the table wp_postmeta grows huge, because unfortunately, all uploaded attachements are handled as posts, hence quite some plugins like WPML or ELEMENTOR hook into it and save their metadata for every damn single uploaded attachement 🙁
Thanks so far, hope we can get a really good discussion going here!!!!
- You must be logged in to reply to this topic.