Hope it is useful.
I agree, you may want to look at adding another card and disks, for
speed and to segregate the various operations (temp, logging, data).
Splitting up your MYD and MYI files may help, though if you have enough
RAM to keep indexes in memory, maybe you don't need to do that.
With the sheer size of your data, I suggest you consider some form of
striping with your RAID, not just mirroring. I'm a big fan of RAID 10
personally but if your data is pretty static then RAID 5 gives you the
read speed at a lower cost. If you have a lot of write operations then
RAID 5 may not be such a good choice. You might be surprised how much
you will gain in read speed and therefore MySQL query speed if you go
from RAID 1 to say a 6-disk RAID 10 setup.
Depends on funds of course. For a good LSI card and 6 small fast
internal disks you're probably looking at $2K or so. Depending on what
you have now you could put MySQL logging on some inexpensive slower
disks and re-use existing disks in a new setup.
Good luck!
Dan
RV Tec wrote:
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]
