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).
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...
[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 |
±-------------------±------±---------±------------------ ------------+
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!..
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 |
±-------------------±------±---------±------------------ ------------+
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…
[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.
[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!!!