Re: Mysql issue / crashing
Sorry Would you be so kind as to explain your thinking. How would upgrading Mysql fix the issue? Regards Brent Clark On 20/04/2011 06:23, Suresh Kuna wrote: Install the latest version of mysql on top of the current version and start the database. On Tue, Apr 19, 2011 at 9:34 PM, Brent Clark brentgclarkl...@gmail.com mailto:brentgclarkl...@gmail.com wrote: Thanks for replying 5.1.55 On 19/04/2011 13:55, Suresh Kuna wrote: What is the version of MYSQL you are using currently ? On Tue, Apr 19, 2011 at 3:46 PM, Brent Clark brentgclarkl...@gmail.com mailto:brentgclarkl...@gmail.com wrote: Ive added innodb_force_recovery=4 Still no go. Original Message Subject:Mysql issue / crashing Date: Tue, 19 Apr 2011 12:15:30 +0200 From: Brent Clark brentgclarkl...@gmail.com mailto:brentgclarkl...@gmail.com To: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Hiya Im getting the following I ran myisamchk --silent --force */*.MYI But still I get the following. I cant see how I can bring Mysql up. # mysqld2912 110419 12:13:22 [Warning] 'for replication startup options' is deprecated and will be removed in a future release. Please use ''CHANGE MASTER'' instead. 110419 12:13:22 [Note] Plugin 'FEDERATED' is disabled. 110419 12:13:22 InnoDB: Initializing buffer pool, size = 512.0M 110419 12:13:22 InnoDB: Completed initialization of buffer pool InnoDB: Log scan progressed past the checkpoint lsn 10 96395066 110419 12:13:22 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 10 96451805 110419 12:13:22 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 110419 12:13:22 - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=201326592 read_buffer_size=2097152 max_used_connections=0 max_threads=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 606853 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = (nil) thread_stack 0x2 mysqld(my_print_stacktrace+0x2d) [0xb75de06d] mysqld(handle_segfault+0x49c) [0xb72ac0cc] [0xb7018400] mysqld(page_cur_parse_insert_rec+0x580) [0xb7500390] mysqld [0xb74ea325] mysqld(recv_recover_page+0x502) [0xb74ec2e2] mysqld(buf_page_io_complete+0x624) [0xb74a22e4] mysqld(fil_aio_wait+0x12d) [0xb74bdb8d] mysqld [0xb7533d80] /lib/i686/cmov/libpthread.so.0 [0xb6ffd4c0] /lib/i686/cmov/libc.so.6(clone+0x5e) [0xb6ded84e] The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash -- Thanks Suresh Kuna MySQL DBA -- Thanks Suresh Kuna MySQL DBA
Re: Mysql issue / crashing
It will, try it out. On Wed, Apr 20, 2011 at 1:11 PM, Brent Clark brentgclarkl...@gmail.comwrote: Sorry Would you be so kind as to explain your thinking. How would upgrading Mysql fix the issue? Regards Brent Clark On 20/04/2011 06:23, Suresh Kuna wrote: Install the latest version of mysql on top of the current version and start the database. On Tue, Apr 19, 2011 at 9:34 PM, Brent Clark brentgclarkl...@gmail.comwrote: Thanks for replying 5.1.55 On 19/04/2011 13:55, Suresh Kuna wrote: What is the version of MYSQL you are using currently ? On Tue, Apr 19, 2011 at 3:46 PM, Brent Clark brentgclarkl...@gmail.comwrote: Ive added innodb_force_recovery=4 Still no go. Original Message Subject:Mysql issue / crashing Date: Tue, 19 Apr 2011 12:15:30 +0200 From: Brent Clark brentgclarkl...@gmail.com To: mysql@lists.mysql.com Hiya Im getting the following I ran myisamchk --silent --force */*.MYI But still I get the following. I cant see how I can bring Mysql up. # mysqld2912 110419 12:13:22 [Warning] 'for replication startup options' is deprecated and will be removed in a future release. Please use ''CHANGE MASTER'' instead. 110419 12:13:22 [Note] Plugin 'FEDERATED' is disabled. 110419 12:13:22 InnoDB: Initializing buffer pool, size = 512.0M 110419 12:13:22 InnoDB: Completed initialization of buffer pool InnoDB: Log scan progressed past the checkpoint lsn 10 96395066 110419 12:13:22 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 10 96451805 110419 12:13:22 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 110419 12:13:22 - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=201326592 read_buffer_size=2097152 max_used_connections=0 max_threads=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 606853 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = (nil) thread_stack 0x2 mysqld(my_print_stacktrace+0x2d) [0xb75de06d] mysqld(handle_segfault+0x49c) [0xb72ac0cc] [0xb7018400] mysqld(page_cur_parse_insert_rec+0x580) [0xb7500390] mysqld [0xb74ea325] mysqld(recv_recover_page+0x502) [0xb74ec2e2] mysqld(buf_page_io_complete+0x624) [0xb74a22e4] mysqld(fil_aio_wait+0x12d) [0xb74bdb8d] mysqld [0xb7533d80] /lib/i686/cmov/libpthread.so.0 [0xb6ffd4c0] /lib/i686/cmov/libc.so.6(clone+0x5e) [0xb6ded84e] The manual page at http://dev.mysql.com/doc/mysql/en/crashing.htmlcontains information that should help you find out what is causing the crash -- Thanks Suresh Kuna MySQL DBA -- Thanks Suresh Kuna MySQL DBA -- Thanks Suresh Kuna MySQL DBA
Re: Mysql issue / crashing
On 20/04/2011 10:10, Suresh Kuna wrote: It will, try it out. Thanks for replying. My Colleague and I, we tried a different route. We retried innodb_force_recovery. But this time we started at 1 and progressed to 6. At 6 we were able to able to start working. So for our recovery procedure we have opted for mysqldump and reimport. Dont get me wrong, we know its slower, and may not be bullet proof, but we are not seeing missing data, but we are reimporting for an extra measure. Brent P.s. The one cool thing is that we have been able to add is 'innodb_file_per_table'. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
PECL: mysqlnd_ms (Load-Balancing)
http://pecl.php.net/package/mysqlnd_ms/download/1.0.0/ This is a really great idea to use replication-salves for read-access without touch the php-application and if this will work with php_flag site-specific using the available slaves automatically / leave them out if they are stopped this can make many setups very scaleable and much more efficient as do the work in php-scripts with all the needed decisions read-query, which mysql-host and is the host currently available I think this would be finally a thing to directly include in mysqlnd and leave default off The replication and load balancing plugin is a plugin for the mysqlnd library. It can be used with PHP MySQL extensions (ext/mysql, ext/mysqli, PDO_MySQL). if they are compiled to use mysqlnd. The plugin inspects queries to do read-write splitting. Read-only queries are send to configured MySQL replication slave servers all other queries are redirected to the MySQL replication master server. Very little, if any, application changes required, dependent on the usage scenario required. -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
How to avoid deadlocks.. advice needed also insight, illumination and a bit of hallucination...
I'm running into some deadlocks issues. I have this structure accounting |---movements To know the balance of the account, I usualy do a sum(movements.amount) where accounting.id=someid The issue is that the sum is starting to run very slow due hardware constraints, and I can't trow more hardware :(, so I need to find a software solution My approach was to create a balance field inside accounting, but I'm running into deadlocks, because accounting has a tree structure based upon accounting.id and accounting.parentid. Btw, I can't use triggers or store procedure due insert management reason here. Out of discussion. So, to have all the accounts in balance, I do a drill down to get all paretns, grandparent from an account... So I think, I have race conditions and multiversioning issues. I read about innodb locking and decided to use lock in share mode... But I'm runing with some deadlocks: TRANSACTION 0 264994, ACTIVE 1 sec, process no 5031, OS thread id 140061201196816 starting index read mysql tables in use 1, locked 1 LOCK WAIT 151 lock struct(s), heap size 30704, 36974 row lock(s) MySQL thread id 62743, query id 35566790 localhost 127.0.0.1 vortex Updating UPDATE `account` SET `balance` = '-3961.30' WHERE `accountid` ='408' LIMIT 1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 154 page no 11 n bits 288 index `PRIMARY` of table `management`.`account` trx id 0 264994 lock_mode X locks rec but not gap waiting Record lock, heap no 19 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 4; hex 0198; asc ;; 1: len 6; hex 0001b314; asc ;; 2: len 7; hex 3b0b18; asc ; ;; 3: len 4; hex 0194; asc ;; 4: len 1; hex 02; asc ;; 5: len 20; hex 414e4149535f5a41495a41525f414c434152415a; asc LOANS;; 6: SQL NULL; 7: SQL NULL; 8: len 1; hex 00; asc ;; 9: len 1; hex 01; asc ;; 10: len 3; hex 8fb56d; asc m;; 11: len 1; hex 00; asc ;; 12: len 1; hex 80; asc ;; 13: len 9; hex 7086e1; asc ;; *** (2) TRANSACTION: TRANSACTION 0 264995, ACTIVE 1 sec, process no 5031, OS thread id 140061201999632 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 4360 lock struct(s), heap size 456688, 579126 row lock(s) MySQL thread id 60636, query id 35566800 localhost 127.0.0.1 vortex Updating UPDATE `account` SET `balance` = '1.31' WHERE `accountid` ='11009' LIMIT 1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 154 page no 11 n bits 288 index `PRIMARY` of table `management`.`account` trx id 0 264995 lock mode S locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 4; hex 0001; asc ;; 1: len 6; hex 00040aba; asc ;; 2: len 7; hex 3c1b0a; asc ;; 3: SQL NULL; 4: len 1; hex 01; asc ;; 5: len 6; hex 41435449564f; asc ACTIVO;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 1; hex 00; asc ;; 9: len 1; hex 01; asc ;; 10: len 3; hex 8faa7d; asc };; 11: len 1; hex 01; asc ;; 12: len 1; hex 80; asc ;; 13: len 9; hex 78f72f3efa; asc / ;; Any clues about how to avoid this?
Re: How to avoid deadlocks.. advice needed also insight, illumination and a bit of hallucination...
The smoothest way to avoid deadlocks, is to ensure that all your sessions lock their tables in exactly the same order. From your explanation, that might not be as easy as one would expect, though. If you can't create triggers, is it acceptable to have delayed updates on the totals? Your idea was good, but the classic way to go about that is materialized views - in this case also known as aggregate tables. You simply run a cron job every hour or whatever that drops and recreates a (temporary) table holding all the summaries you'll ever need. Sure, the build job is heavy - but it only runs once every so often; and building an aggregate of ten subset is not as heavy as running the aggregate query for every subset separately, so there's a definite win. If you can live with 24h old data, just run the aggregate build somewhere during the slow hours. - Original Message - From: Andrés Tello mr.crip...@gmail.com To: Mailing-List mysql mysql@lists.mysql.com Sent: Wednesday, 20 April, 2011 9:15:09 PM Subject: How to avoid deadlocks.. advice needed also insight, illumination and a bit of hallucination... I'm running into some deadlocks issues. I have this structure accounting To know the balance of the account, I usualy do a sum(movements.amount) where accounting.id=someid The issue is that the sum is starting to run very slow due hardware constraints, and I can't trow more hardware :(, so I need to find a software solution My approach was to create a balance field inside accounting, but I'm running into deadlocks, because accounting has a tree structure based upon accounting.id and accounting.parentid. Btw, I can't use triggers or store procedure due insert management reason here. Out of discussion. So, to have all the accounts in balance, I do a drill down to get all paretns, grandparent from an account... So I think, I have race conditions and multiversioning issues. I read about innodb locking and decided to use lock in share mode... But I'm runing with some deadlocks: TRANSACTION 0 264994, ACTIVE 1 sec, process no 5031, OS thread id 140061201196816 starting index read mysql tables in use 1, locked 1 LOCK WAIT 151 lock struct(s), heap size 30704, 36974 row lock(s) MySQL thread id 62743, query id 35566790 localhost 127.0.0.1 vortex Updating UPDATE `account` SET `balance` = '-3961.30' WHERE `accountid` ='408' LIMIT 1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 154 page no 11 n bits 288 index `PRIMARY` of table `management`.`account` trx id 0 264994 lock_mode X locks rec but not gap waiting Record lock, heap no 19 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 4; hex 0198; asc ;; 1: len 6; hex 0001b314; asc ;; 2: len 7; hex 3b0b18; asc ; ;; 3: len 4; hex 0194; asc ;; 4: len 1; hex 02; asc ;; 5: len 20; hex 414e4149535f5a41495a41525f414c434152415a; asc LOANS;; 6: SQL NULL; 7: SQL NULL; 8: len 1; hex 00; asc ;; 9: len 1; hex 01; asc ;; 10: len 3; hex 8fb56d; asc m;; 11: len 1; hex 00; asc ;; 12: len 1; hex 80; asc ;; 13: len 9; hex 7086e1; asc ;; *** (2) TRANSACTION: TRANSACTION 0 264995, ACTIVE 1 sec, process no 5031, OS thread id 140061201999632 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 4360 lock struct(s), heap size 456688, 579126 row lock(s) MySQL thread id 60636, query id 35566800 localhost 127.0.0.1 vortex Updating UPDATE `account` SET `balance` = '1.31' WHERE `accountid` ='11009' LIMIT 1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 154 page no 11 n bits 288 index `PRIMARY` of table `management`.`account` trx id 0 264995 lock mode S locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 4; hex 0001; asc ;; 1: len 6; hex 00040aba; asc ;; 2: len 7; hex 3c1b0a; asc ;; 3: SQL NULL; 4: len 1; hex 01; asc ;; 5: len 6; hex 41435449564f; asc ACTIVO;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 1; hex 00; asc ;; 9: len 1; hex 01; asc ;; 10: len 3; hex 8faa7d; asc };; 11: len 1; hex 01; asc ;; 12: len 1; hex 80; asc ;; 13: len 9; hex 78f72f3efa; asc / ;; Any clues about how to avoid this? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql issue / crashing
Okie cool, Can you paste the error log details when it came up with force recovery 6. On Wed, Apr 20, 2011 at 6:16 PM, Brent Clark brentgclarkl...@gmail.comwrote: On 20/04/2011 10:10, Suresh Kuna wrote: It will, try it out. Thanks for replying. My Colleague and I, we tried a different route. We retried innodb_force_recovery. But this time we started at 1 and progressed to 6. At 6 we were able to able to start working. So for our recovery procedure we have opted for mysqldump and reimport. Dont get me wrong, we know its slower, and may not be bullet proof, but we are not seeing missing data, but we are reimporting for an extra measure. Brent P.s. The one cool thing is that we have been able to add is 'innodb_file_per_table'. -- Thanks Suresh Kuna MySQL DBA
