Any optimizations I can make for a new server?

[Znupi]

Mr.Carramba: We have thought of keeping the huge tables in RAM (temporarily) and writing them to disk when we are finished with them. It's definitely something we will try. Also, optimizing MySQL is indeed a very good idea, but what optimizations would you recommend? Any good resources on this topic?
moberry: memcached is only useful when doing lots of identical reading queries. My brother's program that will run on the server does pretty much the opposite, running always-different queries and mostly writing ones, so I don't think memcached would improve performance that much.

As for the partitioning scheme, I think I'm going to keep a separate /mysql partition (as long as there's no performance regression) just for the sake of having that partition intact in case of system failure (broken update, outside attack, overload etc.).

Thanks

[moberry]

Just because the queries are different every time doesn't the data can't be cached. It all depends on how much you plan on reading back. If you plan to read very little back then no, not a good solution.

Next question would be, if you never plan on reading the data back. What's the point of saving it?

[Mr.Carramba]

Mr.Carramba: We have thought of keeping the huge tables in RAM (temporarily) and writing them to disk when we are finished with them. It's definitely something we will try. Also, optimizing MySQL is indeed a very good idea, but what optimizations would you recommend? Any good resources on this topic?
moberry: memcached is only useful when doing lots of identical reading queries. My brother's program that will run on the server does pretty much the opposite, running always-different queries and mostly writing ones, so I don't think memcached would improve performance that much.

As for the partitioning scheme, I think I'm going to keep a separate /mysql partition (as long as there's no performance regression) just for the sake of having that partition intact in case of system failure (broken update, outside attack, overload etc.).

Thanks

http://forums.mysql.com/read.php?10,...416#msg-230416
basically
give more cache to the indexes
force index loading in memory
see docs for how-to

[Znupi]

moberry: This is not about the project, it's about the server and its configuration. We did look at memcached (thanks for the suggestion) and he decided it is not useful for his application (he worked with other caching systems before).
Mr.Carramba: Thanks!

[songshu]

as mentioned before, add RAM and as much as you can get in there, it will really increase your mysql performance. it would also help if mysql would have its own harddisk.

second, maybe not needed but its something i personally prefer, you could use something like Vserver to seperate different environments running next to each other without having a setback in performance in virtualisation overhead.

third, i used to tweak the hell out of the kernel when starting with Slackware 6 years ago, but nowadays i just use the stock kernel since the hours and days extra work are simply not worth the extra nano second you gain.

[moberry]

1. Definitely upgrade the ram. 2gb is pretty low for a 2gb+ high performance database. If money is an issue, and you're not doing tons of processing with the data, you could skimp out on the processor some to offset the cost.


2. Look into a performance enhancing data redundancy scheme (raid 10 or 0+1). This will of course provide some security for the data as well as increasing read and write speeds.

[Znupi]

moberry:
1. Yes, we will probably upgrade the ram more or less as soon as we get it. RAM is pretty cheap these days, so we can afford both the hefty CPU and another couple of RAM sticks.
2. Thanks for the suggestion, that would be a really good solution. Unfortunately the server is already ordered, but I'm having a gut feeling that even if it wasn't, the raid setup would be too costly.
songshu: Thanks, but I want to keep it relatively simple. Virtualising different environments sounds quite complicated and I don't think we're going to need it. And about the kernel, well, you said it yourself, it's not worth it . On top of that, if I tweak the kernel later repository upgrades may break the whole system, which I would like to avoid.

[songshu]

songshu:[/b] Thanks, but I want to keep it relatively simple. Virtualising different environments sounds quite complicated and I don't think we're going to need it. And about the kernel, well, you said it yourself, it's not worth it . On top of that, if I tweak the kernel later repository upgrades may break the whole system, which I would like to avoid.

The concept of virtualisation is definitely going to take some reading and trying before one can understand how simple it actually is, so just consider it a recommendation.

depending on how mission critical the server is, it does not hurt to optimize the kernel from an educational point of view, so if you have the time to do so i would say go for it, but if you want your life to be simple or do not have the time for failure then don't bother

[Mr.Carramba]

well if you can afford, get an SSD disk for your mysql partition, then it will not run but fly

[windependence]

Mr.Carramba: We have thought of keeping the huge tables in RAM (temporarily) and writing them to disk when we are finished with them. It's definitely something we will try. Also, optimizing MySQL is indeed a very good idea, but what optimizations would you recommend? Any good resources on this topic?
moberry: memcached is only useful when doing lots of identical reading queries. My brother's program that will run on the server does pretty much the opposite, running always-different queries and mostly writing ones, so I don't think memcached would improve performance that much.

As for the partitioning scheme, I think I'm going to keep a separate /mysql partition (as long as there's no performance regression) just for the sake of having that partition intact in case of system failure (broken update, outside attack, overload etc.).

Thanks

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. I know it's development, but mostly, you want a high performance MySQL server.

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.

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.

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.

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.

-Tim