Skip to:

Performance optimizations in database

  • sx1001


    Hi guys,

    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!!!!

Viewing 1 replies (of 1 total)
  • Hi,

    About 1 and 2, these meta are not generated by BuddyPress, so I advise you to identify the plugins involved and ask their support.

    About 3, you can probably do it for your site, it’s more risky to do it from the BuddyPress plugin.

    About 4. The only approaching string added by BuddyPress is 'Deleted User' and it’s only added at runt time, so to me these are not added by BuddyPress into the DB, so I’d try to see if this comes from another plugin and contact their support.

    About 5. It’s an interesting point, only the first subject of the messages thread seems necessary, and I wonder do we even need a subject to start a private conversation 🤔 ?

    About 6. I don’t think BuddyPress adds postmeta or if it does it’s probably when Post types Activities are on (Blogs component active).

Viewing 1 replies (of 1 total)
  • You must be logged in to reply to this topic.
Skip to toolbar