Never Ending MySql problems

I do not have the option to post in forums, administrator please do not delete this blog.

I thought 7.0.4 would stop the SQL issues. They're a bit better but have not stopped.

I've tried all the speed tips(http://www.boonex.com/unity/blog/entry/speed_problems_on_7_0_2)


MySql services goes down way too often lately. This causes all my websites to not function. Yesterday it happened 13 times, sometimes it's more than 13 times a day. Screenshot on http://www.sondeza.com/art/mysql_problems.jpg


My ISP has to constantly restart the MySQL services.

Below a reply from my ISP

"The reason this is (gnerally) done, is that the msql service gets hung up, (using up all the available CPU), that then causes the web server to stop responding. We (our techs), see an alarm on our monitoring system and manually restart the service.

Here are some suggestions to consider:


A) Migrate your SQL back-end to an additional(independent) server just to run your databases.


The current server you have may be enough to handle the load of doing SQL operations only, so you wouldn't (necessarily) need an another "high end" server to push out the web content.


B) Alternate Database back-end. (There may be other SQL databases that are better suited to what you are trying to do. Postgress is once I have worked with before in the past, and I believe it my "scale" better on multi CPU/CORE setup.

C) Another (possibility is) that he way the queries to the databases themselves may be able to be optimized in such a way that doesn't create such a huge load one them. I'm not a web developer nor a DBA, but I have many colleges (in the business) and I constantly hear them gripe about inefficient queries making the SQL server do "way too much work" to return what (often could have been written) in much list intensive way to get the same query answer. (I only mention this because I hear about it from developers who are also friends I "talk shop with") I am not suggesting the reason for the issues you are having are due to poorly written queries. However it might be worthwhile to see if they can be easily be modified to be less server load intensive.

D) The databases on CO103 are (currently) only using up (just) under 3GB of actual space to hold its data.
If the problem is IO bound (too much mixed workload) reads & writes to a single disk can cause enormous IO bottle-necking. I don't know that this would help (considerably) in your particular case; but for $99-$130 (newegg/amazon) you can get a 50GB OCZ Vertex 2 SSD.

Move the databases and, /tmp (and perhaps even swap) onto the to a $120 50GB SSD drive. (Non recurring outlay of money, recurring benefits).
I have two of these in my computer as a stripe. I consistently get 400-350MB/second read and write speeds. A single drive will sustain 150MB/second easily.

They (good SSDs) also offer excellent performance under mixed workload (40K IOPS per second advertised). If it doesn't improve the issue dramatically, the drive can still be left in production on the existing server. Should you find you want to turn that into a "dedicated" SQL server. At that point offloading everything but SQL services off of that machine and everything else, onto a lower cost "web-server only" dedicated server would be another suggestion.

Here are links to the best and fastest 50GB SSD drives available: (that I know of, price, performance, reputation, and my own personal experience)

http://www.newegg.com/Product/Product.aspx?Item=N82E16820227528

http://www.amazon.com/gp/product/B003DS7IGA



The SQL services get restarted because the SQL server can't keep with the requests the web server is making, the failed http service shows up on our monitoring system. The web site stops loading. Then, one of our techs manually restarts the services, so that the site will come back up.

I understand that it is a BIG MACHINE with LOTS of memory installed and 4 CPU cores. It seems like it should have no problems, but obviously it is.

Please consider some of my suggestions, and help us come up with a plan to keep your sites running smoothly, and keep you and your customers happy."

Quotation is on http://www.sondeza.com/art/quotation.jpg

I'm thinking of going with option D and my ISP sent me a quotation for $500.00 buy and implement the hardware. $500.00 is almost all the revenue that we generate per month. Do you think that option D is the best option or is there something else that I'd need to do?