Single site, single large database ….scaling?

  • I’ve run into issues with some wordpress, single-site (not multi-site) databases getting rather large in the past due to too many articles increasing the size, and leaving me with web hosts complaining about “slow queries” in MySQL.

    Anyone else run into this issue with a single site and a ton of content? At this point, it’s becoming a pain in my arse, and I’m willing to consider anything other than crazy expensive hardware or cloud upgrades…mainly because I’ve not been able to monetize the sites to their full potential yet.

    In fact, it’s a large WordPress site that doesn’t get a crazy amount of traffic. So the size of the database is the issue, rather than the need for scaling due to traffic or amount of queries. I welcome all replies, as I’m somewhat lost on this one.

    I’ve been considering HyperDB, SharDB and a host of different options, but I’m honestly not sure what the best solution to this is. Those seem more geared towards multi-site installs, which I’m not, so I’m a bit lost.

    I’ve also checked into query caching plugins, but that doesn’t solve the issue of slow queries…only lessens it.

Viewing 6 replies - 1 through 6 (of 6 total)

  • SharDB is for multisite only. Don’t install it in WP single site.

    Your first stop should be establishing the exact queries that are causing the issue.

    I’ve dealt with a 200mb+ DB which performed well except for a ‘Recent Comments’ widget so the task then became optimising that specific widget.
    If he’d not wanted that widget then there would not have been the problem.
    If he’d not have had a TON of comments made for new posts which were made rapidly and were huge the problem might not have existed.

    So I think you should first find out what is the precise cause and what triggers it.

    Thread Starter ashworth102680

    (@ashworth102680)

    Thanks for the info guys. Appreciate it.

    @mark –
    These databases were nearly 1.3GB, which is understandable surrounding the slow queries issue.

    Is there no reliable way to shared your single WP database? I think that’s where I’m hung up. The widgets and plugins were kept to a minimum, which is why I felt like there was nothing else I could do in that situation. I’m trying to learn from it now before experiencing it again.

    As I understand dbs it’s not how big it is, it’s what you are trying to do with it. You do need to know the queries.

    Have you looked at the database itself to determine what tables are the biggest, and if they have unnecessary data in them? I do know the wp_options table can build up with transients.

    (stab in the dark there. if you have thousands of posts and tags and categories and comments, well… i dunno what to tell ya.)

    Thread Starter ashworth102680

    (@ashworth102680)

    Yes, we tried clearing out that data in options. Unused tags, etc. The optimization of data and repairing indexes, etc…did all of that. I think it was just the sheer size and how long it took for MySQL to sift through all the data that was causing the slow queries.

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Single site, single large database ….scaling?’ is closed to new replies.