Never Ending MySql problems

Taumorea posted 30th of November 2010 in Community Voice. 6 comments.

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?




 
Comments
·Oldest
·Top
Please login to post a comment.
Zarcon
I do not understand why you "do not have the option to post in the forums" since you are an advanced member. Just go to:

http://www.boonex.com/unity/forums/

Chose the correct category from the left menu (ex. Dolphin7)
Then choose 'New Topic'
Taumorea
Ooooh!

I see what you're talking about. Just had a blonde moment there.

Thanks.
Nathan Paton
The temptation...

I also don't know why you can't post this in the forums. Advanced members have the necessary privileges. If you're receiving an error, please say so and post what it is.
AlexT
Have you updated to 7.0.4 version ? - it has many speed improvements.
Also how many online members do you have when mysql server stops working ?

Please can you send me access to your server to check the problem more carefully ?
I need root access to do it... maybe it is just configuration issue.
Taumorea
Hi Alex,


Yes, I've upgraded to 7.0.4

I cannot say for sure how many member are online when MySQL gives up, but we got about 31 628 members.

I'll inbox you my server details shortly.

Thank you.

Rgds,
T
AlexT
The main problem is that your cronjobs is not working right, and because of this some tables are not pruning and grow a lot !
sys_banners_shows - 1.8 GiB
sys_sessions - 422.1 MiB
bx_spy_data - 262.5 MiB
bx_spy_friends_data - 74.2 MiB
sys_sbs_queue - 65.6 MiB
bx_photos_views_track - 135.7 MiB
bx_videos_views_track - 34.6 MiB
sys_profile_views_track - 22.2 MiB

I suggest to truncate all above tables manually (spy data will be lost but it will be collected again very soon, it will not affect see more any critical functionality) and make sure that cron job is running correctly.

I suggest to run pruning process when your server is not loaded, to change pruning process start time change `time` field in `sys_cron_jobs` table in the following record (clean /cache/ folder after this change):
cmd 0 0 * * * BxDolCronCmd inc/classes/BxDolCronCmd.php

Also I suggest to increase the following settings in your MySQL server: tmp_table_size and max_heap_table_size
set it to 256Mb to 1Gb depending of free RAM on the server

Additionally your server can be speed-up a lot, please refer to these tutorials:
http://www.boonex.com/trac/dolphin/wiki/HostingServerSetupRecommendations (especially webserver section)
http://www.boonex.com/trac/dolphin/wiki/TutorialCachingTypes
http://www.boonex.com/trac/dolphin/wiki/TutorialUnderstandingCacheEngines
 
 
Below is the legacy version of the Boonex site, maintained for Dolphin.Pro 7.x support.
The new Dolphin solution is powered by UNA Community Management System.
PET:0.14147806167603