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?

[Updated on: Sun, 09 September 2007 20:32]

[Updated on: Mon, 10 September 2007 02:33]

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 " .
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!

[Updated on: Mon, 10 September 2007 05:10]

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!

[Updated on: Mon, 10 September 2007 05:19]

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!!!!!!

[Updated on: Mon, 10 September 2007 18:59]

[Updated on: Tue, 11 September 2007 02:32]

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?

[Updated on: Tue, 11 September 2007 03:54]

[Updated on: Tue, 11 September 2007 05:49]

[Updated on: Tue, 11 September 2007 10:35]

[Updated on: Wed, 12 September 2007 23:55]

allworknoplay wrote on Fri, 14 September 2007 01:28

+-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | device_id | int(10) | NO | PRI | | | | timestamp | int(10) | NO | PRI | | | | data_type | varchar(32) | NO | PRI | | | | object | varchar(120) | NO | PRI | | | | id | varchar(10) | NO | MUL | | | | actual | int(1) | NO | | 0 | | | gdata | bigint(20) | NO | | 0 | | | gdata1 | bigint(20) | NO | | 0 | | +-----------+--------------+------+-----+---------+-------+

Wait! As per mySQL standards, you should have only ONE primary key per table!
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
"A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY."

So you should have only one PRI, the other indexes should be noted as MUL, just like the field "id".
That's why your table was crashing man! I think we found out! Because the "timestamp" may not be unique (as I said earlier in message #1777 in this topic), same for the "data_type" and "object" fields! So be sure that "device_id" is UNIQUE in your design! (i.e: using AUTO_INCREMENT, not being generated by your script)

If you had had the scripting error_reporting, you would have had an error like "can't INSERT duplicate keys" or "can't DELETE duplicate keys".

allworknoplay wrote on Fri, 14 September 2007 01:28

Plus this stuff is PRODUCTION. If I mess up at all in any which way, I am in TROUBLE!!!!

Well, it can't be worse than MyISAM ....at least if you design your tables & write scripts properly!

[Updated on: Fri, 14 September 2007 00:18]

You can have an INDEX that is NOT UNIQUE. But you can't have more than ONE PRIMARY INDEX, and that PRIMARY INDEX should be UNIQUE.

UNIQUE = there is no duplicate (in the same column). You can't consider 4 columns being unique as a whole for a PRIMARY INDEX.

I don't know if I answered your question?...

For example, if I do:
SELECT * FROM data_day WHERE device_id = 1175214041;
it should always return 0 or 1 result, even if the other columns are different as a whole.

The idea behind a PRIMARY INDEX is that it identifies each row uniquely. Let's imagine you have a list of prices, and your items have a bar code. The bar code is a PRIMARY INDEX because you can only have one price per bar code (even if the packaging changes, or the name of the product changes, or where it comes from changes; it doesn't matter, because we're talking about the same product).

What cause the crash is not the INSERT, it's the DELETE. I don't know how you make your query, but I guess MySQL wants to delete every row that as the same device_id (or any of the other 3 PRIMARY INDEXes being the same), resulting in a crash. When mySQL uses a PRIMARY INDEX for deleting, only ONE ROW can be deleted... whereas your table may contains more than a row with the same device_id (or more than a row with the same timestamp, etc).

Imagine that I ask you: "delete the product which has the bar code 1175214041", then you start to search on your list "which product has the bar code 1175214041?" and you end up with more than a single result, which one would you delete? (as you can only delete one) ... => crash

UNIQUE(device_id, timestamp,data_type,object) means:
- device_id contains no duplicate ;
- timestamp contains no duplicate ;
- data_type contains no duplicate ;
- object contains no duplicate.

UNIQUE(device_id, timestamp,data_type,object) doesn't mean: "there's only ONE UNIQUE row having a defined: device_id AND timestamp AND data_type AND object".

[Updated on: Fri, 14 September 2007 11:35]

[Updated on: Fri, 14 September 2007 12:22]

OK, if you didn't intend to do what I wrote... it simply means that you have NO PRIMARY INDEX! ...

I'm not saying that what you want you to do is wrong, I'm just saying that the columns which are set to UNIQUE (or "PRIMARY INDEX") are wrong/misused.

SQL commands are:
ALTER TABLE data_day DROP PRIMARY KEY
(should delete all primary keys)

ALTER TABLE data_day DROP INDEX device_id
ALTER TABLE data_day DROP INDEX timestamp
ALTER TABLE data_day DROP INDEX data_type
ALTER TABLE data_day DROP INDEX object
(should remove the unique index)

ALTER TABLE data_day ADD INDEX device_id
ALTER TABLE data_day ADD INDEX timestamp
ALTER TABLE data_day ADD INDEX data_type
ALTER TABLE data_day ADD INDEX object
(should add a simple index)

And you're done!

Copy/paste your fields' type again, to show 5 beautiful "MUL" and no more "PRI".

[Updated on: Fri, 14 September 2007 15:13]