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
RV Tec wrote:
Folks,
I had some recommendations about operating system last time I posted,
and decided to follow it. It's been a couple of weeks running Gentoo
Linux 2006.0 SMP 2.6.15-gentoo-r5, with glibc 2.4 (NPTL), gcc 3.4.4, XFS
as my FS, deadline scheduler and this has proven to be really stable --
MySQL is 4.0.26 (with gentoo patches, but built on my own), and the
machine is a 2x250 Opteron, 2GB RAM, with LSI MegaRAID 320 (15k RPM disks).
Although this is perfectly fine, I have some questions that maybe you
guys can help me out.
1) Is there a way to see MySQL using both processors? Is SMP helpful in
this case? (This server is dedicated to MySQL, only one instance).
2) Is there a way to confirm that MySQL is using NPTL? The glibc 2.4 is
NPTL only, but I'm not sure if this is right.
3) My database is entirely MyISAM, reaching almost 35GB, there are 3
large tables with (approx) 6GB each. And this can really slow things
down with joins. How can I make this perform better? More RAM? The MYI
files are almost twice the size of its respective MYD (lotsof indexes).
Thanks a lot your help, guys.
Best regards,
RV
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
