Re: mysql db's hosed? Help needed badly

-- 
Rick Zeman
Manager of Information Technology
Melwood Horticultural Training Center
301.599.4574 - HelpDesk
301.599.4560 - MyDesk
http://www.melwood.org

>>> Erik Weber <twiztargmail.com> 10/3/2007
3:33:56 AM >>>
Rick Zeman wrote:
> The saga continues....
>
> Last week I'd found out that I'd never run the
quarantine script.  I
> ended up running it again yesterday, starting in the
morning and let
it
> proceed all day and then overnight and it was
processing 5-10 per
minute. 
> Came in this morning and it was still running.  Later
on this morning
it
> stopped dead in its tracks.  I let it sit and it didn't
process any
more
> for hours and CPU utilization was maxxed by the mysql
processes
> (mysql-5.0.26-12 on SuSE 10.2).
> After letting it sit for hours, I tried to shut down
mysql.  It
wouldn't
> shut down after repeated attempts to shut it down
cleanly so it got
shut
> down dirty.  According to the transaction log when it
restarted, it
played
> back the incomplete transactions and the log seemed to
be happy.
>
> BUT, as soon as mysql loads the processor load jacks
>100% (dual
core),
> amavis can barely process any mail
("status=deferred (conversation
with
> 127.0.0.1[127.0.0.1] timed out while receiving the
initial server
> greeting"), and I'm dead in the water effectively
because I have way
more
> mail incoming than can be processed.  Right now, I've
reverted to the
> previous mail server.
>
> SO, what do you mysql gurus recommend for me (besides
becoming an
instant
> dba)?  Somehow, my Learning MySQL book doesn't cover
this.  
> The innodb ibdata1 file is HUGE.  Below are size
listings of the two
> pertinent directories.
>   

Check your mysql error logs to see if it does a restart when
this occur.

If it does, try to remove all instances talking to it (so
you can talk 
to it in peace), and do some SHOW TABLE STATUS. You could
also do some 
CHECK TABLE <insert table name> to see if it gives you
any errors.

If anything is broken, it should hopefully be your bayes 
database/tables, which you can safely DROP. You'll have to
do some 
re-training though.

________________-

I'm perfectly willing to do the retraining (it was the
falling behind
that got me in this trouble to begin with).  How exactly do
I drop those
tables as there's nothing useful in the logs, just
complaints about the
dirty shutdown.  I just need to get this guy back up.    Thanks.

071002  8:03:03  InnoDB: Starting shutdown...
 10 11 12 13 14 15 16 17 18071002 08:09:49  mysqld started
071002  8:09:49  InnoDB: Database was not shut down
normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd
files...
InnoDB: Restoring possible half-written data pages from the
doublewrite
InnoDB: buffer...
071002  8:09:51  InnoDB: Starting log scan based on
checkpoint at
InnoDB: log sequence number 5 494286695.
071002  8:09:52  InnoDB: Starting an apply batch of log
records to the
database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12
13 14 15 16 17
18 19 20 21 2
2 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
42 43 44 45 46
47 48 49 50 5
1 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
71 72 73 74 75
76 77 78 79 8
0 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
071002  8:09:59  InnoDB: Starting an apply batch of log
records to the
database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12
13 14 15 16 17
18 19 20 21 2
2 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
42 43 44 45 46
47 48 49 50 5
1 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
71 72 73 74 75
76 77 78 79 8
0 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
071002  8:10:05  InnoDB: Starting an apply batch of log
records to the
database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12
13 14 15 16 17
18 19 20 21 2
2 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
42 43 44 45 46
47 48 49 50 5
1 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
71 72 73 74 75
76 77 78 79 8
0 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 5
499206110
InnoDB: 1 transaction(s) which must be rolled back or
cleaned up
InnoDB: in total 562021 row operations to undo
InnoDB: Trx id counter is 0 29235456
071002  8:10:12  InnoDB: Starting an apply batch of log
records to the
database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12
13 14 15 16 17
18 19 20 21 2
2 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
42 43 44 45 46
47 48 49 50 5
1 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
71 72 73 74 75
76 77 78 79 8
0 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Starting in background the rollback of uncommitted
transactions
071002  8:10:17  InnoDB: Rolling back trx with id 0
29234583, 562021 rows
to undo

InnoDB: Progress in percents: 1071002  8:10:17  InnoDB:
Started; log
sequence number 5 
499206110
071002  8:10:17 [Note] /usr/sbin/mysqld: ready for
connections.
Version: '5.0.26'  socket: '/var/lib/mysql/mysql.sock' 
port: 3306  SUSE
MySQL RPM
 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
24 25 26 27 28
29 30 31 32 3
3 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
53 54 55 56 57
58 59 60 61 6
2 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
82 83 84 85 86
87 88 89 90 9
1 92 93 94 95 96 97 98 99 100
InnoDB: Rolling back of trx id 0 29234583 completed
071002  9:31:10  InnoDB: Rollback of non-prepared
transactions completed
_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users