Any optimizations I can make for a new server?

[linux_tech]

There are some more tips to optimize large queries here-
http://www.debianadmin.com/top-84-my...ance-tips.html
http://www.debuntu.org/2006/07/21/75...-response-time

[Znupi]

Don't take offense to this because it's not meant to be that way but it's pretty obvious you have little experience setting up a hiigh capacity database server which is primarily what you are doing if I read you right.

None taken. As I said in my first post, I'm quite new to this.

That being said, you're not going to want to hear this, but you should have done your research before you ordered the machine. The three most critical parameters for that kind of database server is disk performance, memory, and CPU, in that order. RAID should have been a must, with as many small drives as possible. More spindles increases throughput exponentially, especially when you are randomly accessing large amounts of data. A single drive, no matter how fast, is just going to suck for this. Secondly, databases are memory hungry, and as was said here, the more of the data you can get into memory, the better. 4 or even 8GB would be better. Memory is cheap right now. And finally, CPU is the absolute least of your worries. A 2 gig dual core would probably be fine here.

Indeed, RAID would've been a good idea. But considering the fact that he was running this program on a 6-year old 40GB IDE HDD, this is going to be quite an improvement. We're going to do regular backups on our desktops using some cron jobs (I know this isn't quite the best data redundancy system, but it's what we got). It's definitely a plan for the future, though.

I would not create a separate /mysql partition, as this mucks with the locations of files that other applications depend on, and it will be a nightmare for you as you add other applications to the system, trust me. As to losing your data, as I state below, RAID should have been mandatory, and backups are an absolute necessity even of you have a RAID array.

I don't get this -- isn't mysqld the only program that actually accesses the database files? Don't all client applications connect to the mysql server and issue queries? So, a client application would only have to know the IP and the port of the mysql server, while mysqld does all the lookups and such. Maybe you understood wrong -- I don't want to install mysql to a separate partition, I just want to keep the database files on a separate partition and change a few mysql configs to make it look on that partition for data. I've done that before.

On the OS, if you want as good a performance as you can get from the hardware, then you really should run software compiled on the box such as is done in the BSD operating systems. Gentoo would be good as well, but BSD has been around since the 60s, is real Unix, and if you compile your MySQL, PHP, and other software specifically on the hardware, you squeeze every last drop out of it.

Gentoo, Archlinux and other hard-to-setup solutions were recommended before. While I am sure that would increase performance, we just don't have the time to do it. Thanks for the suggestion, though. And thanks for telling me that BSD systems are always compiled on the machine they run -- I didn't know that!

Basically, you're trying to make chicken salad out of chicken ***** because of your budget constraints. Exactly what kind of development will he be doing on this box? We may be able to offer some suggestions if we knew more about what he will be doing.

He has some programs that gather data and create huge relational databases. After he's done with that, he creates some statistics out of those databases and uses them on an actual production server which is hosted elsewhere.

Thanks to everyone for replying here and giving your advice. The server is coming in tonight (hopefully) and I will probably get to hack at it this weekend. I'm pretty excited .

[JeppeM]

Hey

From reading the post it seems like you have some great things to try out... One thing i would suggest is disabling the atime on the /mysql partition... atime is a small timestamp which is written every time a file is accessed, so you're able to see if it's being used (ie., we clean up our file servers of files which have not been accessed in the last 4 years). It's different from modification time, but often not needed on systems like this... So mount it with noatime and nodiratime - you can read more about the different mount options here: http://linux.die.net/man/8/mount

Hope this helps, and best of luck with the system!