Hi!
>>>>> "Peter" == Peter Zaitsev <[EMAIL PROTECTED]> writes:
Peter> Hello mysql,
Peter> 1) Today I tried to reload my tables with backuing up and recovering
Peter> whole database by mysqldump - it seems like it does not work -
Peter> mysqld crashed during loading data back even after I've removed all
Peter> bad tables and bdb logs to have a clean system.
Peter> This looks like the same problem as I reported with insert test -
Peter> BDB hanges/crashes during huge multiple insert queries.
Peter> I've uploaded the mysqldump output wich crashes mysqld during load
Peter> to the secret directory as layers_la00.sql.gz.
Peter> The thing is after I've dumped the same data without the extended
Peter> insert I could load it back without any problems.
I will look at this as after the weekend.
Peter> 2) Then I was loading the data from .sql file I saw the followings:
Peter> onyx:/spylog/db # mysqladmin processlist | grep -v Sleep
Peter>
+-----+-------+------------+-------------+------------+------+-------------------+------------------------------------------------------------------------------------------------------+
Peter> | Id | User | Host | db | Command | Time | State
| Info
|
Peter>
+-----+-------+------------+-------------+------------+------+-------------------+------------------------------------------------------------------------------------------------------+
Peter> | 34 | root | localhost | la00 | Field List | 494 | Repair by
sorting |
|
Peter> | 36 | root | localhost | lacontrol | Query | 64 | System lock
| insert into layers_la00.g00keywords (counter_id,keyword) values (106339,'RSBAC')
|
Peter> | 38 | root | localhost | la00 | Field List | 468 | Waiting for
table |
|
Peter> | 39 | root | localhost | layers_la00 | Query | 0 | update
| INSERT INTO g03dirs VALUES (110912,8288,'pictures/company/itartass/calendar')
|
Peter> | 81 | root | localhost | la00 | Field List | 296 | Waiting for
table |
|
Peter> | 121 | titan | php.local | counter | Query | 5 | System lock
| SELECT lsh.begin AS period, sh.hosts7d,sh.visitors7d
Peter> FROM layers_la00.g00layers_stat_hits AS ls |
Peter> | 125 | root | localhost | | Query | 0 |
| show processlist
|
Peter>
+-----+-------+------------+-------------+------------+------+-------------------+------------------------------------------------------------------------------------------------------+
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:
The "System Lock" means that you got some problem with fnctl() on your
system.
The fix is to restart mysqld with --skip-locking and without
--enable-locking to ensure that fcntl() lock is not used.
If you are using 'mysqldump --opt', it should generate locks for the
tables. What command did you use ?
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:
Do you know if some other thread was using the table at this point ?
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.
Ok, I see what you mean. This looks VERY strange.
This could be a thread concurrency problem;
Do you use our patched glibc on your machine ?
Is the table you are inserting into a BDB table or a MyISAM table ?
Is g03stat_404refs a MyISAM or a BDB table ?
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...
Regards,
Monty
---------------------------------------------------------------------
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
