XF 2.1 mail_queue (4gb) and reaction_content (2gb) are my biggest two tables

[HJW]

On a forum with around 1m posts these are my biggest two tables.

Seems strange that mail queue is twice the size of post and reaction content is the same size.

3993616 /var/lib/mysql/xf/xf_mail_queue.ibd
2117648 /var/lib/mysql/xf/xf_reaction_content.ibd
2011160 /var/lib/mysql/xf/xf_post.ibd
1998860 /var/lib/mysql/xf/xf_ip.ibd

Now I'm running out of space I need to clear some of them, is there something I should be running to clean up some of these tables?

My whole DB gzipped is less than 2gb but a search on the server says its 14gb

I've tried optimising these tables but it seems to make no change, is there something else I should be doing?

 

[ehd]

mail queue is empty here...

 

[Chromaniac]

mail_queue is empty for me. reaction_content is 17MB which seems reasonable for 70K entries in it.

 

[HJW]

yep mail queue empty for me too but still 4gb

reaction content is 11,970,041 rows and 2 Gb

Not sure if that's right, I'd assume not as 1m posts is about the same size

 

[JulianD]

yep mail queue empty for me too but still 4gb

reaction content is 11,970,041 rows and 2 Gb

Not sure if that's right, I'd assume not as 1m posts is about the same size

You probably need to optimize that table using the OPTIMIZE TABLE syntax or in PHPMyAdmin > Operations > Table Maintenance > Optimize table

 

[HJW]

You probably need to optimize that table using the OPTIMIZE TABLE syntax or in PHPMyAdmin > Operations > Table Maintenance > Optimize table

Tried an optimize before and didn't do anything.

Tried a defrag just now and that fixed it

Would it have been the combination of a defrag and optimise that fixed it?

I want to try it on reaction content and IP but they both have 10+ million rows, so would it be a better idea to turn off the forum then do it?

 

[Chromaniac]

so defrag is essentially taking a mysqldump of the database and importing it into a fresh database?

 

[HJW]

well that went well trying to optimise the search index table and the site offline for a bit



I guess because it's MyISAM it can't be defragged? No difference to the size and 1.3 gb with 1.4 rows, which probably is about right

Now scared to do the reaction_content table incase the site is down for an hour

so defrag is essentially taking a mysqldump of the database and importing it into a fresh database?

I wouldn't of thought so, but I know nothing

 

[Chromaniac]

might be worth considering. stop the forum. extract forum database to sql file. import sql file to a new database. see if the new database has same issues? if not, switch to that database?

 

[HJW]

might be worth considering. stop the forum. extract forum database to sql file. import sql file to a new database. see if the new database has same issues? if not, switch to that database?

Thanks, yes that's what I'm doing now. Shouldn't really be playing with a live site

 

[Chromaniac]

your server seems pretty powerful. pausing the forum is a must. extracting the database and then importing it into a fresh one should not take more than 30 minutes

 

[briansol]

How to reclaim space in InnoDB when innodb_file_per_table is ON

Nilnandan Joshi explains how to reclaim space when using the innodb_file_per_table - without performance or availability problems thanks to Percona Toolkit.

www.percona.com

 

[Joe Link]

@HJW are you still having this issue?

 

[HJW]

The mail xf_mail_queue one has been fixed with a defrag. Seems to need to be done pretty regularly as it fills up to hundreds of mb even though its empty

But reaction content is now 5gb and 15 million rows and I've dare not run a defrag on that yet it's been on my list to do..

 

[Joe Link]

The mail xf_mail_queue one has been fixed with a defrag. Seems to need to be done pretty regularly as it fills up to hundreds of mb even though its empty

By defrag do you mean by running the optimize command, or something else?

 

[Xon]

@HJW MySQL table compression works well at dropping disk-usage, it does increase memory pressure a little. But if you can OpenZFS's inline compression is amazing for MySQL databases. The ability to make cheap snapshots is also great for database backups.

The mail xf_mail_queue one has been fixed with a defrag. Seems to need to be done pretty regularly as it fills up to hundreds of mb even though its empty

This is somewhat expected, as all content is journaled to the queue before being sent. This makes sending notifications a lot speedier, but has this trade-off.

 

[HJW]

By defrag do you mean by running the optimize command, or something else?

This:

@HJW MySQL table compression works well at dropping disk-usage, it does increase memory pressure a little. But if you can OpenZFS's inline compression is amazing for MySQL databases. The ability to make cheap snapshots is also great for database backups.

Thanks that's interesting I'll look into it. Currently getting 5million reactions a month, so the reactions table is growing quickly!

 

[Joe Link]

Thanks!