Support » Fixing WordPress » WordPress Database – is 300mb too big?

  • My site has over 50,000 posts and the overall database size is just over 300mb, with wp_posts being over 100mb

    Have I out-grown wordpress?

    I have hypercache installed and running.
    I have checked the plugins,removed ones I don’t need and using wptuner have found the query slowing things down is:
    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘private’) AND wp_posts.ID NOT IN ….ORDER BY wp_posts.post_date DESC LIMIT 0, 15
    which is located in: [wp-blog-header.php(14): wp()]

Viewing 13 replies - 1 through 13 (of 13 total)
  • How slow?

    If you have revisions turned on you’ll notice that the database is considerably bloated– good idea, bad implementation in my opinion.

    You do have a large database. What are your server specks? You well have outgrown your DB server.

    Revisions take up a lot of space :/

    You can try to clean them out:

    To turn off post revisions, add this to your wp-config.php file:

    define('WP_POST_REVISIONS', false);

    Moderator Mark Ratledge


    Forum Moderator

    As apljdi and ipstenu say: DB bloat. Also, some website stats and logging plugins end up with huge tables of data. And there’s also Clean Options « WordPress Plugins.

    I already have post revisions turned off.

    I have increased my memory limit in wp-config to 96mb, does it need to be increased further?
    define(‘WP_MEMORY_LIMIT’, ’96M’);

    My server spec:
    Guaranteed CPU 3.0 GHz -8 processor cores
    2GB Memory
    2GB SWAP space

    Hewlett Packard DL380G6 servers with dual quad core processors,
    DDR3 EEC FB RAM, hardware RAID10 and enterprise class SAS drives

    I am running hypercache and have eccacelerator running on the server.

    I think WP should run okay on that setup, even with that big a DB.

    You may need to tune your VPS. Check the specs and stats on your server, see what’s running. What’s your load average, what’s your memory usage etc.

    When I moved to VPS, I used this as a starting point:

    thanks Ipstenu.

    Here are the memory stats:

    total used free shared buffers cached
    Mem: 2048 1892 155 0 165 1228
    -/+ buffers/cache: 499 1549
    Swap: 2047 0 2047

    When I ran top
    I saw that mysqld time+ = 986:122.12
    it is the top cpu and memory user

    Just had a check at the mysql vaariables, there are some missing variables that are in the guide, such as:
    max_connections, wait_timeout, tmp_table_size, key_buffer, should I add them in?

    Here is my.cnf
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 1M
    record_buffer = 1M

    max_allowed_packet = 16M

    table_cache = 500
    thread_cache_size = 128

    wait_timeout = 15
    connect_timeout = 15
    interactive_timeout = 15
    max_connect_errors = 10

    query_cache_type = 1
    query_cache_limit = 2M
    query_cache_size = 32M


    open_files_limit = 8192

    max_allowed_packet = 16M

    key_buffer = 64M
    sort_buffer = 32M
    read_buffer = 16M
    write_buffer = 16M

    It’s hard to read formatted output when it gets unformated 😉 Try wrapping it in code tags next time.

    Just had a check at the mysql vaariables, there are some missing variables that are in the guide, such as:
    max_connections, wait_timeout, tmp_table_size, key_buffer, should I add them in?

    I would. The wait_timeout may help some, dumping old connections.

    Taking a quick look at my site, I get this right now (which is a slow day, but average for this time of year – websites based on TV shows are prey to the whims of the networks and Presidential announcements)

    #### mysql     15   0  139m  47m 5340 S  1.3  9.4 224:08.26 mysqld

    (I really love TOP, it’s impressive and confusing and helpful).

    Obviously I’m not going to be worried about the time the process has been running and my CPU usage is low. It spikes now and then, but generally isn’t my heavy hitter.

    Since yours is, clearly, SQL, you may want to look into caching SQL queries.

    The other thought is “What else is running on your server?” If you have anything else making a lot of SQL connections, that could be exacerbating the problem.

    I recommend the following bash script:

    You upload it to your server and run it from the command line:

    $ bash

    It will output a ton of good advice for you to follow based on the MySQL server status variables. In my experience it is very effective at identifying what is your problems as well as saving you from danger by calculating how much memory MySQL can use with the settings you have and warning you to lower some of the values if necessary.

    Use it to find out some settings to change in my.cnf (as well as changing them on the live server) then come back after a couple of days and see what the new advice is. It changes after you’ve given it time to accrue data with the values you set before.

    SQL_CALC_FOUND_ROWS query is a nightmare for large wordpress websites.

    Read through this post to find the solution

    This patch solution cut my page load time from 5 seconds to 1 second with 250,000+ article directory.

    I noticed that the size of my database jumped from 16 MB to 120 MB in the last 6 six months. It took 18 months to come to 16 MB and then within 6 months jumped to 120 MB. The number of posts per day always remained the same, now there are 3300 posts and 1000 comments. The patch from and “W3 Total Cache”, does this clear the cache of my 120 MB database?
    Not sure if the developers of wordpress did not put those patches already in 3.0.4.? Thanks for any information!

    rainer: As described above your problem is probably related to revisions, which bloat the database with dozens of copies of each post to keep track of all edits you’ve ever made.

    You can limit the number of revisions saved for each post or disable them entirely:

    There are also plugins available to remove existing revisions (which won’t be removed when you disable revisions in your configuration, that will only apply to future edits).

    Thanks jeremyclarke, I tried those plugins which promise to delete past revision files but when looking at MySQL it always said wp_posts: 120 MB

    Only when making the query of DELETE FROM wp_posts WHERE post_type = “revision”; directly at phpmyadmin something happened.

    My database is now 21 MB what I feel is the real size for that number of posts. I admit having done 4, 5, 6 revisions at each post so what happened I had exactly the same size database like someone who has 15-20 thousand posts online.

    There should be an adjustement which deletes revisions automatically after lets say 2 days when a post is published. Not that all those revisions are stored endlessly?

    rainer: You can add a define for the number of revisions you want to keep in wp-config.php:

    I recommend 4 or 5, this will almost always be enough to revert back to a working state even if you close/open the window and some autosaves are added. You can make it even lower if you want but at 2 or 3 you may find that it’s not enough to actually get you back to a working version of a post.

Viewing 13 replies - 1 through 13 (of 13 total)
  • The topic ‘WordPress Database – is 300mb too big?’ is closed to new replies.