When using the BDB tables and RedHat 6.2 or 7.0, should --skip-locking be used or not? I know it was recommended for earlier versions of RedHat, like version 5.x. Thanks in advance, Rick ----------------------------------------- / Rick Mehalick Senior Consultant / Shell Services International SSI-GPAX / Phone: 281-544-5092(WCK) / Fax: 281-544-2646(WCK) / email: [EMAIL PROTECTED] ----------------------------------------- -----Original Message----- From: Michael Widenius [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 08, 2001 6:49 PM To: Peter Zaitsev Cc: [EMAIL PROTECTED] Subject: BDB 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
