We are in the position of ordering new hardware. The machines primary function will be to serve as a MySQL server. We only have a few databases running on our current hardware. However, one of those databases is quite large. It currently takes up about 5GB. There are roughly three million records in it and growing daily. We expect it to exceed 30GB. There are less that 10 users. Data is imported in a night batch. There is rarely simultaneous table updates and table reads. Read queries are all very simple. No joins, very little order by. We are already experiencing some slow downs. The current hardware is a Pentium 400 running Windows NT with 256MB ram and SCSI Disk set up with RAID 5.
Does the OS that MySQL runs on affect performance. How much ram would be sufficient for us and what is the best hard disk set up? RAID 0?
The participants of this forum have been very helpful in the past and, once again, your advice is greatly appreciated.
Paul
Ideal MySQL hardware advice
Moderator: General Moderators
-
Paul Oertel
- Forum Newbie
- Posts: 18
- Joined: Fri May 31, 2002 3:44 am
- Location: Japan
http://www.mysql.com/doc/D/i/Disk_issues.htmlwhat is the best hard disk set up? RAID 0?
As for OS, I would definately switch from WindowsNT to either Linux or FreeBSD for the mySQL server.
As for processor/memory, bigger is always better...
-
Paul Oertel
- Forum Newbie
- Posts: 18
- Joined: Fri May 31, 2002 3:44 am
- Location: Japan
Do you mean duel processors? If you do, the answer is no, its just how it the computer inself preforms. On of my old host was on a 350mhz intell II, and i would never have guessed untill he told me. And he was running FreeBSD.
With OS try out a handfull of em, and decide from that. Do your homework.
With OS try out a handfull of em, and decide from that. Do your homework.
-
Paul Oertel
- Forum Newbie
- Posts: 18
- Joined: Fri May 31, 2002 3:44 am
- Location: Japan
I'm not sure I can get approval for a BSD variant but a Linux machine might be possible. Doesn't Linux have a maximum file size of 4 GB in the default installation. Since we anticipate table sizes to exceed 30 GB or more this would be a problem.
Right Multiple Processors (not just two).
I suppose that reading a writing to the hard disks is the realy bottleneck when dealing with MySQL performance.
Thanks,
Paul
Right Multiple Processors (not just two).
I suppose that reading a writing to the hard disks is the realy bottleneck when dealing with MySQL performance.
Thanks,
Paul
(taken here)MySQL Server is coded from the start to be multi-threaded, while PostgreSQL uses processes. Context switching and access to common storage areas is much faster between threads than between separate processes. This gives MySQL Server a big speed advantage in multi-user applications and also makes it easier for MySQL Server to take full advantage of symmetric multiprocessor (SMP) systems
So you are using win32 in your company? Be careful before you install a second (may be unbeloved) OS. If your administrator is not diposed to (or has no time for) get used with his new 'stepchild' you get in trouble if you have no real good arguments (I have seen thisI'm not sure I can get approval for a BSD variant but a Linux machine might be possible
You have more than 3GB real-RAM? niceDoesn't Linux have a maximum file size of 4 GB in the default installation. Since we anticipate table sizes to exceed 30 GB or more this would be a problem.
MySQL and hardware
Based on what MySQL has to say on it's own site, Linux and FreeBSD are both very good ideas. As a matter of fact FreeBSD may be even better based on how it handles threads. In addition, there is a gethostname_r bug that can cause memory overwrite issues in certain versions of GCC.
As for the idea that MySQL doesn't take advantage of multiple processors (SMP), that's simply not true. It does using threads. That said, it then becomes a slave to how well the OS deals with threads. The MySQL site has a lot of good information on this. They really recommend going to the 2.4 kernel as it's handling of threads is a good deal better than 2.2 based kernels. However, there are lot of people out there using heavily loaded systems running RedHat 6.2 with 2.2 kernels.
As for RAID settings, 0 is a lot like Russian Roulette. Sooner or later, you are going to pull the trigger and the guns is going to fire! RAID 0 has the least amount of fault tolerance of all the RAID settings. When a drive dies, you're fired! RAID 5 is a very good setting in that with some controllers, the system will continue to work in a degraded state until you pay it some attention. RAID 1 is another good one, but doesn't have the perfomance of RAID 5 or RAID 0.
Multi level arrays are another option as well. RAID 10 or RAID 50, for example. I've considered using a RAID 50 setup, but decided to stick with RAID 5 as we were having supply problems at the time. Venezuelan customs is a pain in the arse sometimes making it very difficult for a business to stay in business.
A good setup that a lot of people are using is dual processors on linux boxes with 1.5 Gigs of ram and up. I've heard of systems averaging (during peak periods of business) 900 to 1200 queries a second. That's awesome performance! However, you don't get that performance without doing your homework and properly tweaking your machine. Stay close to the mysql lists and read the documentation at mysql.com. There is a ton of good 411 there.
BTW, we are using to Supermicro Pedesal servers with Dual P3 's at 1Gig and 1.5Gigs of RAM. The controllers are Adaptec 2100S's (Ultra 160) with Quantum Atlas 10K RPM drives. There is a third machine that's just a commodity PC that also has the take from the Primary Server replicated to it as well.
Later on,
BDKR (TR Cox)
As for the idea that MySQL doesn't take advantage of multiple processors (SMP), that's simply not true. It does using threads. That said, it then becomes a slave to how well the OS deals with threads. The MySQL site has a lot of good information on this. They really recommend going to the 2.4 kernel as it's handling of threads is a good deal better than 2.2 based kernels. However, there are lot of people out there using heavily loaded systems running RedHat 6.2 with 2.2 kernels.
As for RAID settings, 0 is a lot like Russian Roulette. Sooner or later, you are going to pull the trigger and the guns is going to fire! RAID 0 has the least amount of fault tolerance of all the RAID settings. When a drive dies, you're fired! RAID 5 is a very good setting in that with some controllers, the system will continue to work in a degraded state until you pay it some attention. RAID 1 is another good one, but doesn't have the perfomance of RAID 5 or RAID 0.
Multi level arrays are another option as well. RAID 10 or RAID 50, for example. I've considered using a RAID 50 setup, but decided to stick with RAID 5 as we were having supply problems at the time. Venezuelan customs is a pain in the arse sometimes making it very difficult for a business to stay in business.
A good setup that a lot of people are using is dual processors on linux boxes with 1.5 Gigs of ram and up. I've heard of systems averaging (during peak periods of business) 900 to 1200 queries a second. That's awesome performance! However, you don't get that performance without doing your homework and properly tweaking your machine. Stay close to the mysql lists and read the documentation at mysql.com. There is a ton of good 411 there.
BTW, we are using to Supermicro Pedesal servers with Dual P3 's at 1Gig and 1.5Gigs of RAM. The controllers are Adaptec 2100S's (Ultra 160) with Quantum Atlas 10K RPM drives. There is a third machine that's just a commodity PC that also has the take from the Primary Server replicated to it as well.
Later on,
BDKR (TR Cox)