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
