Archive for the ‘mysql’ Category

WordPress Performance: Time Consuming MySQL Queries and Caching

Sunday, January 24th, 2010

I have been looking at a client’s WordPress install the last few days. They have a large number of posts and page load times in general have been increasing. I installed some query logging based on the code in this article …
Rails-Like SQL Query Logging in WordPress

Checking over the logs it became obvious that queries using SQL_CALC_FOUND_ROWS are a problem. Digging through code it appears that where a limit on results is used it uses this query modifier to allow simpler coding of paging. The problem is that since the main query to grab blog posts on the blog home page or any archive page uses this, on a large blog this becomes quite inefficient.

For now I have tweaked MySQL so that the MySQL Query Cache is now larger. These articles were useful:
MySQL Query Cache (at MySQL Performance Blog)
Turn on MySQL query cache to speed up query performance?

Looking at the log after this change query times have dropped dramatically (from over 0.5 seconds and up to milliseconds). This has immediately improved performance of the blog and overall load on the server.

I am currently looking at some WP Caching API and plugins to further improve performance.

If you are looking for some more ways to speed up WordPress check out this article as a starting point:
4+1 Ways To Speed Up WordPress With Caching

And … for myself – just so I remember:
Install Memcache onto cPanel running CentOS

Presentation on Performance and Scalability

Monday, August 31st, 2009

This is more a note to myself to read this …

Real World Web: Performance & Scalability:

Tip for Setting up Apache, MySQL and PHP on Vista

Monday, March 16th, 2009

Just setup a PHP/MySQL development server on my Vista-based laptop.

Here is a good rundown of setting up a VAMP (Vista, Apache, MySQL, PHP) server from Devshed:
Creating a VAMP

The other thing to know, which this article skipped, is that you should run your text editor as administrator when editing the config files. If you don’t do this you won’t be editing the correct file (only your Users version of it which isn’t actually used by the system).

Great blog on MySQL performance …

Wednesday, April 23rd, 2008

Some great stuff on getting the best performance out of MySQL …
MySQL Performance Blog

I have just been reading this …
ORDER BY … LIMIT Performance Optimization. Pagination is used on just about every site I work on – that article is great on speeding up queries using ORDER BY and LIMIT.

Having problems importing a large MySQL database? Try this …

Saturday, April 19th, 2008

Bigdump is a PHP script that staggers the import of a large MySQL dump. It processes a small chunk and then refreshes. This continues until the database is fully imported. This is great for hosts that don’t give you access to ssh or who place restrictions on file uploads or server execution times.

The only thing to configure is the database host, user, and password. Simply upload the dump file to the same directory as the bigdump file (use FTP or the built-in uploader) and then process that file.

Killing MySQL Database Perormance

Saturday, March 22nd, 2008

Great presentation reviewing how to kill database performance.

How not to optimize a MySQL query

Thursday, March 20th, 2008

Response to a blog post on MySQL optimization. Has some good comments on using (or rather avoiding use of) SQL_SMALL_RESULT, HIGH/LOW_PRIORITY, and INSERT DELAYED. STRAIGHT_JOIN.

read more | digg story

PHP and MySQL Optimization

Wednesday, March 19th, 2008

A list featuring a few very simple tips for optimizing php/mysql applications …

read more | digg story