mysql tables crashing

Ok here is one of my codes…

INSERT INTO data_day (device_id,timestamp,data_type,object,actual,gdata) VALUES (‘“.$device_id.”’,‘“.$timestamp.”’,‘disk’,‘“.$VLetter.”’,‘1’,’ “.$PercentUsedDisk.”')

The first 4 obviously are the ones in question, and the last 2 (actual,gdata) could be of any value, that doesn’t and shouldn’t have any affect on the INSERTS…

If you don’t really need to have 4 seperated columns, you could merge columns device_id,timestamp,data_type,object into a single UNIQUE column (let’s call it “merged”).
You will separate the values by a separator (let’s pick up the semicolon).
So your “merged” column’s data could look like this:
1175214041;1189629601;disk;C

I have no other suggestion for now (at the mySQL level) I could think of (yet).

This particular script doesn’t have any DELETES.

I have another script that loops through this table 2.5 million rows and just deletes anything with a timestamp older than a certain period like 24 hours…

So I don’t think I have any WHERE clauses in the DELETE statements because I assume I didn’t have to be specific, all I care is that if any record with a timestamp older than say 24 hours, then DELETE…

[B]jcn50 wrote on Fri, 14 September 2007 15:48[/B]
If you don't really need to have 4 seperated columns, you could merge columns device_id,timestamp,data_type,object into a single UNIQUE column (let's call it "merged"). You will separate the values by a separator (let's pick up the semicolon). So your "merged" column's data could look like this: 1175214041;1189629601;disk;C

I have no other suggestion for now (at the mySQL level) I could think of (yet).

Well I can’t merge them because I have other programs that call this table that look for a specific column and do something else with it, like the data_type and object.

That’s what makes this whole thing difficult, because everything is production and one change to one column or table could have affects on other scripts that I completely forgot uses it…

I think my first two steps at this point are to drop the index on the ID column and start using DELETE QUICK everywhere I can think of!!!

[B]allworknoplay wrote on Fri, 14 September 2007 19:45[/B]
Ok here is one of my codes...

INSERT INTO data_day (device_id,timestamp,data_type,object,actual,gdata) VALUES (‘“.$device_id.”’,‘“.$timestamp.”’,‘disk’,‘“.$VLetter.”’,‘1’,’ “.$PercentUsedDisk.”')

The first 4 obviously are the ones in question, and the last 2 (actual,gdata) could be of any value, that doesn’t and shouldn’t have any affect on the INSERTS…

I bet you won’t have a lot of completed INSERTs, because data_type is not UNIQUE on this line of the script!

How many results do you have for this query?
SELECT * FROM data_day WHERE data_type = “disk”;

[B]allworknoplay wrote on Fri, 14 September 2007 19:49[/B]
I have another script that loops through this table 2.5 million rows and just deletes anything with a timestamp older than a certain period like 24 hours...

So I don’t think I have any WHERE clauses in the DELETE statements because I assume I didn’t have to be specific, all I care is that if any record with a timestamp older than say 24 hours, then DELETE…

2.5 million rows?! Wow, DELETE QUICK is a must D… Because after each DELETE, your indexes are rebuilt! That’s a waste of resources.
I hope your mySQL server automatically locks the table “data_day”, because if you have an INSERT when the INDEX is being rebuilt, you are likely to have a bunch of crashes!

Here is one of my DELETE scripts, short and small, I guess all I have to do is add QUICK right?

$timeframe_day = strtotime(“-24 hours”);
$query_day = “DELETE FROM data_day WHERE timestamp <='”.$timeframe_day."’ ";
$result_day = @mysql_query($query_day);
unset($query_day,$result_day);

So…

DELETE FROM data_day WHERE timestamp

Becomes…

DELETE QUICK FROM data_day WHERE timestamp

Also, do you think the UNSET variable is necessary?
I use it in my other program that has over 2000 lines of code. I just want to release any memory PHP is using and avoid and possible overlapping of variables…

[B]allworknoplay wrote on Fri, 14 September 2007 20:25[/B]
Also, do you think the UNSET variable is necessary? I use it in my other program that has over 2000 lines of code. I just want to release any memory PHP is using and avoid and possible overlapping of variables..
If your script does something else that this DELETE QUICK thereafter, yes it could help. Otherwise, if it's only a cron job that run 1 time each day (a "short script" as you call it) the variables will be unset anyway at the end of the script's execution. If you use $query_day and $result_day again later, it's a waste of time because you destroy the memory allocated by it, and you re-create a memory space again after. I guess UNSET is usefull if your script is running for, let's say, a week...

Not to derail the topic, but how well do you know PHP?

[B]allworknoplay wrote on Fri, 14 September 2007 20:38[/B]
Not to derail the topic, but how well do you know PHP?

Well, I use it very much, also doing PHP executable for Windows. But I didn’t upgrade myself to PHP5 yet (… Also, I never used the object-oriented programming (for me the code should be sequential).

OK, when I think about it, the crash may come from the “thread_concurrency” variable set to >1.

  • Imagine that you have one of your script is running a lot of DELETE queries;
  • At the same time, another script is running a lot of INSERT queries.

Crash can occurs when an INSERT query occurs while rebuilding the INDEXes: the server will miss some inserted keys, ending with more rows than the number of keys.
mysql> check table data_day;
±-------------------±------±---------±------------------ ------------+
| Table | Op | Msg_type | Msg_text |
±-------------------±------±---------±------------------ ------------+
| collector.data_day | check | warning | Table is marked as crashed |
| collector.data_day | check | error | Found 2434089 keys of 2434281 |
| collector.data_day | check | error | Corrupt |
±-------------------±------±---------±------------------ ------------+

This is the only logical case I could think of! :o
As we said earlier, the advantage of using DELETE QUICK is that the INDEX is not rebuilt. Also, when you run OPTIMIZE TABLE, the entire table will be locked, as per mySQL documentation:
[URL=“http&#58;&#47;&#47;MySQL :: MySQL 8.0 Reference Manual :: 13.7.3.4 OPTIMIZE TABLE Statement”]http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html[/URL]

So my #1 suggestion will be to set thread_concurrency to 1, even if you have 2 CPUs…
Also, previous mySQL version (< v5) didn’t have this thread_concurrency variable!..

I have NO IDEA what this means…

DELETE QUICK is not useful when deleted values lead to underfilled index blocks spanning a range of index values for which new inserts occur again. In this case, use of QUICK can lead to wasted space in the index that remains unreclaimed. Here is an example of such a scenario:

[B]jcn50 wrote on Fri, 14 September 2007 17:04[/B]
OK, when I think about it, the crash may come from the "thread_concurrency" variable set to >1.
  • Imagine that you have one of your script is running a lot of DELETE queries;
  • At the same time, another script is running a lot of INSERT queries.

Crash can occurs when an INSERT query occurs while rebuilding the INDEXes: the server will miss some inserted keys, ending with more rows that the number of keys.
mysql> check table data_day;
±-------------------±------±---------±------------------ ------------+
| Table | Op | Msg_type | Msg_text |
±-------------------±------±---------±------------------ ------------+
| collector.data_day | check | warning | Table is marked as crashed |
| collector.data_day | check | error | Found 2434089 keys of 2434281 |
| collector.data_day | check | error | Corrupt |
±-------------------±------±---------±------------------ ------------+

This is the only logical case I could think of! :o
As we said earlier, the advantage of using DELETE QUICK is that the INDEX is not rebuilt. Also, when you run OPTIMIZE TABLE, the entire table will be locked, as per mySQL documentation:
[URL=“http&#58;&#47;&#47;MySQL :: MySQL 8.0 Reference Manual :: 13.7.3.4 OPTIMIZE TABLE Statement”]http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html[/URL]

So my #1 suggestion will be to set thread_concurrency to 1, even if you have 2 CPUs…
Also, previous mySQL version (< v5) didn’t have this thread_concurrency variable!..

That sounds like the best answer/solutions so far.

This is what I will do, change the thread_concurrency to 1.
And use DELETE QUICK in all my programs, starting with all
my garbage cleanup scripts…

Then we can see how stable this son of a gun can be!!!

I will do these first, and if the crash still happens, I will drop by INDEX on the ID column…

My SQL version is: 5.0.21

[B]allworknoplay wrote on Fri, 14 September 2007 21:06[/B]
I have NO IDEA what this means...

DELETE QUICK is not useful when deleted values lead to underfilled index blocks spanning a range of index values for which new inserts occur again. In this case, use of QUICK can lead to wasted space in the index that remains unreclaimed. Here is an example of such a scenario:

I guess it have some relation with “contiguous space use”.
It’s the same as “disk optimization”.

INDEX:
0: data
1: data
2: data

DELETE QUICK 1;

[B]NEW INDEX:
0: data

2: data[/B]

=> result: no more “1: data” in the INDEX, INDEX not optimized, INDEX having a “hole” at line #2.

Ok, don’t forget to change the thread_concurrency to 1 too.

[B]jcn50 wrote on Sat, 15 September 2007 12:04[/B]
Ok, don't forget to change the thread_concurrency to 1 too.

I made a boo-boo. I repaired the table and changed my script to DELETE QUICK.

Walked away, made some coffee, 20 mins later I came back and I got an email again my table got corrupted. This time it was FAST. Usually 2-3 days, but never within 20 mins of REPAIRING a table.

So I said, that’s it, I am dropping this INDEX because it’s causing too many problems…

Low and behold, I forgot to clear out my mysql error log file, so when my script ran, it emailed me because it thought the table was corrupted!!!

The key is to drink coffee first!!!

So now I have a DELETE QUICK AND I dropped the Index on the ID column.

Now if the table never gets corrupted again, I will not know which one was the actual solution!!!

How is the debugging doing?

[B]jcn50 wrote on Mon, 17 September 2007 10:06[/B]
How is the debugging doing?

So far no crash…so that is good.

Still need to finish off the error output logs if I can’t
make a successful mysql query…

But I’m starting to feel a little better now…

Don’t want to jinx anything though!!!

Let’s make a party on October the 1st if no more crash! :smiley:

[B]jcn50 wrote on Mon, 17 September 2007 18:07[/B]
Let's make a party on October the 1st if no more crash! :D

I think you’re trying to jinx me!!!