0

I have a site that is used to log client and meal information. It is based on Wordpress, but has quite a bit of custom code. We have custom taxonomies set for the client and meal data.

Our problem is that the database has gotten quite large and it's causing the site to operate very slow. I would like to essentially archive out the different years of the database into separate databases so that we could still refer back to some of the data if we needed to, but decrease the size of the database for the active year.

I've searched for an archival tool or plugin that could do this, but haven't been able to find anything (most of the results are for Wordpress archives - not the actual process of archiving out data from the Wordpress database). For example, in my database, the "postmeta" table currently has 2,656,529 rows, the "posts" table has 241,725 rows, and the "term_relationships" table has 1,164,119 rows. The entire size of the database is 356.7 MB.

We have custom reports created for the front-end admins to be able to generate meal & client reports, but they now will break if you select a date range longer than 1 month.

FYI - I've ran the WP Optimize plugin and have removed all post revisions already (which were very few to begin with). What I need to do is actually split these tables of the database up so that all of the meals that have been logged for 2012 are separate from 2013 which neesd to be separate from 2014.

Does anyone have any suggestions as to what we can do to get the database under control?

  • 350 megs isn't too bad, what does your host say? You could try moving the databases to solid state drives, adding more memory/processors on the server or otherwise throwing more power at it. – Andrew Bartel May 28 '14 at 22:13
  • 1
    I host the site locally on my own dedicated server. I know the size itself isn't necessarily the problem, but I would figure the 2.6 million rows under postmeta are definitely causing it to run slow. I really just need to know of a good way to be able to split this up at least for testing purposes. – Justin May 28 '14 at 22:22
  • In that case use hyper db – Andrew Bartel May 29 '14 at 2:55
  • Have you used this Wordpress plugin Andrew? I looked through the plugin details and FAQ, but I don't see how to actually split tables. It states that the plugin can partition off certain tables to be in different databases. It doesn't state that it can split those tables based on a date range. – Justin May 29 '14 at 14:32
  • I have not and I'd have to look at the database myself to really give you much more help, but, I'd probably post this question on dba stack exchange as it's not really WordPress specific and is about managing a database with a lot of rows in one specific table. – Andrew Bartel May 29 '14 at 15:27
1

Coming from a different angle, have you looked into adding indexes to the relevant tables to maximize efficiency? Sometimes the right index can make an amazing difference, even when the files aren't particularly large. When they are as big as yours the difference could be all you need.

You mentioned that you host the server yourself, so you have access to the MySQL configuration. MySQL tuning can do amazing things, this is another area to look into.

Also, you could move your MySQL storage to SSD which will improve performance noticeably. You didn't say whether that had been done, and it's not very expensive.

  • No mention of general MySQL maintenance either, simple optimize or repair can make a major difference in performance. – Steve Mar 28 '18 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.