Page 1 of 1

Ideal MySQL hardware advice

Posted: Mon Jun 10, 2002 11:46 pm
by Paul Oertel
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

Posted: Tue Jun 11, 2002 8:00 am
by Wandrer
what is the best hard disk set up? RAID 0?
http://www.mysql.com/doc/D/i/Disk_issues.html

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...

Posted: Tue Jun 11, 2002 11:20 am
by fatal
Definitly go for Linux or freebsd. Only because those os's sepecialize for web serverices amoung other things, windows will always be a desktop not a server in my opinion.

Posted: Tue Jun 11, 2002 10:19 pm
by Paul Oertel
Does MySQL benefit from multiple CPUs?

Paul

Posted: Tue Jun 11, 2002 10:27 pm
by fatal
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.

Posted: Wed Jun 12, 2002 12:45 am
by Paul Oertel
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

Posted: Wed Jun 12, 2002 2:13 am
by volka
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
(taken here)
I'm not sure I can get approval for a BSD variant but a Linux machine might be possible
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 this :( ). Installing a mysql-server for testing on your windozes shouldn't take longer than 10 minutes. Your affords in mysql aren't lost if you change to linux.
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.
You have more than 3GB real-RAM? nice :D This parameter is simple to change before compiling a new kernel. But the database is not loaded as one piece in your memory. A 30GB database does not mean that you need 30GB ram ;)

MySQL and hardware

Posted: Wed Jun 12, 2002 12:15 pm
by BDKR
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)