Geek Stuff: Blog & Database Issues

This post is a very technical discussion of blogging and database management – so if that’s not your interest area, well… “There’s nothing to see here. These aren’t the droids you’re looking for. Move along.”

I’ve spent the day working on this website, primarily battling what seem to be ongoing performance issues. In addition to very slow page loads, I’m seeing periodic internal server error message on the site, where nothing will display at all. (I also did a little php & css work, adding a custom header and a few format changes.) The various site fix tactics I’ve tried include:

  • Upgraded WordPress to v. 2.5
  • Repaired & optimized my SQL tables
  • Disabled all plugins and removed all sidebar widgets, and re-added them one at a time, testing for speed impact. Based on results, left Google sitemaps plugin disabled.
  • Installed wp-cache to cache requested pages for 60 min
  • Ran a fairly useful website analysis tool on my site
  • Reduced the posts-per-page from 10 to 5
  • Changed images displayed on the main page from full sized to thumbnail
  • Turned on error logging to track my site behind the scenes

I did some searching on hosting providers and complaints about shared hosting site performance. All major providers had many complaints; all had many praises. I’m on GoDaddy’s hosting platform. I can call support (again!) about the performance issues but I’d like to prevent them with empirical rather than anecdotal evidence of site issues.

The only additional fix I haven’t tried because of its drastic nature is to export the DB offsite, and then nuke the entire site & database and rebuild from scratch and re-import the posts – either on space provided by my current hosting provider, or new space. This seems unnecessarily difficult.

Alpha geek Bill sent me a link to this article on WordPress performance and configuration problems – it helped me understand why I might be having some of these site issues.

Below are what seem to be the most egregious errors from my database’s status page. If anyone is aware of any quick WP code or database tweaks to help address some of these issues, it would be super helpful.

Handler_read_rnd
4,155.38 M
The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don’t use keys properly.

Handler_read_rnd_next
610.95 G
The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

Qcache_lowmem_prunes
414 M
The number of queries that have been removed from the cache to free up memory for caching new queries. This information can help you tune the query cache size. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache.

Key_reads
247 M
The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.

Select_range_check
7,095
The number of joins without keys that check for key usage after each row. (If this is not 0, you should carefully check the indexes of your tables.)

Opened_tables
72 M
The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.

2 Responses to “Geek Stuff: Blog & Database Issues”

  1. Vince Says:

    If the problem is the MySQL database, and that’s what it appears to be from the information you’ve provided, you may have no choice but to use PhpMyAdmin (does GoDaddy provide that?) to export your data, delete your existing tables and then re-import.

    Also, in your wp-config.php, do you have the lines

    // Enable the WordPress Object Cache:
    define(ENABLE_CACHE, true);

    I doubt that you have the access to do this, but the MySQL Query Cache needs to be optimised for maximum performance. This is normally done by finding your MySQL configuration file my.cnf, then find the setting query-cache-type or if necessary add it and change the value to 1, then find the setting query_cache_size or if necessary add it and change the value to . This will enable the MySQL Query Cache and instruct it to use 26M of RAM.

    Finally, the latest stable version of WordPress is 2.5.1. You appear to have 2.5, which has over 70 bug fixes, including important security fixes. Go here for more info:

    http://wordpress.org/development/2008/04/wordpress-251/

  2. Vince Says:

    Uh, 2.5.1 has the bug fixes. Duh!