MySQL Performance: MySQL => mysql tables crashing

Members

Help

Home

Home » Performance » MySQL » mysql tables crashing

Show: Today's Messages :: Show Polls :: Message Navigator

Re: mysql tables crashing [message #1908 is a reply to message #1903 ]

Wed, 19 September 2007 18:07

jcn50
Messages: 44
Registered: September 2007

Member

allworknoplay wrote on Wed, 19 September 2007 14:05

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.

Did you do both at the same time?.. I guess only one is the solution (DELETE QUICK?)...

allworknoplay wrote on Wed, 19 September 2007 14:05

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?

Of course it doesn't make any sense to have crashes when using many indexes. Confused

Maybe:
- the indexes' rebuilding time was too looong
- ending in a timeout from the mysqld
- producing a "MySQL server has gone away" error (that can't be seen because no verbose error_log)
- and corrupting the table because the server stopped working on it, flushing all the indexes' data after flush_time
http://dev.mysql.com/doc/refman/5.1/en/server-system-variabl es.html#option_mysqld_flush_time
and/or after table_lock_wait_timeout
http://dev.mysql.com/doc/refman/5.1/en/server-system-variabl es.html#option_mysqld_table_lock_wait_timeout

Very Happy

[Updated on: Wed, 19 September 2007 18:13]

Enjoy the Net!

Report message to a moderator

need help [message #1968 is a reply to message #1697 ]

Sun, 30 September 2007 12:15

ymik
Messages: 1
Registered: September 2007
Location: SPb, Russia

Junior Member

Hello All.

We have some throuble with our DB.

When we cast request such as "update user lastactyvity=12131241234 WHERE userid in (1,2,3,4,5,6,7...)" we got error "Table '%-.64s' is marked as crashed and should be repaired" and our replication is fault.

What thing can produse this error with unknown table '%-.64s'? How can we relieve from this bug?

Tables is on MyISAM, MySQL version is 5.0.32-Debian_7etch1

Report message to a moderator

Re: need help [message #1991 is a reply to message #1968 ]

Thu, 04 October 2007 09:27

allworknoplay
Messages: 58
Registered: September 2007
Location: New York

Member

Well after a successful 3 weeks, I came to work only to
find that my table has crashed. This is what MYSQL error log
shows:

[ERROR] /usr/local/mysql/libexec/mysqld: Incorrect key file for table './collector/data_day.MYI'; try to repair it

I think part of the cure is for me to separate this table
into multiple tables so that it is not being hammered so much,
part of the problem I think is that I have many types of data
in it and perhaps the scripts are all fighting for the table's resources???

Report message to a moderator

Re: mysql tables crashing [message #1994 is a reply to message #1697 ]

Thu, 04 October 2007 18:40

jcn50
Messages: 44
Registered: September 2007

Member

I'll suggest a last attempt: a conversion to INNODB.
If an INNODB conversion doesn't change anything, it's an hardware problem (hard drive is having bad sectors/blocks).

We've tried everything on this. INNODB is the last (software) solution I could think of, since we've done our homework.

[Updated on: Thu, 04 October 2007 18:56]

Enjoy the Net!

Report message to a moderator

Re: need help [message #1995 is a reply to message #1968 ]

Thu, 04 October 2007 18:57

jcn50
Messages: 44
Registered: September 2007

Member

Sorry man, we didn't reply because you need to follow this thread from the beginning to know where your errors come from...
If you think it's a bug, update for the last version of the mySQL engine.
I'm not aware of any known "Table '%-.64s'" error...
Good luck!

ymik wrote on Sun, 30 September 2007 16:15

[Updated on: Thu, 04 October 2007 18:59]

Enjoy the Net!

Report message to a moderator

Re: mysql tables crashing [message #1996 is a reply to message #1994 ]

Fri, 05 October 2007 08:44

allworknoplay
Messages: 58
Registered: September 2007
Location: New York

Member

jcn50 wrote on Thu, 04 October 2007 18:40

Your code is really coming in handy. I am seeing a lot of duplicate errors. But in my script the code is something like...

INSERT INTO .... IF DUPLICATE THAN UPDATE....

That's basically what I'm doing in my code and your code is pinpointing exactly that line from my code....

I am going to try one more thing before I have to convert to INNODB because I just don't know INNODB that well...

I have begun separating the table into many tables, this in
a way will share the load of INSERTS and balance it out...

Also, I should run OPTIMIZE TABLE once a week like on a Saturday....

Report message to a moderator

Re: mysql tables crashing [message #2003 is a reply to message #1996 ]

Sat, 06 October 2007 00:59

jcn50
Messages: 44
Registered: September 2007

Member

allworknoplay wrote on Fri, 05 October 2007 12:44

Thanks!

I hope you can work out something from the DUPLICATE errors.

allworknoplay wrote on Fri, 05 October 2007 12:44

I am going to try one more thing before I have to convert to INNODB because I just don't know INNODB that well...

I don't think that it basically change something when you switch from MyISAM to InnoDB, unless you use Transactional Syntax, where InnoDB is required.

allworknoplay wrote on Fri, 05 October 2007 12:44

I have begun separating the table into many tables, this in
a way will share the load of INSERTS and balance it out...

Hum... to me it doesn't make any sense: the server should handle your DB design. Server are not capricious like girls Wink

[Updated on: Sat, 06 October 2007 01:00]

Enjoy the Net!

Report message to a moderator

Re: mysql tables crashing [message #2010 is a reply to message #2003 ]

Sat, 06 October 2007 08:34

allworknoplay
Messages: 58
Registered: September 2007
Location: New York

Member

jcn50 wrote on Sat, 06 October 2007 00:59

allworknoplay wrote on Fri, 05 October 2007 12:44

Thanks!

I hope you can work out something from the DUPLICATE errors.

allworknoplay wrote on Fri, 05 October 2007 12:44

I am going to try one more thing before I have to convert to INNODB because I just don't know INNODB that well...

I don't think that it basically change something when you switch from MyISAM to InnoDB, unless you use Transactional Syntax, where InnoDB is required.

allworknoplay wrote on Fri, 05 October 2007 12:44

I have begun separating the table into many tables, this in
a way will share the load of INSERTS and balance it out...

Hum... to me it doesn't make any sense: the server should handle your DB design. Server are not capricious like girls Wink

Haha I know! People are doing a lot more than I am with mysql and they seem to be doing fine. I ran a program called mysql tuner, and it said that my inserts to reads ratio was like 90% inserts and 10% reads. It also said that mysql was trying to use more RAM than the system allowed...

I'm not sure how mysql handles duplicates, perhaps somehow over time enough duplicates causes it to crash? Who knows...

But I am feeling better that the table is starting to become separated....if THAT still fails, than INNODB may be the choice.....

Report message to a moderator

Pages (3): [ « ‹ 1 2 3]

Previous Topic:	phpMyAdmin (query profiling)
Next Topic:	mysql takin' too much memory

Goto Forum:

-=] Back to Top [=-

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

]

Current Time: Sun Jul 5 17:29:10 EDT 2009

Total time taken to generate the page: 0.10877 seconds

.:: Contact :: Home ::.

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.