WordPress.org

1. ridesign
   Member
   Posted 2 years ago #

   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()]

2. apljdi
   Member
   Posted 2 years ago #

   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.

3. Ipstenu
   Half-Elf Support Rogue & Mod
   Posted 2 years ago #

   Revisions take up a lot of space :/

   You can try to clean them out: http://www.mydigitallife.info/2008/07/22/how-to-delete-existing-wordpress-post-revisions-storedsaved/

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

   define('WP_POST_REVISIONS', false);

4. songdogtech
   Member
   Posted 2 years ago #

   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.

5. ridesign
   Member
   Posted 2 years ago #

   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:
   VPS
   Guaranteed CPU 3.0 GHz -8 processor cores
   2GB Memory
   2GB SWAP space

   Hardware
   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.

6. Ipstenu
   Half-Elf Support Rogue & Mod
   Posted 2 years ago #

   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: http://www.earnersblog.com/vps-optimization-guide/

7. ridesign
   Member
   Posted 2 years ago #

   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
   [mysqld]
   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

   skip-locking
   skip-innodb
   safe-show-database
   skip-bdb

   [mysqld_safe]
   open_files_limit = 8192

   [mysqldump]
   quick
   max_allowed_packet = 16M

   [myisamchk]
   key_buffer = 64M
   sort_buffer = 32M
   read_buffer = 16M
   write_buffer = 16M

8. Ipstenu
   Half-Elf Support Rogue & Mod
   Posted 2 years ago #

   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)

   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
   #### 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.

9. jeremyclarke
   Member
   Posted 1 year ago #

   I recommend the following bash script:

   https://launchpad.net/mysql-tuning-primer

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

   $ bash mysql-tuning-primer.sh

   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.

10. Melbourne Cup Odds
    Member
    Posted 1 year ago #

    SQL_CALC_FOUND_ROWS query is a nightmare for large wordpress websites.

    Read through this post to find the solution http://wordpress.org/support/topic/slow-queries-sql_calc_found_rows-bringing-down-site?replies=4

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

11. rainer23
    Member
    Posted 1 year ago #

    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 http://core.trac.wordpress.org/ticket/10964 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!

12. jeremyclarke
    Member
    Posted 1 year ago #

    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:

    http://codex.wordpress.org/Revision_Management

    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).

    http://wordpress.org/extend/plugins/search.php?q=revisions

13. rainer23
    Member
    Posted 1 year ago #

    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?

14. jeremyclarke
    Member
    Posted 1 year ago #

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

    http://codex.wordpress.org/Editing_wp-config.php#Specify_the_Number_of_Post_Revisions

    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.

Topic Closed

This topic has been closed to new replies.