I'm using MySQL5 to do some quite slow queries, and I am interested to
know how much of an improvement I will be able to achieve by using
better hardware.

The slowest query does a 20 table LEFT JOIN on well-indexed tables with
between 1000-10000 rows. It takes about 5 seconds to complete without
any other load, which is the most I can really cope with.

The server PC is a fairly bog-standard Dell machine (2Ghz Celeron, 512Mb
RAM) running XP Pro, and I am considering using a dual-processor (~3GHz)
machine instead.

I would hope to be able to reduce the execution time of this query by
50%+. Am I being realistic?

John

John wrote:
Not necessarily. Depends on where the bottleneck is. Are you
CPU-bound, I/O-bound, or memory-bound? What does the Task Manager tell
you about CPU & memory performance while you are doing these queries?

You can also monitor MySQL-specific performance using their MySQL
Administrator GUI tool on Windows. Under "Health" you can get lots of
stats about cache performance, etc. A lot of this information is
available in the mysql command-line interface too.

Before you spend money on new hardware, try doing some tuning. The
config parameter that most frequently has a good effect is the
key_buffer_size. This is the amount of RAM to allocate to cache indexes
for MyISAM tables.
http://dev.mysql.com/doc/refman/5.0/...key-cache.html
http://dev.mysql.com/doc/refman/5.0/...variables.html

Also consider preloading indexes.
http://dev.mysql.com/doc/refman/5.0/...reloading.html

If you use InnoDB tables instead of MyISAM tables, read the page on
performance improvements specific to InnoDB. Lots of tips there.
http://dev.mysql.com/doc/refman/5.0/...db-tuning.html

Also make sure your indexes are being used as you expect them to. Use
the EXPLAIN query analyzer.
http://dev.mysql.com/doc/refman/5.0/en/explain.html

Finally, read the whole chapter on Optimization.
It's time well spent, if you are designing complex queries like yours
with a requirement for best performance.
http://dev.mysql.com/doc/refman/5.0/...imization.html

There's also a good book called "High Performance MySQL" by Jeremy
Zawodny and Derek Balling. Some chapter excerpts are reprinted on the
MySQL.com web site.

Regards,
Bill K.

Max out the RAM on the machine if possible. If you do decide on a new
server, get the fastest single processor that you can afford. The
large single query that you run will not benefit from a
multi-processor box because the query will not be able to be
multi-threaded into smaller quick transactions.

John A.



On Thu, 27 Apr 2006 18:43:39 +0100, John <no@email> wrote: