What do you mean by "table crashing"??... What kind of error do you have?...

The only way to get a mySQL daemon stalled when using scripts, is when you try to INSERT overflow data...
For example, let's say that you have a TEXT field, but in your scripts you try to INSERT a variable, having a length > 65535 characters... So you will need to use the substr() function.

Hope this helps~.

My main table has been crashing as well.

I beleive it is due to a lot of INSERTS and I need
to start tracking this down.

It crashes about 2-3 a week so that's not to bad but
really shouldn't be happening at all...

The issue is that my table is indexed and I think
all the inserts and possibly deletes is causing the index
to be out of sync...

allworknoplay wrote on Sat, 08 September 2007 17:56

The issue is that my table is indexed and I think all the inserts and possibly deletes is causing the index to be out of sync...

How big is your index?..
What kind of "crash error" do you have?.. I really think it has to do with some overflow INSERTs...

You can use "DELETE QUICK" instead of "DELETE".

jcn50 wrote on Sun, 09 September 2007 11:47

How big is your index?.. What kind of "crash error" do you have?.. I really think it has to do with some overflow INSERTs... You can use "DELETE QUICK" instead of "DELETE".

Yes! That is what I meant to say, I'm INSERTing so much
it's having a hardtime keeping up. Atleast that is my theory.

I think it is the sporadic "spikes" that causes the issue.
The data flow for the most part is pretty even but there are
times when data gets put in by our system at the same time
and perhaps during that peak, the DB can't keep up...

My index size for the specific table that crashes often
is about 113 megs.

I will look into DELETE QUICK, I wasn't aware of that command.

My key_buffer is set to 600 megs...

Again: how do you know that your TABLE IS CRASHING?..

allworknoplay wrote on Sun, 09 September 2007 16:36

Yes! That is what I meant to say, I'm INSERTing so much it's having a hardtime keeping up. Atleast that is my theory.

What I meant by "overflow INSERT" is something like this:
- having a table with a TEXT (or BLOB) field/column
- INSERT a variable, let's call it $in
- make the mySQL query with a PHP script using $in [where it should be using substr($in,65530)]
- your mySQL query crash (not the server, only the query) so your INSERT is not recorded into the DB
- next / others queries are proceeded.

Add a full mySQL errors' report in your scripts.

Also, you can try the mySQL query "SHOW PROCESSLIST" or "SHOW FULL PROCESSLIST" to see if there's a lot of queued INSERT.

allworknoplay wrote on Sun, 09 September 2007 16:36

I think it is the sporadic "spikes" that causes the issue. The data flow for the most part is pretty even but there are times when data gets put in by our system at the same time and perhaps during that peak, the DB can't keep up...

The DB can keep up, simply because all INSERTs are put on a waiting list, but some scripts may hang the server (waiting for the INSERT query to complete), and then timeout.
In that case you can use "INSERT DELAYED": your scripts will not wait for the INSERT to be completed anymore, and your mySQL server will execute the INSERT query when the load will be lower.

allworknoplay wrote on Sun, 09 September 2007 16:36

My key_buffer is set to 600 megs...

The cumulative size of all your indexes are > 113 MB?...
How much physical RAM do you have?

jcn50 wrote on Sun, 09 September 2007 20:18

Again: how do you know that your TABLE IS CRASHING?..

Thanks for taking the time to respond to my questions I really
appreciate it!!

I have one table called "data_day" that is heavily used.
Everyday I check the row count and file size with a script
I wrote. The data in that table should only be within a 36 hour
period so I have another program that deletes data older than 36 hours.

When the table crashes, the many INSERTS continue to still happen but the DELETES can't. So the table size gets really big.
When I see this, I jump on the server and type:
CHECK TABLE data_day

That's when it tells me that the INDEX's are corrupted because
some numbers doesn't match another number.

"REPAIR TABLE data_day" fixes the issue but I have to do this almost every 2-3 days...

The error log size should be 0, so now I have a cron job
that checks it and if anything goes into it, I get sent
an email. This way I can try to fix the problem asap, instead
of finding out hours later...

jcn50 wrote on Sun, 09 September 2007 20:18

What I meant by "overflow INSERT" is something like this: - having a table with a TEXT (or BLOB) field/column - INSERT a variable, let's call it $in - make the mySQL query with a PHP script using $in [where it should be using substr($in,65530)] - your mySQL query crash (not the server, only the query) so your INSERT is not recorded into the DB - next / others queries are proceeded. Add a full mySQL errors' report in your scripts. Also, you can try the mySQL query "SHOW PROCESSLIST" or "SHOW FULL PROCESSLIST" to see if there's a lot of queued INSERT.

My table does not have TEXT or BLOB field/column.
I'm not using substr($in,65530) however that sounds like a good
idea. I only have 8 columns that consist of: INT, VARCHAR and BIGINT. Only the first column is a primary key and another is INDEXED.

I have tried SHOW PROCESSLIST but I have not tried the FULL PROCESSLIST so I will give that a shot as well...

How would you suggest going about adding a mysql error's report
in my scripts? Also can you elaborate on the "substr($in,65530)"
I understand it is limiting the actual data to 65K characters but what is the exact reason for doing so and why would more characters cause a crash?

jcn50 wrote on Sun, 09 September 2007 20:18

The DB can keep up, simply because all INSERTs are put on a waiting list, but some scripts may hang the server (waiting for the INSERT query to complete), and then timeout. In that case you can use "INSERT DELAYED": your scripts will not wait for the INSERT to be completed anymore, and your mySQL server will execute the INSERT query when the load will be lower.

I've heard about the INSERT DELAYED may make things worse
in the case of a crash and repairing the table would be a little
bit more difficult so I'm a little concerned about playing around with the INSERT DELAYED command...

jcn50 wrote on Sun, 09 September 2007 20:18

The cumulative size of all your indexes are > 113 MB?... How much physical RAM do you have?

113MB is the exact size of all the *.MYI's when I add them
up...

The server is a Dual Core Xeon with 2GIG's RAM.


Again, thanks for taking the time to chat with me about this...I've tried to do as much homework as I can on mysql
and at the end of the day, you just can't know everything right?

Oh and my MySQL version is 5.0.23

Hello All,

thanks for reply.

I am using phpMyAdmin frontend to access mysql databases.
Table crash mean when I select database and see the list of tables. In action with specific tables status displayed "in use" instead of operation command like browse/select/insert/properties..etc.

I set manual restrictions while insert records into tables. trim the contact as per field size. though atleast one table i can see daily with status "in use".

anyone have idea can i trace the problem using error log files??
where and how can i read:
-->The Slow Query Log
-->The General Query Log

Please help me.

regards.

-Rashmi

allworknoplay wrote on Mon, 10 September 2007 02:43

Thanks for taking the time to respond to my questions I really appreciate it!!

You're welcome .

allworknoplay wrote on Mon, 10 September 2007 02:43

When the table crashes, the many INSERTS continue to still happen but the DELETES can't. So the table size gets really big. When I see this, I jump on the server and type: CHECK TABLE data_day That's when it tells me that the INDEX's are corrupted because some numbers doesn't match another number.

It will be good if you can copy/paste here what the server exactly tells you.
Are your INSERT and DELETE queries done from the same script? Or do you have multiple scripts working on your DB at the same time?

allworknoplay wrote on Mon, 10 September 2007 02:43

"REPAIR TABLE data_day" fixes the issue but I have to do this almost every 2-3 days...

I've never done a REPAIR TABLE in my life, but let's check it:
1) Do you have a WESTERN DIGITAL hard drive?... I got many problems with this hard drive brand in the past, so it may be a hardware problem. I know no WESTERN DIGITAL hard drive that worked more than 5 years ... MAXTOR ones aren't reliable too.
2) Most likely, I think the problems lies in the scripting. For example: let's imagine that your scripts managed to INSERT two same PRIMARY KEYs in the DB (it seems impossible, however I've done it quite a few times!), the mySQL server is likely to behave weirdly.
Thus said, never use a TIMESTAMP value as a PRIMARY KEY.
So the only way to know what's happening, it's to create its own error_log reporting (see below).

allworknoplay wrote on Mon, 10 September 2007 02:43

I only have 8 columns that consist of: INT, VARCHAR and BIGINT. Only the first column is a primary key and another is INDEXED.

Considering what you wrote, it is likely that the VARCHAR column can be subject to an overflow.
Numeric columns like INT or BIGINT have an integrated overflow-protection. For example: if you try to put "3000000000" into an UNSIGNED INT column, the mySQL will automatically record "2147483647" (the max number).

allworknoplay wrote on Mon, 10 September 2007 02:43

I have tried SHOW PROCESSLIST but I have not tried the FULL PROCESSLIST so I will give that a shot as well...

SHOW PROCESSLIST and SHOW FULL PROCESSLIST are the same. Some people have just an habit to know only one, so I wrote both. It's not that important if you only do SHOW PROCESSLIST.
What is important is to see the load of your server, and to check what kind of query is taking a long time.
Now looking at you wrote, it seems that the DELETE are taking a bunch of time. The only way I can think of fixing this is to use DELETE QUICK. Because when you do a DELETE, the server is likely to copy all of your table, without the row you wanted to DELETE... Very resources consuming, especially if you do a lot of INSERTs thereafter, DELETE QUICK is a must!

allworknoplay wrote on Mon, 10 September 2007 02:43

How would you suggest going about adding a mysql error's report in my scripts? Also can you elaborate on the "substr($in,65530)" I understand it is limiting the actual data to 65K characters but what is the exact reason for doing so and why would more characters cause a crash?

You need to create a function, that writes to a file (or sends an email) when a query failed.

There are multiple ways to do it, but it could look like this:

function error($detailed_error,$which_query,$where)
{
/* let's say you log errors in a file */
$f=fopen("C:\\error_log.txt","a");
fwrite($f,"The detailed error is: ".$detailed_error."\r\n\r\n");
fwrite($f,"The query it tried to do is: ".$which_query."\r\n\r\n");
fwrite($f,"Line in the script where it happened: ".$where."\r\n\r\n*****************\r\n\r\n");
fclose($f);
}

One you're satisfied with your function, you have to put it in your script, after EVERY mySQL query! You call it like this:

$query = "INSERT whatever you want";
$result = mysql_query($query);
if ($result === FALSE) error(mysql_error(),$query,__LINE__);

mysql_error() is an integrated function which gives you the full text of the error, whereas __LINE__ is a constant in PHP.

It's time for some scripts update .

allworknoplay wrote on Mon, 10 September 2007 02:43

I've heard about the INSERT DELAYED may make things worse in the case of a crash and repairing the table would be a little bit more difficult so I'm a little concerned about playing around with the INSERT DELAYED command...

That's because we don't know (yet) what causes the crash. But if you have, let's say 20 computers accessing the same DB, and having an INSERT every 0.5 second, believe me INSERT DELAYED is good for the employee looking at her screen and saying "why the computa is soo slow taday " D.
More seriously: let's stick with the INSERT for now, and see if the error() function gives more details about the crash.

allworknoplay wrote on Mon, 10 September 2007 02:43

113MB is the exact size of all the *.MYI's when I add them up... The server is a Dual Core Xeon with 2GIG's RAM.

I don't see any reason for having a huge key_buffer_size of 600 MB then... You can cut-it off half to 300 MB.

allworknoplay wrote on Mon, 10 September 2007 02:43

Again, thanks for taking the time to chat with me about this...I've tried to do as much homework as I can on mysql and at the end of the day, you just can't know everything right?

Sure, we are all here to learn! cool:

rashmirani wrote on Mon, 10 September 2007 05:18

anyone have idea can i trace the problem using error log files?? where and how can i read: -->The Slow Query Log -->The General Query Log

See my previous post for error logging. Apart from SHOW PROCESSLIST, you can make your own "slow query log" taking a timestamp before and after your query.

For example:
$query = "whatever you want to INSERT / DELETE";
$time_before=date("U");
$result = mysql_query($query);
$time_after=date("U");
// let's say you want to have all the queries that took more than a minute:
if ($time_afer-$time_before>60) error("Slow query",$query,__LINE__);


Now I'm gonna rest after I took on your job!

jcn50 wrote on Mon, 10 September 2007 04:45

It will be good if you can copy/paste here what the server exactly tells you. Are your INSERT and DELETE queries done from the same script? Or do you have multiple scripts working on your DB at the same time?

I didn't get a chance to save the output since I just ran
REPAIR TABLE, next time,(hoping there is no next time) it happens I'll post it here.

Yes both the INSERT/DELETE are done within the same script. Basically I have about 4 scripts that are accessed by our systems about 5 times a second. Each access creates MANY inserts and towards the end a DELETE to clean things up. The INSERTS vary depending on the data. They can easily be about 20 inserts a second within the same script or 1000 /sec within the same script. Now if you multiply that by how often per second the script gets accessed, I get a sense that the scripts are almost all fighting for INSERT resources.

I think I will modify the scripts to use BULK INSERT and see if that does any difference...I think it will!!??

jcn50 wrote on Mon, 10 September 2007 04:45

I've never done a REPAIR TABLE in my life, but let's check it: 1) Do you have a WESTERN DIGITAL hard drive?... I got many problems with this hard drive brand in the past, so it may be a hardware problem. I know no WESTERN DIGITAL hard drive that worked more than 5 years ... MAXTOR ones aren't reliable too. 2) Most likely, I think the problems lies in the scripting. For example: let's imagine that your scripts managed to INSERT two same PRIMARY KEYs in the DB (it seems impossible, however I've done it quite a few times!), the mySQL server is likely to behave weirdly. Thus said, never use a TIMESTAMP value as a PRIMARY KEY. So the only way to know what's happening, it's to create its own error_log reporting (see below).

1) It's an HP DL320 DualCore Xeon so I don't know what brand the disk is. I'm sure it's HP-something. It's a SATA drive with hardware RAID 1.
I do agree with you though, both Maxtor and WD suck! I've used them in my experience and they fail often..Seagate is the best.

2)I think you're right. I truly think it's the scripting that's not making the best use of mysql syntax.

I will take your advice and go over EVERY INSERT command as well as DELETE command, basically any query, and create a condition to output to an error log file if there's a problem.
My scripts are all over 1000 lines of code so it's going to take some time and careful planning to make sure I don't mess anything up!!!

jcn50 wrote on Mon, 10 September 2007 04:45

Considering what you wrote, it is likely that the VARCHAR column can be subject to an overflow. Numeric columns like INT or BIGINT have an integrated overflow-protection. For example: if you try to put "3000000000" into an UNSIGNED INT column, the mySQL will automatically record "2147483647" (the max number).

I will go ahead and use the substr function. Do you think by using this for all the VARCHAR's it will be a strain or cause more memory usage for PHP?

jcn50 wrote on Mon, 10 September 2007 04:45

DELETE QUICK is a must!

Yes I am starting to become a believer in this function!!!

jcn50 wrote on Mon, 10 September 2007 04:45

You need to create a function, that writes to a file (or sends an email) when a query failed. There are multiple ways to do it, but it could look like this: function error($detailed_error,$which_query,$where) { /* let's say you log errors in a file */ $f=fopen("C:\\error_log.txt","a"); fwrite($f,"The detailed error is: ".$detailed_error."\r\n\r\n"); fwrite($f,"The query it tried to do is: ".$which_query."\r\n\r\n"); fwrite($f,"Line in the script where it happened: ".$where."\r\n\r\n*****************\r\n\r\n"); fclose($f); } One you're satisfied with your function, you have to put it in your script, after EVERY mySQL query! You call it like this: $query = "INSERT whatever you want"; $result = mysql_query($query); if ($result === FALSE) error(mysql_error(),$query,__LINE__); mysql_error() is an integrated function which gives you the full text of the error, whereas __LINE__ is a constant in PHP. It's time for some scripts update .

I like your code and I will steal it..

This is the part of coding that I hate, the long long
time it takes to find out the cause for leaks, corruptions etc...

jcn50 wrote on Mon, 10 September 2007 04:45

I don't see any reason for having a huge key_buffer_size of 600 MB then... You can cut-it off half to 300 MB.

Yeah, I had a couple other tables that were getting big because
they stored history data. I've since removed those but they added up to about 600 which is why my key buffer was at 600.
I could probably safely change it back to the default of 384
just to give it some room for growth...


It will take me awhile to go over the code and add your suggestions, but I will most definitley come back here to update everyone on the results.

The good news is that after some tweaks over the weekend, it hasn't crashed yet, so it's been a good 3 days thus far.

The tweak I made, in case anyone wants to know is my read_rnd_buffer, it was set to 8M when it should have been 2M
since I only have 2Gigs.

I also changed my wait_timeout to 600 seconds instead of the
default of 28,800 seconds!!!!!!

HI All,
Thanks for all reply.
let me explain my problem again here.

My site hosted on linux server before and run well.
before a month site hosted on windows server.
we are using pear class to execute query and using phpmyadmin as mysql frontend.

Problem of mysql table "in use" started after the day when site hosted on windows server. daily more than 3 tables show status "in use" frequently.

I applied required changes in my.ini file. after mysql configuration changes applied now 1 or 2 tables shows status "in use" when see table list in phpmyadmin.
Once i repair table then all run well.

now please guide me how to detect the problem area and solve this problem.

-Rashmi

rashmirani wrote on Tue, 11 September 2007 06:32

HI now please guide me how to detect the problem area and solve this problem.

Man, you're not into it: the fact that your tables are "in use" is because they crashed. And, like you said, you need to make a repair.
Before doing a repair, you can use CHECK TABLE and open your mySQL error_log.

Check the previous posts in this thread for error reporting and logging, otherwise you won't find the cause. How can you find the origin of something if you only know/see the consequence?? How do you want us to help you if you only tell us that you need to repair your tables??

Update your scripts and post the error(s) you got here in this post. There is no way someone else does it for you, especially when I already wrote the code you need to add...

As allworknoplay suggested, check your read_rnd_buffer value, you may need to lower it to 2MB; same for the wait_timeout value.



Alternatively, you can tell us:
- what is the version your Windows OS?
- what is the filesystem used? (FAT32 or NTFS)
- how big is your DB?
- how big is your indexes?
- the brand of your HD?

Hi jcn50,
Thanks for suggestion.
In my site more than 300 scripts are in use.
so, I already modify code in such a way, whenever mysql error occured, I will receieve email consist errorsum query.

Ans. of your question as much I have :
- what is the version your Windows OS?
win 2K3 R2 service pack 2
- how big is your DB?
1.5 GB, no. of tables more than 80
- RAM
2GB

I used MyIsam storage Engine.
my.ini configuration at present as:
max_connections=400
table_cache=4096
thread_cache_size=16
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=205M
key_buffer_size=350M
read_buffer_size=2M
read_rnd_buffer_size=8M
sort_buffer_size=3M

awating reply with suggestion for changes if require any.
-Rashmi