Re: Quick Linux/MySQL performance questions.
RV Tec schrieb: 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). PS (*nix) should tell you how your processors are used. 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. Depends on which lib mysql uses in your case. 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). Read the Optimization sections of the mysql docs. More RAM will speed it up also. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions.
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]
Re: Quick Linux/MySQL performance questions. (fwd)
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). PS (*nix) should tell you how your processors are used. PS does show me about the CPU usage, but it doesnt tell me which processor, or if they're being used at the same time. Or am I missing a magic PS switch? 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. Depends on which lib mysql uses in your case. Yeah, I'm aware of that. Since glibc 2.4 has only NPTL, I was wondering if it is possible to MySQL use a threading system of its own. However, what I want to know, is a way to confirm that it has been compiled against NPTL. This appears on my config.log session: --enable-threads=posix Thread model: posix Is that NPTL? I know NPTL is POSIX, but maybe this should say Thread model: NPTL. 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). Read the Optimization sections of the mysql docs. More RAM will speed it up also. Thanks. I'll try to bump it up to 4GB to see what happens. Thanks a lot! RV -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions.
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]
Re: Quick Linux/MySQL performance questions.
On 5/12/06, RV Tec [EMAIL PROTECTED] 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] Be aware that Gentoo builds glibc by default twice (read the einfo of the ebuild?) one with linuxthreads and the other with NPTL, you can set USE flags to avoid that (I guess its a matter of compatibility). so, you may be running MySQL with linuxthreads instead. Check it. More RAM, that's my advice, since your system seems pretty powerful. Also check MySQL manual on optimization, and check the variables and the way they are set on your system. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions.
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]
Re: Quick Linux/MySQL performance questions.
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]
Re: Quick Linux/MySQL performance questions. (fwd)
On Fri, 2006-05-12 at 10:59 -0400, RV Tec wrote: Yeah, I'm aware of that. Since glibc 2.4 has only NPTL, I was wondering if it is possible to MySQL use a threading system of its own. However, what I want to know, is a way to confirm that it has been compiled against NPTL. This appears on my config.log session: --enable-threads=posix Thread model: posix Is that NPTL? I know NPTL is POSIX, but maybe this should say Thread model: NPTL. NPTL stands for Native POSIX Thread Library, so yeah, it's using NPTL. -- Pat Adams Digital Darkness Promotions Check out the Dallas Music Wiki http://digitaldarkness.com/tiki signature.asc Description: This is a digitally signed message part
