I'll add "make sure logs and data are on separate partitions" so
you're not doing excessive seeking back and forth.
-Sheeri
On 5/12/06, Dan Buettner <[EMAIL PROTECTED]> wrote:
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
