Hello mysql,
1) Today I tried to reload my tables with backuing up and recovering
whole database by mysqldump - it seems like it does not work -
mysqld crashed during loading data back even after I've removed all
bad tables and bdb logs to have a clean system.
This looks like the same problem as I reported with insert test -
BDB hanges/crashes during huge multiple insert queries.
I've uploaded the mysqldump output wich crashes mysqld during load
to the secret directory as layers_la00.sql.gz.
The thing is after I've dumped the same data without the extended
insert I could load it back without any problems.
2) Then I was loading the data from .sql file I saw the followings:
onyx:/spylog/db # mysqladmin processlist | grep -v Sleep
+-----+-------+------------+-------------+------------+------+-------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State |
|Info
| |
+-----+-------+------------+-------------+------------+------+-------------------+------------------------------------------------------------------------------------------------------+
| 34 | root | localhost | la00 | Field List | 494 | Repair by sorting |
|
| |
| 36 | root | localhost | lacontrol | Query | 64 | System lock |
|insert into layers_la00.g00keywords (counter_id,keyword) values (106339,'RSBAC')
| |
| 38 | root | localhost | la00 | Field List | 468 | Waiting for table |
|
| |
| 39 | root | localhost | layers_la00 | Query | 0 | update |
|INSERT INTO g03dirs VALUES (110912,8288,'pictures/company/itartass/calendar')
| |
| 81 | root | localhost | la00 | Field List | 296 | Waiting for table |
|
| |
| 121 | titan | php.local | counter | Query | 5 | System lock |
|SELECT lsh.begin AS period, sh.hosts7d,sh.visitors7d
FROM layers_la00.g00layers_stat_hits AS ls |
| 125 | root | localhost | | Query | 0 | |
|show processlist
| |
+-----+-------+------------+-------------+------------+------+-------------------+------------------------------------------------------------------------------------------------------+
as you see here one thread is doing repear as this happened after
mysqld crash, but note first insert query in system lock state (I've
saw several queries at this place, but all of them spent in system
lock time several tenths of seconds) this can nothing to do with
recovery (this table was just created by mysqldump couple of minutes
before) - I habe the only explanation which looks ok the system lock
takes place while many inserts are going to othe table (g03dirs) - as
soon as the table changed all other queries which was in system lock
state got a chance to run. The other thing is mysqldump does not uses
lock tables to insert data so this looks like real perfomance
(concurency) problem Some
more examples:
onyx:/spylog/db # mysqladmin processlist | grep -v Sleep
+-----+-------+------------+-------------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
| |
+-----+-------+------------+-------------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| 36 | root | localhost | lacontrol | Query | 384 | update | replace into
|layers_la00.g00stat_404refs (layer_id,id,visitors,loads,hits) values
(2,2,0,4,0),
(2,9, |
| 39 | root | localhost | layers_la00 | Query | 0 | update | INSERT INTO
|g03stat_404pages VALUES (149,1563,0,1,0)
| |
| 272 | root | localhost | | Query | 0 | | show processlist
| |
+-----+-------+------------+-------------+---------+------+--------+------------------------------------------------------------------------------------------------------+
as you see here replace is "hanged" - it's simple query wich should
not take so long. But just few seconds after:
onyx:/spylog/db # mysqladmin processlist | grep -v Sleep
+-----+-------+------------+-------------+---------+------+-------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
| |
+-----+-------+------------+-------------+---------+------+-------------+------------------------------------------------------------------------------------------------------+
| 36 | root | localhost | lacontrol | Query | 16 | update | replace into
|layers_la00.g00stat_enterpages (layer_id,id,hits,loads) values
(2,2048,2,2),
(2,1,60,60 |
| 39 | root | localhost | layers_la00 | Query | 0 | update | INSERT INTO
|g03stat_404refs VALUES (6,76851,0,1,0)
| |
| 271 | titan | mail.local | counter | Query | 1 | System lock | select
visitors,
visitors7d,
visitors30d,
|
| 273 | root | localhost | | Query | 0 | | show
|processlist
| |
+-----+-------+------------+-------------+---------+------+-------------+------------------------------------------------------------------------------------------------------+
As you see here the mysqldump moved to loading other table and the
replace passed, and now the other replace is waiting for insert. And
so on the thing continues with each table.
This does not only touches update queries but the simple one also:
mysql> select count(*) from layers_la00.g00dirs;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (32.08 sec)
this also affects all tables - I just tried to check this with newly
created table in other database:
mysql> create table z (z int) type=BDB;
Query OK, 0 rows affected (0.90 sec)
mysql> select count(*) from z;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.03 sec)
mysql> select count(*) from z;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (20.08 sec)
mysql>
Also I checked more and it seems like BDB lets other query to run not
only then last insert to the table completes, but sometimes more often
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
