Mysql server constantly crashing

Sticking this in the general queries as I couldn't see anywhere appropriate.

Running couple of stores on 1.3.8a
Windows Server

We started getting regular occurences of a connection to database failure. When this happened I logged into the control panel to find the mysql service had stopped. Once restarted stores came back up as expected. We were advised by hosting company to log into the server directly and set the mysql serivce to auto recover which we did.

Last week the connection to the database failed again but this time although the auto recover restarted mysql service the database connections were not restored. My host mentioned the possibillity of database corruption causing the issue and kindly offered to set an engineer onto it for €150 per hour !!!!!!! I simply don't have that kind of money to throw away :(

At the time we had to restore the server completely from a backup which was a big pain as it meant nearly a days worth of activity was wiped. Now the same has happened again with another restore needed.

Oviously we need to get to the root of the problem which is why is the mysql service crashing and I'm wondering would any of the community here have any pointers towards this.

Many thanks in advance

These sorts of problems aren't solved by uninformed guesses from people with no access to the server. They're solved by expert assessment of server and database logs, analysis of the database itself and trapping and examination of queries e.g. via the mysql slow query log. There's a reason why they can charge 150 euro an hour for this expertise.

What service are they providing for you? Is this shared hosting, a VPS, a managed server, an unmanaged dedicated server? Is the mysql server on the same server as your web server or a dedicated mysql server? Why the heck are you running your Zen Carts on a windows server? The underlying theme of (most of) these questions is trying to establish who is responsible for keeping your mysql server running effectively.

Hi Kuroi,

Thanks for the reply. I hear you loud and clear about the expertise and associated value / cost of it but I'm in the situation where I can hardly afford even 1-2 hours @ €150 nevermind giving an open ended off you go at €150 an hour to them.

It's a VPS server - It's windows because when we were starting out on shared hosting we took a windows option primarily due to not having a clue about the other hosting platforms and being familiar with windows operationally. (Granted this may have been an ill thought out and uniformed decision) When we needed a more reliable performance we were steered towards a VPS server and selected windows again. I've realised since then that windows is very much in the minority as a hosting platform. :dontgetit I suppose we're typical of most zen cart users - small business that can't afford inhouse pc / web expertise and with current economics can't afford to outsource either !

I was under the impression that the hosting company would have been responsible for the mysql server but they are saying that they are not responsible for corruptions to databases caused by software / programs we have added onto the server - and by this they mean zencart as this is the only addition. In fact as far as they are concerned even turning on the mysql service through the control panel was an addition to the basic configuration which they don't provide free support for. :censored: They mentioned the possiibllity of "inoodb" being corrupted but could not say for sure until I let them loose at the €150 an hour. As far as I know the mysql server is on the same VPS - path to database was localhost if memory serves.

I went off and googled corrupted mysqls services etc but I think I am getting results and solutions off linux / unix servers as none of it seems to correspond with what we have on our server. Basically just posting here hoping someone has had experience with windows servers and mysql service errors and may be able to point me in the right direction. Thanks again for the reply.

Who does upgrades on the server, not just mysql, but also php and any operating system patches? (Goes to who is responsible for maintaining the server)

Have you tried exporting the database and installing it under xampp? (Goes to whether the database is corrupt - they haven't actually provided any reason to believe that it is that rather than a problem elsewhere with the infrastructure - they just want you to pay to find out)

Do you have access to phpMyAdmin for your databases? If so, is the status tab enabled and yielding any clues?

Why don't you just ask your host to move you off Windoze and put it on a unix / linux box?

Far more reliable, and you have a greater degree of control over it. (CHMODS, etc).

Also php and MySQL live more happily on a linux server.

kuroi:

Who does upgrades on the server, not just mysql, but also php and any operating system patches? (Goes to who is responsible for maintaining the server)

Have you tried exporting the database and installing it under xampp? (Goes to whether the database is corrupt - they haven't actually provided any reason to believe that it is that rather than a problem elsewhere with the infrastructure - they just want you to pay to find out)

Do you have access to phpMyAdmin for your databases? If so, is the status tab enabled and yielding any clues?

Hi Kuroi,

The host would install any upgrades which as you infer should make them responsible for maintenance. Will have a look at xampp and see if I can install the databases there.

I do have access to phpMydmin and there is a status tab there (which I never knew about thank you for the pointer)

This MySQL server has been running for 0 days, 0 hours, 45 minutes and 48 seconds. It started up on Jul 03, 2010 at 03:13 PM.

The following are in the red...

Slow_queries 287 The number of queries that have taken more than long_query_time second

Innodb_buffer_pool_reads 306 k The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read.

Innodb_row_lock_time_avg 11 k The average time to acquire a row lock, in milliseconds.
Innodb_row_lock_time_max 51 k The maximum time to acquire a row lock, in milliseconds.
Innodb_row_lock_waits 18 The number of times a row lock had to be waited for.

Handler_read_rnd 34 k The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.

Handler_read_rnd_next 827 k The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

Created_tmp_disk_tables 145 The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.

Key_reads 4,651 The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.

Sort_merge_passes 13 The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.

Opened_tables 3,115 The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.

Would the above figures be normal for a zencart installation from your experience ?

The things that I'd pick out of that data are that your mysql server has only been running for 45 minutes - did you restart it? Or is this further evidence of it crashing frequently.

The other is the number of slow queries. 287 in 45 minutes is a lot. I prefer that number to be 0. Though we don't know what the threshold is set to.

As a comparable, the (sorry, site offline) site mysql server has been running for 13 days, during which it has handled a 377 million (yes million) queries of which 41 were flagged as slow.

I'm afraid you need to get inside your slow query log. It is possible that it is you tying up the mysql server and crashing it.

Please note DrByte's request. Your version of Zen Cart and the mods that you have installed are likely to be vital information going forward if this does prove to be an issue with your Zen Cart.

As an aside, the default "long_query_time" is 10 seconds. If your server is using the default then your number would suggest an average of slightly more than 6 a minute, which would mean that you have excessively long queries running concurrently.

kuroi:

The things that I'd pick out of that data are that your mysql server has only been running for 45 minutes - did you restart it? Or is this further evidence of it crashing frequently.

The other is the number of slow queries. 287 in 45 minutes is a lot. I prefer that number to be 0. Though we don't know what the threshold is set to.

As a comparable, the (sorry, site offline) site mysql server has been running for 13 days, during which it has handled a 377 million (yes million) queries of which 41 were flagged as slow.

I'm afraid you need to get inside your slow query log. It is possible that it is you tying up the mysql server and crashing it.

Please note DrByte's request. Your version of Zen Cart and the mods that you have installed are likely to be vital information going forward if this does prove to be an issue with your Zen Cart.

Hi Kuroi,

Yes it had crashed and been reset automatically by windows. I believe I have to enable a slow query log before it starts recording ?

Didn't see DrBytes request there but certainly will post up specs / send a pm if I find it's a zencart specific issue.

Update,

I am fairly sure that the crash was not being caused by Zencart. Turned out there was an abandoned message board on a domain hosted on our server. Although not linked from the parent site at all there had been 300,000 spam posts on it. Completely deleted the forum / database and site itself and we've been up for 19 hours now with no crash and no further slow queries.