What's a good strategy for planning Wordpress DB growth?

Question

Hoping for some feedback on the topic of optimizing and staying on top of performance as a Wordpress database grows... to paint a picture.... let's say you have a wordpress / buddypress MU site that begins at around 150kb (vanilla install)... over time users add blogs, forums, posts, and comments and the database grows to 5mb.... then 10mb the next year.... Also assuming that hosting controls are a standard colocated setup such as Cpanel or Plesk.

• At what point does the number of entries in the database impact performance of the front-end website?
• What can you do as a website manager to keep this running smoothly as your database grows?
• What can you expect in terms of performance after year 5 when your database is 25 - 30mb big?

Thanks for any feedback you might have on keeping a tight ship.

Regards,

S.

Answer 1

Your specific questions:

1) There is no strict limit to the "number of entries" a DB may contain before performance is affected. Performance depends just as much on your hardware and configuration as it does on the size and structure of the DB.

2) If you're worried about the scalability of your DB layer, you can run it in a cluster, or on a cloud box or VPS that allows resizing. If your DB starts to become sluggish, you can size up (though usually at extra cost). These options add cost, but are really the best way to ensure scalability of a DB.

3) This really depends on your hosting setup, and your DB architecture. But in general (unless you're on a really cheap box), I wouldn't worry about a 30MB WordPress database. WordPress does a good job of indexing tables, and even an OOB MySQL configuration should easily handle WordPress queries on a DB this size. When you get up into Gigabytes--that's when you might need to look seriously at performance optimization options.

In general:

If you're worried about performance, concentrate on tuning your existing MySQL setup, and/or setting up a caching layer. Caching can greatly reduce the burden on MySQL (particularly with WordPress sites, as they generally perform a large number of DB queries).

If after you've properly tuned MySQL and set up a decent caching layer, you're still worried about outgrowing your hardware configuration, you could institue a policy of deleting content after x amount of time.

None of this stuff is specific to WordPress. And I'm not sure that the question has any answers that don't apply to any website or application running on a LAMP stack. But maybe someone else has suggestions concerning MU table structures or other WP-specific DB tricks... I dunno.

Answer 2

Strictly from a MySQL Point-of-View, I have suggestions on how to improve caching of data/indexes for a MySQL Instance.

Keep in mind that there are two major Storage Engines for MySQL

• MyISAM
• InnoDB

Their caching mechanisms are different. There is something that you can do to tune for the Storage Engine of your choice.

MyISAM

MyISAM only caches index pages. It never caches data. You can do do two things to improve I/O for MyISAM tables.

MyISAM Improvement #1

Any MyISAM table that has VARCHAR columns can be internally converted to CHAR without touching the initial design. Suppose you have a table called mydb.mytable and you want to improve I/O for it, perform the following on it:

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

This will increase the size of the table 60%-100% but will yield a 20-30% performance increase in I/O without changing anything else. I wrote about this before in the DBA StackExchange:

• http://dba.stackexchange.com/a/17434/877
• http://dba.stackexchange.com/a/10080/877
• http://dba.stackexchange.com/a/2643/877

MyISAM Improvement #2

You need to increase the MyISAM Key Cache (as sized by key_buffer_size). Run this query, please:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql','performance_schema')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will show you the ideal key_buffer_size based on your current dataset.

InnoDB

InnoDB caches both data and indexes. If you converted all you data to InnoDB and are currently running WordPress from an all InnoDB database, you need to size your InnoDB Buffer Pool (sized with innodb_buffer_pool_size). Run this query, please:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

This will show you the ideal key_buffer_size based on your current dataset.

Projections

If you project that you dataset will grow 20 times as much, just multiple what this query recommends by 20. Suppose your MyISAM dataset is 15MB and 3MB is the sum of your indexes. If you estimate that you will have 20 times as much data, set the key_buffer_size to 60MB like this in /etc/my.cnf:

[mysqld]
key_buffer_size=60M

then restart MySQL. The same thing would apply to the InnoDB Buffer Pool.

If all your data is InnoDB, you need to perform a full Cleanup of your InnoDB infrastructure which I posted in StackOverflow.

Answer 3

At what point does the number of entries in the database impact performance of the front-end website?

When queries start hitting the resource limit of your hosting account.

What can you do as a website manager to keep this running smoothly as your database grows?

Keep an eye on resource usage. Take steps to increase resource and / or optimize usage.

What can you expect in terms of performance after year 5 when your database is 25 - 30mb big?

Should be no change in performance on that tiny a database.

If you expect your site to grow that slowly, then you have plenty of time to learn how to manage growth.