>>>>> "Tran" == Tran Hung <hungtn@stripped> writes:
Tran> I'm using MYSQL version 3.22.24 trial on WinNT 4.0. And I started mysqld with the
> following options:
Tran> Mysqld-nt --big-tables --standalone --datadir=.\Database --skip-new
> --skip-grant-tables -O max_connections=300 -O max_allowed_packet=30M
Tran> and I usually ran into these problems :
Tran> a.. When I use more than 50 JDBC connections to insert records simultaneously
> into a DB & one connection to query (not a complex query with limit records) the DB,
> sometimes the query connection runs OK. But sometimes, it appears to hang while consuming
> 100% CPU until I decided to terminate mySQL by using Windows NT Task Manager. While mySQL
> was hung up, I used MySQLWinAdmin to list pending SQL processes, I observed a list of
> queries in locked state. Using EXPLAIN to verify the SQL syntax, I found no problems with
> my SQL statements.
You should have posted the output of 'MYSQLADMIN proc' ; Without this
it's hard to say what's going on :(
Tran> a.. After mySQL was terminated by force as mentioned above, the database was in
> error (or damaged). I had to use isamchk -s & isamchk -o to repair the DB so it can be
> useful again. After the repair, the number of records in the DB was increased to almost
> twicwe as many (from 109000 records to 197000). The additional records (resulting trom the
> repair) contains unrecognizable (dirty) data.
I assume you don't have a copy of the table before repair so we can
check what isamchk did wrong ?
isamchk tries to get back as many rows as possible. If the size of the
file somehow was wrong (set by the OS) it may find more rows that was
originally in the table, but I haven't seen this happen myself.
Anyway, the new myisamchk in MySQL 3.23 should be better in this case...
Tran> a.. Finally, I tried to restart my MYSQL servers (two servers, both can read
> & update a DB on a shared drive on one of them) to run all the same data with above
> options (disable -skip-locking one). All seems OK, then the server (sharing the drive
> containing the DB) ran into this problem :
Note that on NT, MySQL runs by default without locking. This will
give you a serious problem....
You have to start with --enable-locking to get locking on NT. I
doubt however that you will get any benefit of having two servers
share the same data on NT. It's much more likely that things will go
much slower than before.
Tran> I would appreciate if anybody can help me with the following questions:
Tran> - Are there anything wrong with my start options ?
No, it looks ok, except for the --skip-new option. I don't know of
any reason to use this on MySQL 3.22.
Tran> a.. How to recover the DB after it crashed (so that we can eliminate extra
> records with dirty data as described above) ?
If you can upload an table that will contain too many rows AFTER you
repair it to: ftp://www.mysql.com/pub/mysql/secret
I may be able to answer this question.
Tran> b.. And what happened with the sharing server when the above error messages
> popped up ?
Note that the whole problem may be that you run without --enable-locking
If you run isamchk on a file, you must take down all servers that may
use the file...
Tran> Once again, thank you for your time, and help.
PS: Sorry for the long delay before replying, but I just come back from
a 2 weeks vacation trip.
|• Help me||Tran Hung||6 Nov|
| • Help me||Michael Widenius||13 Nov|