MySQL Database crashing server

meetagrawal · March 31, 2011, 12:00am

I am currently using hostmonster.com. They shut down my site because the MySQL database is causing the server to go down quite a bit. They informed me to look at the log and fix the issue, but I am not a programmer. They said it would require optimizing or repairing it.

What is the best solution to fix this so I can get my website back up and running again? Is there some CS update or module I could install to fix the issue?

Heres the last log. Thanks in advance!

[QUOTE]# Wed Mar 30 20:00:34 2011

Query_time: 236.698347 Lock_time: 26.228734 Rows_sent: 1 Rows_examined: 1

use tfgfurni_divyaproducts;

SELECT * FROM cscart_sessions WHERE session_id = ‘fpjfmm9oc0a00atiodrl4vv3q2’ AND area = ‘C’

Wed Mar 30 20:00:34 2011

Query_time: 236.698280 Lock_time: 26.438830 Rows_sent: 1 Rows_examined: 1

SELECT * FROM cscart_sessions WHERE session_id = ‘fpjfmm9oc0a00atiodrl4vv3q2’ AND area = ‘C’

Wed Mar 30 20:00:34 2011

Query_time: 236.708752 Lock_time: 25.749205 Rows_sent: 1 Rows_examined: 1

use tfgfurni_divyaproducts;

SELECT * FROM cscart_sessions WHERE session_id = ‘1ltl2tgk92oasd75f81sioieq0’ AND area = ‘C’

Wed Mar 30 20:00:34 2011

Query_time: 236.708650 Lock_time: 26.051466 Rows_sent: 1 Rows_examined: 1

SELECT * FROM cscart_sessions WHERE session_id = ‘1ltl2tgk92oasd75f81sioieq0’ AND area = ‘C’

Wed Mar 30 20:00:34 2011

Query_time: 236.708126 Lock_time: 26.438741 Rows_sent: 1 Rows_examined: 1

SELECT * FROM cscart_sessions WHERE session_id = ‘iq184h4b7v3o920nkiuclt7mi4’ AND area = ‘C’

Wed Mar 30 20:00:34 2011

Query_time: 236.709043 Lock_time: 26.051691 Rows_sent: 1 Rows_examined: 1

SELECT * FROM cscart_sessions WHERE session_id = ‘1ltl2tgk92oasd75f81sioieq0’ AND area = ‘C’

Wed Mar 30 20:00:34 2011

Query_time: 236.709124 Lock_time: 25.749245 Rows_sent: 1 Rows_examined: 1

SELECT * FROM cscart_sessions WHERE session_id = ‘1ltl2tgk92oasd75f81sioieq0’ AND area = ‘C’

Wed Mar 30 20:00:34 2011

Query_time: 236.709323 Lock_time: 25.749380 Rows_sent: 1 Rows_examined: 1

SELECT * FROM cscart_sessions WHERE session_id = ‘1ltl2tgk92oasd75f81sioieq0’ AND area = ‘C’

Wed Mar 30 20:00:36 2011

Query_time: 224.015123 Lock_time: 64.787279 Rows_sent: 0 Rows_examined: 8

use tfgfurni_divyaproducts;

SELECT location FROM cscart_block_positions WHERE object_id = 32

Wed Mar 30 20:00:36 2011

Query_time: 224.015185 Lock_time: 64.787310 Rows_sent: 0 Rows_examined: 8

SELECT location FROM cscart_block_positions WHERE object_id = 32

Wed Mar 30 20:00:36 2011

Query_time: 224.207776 Lock_time: 66.741216 Rows_sent: 0 Rows_examined: 8

use tfgfurni_divyaproducts;

SELECT location FROM cscart_block_positions WHERE object_id = 32

Wed Mar 30 20:00:47 2011

Query_time: 6.815876 Lock_time: 5.012188 Rows_sent: 1 Rows_examined: 1

use tfgfurni_divyaproducts;

SELECT * FROM cscart_sessions WHERE session_id = ‘iq184h4b7v3o920nkiuclt7mi4’ AND area = ‘C’

Wed Mar 30 20:04:17 2011

Query_time: 210.323644 Lock_time: 3.199283 Rows_sent: 0 Rows_examined: 3

use tfgfurni_divyaproducts;

REPLACE INTO cscart_stored_sessions SELECT * FROM cscart_sessions WHERE expiry < 1301536836[/QUOTE]

clips · March 31, 2011, 12:00am

I wouldn’t be able to help, but I’m sure others will need more information. The biggest thing they would need is what version of CS-Cart you are using. I guess it may be in some of what you posted, but I’m not familiar enough with the code to know.

tbirnseth · March 31, 2011, 12:00am

I would guess that you do not have a primary key defined for your cscart_sessions table. This will be read each time a page is loaded and if somehow you’ve managed to get rid of the primary key, it will grow HUGE and no one will be able to do anything useful.

