Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » mysql tables crashing
Re: mysql tables crashing [message #1845 is a reply to message #1697 ] Fri, 14 September 2007 15:45 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
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....
Report message to a moderator
Re: mysql tables crashing [message #1846 is a reply to message #1697 ] Fri, 14 September 2007 15:48 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
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).

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1847 is a reply to message #1697 ] Fri, 14 September 2007 15:49 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
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....
Report message to a moderator
Re: mysql tables crashing [message #1848 is a reply to message #1846 ] Fri, 14 September 2007 15:52 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
jcn50 wrote on Fri, 14 September 2007 15:48

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!!!
Report message to a moderator
Re: mysql tables crashing [message #1849 is a reply to message #1845 ] Fri, 14 September 2007 15:53 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
allworknoplay wrote on Fri, 14 September 2007 19:45

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";

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1850 is a reply to message #1849 ] Fri, 14 September 2007 15:57 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
jcn50 wrote on Fri, 14 September 2007 15:53



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



mysql> SELECT count(*) FROM data_day WHERE data_type = "disk";
+----------+
| count(*) |
+----------+
| 502802 |
+----------+
1 row in set (6.44 sec)
Report message to a moderator
Re: mysql tables crashing [message #1851 is a reply to message #1847 ] Fri, 14 September 2007 16:07 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
allworknoplay wrote on Fri, 14 September 2007 19:49

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

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1852 is a reply to message #1850 ] Fri, 14 September 2007 16:12 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
allworknoplay wrote on Fri, 14 September 2007 19:57

jcn50 wrote on Fri, 14 September 2007 15:53



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



mysql> SELECT count(*) FROM data_day WHERE data_type = "disk";
+----------+
| count(*) |
+----------+
| 502802 |
+----------+
1 row in set (6.44 sec)



Thanks!...

I've just made some tests on a small mySQL DB: you're right you can set an UNIQUE KEY with multiple fields... Embarassed
Now I'm very consfused. Let me think! Surprised

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1853 is a reply to message #1851 ] Fri, 14 September 2007 16:14 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
jcn50 wrote on Fri, 14 September 2007 16:07



2.5 million rows?! Wow, DELETE QUICK is a must Laughing... 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!


Yes!!!
That's what I'm talking about!!! There's so much to know about the little things, I wasn't formally trained in mysql so I don't know all the little details...but I've been using it for years..

Anyways...
I think you have hit the nail on the head awhile back already
about the DELETE QUICK syntax.

I didn't know the ramifications and what mysql does on DELETES.

So wait, if normal DELETES cause mysql to rebuild the INDEX, the DELETE QUICK leaves the INDEX alone? And if so, that's ok?


Also, there is no table locking! I don't do anything or tell mysql to do any kind of table locking...

It is VERY VERY much possible that INSERTS are occuring while the DELETES are taking place....

The only time the table locks is when I do a REPAIR table...
Report message to a moderator
Re: mysql tables crashing [message #1854 is a reply to message #1852 ] Fri, 14 September 2007 16:19 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
jcn50 wrote on Fri, 14 September 2007 16:12



I've just made some tests on a small mySQL DB: you're right you can set an UNIQUE KEY with multiple fields... Embarassed
Now I'm very consfused. Let me think! Surprised



Hey, if you can learn something from this troubleshooting, that just makes you even better!!!

I was going to do a test myself on a fake table just to see the results but you've already done that...

Again, my take is that the combination needs to be unique. Anything else and the data goes in....
Report message to a moderator
Re: mysql tables crashing [message #1855 is a reply to message #1853 ] Fri, 14 September 2007 16:19 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
allworknoplay wrote on Fri, 14 September 2007 20:14

[So wait, if normal DELETES cause mysql to rebuild the INDEX, the DELETE QUICK leaves the INDEX alone? And if so, that's ok?

As per the mySQL documentation:
http://dev.mysql.com/doc/refman/5.0/en/delete.html
"If you are going to delete many rows from a table, it might be faster to use DELETE QUICK followed by OPTIMIZE TABLE. This rebuilds the index rather than performing many index block merge operations."

So I bet it's no problem, if you add OPTIMIZE TABLE at the end of your cron job.

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1856 is a reply to message #1697 ] Fri, 14 September 2007 16:25 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
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..
Report message to a moderator
Re: mysql tables crashing [message #1857 is a reply to message #1856 ] Fri, 14 September 2007 16:31 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
allworknoplay wrote on Fri, 14 September 2007 20:25

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

[Updated on: Fri, 14 September 2007 16:34]

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1858 is a reply to message #1857 ] Fri, 14 September 2007 16:35 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
jcn50 wrote on Fri, 14 September 2007 16:31

allworknoplay wrote on Fri, 14 September 2007 20:25

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.



This script runs every 30 mins, so the optimize table if I ran it, would lock up the table for however long it takes to optimize it...maybe 1 minute?

I am wondering if I use the DELETE QUICK on this script, and then at midnight I run another script that optimizes the table?

This way I don't try to optimize the table every 30 mins which might wreak havoc...
Report message to a moderator
Re: mysql tables crashing [message #1859 is a reply to message #1857 ] Fri, 14 September 2007 16:37 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
jcn50 wrote on Fri, 14 September 2007 16:31


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



Ok thanks, I'm trying to get into the habit of writing clean code.
Report message to a moderator
Re: mysql tables crashing [message #1860 is a reply to message #1697 ] Fri, 14 September 2007 16:38 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
Not to derail the topic, but how well do you know PHP?
Report message to a moderator
Re: mysql tables crashing [message #1861 is a reply to message #1860 ] Fri, 14 September 2007 16:42 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
allworknoplay wrote on Fri, 14 September 2007 20:38

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 Sad... Also, I never used the object-oriented programming (for me the code should be sequential).

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1862 is a reply to message #1861 ] Fri, 14 September 2007 16:44 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
jcn50 wrote on Fri, 14 September 2007 16:42

allworknoplay wrote on Fri, 14 September 2007 20:38

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 Sad... Also, I never used the object-oriented programming (for me the code should be sequential).



Ok I mainly use PHP on linux. Played with it a couple times on windows but it is absolutely integral to our systems on linux platform.

I don't use PHP with OO either, my take on it is, why complicate things more just to say you are using OO?

As you can see from my delete script code, it's a couple of lines
and that's it. Why use OO for that?

Most of my programs are from top to bottom, so I don't see a need to use PHP in OO form at all...
Report message to a moderator
Re: mysql tables crashing [message #1863 is a reply to message #1858 ] Fri, 14 September 2007 16:45 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
allworknoplay wrote on Fri, 14 September 2007 20:35

This script runs every 30 mins, so the optimize table if I ran it, would lock up the table for however long it takes to optimize it...maybe 1 minute?

I am wondering if I use the DELETE QUICK on this script, and then at midnight I run another script that optimizes the table?

This way I don't try to optimize the table every 30 mins which might wreak havoc...



I don't really know about the time to optimize the table... However when you use DELETE only, the OPTIMIZE TABLE is silent and made each time. So you can add only one OPTIMIZE TABLE after your 30 min scripts Wink.

One question is: are you sure your script's execution's time is < 30 min??...

[Updated on: Fri, 14 September 2007 16:49]

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1864 is a reply to message #1852 ] Fri, 14 September 2007 16:47 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
jcn50 wrote on Fri, 14 September 2007 20:12


I've just made some tests on a small mySQL DB: you're right you can set an UNIQUE KEY with multiple fields... Embarassed
Now I'm very consfused. Let me think! Surprised


OK, after using my head, I'm wondering if your problem doesn't come from the "thread_concurrency" variable? It's set to 10 on your server?

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1865 is a reply to message #1862 ] Fri, 14 September 2007 16:49 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
allworknoplay wrote on Fri, 14 September 2007 20:44


Ok I mainly use PHP on linux. Played with it a couple times on windows but it is absolutely integral to our systems on linux platform.

I don't use PHP with OO either, my take on it is, why complicate things more just to say you are using OO?

As you can see from my delete script code, it's a couple of lines
and that's it. Why use OO for that?

Most of my programs are from top to bottom, so I don't see a need to use PHP in OO form at all...




Oh, a Linux guy Very Happy...
The topic is becoming a chat Razz... Maybe we should switch to an IM soft?

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1866 is a reply to message #1864 ] Fri, 14 September 2007 16:56 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
jcn50 wrote on Fri, 14 September 2007 16:47

jcn50 wrote on Fri, 14 September 2007 20:12


I've just made some tests on a small mySQL DB: you're right you can set an UNIQUE KEY with multiple fields... Embarassed
Now I'm very consfused. Let me think! Surprised


OK, after using my head, I'm wondering if your problem doesn't come from the "thread_concurrency" variable? It's set to 10 on your server?



# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 2
back_log = 250
max_connect_errors = 100

It's running on a single DualCore Xeon so I figured 2 would
be the same as 2 processors....
Report message to a moderator
Re: mysql tables crashing [message #1867 is a reply to message #1865 ] Fri, 14 September 2007 16:58 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
jcn50 wrote on Fri, 14 September 2007 16:49



Oh, a Linux guy Very Happy...
The topic is becoming a chat Razz... Maybe we should switch to an IM soft?



Linux is GREAT, and I am a windows guy too...I have my MS certs..
As well as Cisco certs...etc...

I will PM you my AIM name...
Report message to a moderator
Re: mysql tables crashing [message #1868 is a reply to message #1697 ] Fri, 14 September 2007 17:04 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
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! Embarassed
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:
http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html

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

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

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1869 is a reply to message #1697 ] Fri, 14 September 2007 17:06 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
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:
Report message to a moderator
Re: mysql tables crashing [message #1870 is a reply to message #1868 ] Fri, 14 September 2007 17:09 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
jcn50 wrote on Fri, 14 September 2007 17:04

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! Embarassed
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:
http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html

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
Report message to a moderator
Re: mysql tables crashing [message #1871 is a reply to message #1870 ] Fri, 14 September 2007 17:12 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
Ok! Thumbs Up

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1872 is a reply to message #1869 ] Fri, 14 September 2007 17:18 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
allworknoplay wrote on Fri, 14 September 2007 21:06

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;

NEW INDEX:
0: data

2: data

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

[Updated on: Fri, 14 September 2007 17:19]

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1873 is a reply to message #1872 ] Fri, 14 September 2007 17:20 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
jcn50 wrote on Fri, 14 September 2007 17:18



INDEX:
0: data
1: data
2: data

DELETE QUICK 1;

NEW INDEX:
0: data

2: data

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



Ok I think it is all starting to make sense to me now...

Let's go with the battle plan above and I'll let you know the results...

Is it me or does this MB need more users??

Are you on any other msyql boards that has more user traffic?
Report message to a moderator
Re: mysql tables crashing [message #1877 is a reply to message #1873 ] Sat, 15 September 2007 11:57 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
Table crashed last night. Thank god I implemented a cron to check for errors and email me.

I went ahead and used the DELETE QUICK method on the main script
that does most of the deletion. I'm not sure if there are other scripts that delete from this table or not....

If the DELETE QUICK still doesn't work, then my next step is to drop the INDEX on the ID column....
Report message to a moderator
Re: mysql tables crashing [message #1878 is a reply to message #1697 ] Sat, 15 September 2007 12:04 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
Ok, don't forget to change the thread_concurrency to 1 too.

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1879 is a reply to message #1878 ] Sat, 15 September 2007 12:16 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
jcn50 wrote on Sat, 15 September 2007 12:04

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!!!!!!
Report message to a moderator
Re: mysql tables crashing [message #1880 is a reply to message #1697 ] Mon, 17 September 2007 10:06 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
How is the debugging doing?

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1884 is a reply to message #1880 ] Mon, 17 September 2007 18:00 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
jcn50 wrote on Mon, 17 September 2007 10:06

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!!!
Report message to a moderator
Re: mysql tables crashing [message #1885 is a reply to message #1697 ] Mon, 17 September 2007 18:07 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
Let's make a party on October the 1st if no more crash! Very Happy

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1886 is a reply to message #1885 ] Mon, 17 September 2007 18:24 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
jcn50 wrote on Mon, 17 September 2007 18:07

Let's make a party on October the 1st if no more crash! Very Happy



I think you're trying to jinx me!!!!!!!!!!
Report message to a moderator
Re: mysql tables crashing [message #1888 is a reply to message #1878 ] Tue, 18 September 2007 10:18 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
thread_concurrency makes no difference on Linux.

It is also just a hint to the scheduler on how to schedule threads - it has nothing to do with a way MySQL runs threads internally.

Now you've tot the bug - I'd try to get our repeatable test case and get it to bugs.mysql.com

I know there ARE MyISAM corruption bugs out where.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: mysql tables crashing [message #1893 is a reply to message #1888 ] Tue, 18 September 2007 13:38 Go to previous messageGo to next message
jcn50
Messages: 44
Registered: September 2007
Member
Hello Peter,

Peter wrote on Tue, 18 September 2007 14:18

thread_concurrency makes no difference on Linux.

It is also just a hint to the scheduler on how to schedule threads - it has nothing to do with a way MySQL runs threads internally.

You're right, it seems to affect only SOLARIS systems. Shocked
http://dev.mysql.com/doc/refman/4.1/en/server-system-variabl es.html


Peter wrote on Tue, 18 September 2007 14:18


Now you've tot the bug - I'd try to get our repeatable test case and get it to bugs.mysql.com
I know there ARE MyISAM corruption bugs out where.

As he doesn't use the last version of mySQL, he'll have to:
* either upgrade his version to the last one ;
* convert to InnoDB engine if remaining with his current version.

But let's cross fingers, as it seems he doesn't have anymore crash. Surprised

Enjoy the Net!
Report message to a moderator
Re: mysql tables crashing [message #1902 is a reply to message #1893 ] Wed, 19 September 2007 09:58 Go to previous messageGo to next message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
So far so good guys...I'm still crossing my fingers, but I can feel like I can breath a little better as I beleive it's been more than 3 days now and no crash...

I'd like go ahead and do the BULK INSERTS as well since I ran a program called mysqltuner.pl and it showed that my inserts to reads ratio was about 85% to 15% respectively...

I think what I'd like to do now is setup a slave where all the READS will come from and the master will deal with all the inserts...

As for the thread_concurrency, that is a real shame, mysql should put by default a comment in their .cnf file that the thread_concurrency is useless in linux so don't bother to use this variable to troubleshoot any issues....
Report message to a moderator
Re: mysql tables crashing [message #1903 is a reply to message #1893 ] Wed, 19 September 2007 10:05 Go to previous messageGo to previous message
allworknoplay is currently offline allworknoplay
Messages: 58
Registered: September 2007
Location: New York
Member
jcn50 wrote on Tue, 18 September 2007 13:38



As he doesn't use the last version of mySQL, he'll have to:
* either upgrade his version to the last one ;
* convert to InnoDB engine if remaining with his current version.

But let's cross fingers, as it seems he doesn't have anymore crash. Surprised


Honestly, since I've dropped the index on column "ID" which said it was "MUL" and I started using DELETE QUICK, I think it's been humming along.

My only question is that the first 4 columns are still indexes so why would having a 5th index'ed column cause issues? Is there a limit to how many indexes you should have in a given table?

I know that I've read somewhere you should using index'ed columns sparingly and wisely but I don't know if there is an actual limit?
Report message to a moderator
Previous Topic:phpMyAdmin (query profiling)
Next Topic:mysql takin' too much memory
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Sun Jul 5 21:57:45 EDT 2009

Total time taken to generate the page: 0.01525 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.