Buettner,
First of all, thanks a lot for your reply!
This server has 4 disks to MySQL usage, in two pairs of RAID-1, connected
to a single channel (ok, I realize now this means a bottleneck) LSI
PCIe card.
One RAID1 for MySQL logging and temp space, and the other pair for the
database files (MYI/MYD). I was planning a couple of things:
1) Add another LSI card, this time, 2-channel. Put the MYI files on one
mount point, and the MYD at the other one -- different channels.
2) Find a way to measure the max size of the tempdir, used by MySQL.
Depending on its size, I could use a MFS partition. This could avoid me
some "Copying to tmp table", I guess.
What I'm scared to death, is that our queries are really complex, with
lots of left joins and lots of large tables used. Some queries are now
reaching 30 minutes to return... we do have slow queries active, and
after I'm sure the hardware/OS is OK, we'll nail this and try to get it
better.
Best regards,
RV
On Fri, 12 May 2006, Dan Buettner wrote:
Good morning RV -
On your 3rd question, about how to make things faster:
More RAM should help by allowing the server to keep more/all of the indexes
in memory, enabling much faster access. Be sure to adjust the cache settings
in your my.cnf file after adding RAM. (Keep in mind - some my.cnf memory
settings are per database server instance and some are per connection thread
instance!) Large databases eat RAM for breakfast. The rest of your hardware
setup sounds really quite good.
One possibility for some improvement might be to look at adding dedicated
fast disks for MySQL temp space, since you are dealing with large datasets.
2 or more small fast disks in a striped setup, especially on their own SCSI
channel and ideally with their own hardware RAID RAM cache, may reduce disk
and I/O contention if your temp space is currently on the same disks with
your data. Of course this will only be helpful if MySQL is actually using
disk based temp tables during large queries - check your status output to
see.
I've done a lot of reading on and experimentation with MySQL performance and
attended a MySQL training session on performance tuning, and have learned:
once you have reasonable hardware, the biggest thing you can do to improve
speed is to optimize your SQL queries, indexes, and data structure. While
improving your hardware can give perhaps a factor of 10 performance increase,
optimizing your indexes and queries can sometimes give factors of 100's.
Enable your slow query log, if you haven't already, and use the slow query
tool to start looking at what kinds of queries are taking "too long" ("too
long" being defined by you as a MySQL variable in number of seconds). Start
with the slow queries used most often and see how you can optimize those, by
adding or changing indexes for example.
Read up on MyISAM performance, particularly when it comes to index creation
and usage. Keep in mind that 4.x and 5.x are slightly different animals in
this area (MyISAM index usage) and so read the section for your version:
http://dev.mysql.com/doc/refman/4.1/en/optimization.html
Lots of indexes can be helpful, but MySQL may not be able to use them well
depending on how they were created: the order in which you specify columns
when creating a multi-column index affects how/whether MySQL can use it for
certain queries, for example.
Hope this helps.
Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