The keys for this table should look something like:

PRIMARY KEY (`session_id`,`area`), KEY `src` (`session_id`,`expiry`), KEY `expiry` (`expiry`)

meetagrawal · March 31, 2011, 12:00am

How would I be able to check if I do not have that key defined?

s-combs · March 31, 2011, 12:00am

I’ve seen this often during system backups and it’s an indication that the server is under very heavy load. When the load lessons these queries will finally drop off and everything goes back to normal.

If I am correct about this then there is really nothing you can do about it. It is strange though because, whenever I notice this happening it is always only cs-cart related queries hanging from different accounts. The other scripts running queries seem to work as normal…

meetagrawal · March 31, 2011, 12:00am

[quote name=‘S-Combs’]I’ve seen this often during system backups and it’s an indication that the server is under very heavy load. When the load lessons these queries will finally drop off and everything goes back to normal.

If I am correct about this then there is really nothing you can do about it. It is strange though because, whenever I notice this happening it is always only cs-cart related queries hanging from different accounts. The other scripts running queries seem to work as normal…[/QUOTE]

Thanks!

The only issue is that Host Monster is telling me I need to get it fixed, and talk to one of their admins before they put it back online. I don’t know if waiting will help fix this? Also, I’d like to avoid this problem occurring down the road. Can’t afford to have my shopping cart down.

s-combs · March 31, 2011, 12:00am

I’m afraid that this will be an ongoing problem with Hostgator on their shared servers because CS-Cart is a very resource heavy script. They have disabled the accounts of many other CS-Cart users for the same reasons.

struck · March 31, 2011, 12:00am

[QUOTE]The only issue is that Host Monster is telling me I need to get it fixed[/QUOTE]

Host Monster doesn’t eat up the competition, only Hostgator does!

s-combs · March 31, 2011, 12:00am

[COLOR=black]I’m sorry for mentioning Hostgator above… I was confused because of other recent chats about the same thing with them :rolleyes:

I don’t know if Host[/COLOR][COLOR=DarkRed]Monster[/COLOR] [COLOR=Black]is as strict with resources as Host[COLOR=DarkRed]Gator [COLOR=Black]is. [/COLOR][/COLOR][/COLOR]

meetagrawal · March 31, 2011, 12:00am

Thanks for the responses!

So it looks like the issue is more with Host Monster rather than CS-Cart? Someone suggested VPS hosting for me. Any VPS recommendations or other hosting companies that might offer me better services and reliability?

struck · March 31, 2011, 12:00am

Well, then contact S-Combs

[url]http://securecarthost.com/[/url]

You don’t necessarily need a VPS, you simply need the proper resources to handle your site.

tbirnseth · March 31, 2011, 12:00am

[quote]

You don’t necessarily need a VPS, you simply need the proper resources to handle your site

[/quote]

Best line in several current threads of discussion!!!

struck · April 1, 2011, 12:00am

Thanks for the compliment Mr. Birnseth!

(After 1,600 + posts, I knew that I would eventually come up with something useful!)

s-combs · April 1, 2011, 12:00am

I don’t think he meant it that way Struck… There have recently been numerous recommendations of VPS servers from people who don’t completely understand the requirements and, all hell will break loose if another exploit is found in this script.

@ meetagrawal

Our prices are a bit higher than budget hosts but, we don’t pack nearly as many clients on a server and our resource limitations are much more relaxed.

PM me if you have any questions

struck · April 1, 2011, 12:00am

[QUOTE]I don’t think he meant it that way Struck… There have recently been numerous recommendations of VPS servers from people who don’t completely understand the requirements and, all hell will break loose if another exploit is found in this script.[/QUOTE]

It’s all good S-Combs, I did not take it the wrong way nor was I offended in any way, quite the contrary! Like I say, it is all good!

tbirnseth · April 2, 2011, 12:00am

[quote]

don’t think he meant it that way Struck… There have recently been numerous recommendations of VPS servers from people who don’t completely understand the requirements and, all hell will break loose if another exploit is found in this script.

[/quote]

Actually, I did. Sometimes I try to provide too much information and rationale for things I state. His statement said it pretty simply.

tom437 · April 3, 2011, 12:00am

[quote name=‘meetagrawal’]I am currently using hostmonster.com. They shut down my site because the MySQL database is causing the server to go down quite a bit. They informed me to look at the log and fix the issue, but I am not a programmer. They said it would require optimizing or repairing it.

What is the best solution to fix this so I can get my website back up and running again? Is there some CS update or module I could install to fix the issue?

Heres the last log. Thanks in advance![/QUOTE]

Hello,

Clear your Statistics and optimize your database, than from your cpanel repair your MySQL database and you will be good.