Re: Dream MySQL Server?
Mark Maggelet [EMAIL PROTECTED] wrote on 02/03/2004 19:08:02: Scanning a PC manufacturer's website, it seems easy to get 4x2.5GHz Xeon, 1Mb L3, 8Gb ram, dual 15000 rpm Scsi with Raid 1 (for performance as well as reliability). Does this sound balanced for a MySQL engine? Or what would other people advise? I think you're better off with 4hd's and 2cpu's then the other way around since that's where your bottleneck will probably be, double the hd's should cut read times in half (but maybe add to write times) I had assumed that, since the ram was of the same order as the database size, most of the database would be cached. Number of threads is of concern - the system will have many lightweight queries, which I would like answered fast, and a few heavy joins. As I understand it, if the data is in memory and no lock collisions occur, a heavyweight query will hog a CPU until completed. Many CPUs (or virtual CPUs, with hyperthreading) allow many opportunities for simple queries to overlap complex ones. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dream MySQL Server?
My advice is that in the end, hardware does not matter that much. If it solves a problem, it solves it until your data outgrows it again, and eventually you hit your budget limitations and cannot afford an upgrade. Of course, there are certain common sense rules that need to be followed, eg. if you have a 20 GB database, you need at least a 20 GB disk, but otherwise, if your application is good, it runs well on modest hardware, and if not, doubling the datais likely to kill it no matter how powerful hardware you use on it. A good case in point was an earlier post from an 8-CPU Irix user. I would suggest you focus on making the application good. It would be wise to invest a portion of the hardware budget into purchasing a MySQL support contract or consulting services. Done that - and very pleased with the result. The application is already, for reasons given below, as well-tuned as I can make it. Regarding Windows 2000 - I am curious why MySQL is an option, but Linux is not. They kind of go together, almost the same as MS-SQL and Windows, or Oracle and Solaris. Is this a dedicated MySQL machine? If yes, I cannot think of one technical reason to run Windows on it, and I've tried hard in the past. If you were using Oracle or especially MS-SQL, it would make sense. But if you've decided that MySQL is it for your database, I would really have a hard time coming up with any reasonalbe justification for Windows even if Microsoft or somebody else was going to pay me big money for it. This is not a unique system - this is a large scale example of a general system. It is the central database for a number of surrounding specialised hardware units, all of which are controlled by Windows PCs. Normally there are three or four such systems, and our current MySQL/Windows solution is very good. The order I am trying to meet now has about 24 surrounding systems instead of 4. But it is otherwise identical. We have, for example, 24/7 support staff who will have to support end user staff if anything goes wrong. It is hard enough getting them up to speed on the Windows platform - adding the Linux platform for them to learn would be an extra burden. And I would find it difficult to be truly expert on several databases. And the testing burden of the same software against several database back-ends would be considerable. And to the suggestion of using MSSQL: this giant project would support the cost of MSSQL but the smaller systems wouldn't. Aside from the fact that I have already coded some MySQL specific code (which is, of course, reversible), smaller systems would not absorb the cost of MSSQL with replication. (I didn't mention that we actually have a replication pair of the machines described, and MSSQL with replication is much more expensive than without). We are currently retreating from an unsatisfactory Linux development with an outside contractor. Nobody's fault, but our knowledge of Linux and his understanding of our requirements didn't overlap enough. Our management are understandably reluctant to introduce an OS of which we have little knowledge when we are working hard to increase our knowledge base of our primary platform, which is (for better or worse) Windows. Thanks to all who have commented. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dream MySQL Server?
And the testing burden of the same software against several database back-ends would be considerable. Just one comment. If you are using MySQL, there would be no testing burden...against several database backends. The protocol, commands, connection methods, etc, are identical. A client on Windows, Mac, or *nix can talk to a MySQL server on a Windows, Mac, or *nix server, and will never know the difference. All it knows it that it is connecting to port 3306 and it's talking to a MySQL server. There is no difference among operating systems. j- k- -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dream MySQL Server?
I have a requirement for a system that is of the order of 8-10 times the size of my current system. Unfortunately (a) I don't know how many times larger it actually is, and (b) my current system, while very happy, even relaxed, on its current hardware, has not yet been subjected to the full rigour of the target number of users. So it is very difficult to estimate what hardware I need to specify for the new system. Fortunately, the budget is fairly generous. Obviously, we don't want to gold-plate the system - but if a bit of overspend gives faster-than-specified performance, that will be a gain rather than wasted money. So I can get a lot of hardware - if I can confidently state that it will improve MySQL performance. So what should I be planning to use? The database is quite small - 2-4 Gb, but high churn: maybe 25% of it replaced every day. Reads dominate writes, but not overwhelmingly: at a guess, 10:1. The current hardware is dual Xeon 2.0, 2Gb, single Scsi disk. The one fixed factor is that the OS is Windows 2000 (I know the arguments for Linux/BSD, but that is not feasible). Scanning a PC manufacturer's website, it seems easy to get 4x2.5GHz Xeon, 1Mb L3, 8Gb ram, dual 15000 rpm Scsi with Raid 1 (for performance as well as reliability). Does this sound balanced for a MySQL engine? Or what would other people advise? Thanks for any advice, Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dream MySQL Server?
Scanning a PC manufacturer's website, it seems easy to get 4x2.5GHz Xeon, 1Mb L3, 8Gb ram, dual 15000 rpm Scsi with Raid 1 (for performance as well as reliability). Does this sound balanced for a MySQL engine? Or what would other people advise? I think you're better off with 4hd's and 2cpu's then the other way around since that's where your bottleneck will probably be, double the hd's should cut read times in half (but maybe add to write times) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dream MySQL Server?
I'd go with raid 1+0 ... Be a shame to have that much cpu power and become I/O bound.. This way you've got 4 disks feeding the cpu's instead of 2.. Better performance than raid 5, and only 2 more disks than your current config. On Tue, 2 Mar 2004 [EMAIL PROTECTED] wrote: I have a requirement for a system that is of the order of 8-10 times the size of my current system. Unfortunately (a) I don't know how many times larger it actually is, and (b) my current system, while very happy, even relaxed, on its current hardware, has not yet been subjected to the full rigour of the target number of users. So it is very difficult to estimate what hardware I need to specify for the new system. Fortunately, the budget is fairly generous. Obviously, we don't want to gold-plate the system - but if a bit of overspend gives faster-than-specified performance, that will be a gain rather than wasted money. So I can get a lot of hardware - if I can confidently state that it will improve MySQL performance. So what should I be planning to use? The database is quite small - 2-4 Gb, but high churn: maybe 25% of it replaced every day. Reads dominate writes, but not overwhelmingly: at a guess, 10:1. The current hardware is dual Xeon 2.0, 2Gb, single Scsi disk. The one fixed factor is that the OS is Windows 2000 (I know the arguments for Linux/BSD, but that is not feasible). Scanning a PC manufacturer's website, it seems easy to get 4x2.5GHz Xeon, 1Mb L3, 8Gb ram, dual 15000 rpm Scsi with Raid 1 (for performance as well as reliability). Does this sound balanced for a MySQL engine? Or what would other people advise? Thanks for any advice, Alec -- 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: Dream MySQL Server?
[EMAIL PROTECTED] wrote: I'd go with raid 1+0 ... Be a shame to have that much cpu power and become I/O bound.. This way you've got 4 disks feeding the cpu's instead of 2.. Better performance than raid 5, and only 2 more disks than your current config. If you have 8 GB of RAM and 4 GB of database, you would only become I/O bound if write a few hundred blocking commits per second to the disk*. In that case, having a battery backed RAID adapter with write cache enabled is a much better way of improving performance as going from RAID 1 to RAID 1+0. *Presuming the OS has a sane disk cache or is 64 bit. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dream MySQL Server?
Never think enough is enough.. Current operation levels can easily be pushed many times their current level/ratio in a short matter of time, and databases can grow rapidly (Even tho it's not identified here) I have spec'd boxes before based on someone reccomendations for load, and then found 2 months later the box is choking, time to upgrade = potential downtime.. 2 extra disks is minimal cost for the I/O boost and amount of extra capacity you'll get. I agree with the write cache and battery backup... Any decent raidcard has that option for a few bucks... 4GB database today, can easily turn into a 20GB database tomorrow.. Never think small, On Tue, 2 Mar 2004, Jochem van Dieten wrote: [EMAIL PROTECTED] wrote: I'd go with raid 1+0 ... Be a shame to have that much cpu power and become I/O bound.. This way you've got 4 disks feeding the cpu's instead of 2.. Better performance than raid 5, and only 2 more disks than your current config. If you have 8 GB of RAM and 4 GB of database, you would only become I/O bound if write a few hundred blocking commits per second to the disk*. In that case, having a battery backed RAID adapter with write cache enabled is a much better way of improving performance as going from RAID 1 to RAID 1+0. *Presuming the OS has a sane disk cache or is 64 bit. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- 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: Dream MySQL Server?
[EMAIL PROTECTED] wrote: I have a requirement for a system that is of the order of 8-10 times the size of my current system. Unfortunately (a) I don't know how many times larger it actually is, and (b) my current system, while very happy, even relaxed, on its current hardware, has not yet been subjected to the full rigour of the target number of users. So it is very difficult to estimate what hardware I need to specify for the new system. Fortunately, the budget is fairly generous. Obviously, we don't want to gold-plate the system - but if a bit of overspend gives faster-than-specified performance, that will be a gain rather than wasted money. So I can get a lot of hardware - if I can confidently state that it will improve MySQL performance. So what should I be planning to use? The database is quite small - 2-4 Gb, but high churn: maybe 25% of it replaced every day. Reads dominate writes, but not overwhelmingly: at a guess, 10:1. The current hardware is dual Xeon 2.0, 2Gb, single Scsi disk. The one fixed factor is that the OS is Windows 2000 (I know the arguments for Linux/BSD, but that is not feasible). Scanning a PC manufacturer's website, it seems easy to get 4x2.5GHz Xeon, 1Mb L3, 8Gb ram, dual 15000 rpm Scsi with Raid 1 (for performance as well as reliability). Does this sound balanced for a MySQL engine? Or what would other people advise? My advice is that in the end, hardware does not matter that much. If it solves a problem, it solves it until your data outgrows it again, and eventually you hit your budget limitations and cannot afford an upgrade. Of course, there are certain common sense rules that need to be followed, eg. if you have a 20 GB database, you need at least a 20 GB disk, but otherwise, if your application is good, it runs well on modest hardware, and if not, doubling the data is likely to kill it no matter how powerful hardware you use on it. A good case in point was an earlier post from an 8-CPU Irix user. I would suggest you focus on making the application good. It would be wise to invest a portion of the hardware budget into purchasing a MySQL support contract or consulting services. Regarding Windows 2000 - I am curious why MySQL is an option, but Linux is not. They kind of go together, almost the same as MS-SQL and Windows, or Oracle and Solaris. Is this a dedicated MySQL machine? If yes, I cannot think of one technical reason to run Windows on it, and I've tried hard in the past. If you were using Oracle or especially MS-SQL, it would make sense. But if you've decided that MySQL is it for your database, I would really have a hard time coming up with any reasonalbe justification for Windows even if Microsoft or somebody else was going to pay me big money for it. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dream MySQL Server?
Hi Alec, You are not mentioning the application, if this is a web-based application/interface or some internal heavy stuff. I'm running Windows 2000 and MySQL (and are happy, sleep fine every night !) for web-sites, and have only a P4 1.6 processor - and this is working very fine so far. So, if You are running a web-based application/interface, You have to focus on harddisk speed and security/redundency and LAN-I/O (100Mbit is fine), the remaining hardware will cover very well. Best regards Peter Copenhagen Denmark - Original Message - From: Sasha Pachev [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, March 02, 2004 10:45 PM Subject: Re: Dream MySQL Server? [EMAIL PROTECTED] wrote: I have a requirement for a system that is of the order of 8-10 times the size of my current system. Unfortunately (a) I don't know how many times larger it actually is, and (b) my current system, while very happy, even relaxed, on its current hardware, has not yet been subjected to the full rigour of the target number of users. So it is very difficult to estimate what hardware I need to specify for the new system. Fortunately, the budget is fairly generous. Obviously, we don't want to gold-plate the system - but if a bit of overspend gives faster-than-specified performance, that will be a gain rather than wasted money. So I can get a lot of hardware - if I can confidently state that it will improve MySQL performance. So what should I be planning to use? The database is quite small - 2-4 Gb, but high churn: maybe 25% of it replaced every day. Reads dominate writes, but not overwhelmingly: at a guess, 10:1. The current hardware is dual Xeon 2.0, 2Gb, single Scsi disk. The one fixed factor is that the OS is Windows 2000 (I know the arguments for Linux/BSD, but that is not feasible). Scanning a PC manufacturer's website, it seems easy to get 4x2.5GHz Xeon, 1Mb L3, 8Gb ram, dual 15000 rpm Scsi with Raid 1 (for performance as well as reliability). Does this sound balanced for a MySQL engine? Or what would other people advise? My advice is that in the end, hardware does not matter that much. If it solves a problem, it solves it until your data outgrows it again, and eventually you hit your budget limitations and cannot afford an upgrade. Of course, there are certain common sense rules that need to be followed, eg. if you have a 20 GB database, you need at least a 20 GB disk, but otherwise, if your application is good, it runs well on modest hardware, and if not, doubling the data is likely to kill it no matter how powerful hardware you use on it. A good case in point was an earlier post from an 8-CPU Irix user. I would suggest you focus on making the application good. It would be wise to invest a portion of the hardware budget into purchasing a MySQL support contract or consulting services. Regarding Windows 2000 - I am curious why MySQL is an option, but Linux is not. They kind of go together, almost the same as MS-SQL and Windows, or Oracle and Solaris. Is this a dedicated MySQL machine? If yes, I cannot think of one technical reason to run Windows on it, and I've tried hard in the past. If you were using Oracle or especially MS-SQL, it would make sense. But if you've decided that MySQL is it for your database, I would really have a hard time coming up with any reasonalbe justification for Windows even if Microsoft or somebody else was going to pay me big money for it. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- 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]
