Hello Michael, Friday, February 09, 2001, 3:48:53 AM, you wrote: Peter>> as you see here one thread is doing repear as this happened after Peter>> mysqld crash, but note first insert query in system lock state (I've Peter>> saw several queries at this place, but all of them spent in system Peter>> lock time several tenths of seconds) this can nothing to do with Peter>> recovery (this table was just created by mysqldump couple of minutes Peter>> before) - I habe the only explanation which looks ok the system lock Peter>> takes place while many inserts are going to othe table (g03dirs) - as Peter>> soon as the table changed all other queries which was in system lock Peter>> state got a chance to run. The other thing is mysqldump does not uses Peter>> lock tables to insert data so this looks like real perfomance Peter>> (concurency) problem Some Peter>> more examples: MW> The "System Lock" means that you got some problem with fnctl() on your MW> system. MW> The fix is to restart mysqld with --skip-locking and without MW> --enable-locking to ensure that fcntl() lock is not used. Mysql is started with --skip-locking as it's recomended on linux, so this should not be the problem. MW> If you are using 'mysqldump --opt', it should generate locks for the MW> tables. What command did you use ? At first I used mysqldump --all --opt but then mysqld crashed on multiple inserts. The next time I just started it with --all --add-drop-tables options and this is the cases for this usage. Peter>> onyx:/spylog/db # mysqladmin processlist | grep -v Sleep Peter>> +-----+-------+------------+-------------+---------+------+--------+------------------------------------------------------------------------------------------------------+ Peter>> | Id | User | Host | db | Command | Time | State | Info | Peter>> +-----+-------+------------+-------------+---------+------+--------+------------------------------------------------------------------------------------------------------+ Peter>> | 36 | root | localhost | lacontrol | Query | 384 | update | replace into layers_la00.g00stat_404refs (layer_id,id,visitors,loads,hits) values Peter>> (2,2,0,4,0), Peter>> (2,9, | Peter>> | 39 | root | localhost | layers_la00 | Query | 0 | update | INSERT INTO g03stat_404pages VALUES (149,1563,0,1,0) | Peter>> | 272 | root | localhost | | Query | 0 | | show processlist | Peter>> +-----+-------+------------+-------------+---------+------+--------+------------------------------------------------------------------------------------------------------+ Peter>> as you see here replace is "hanged" - it's simple query wich should Peter>> not take so long. But just few seconds after: MW> Do you know if some other thread was using the table at this point ? layers_st00.g00stat_404refs ? No. This is the only thread using it. Peter>> onyx:/spylog/db # mysqladmin processlist | grep -v Sleep Peter>> +-----+-------+------------+-------------+---------+------+-------------+------------------------------------------------------------------------------------------------------+Peter>> | Id | User | Host | db | Command | Time | State | Info | Peter>> +-----+-------+------------+-------------+---------+------+-------------+------------------------------------------------------------------------------------------------------+ Peter>> | 36 | root | localhost | lacontrol | Query | 16 | update | replace into layers_la00.g00stat_enterpages (layer_id,id,hits,loads) values Peter>> (2,2048,2,2), Peter>> (2,1,60,60 | Peter>> | 39 | root | localhost | layers_la00 | Query | 0 | update | INSERT INTO g03stat_404refs VALUES (6,76851,0,1,0) | Peter>> | 271 | titan | mail.local | counter | Query | 1 | System lock | select Peter>> visitors, Peter>> visitors7d, Peter>> visitors30d, Peter>> | Peter>> | 273 | root | localhost | | Query | 0 | | show processlist | Peter>> +-----+-------+------------+-------------+---------+------+-------------+------------------------------------------------------------------------------------------------------+ Peter>> As you see here the mysqldump moved to loading other table and the Peter>> replace passed, and now the other replace is waiting for insert. And Peter>> so on the thing continues with each table. MW> Ok, I see what you mean. This looks VERY strange. MW> This could be a thread concurrency problem; I don't think so. The chance one thread will not be given a time to run in 300 seconds then there are only 3-4 of active threads seems too strange for me even on linux, the other thing is - the additional thread which uses MYISAM runs quite fast. MW> Do you use our patched glibc on your machine ? This is one CPU machine with standard SuSE glibc. MW> Is the table you are inserting into a BDB table or a MyISAM table ? MW> Is g03stat_404refs a MyISAM or a BDB table ? all of the tables I'm speaking about are BDB. Peter>> This does not only touches update queries but the simple one also: mysql>> select count(*) from layers_la00.g00dirs; Peter>> +----------+ Peter>> | count(*) | Peter>> +----------+ Peter>> | 0 | Peter>> +----------+ Peter>> 1 row in set (32.08 sec) Peter>> this also affects all tables - I just tried to check this with newly Peter>> created table in other database: mysql>> create table z (z int) type=BDB; Peter>> Query OK, 0 rows affected (0.90 sec) mysql>> select count(*) from z; Peter>> +----------+ Peter>> | count(*) | Peter>> +----------+ Peter>> | 0 | Peter>> +----------+ Peter>> 1 row in set (0.03 sec) mysql>> select count(*) from z; Peter>> +----------+ Peter>> | count(*) | Peter>> +----------+ Peter>> | 0 | Peter>> +----------+ Peter>> 1 row in set (20.08 sec) Peter>> Also I checked more and it seems like BDB lets other query to run not Peter>> only then last insert to the table completes, but sometimes more often Peter>> and sometimes more seldom... -- Best regards, Peter mailto:[EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php