Dedicated MySQL server
I am planning to setup a separate mysql server, so I need some advises from those who have had experience with hugh load of mysql connections/requests.
I have tried one (1) dedicated mysql server and one (1) dedicated apache web server.
Problem: MySQL server ran out of resources (huge cpu load) before the apache web server, so the apache web was unable to connect to the mysql server and outputed something like "Too many connections".
1. How would you setup the mysql server to handle multiple webservers connections/requests? I mean how could a single mysql server handle many webserver making thouand and thousand of requests/connections every second?
For mysql, I am planning to install an OS (linux) on 1 hard drive, and mysql on a PowerVault. Like if the os crashes, I can plug the PowerVault of another server and get the mysql server back online instantly like plug and play. That way I can setup a clean os (linux) server, plug in the PowerVault to the server, and it works instantly.
2. Am I able to do this?
If I am planning to do daily backup of 50GB+ database, what would be the best approach and what backup software is recommended? Same for restoring a hugh database.
1. are you sure the bottleneck on the mysql server was the CPU? before i jump to buy another server, i would look to make sure i have enough memory (RAM) installed to hold all of the indexes for all of the frequently used tables and that all of my queries are actually using indexes.
3. (re: your backup question) use innodb tables and inno hot backup. http://www.innodb.com/
The server that I had for mysql was a dell dual 2.8Ghz Xeon cpu, 4GB Dell RAM, 80 SATA HD. I have tried using the huge.my.cnf/conf, but that didn't help much either. Since then, I switched from a dedicated mysql server to each webserver hosting their own database. Now I want to retry a dedicated server method again, because 1) I no longer want to go to each server and backup each database, so I just have to go back with a 1 single database again. 2) cheaper for me to purchase one single strong server for mysql for all single cpu webservers. I might want to use the 15,000rpm scsi hard drive this time. I'm not sure if one dedicated mysql server is enough or will it be a bottleneck in a year. Do you think the above server with SCSI hd is enough to handle huge amount of requests/connections from 10-20 webservers.
If I'm able to install an os (linux) on a single hard and mysql on the powervault, then I'll go with this configuration. 1) If the server becomes a bottleneck, all I need to do is purchase another server, install os, and connect the powervault to the new server. (plug and play). 2) If the os gets out-dated, I can install the latest os on another server, connect the powervault, and it goes back online. 3) Fearless if the os crashes or doesn't boot all the way.
How hard would it be to convert a hugh database (MyISAM) with million of tables to INNODB?
Right now I am looking to purchase a powervault. This will be my first powervault. Hopefully, it'll not be hard to setup.
ok, i see where you are going.
are you hosting provider, or in a situation where you can't optimize the queries? i have a hard time believing that mysql would kill a server with 4GB of ram if the queries are at least somewhat optimized.
Let's assume I made a mistake somewhere on that dedicated mysql server without thoroughly investigating it, since I've heard many people running a hugh database with one dedicated mysql server. Typically, would one dedicated server (dual 2.8Ghz xeon, 4GB RAM, 80SATA HD) be enough to handle 50GB-100GB (database size), million of tables inside the database, and constantly have (lets say) million of requests/connections at the same time.
Right now I'm ok on the webservers, because I have tons of single cpu servers which should be more than enough to handle www. I just want to make sure that a single dedicated mysql server wont become a bottleneck.
i think moving the mysql servers off the web servers may be a good idea. however, i don't think one super-beefy server is the way to go. i think many cheap servers or a couple medium sized servers is better. i would also analyze who is using how many resources on your mysql servers and approach those users about figuring out ways o reduce their usage.
I don't see how a dual xeon 2.8 with 4gig of ram and a sata HD would choke uless you were asking far too much of it. Infact I would say that the install of the OS might have been less than optimal. Was it a Windows 2000/2003 server install? Was it a BSD install? Was it a Linux Install? If linux what distro, flavor?
Also, because a couple thousand www hits might not tax a server doesn't mean that it won't generate mysql db hits that could bring the pain to even the largest of servers. 10000 mysql hits in a short amount of time could hurt you if you aren't using fibre channel especially if the queries aren't optimized and the indexes are huge.
It was a redhat linux. I forgot which version it was. Maybe it was my mistake somewhere. However, I've observed that my mysql server ran out of resources (Too many connections) before my www server. From reading longneck's posts, I think I should have 2 mysql servers and 1 www server if I had 3 servers, 3 mysql servers and 1 www server if I had 4 servers.
Not sure how long before my www server becomes a bottleneck for serving www and php only. Unless people on this forum know, I'd have to sit and observe to see how much a single 3Ghz w/HT, 1 or 2GB ram, SATA hard drive could handle before it bottles down.
it could be that the mysql server is actually capable of handling more transaction, but that your connection limit was too low.