Re: innodb log sequence problem
Thank you for answer. The problem is that I wrote in previous message that there is no sql backup just the files for binary backup. Hardware we are using is a simple laptop with Windows 7 that runs 5.1 server in case the originally installed files are in use. It runs an 5.5 server paralelly as well without any problems. 2015.08.05. 17:17 keltezéssel, Reindl Harald írta: Am 05.08.2015 um 17:06 schrieb Csepregi Árpád: 150805 17:02:31 InnoDB: Page dump in ascii and hex (16384 bytes): hex... 150805 17:02:31 InnoDB: Page checksum 1094951825, prior-to-4.0.14-form checksum 1449969277 InnoDB: stored checksum 1467223489, prior-to-4.0.14-form stored checksum 87759728 InnoDB: Page lsn 1054562273 1692468334, low 4 bytes of lsn at page end 3304862103 InnoDB: Page number (if stored to page already) 544833488, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 960999566 InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 7. InnoDB: You may have to recover from a backup Does anyone have any idea how to recover? check hardware, especially memory and restore your backup! -- Csepregi Árpád Integ Rendszerház Kft. Az Ön szolgálatában mindennap. 06-70-629-2114 www.integ.hu Online pénztárgép naplófájl kiolvasó rendszer WWW.KONTROLLSZALAG.HU -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: innodb log sequence problem
Am 05.08.2015 um 17:06 schrieb Csepregi Árpád: 150805 17:02:31 InnoDB: Page dump in ascii and hex (16384 bytes): hex... 150805 17:02:31 InnoDB: Page checksum 1094951825, prior-to-4.0.14-form checksum 1449969277 InnoDB: stored checksum 1467223489, prior-to-4.0.14-form stored checksum 87759728 InnoDB: Page lsn 1054562273 1692468334, low 4 bytes of lsn at page end 3304862103 InnoDB: Page number (if stored to page already) 544833488, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 960999566 InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 7. InnoDB: You may have to recover from a backup Does anyone have any idea how to recover? check hardware, especially memory and restore your backup! signature.asc Description: OpenPGP digital signature
Re: InnoDB error 5
2013/11/21 Reindl Harald h.rei...@thelounge.net Am 21.11.2013 13:51, schrieb Paul Halliday: Had a system crash this morning and I can't seem to get mysql back up and running. This is the error: InnoDB: Progress in percent: 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 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 2013-11-21 08:47:26 1570 [ERROR] InnoDB: Tried to read 16384 bytes at offset 589824. Was only able to read -1. 2013-11-21 08:47:26 802808c00 InnoDB: Operating system error number 5 in a file operation. InnoDB: Error number 5 means 'Input/output error'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html 2013-11-21 08:47:26 802808c00 InnoDB: File operation call: 'read' returned OS error 105. 2013-11-21 08:47:26 802808c00 InnoDB: Cannot continue operation. I followed that link but it doesn't tell me anything outside of what is above. Can I fix this? i would look in the *system logs* because this pretty sure comes from the underlying operating system and is *not* mysql specific which is also in the message statet with returned OS error 105 http://lxr.free-electrons.com/source/include/uapi/asm-generic/errno-base.h#L8 Looks like a broken disk or FS corruption :-( Good luck! Manuel.
Re: InnoDB error 5
It was indeed corruption :/ what a day. I was able to move everything over to another partition and have managed to get mysql up and running again. There was a single file I could not, an .idb (the ,.frm is there). Is it possible to fix this from ibdata or the logs? Thanks. On Thu, Nov 21, 2013 at 9:46 AM, Manuel Arostegui man...@tuenti.com wrote: 2013/11/21 Reindl Harald h.rei...@thelounge.net Am 21.11.2013 13:51, schrieb Paul Halliday: Had a system crash this morning and I can't seem to get mysql back up and running. This is the error: InnoDB: Progress in percent: 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 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 2013-11-21 08:47:26 1570 [ERROR] InnoDB: Tried to read 16384 bytes at offset 589824. Was only able to read -1. 2013-11-21 08:47:26 802808c00 InnoDB: Operating system error number 5 in a file operation. InnoDB: Error number 5 means 'Input/output error'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html 2013-11-21 08:47:26 802808c00 InnoDB: File operation call: 'read' returned OS error 105. 2013-11-21 08:47:26 802808c00 InnoDB: Cannot continue operation. I followed that link but it doesn't tell me anything outside of what is above. Can I fix this? i would look in the *system logs* because this pretty sure comes from the underlying operating system and is *not* mysql specific which is also in the message statet with returned OS error 105 http://lxr.free-electrons.com/source/include/uapi/asm-generic/errno-base.h#L8 Looks like a broken disk or FS corruption :-( Good luck! Manuel. -- Paul Halliday http://www.pintumbler.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: InnoDB error 5
What is the best way to backup your database. Which are the files that I need to store on a usb disk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: InnoDB error 5
Hello Nick, On 11/21/2013 10:32 AM, Nick Cameo wrote: OOoopppsss! I do mean for recovery/continual backup. I will do it manually, but basically get all the data on a USB disk and be able to recover/move it (the data) on another machine, the same machine etc.. I hope I did not just open up a can of worms. We just went live and this post gave me a rude awakening. What is an effective easy to follow protocol for backup and recovery in mysql! Nick from Toronto There are two basic types of backups, logical and physical. Logical backups are performed by a utility that converts your database objects into their CREATE ... commands and exports your data as INSERT ... commands (or as delimited files). These kinds of backups are quite portable and compress well. An example of such a tool is mysqldump. http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html Physical backups can happen many different ways. The easiest version to make/restore is the 'cold copy'. This is exactly what it sounds like. Shutdown your mysqld and make a copy of everything. At the absolute minimum you need the ibdata files, the ib_log files, and all folders inside your --datadir location. Warm or hot copies are provided by tools that coordinate with the server to synchronize the state of the InnoDB data to the moment the non-InnoDB data has been captured. One example of this is MySQL Enterprise Backup. http://dev.mysql.com/doc/mysql-enterprise-backup/3.9/en/index.html Additional details abound in the manual: http://dev.mysql.com/doc/refman/5.6/en/backup-types.html Regards, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: InnoDB error 5
Am 21.11.2013 18:59, schrieb Paul Halliday: It was indeed corruption :/ what a day. I was able to move everything over to another partition and have managed to get mysql up and running again. There was a single file I could not, an .idb (the ,.frm is there). Is it possible to fix this from ibdata or the logs? no - that's what backups are for lesson learned the hard way for production you have usually a replication-slave in the same network which is regulary stopped and it's datadir rsynced to a offsite-backup (one possible backup strategy) and so if one server get a corrupt filesystem there is a just-in-time backup while if things are going terrible wrong (power outage and the slave is also corrupt you rsync back the slightly outdated offsite backup On Thu, Nov 21, 2013 at 9:46 AM, Manuel Arostegui man...@tuenti.com wrote: 2013/11/21 Reindl Harald h.rei...@thelounge.net Am 21.11.2013 13:51, schrieb Paul Halliday: Had a system crash this morning and I can't seem to get mysql back up and running. This is the error: InnoDB: Progress in percent: 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 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 2013-11-21 08:47:26 1570 [ERROR] InnoDB: Tried to read 16384 bytes at offset 589824. Was only able to read -1. 2013-11-21 08:47:26 802808c00 InnoDB: Operating system error number 5 in a file operation. InnoDB: Error number 5 means 'Input/output error'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html 2013-11-21 08:47:26 802808c00 InnoDB: File operation call: 'read' returned OS error 105. 2013-11-21 08:47:26 802808c00 InnoDB: Cannot continue operation. I followed that link but it doesn't tell me anything outside of what is above. Can I fix this? i would look in the *system logs* because this pretty sure comes from the underlying operating system and is *not* mysql specific which is also in the message statet with returned OS error 105 http://lxr.free-electrons.com/source/include/uapi/asm-generic/errno-base.h#L8 Looks like a broken disk or FS corruption :-( signature.asc Description: OpenPGP digital signature
Re: InnoDB error 5
OOoopppsss! I do mean for recovery/continual backup. I will do it manually, but basically get all the data on a USB disk and be able to recover/move it (the data) on another machine, the same machine etc.. I hope I did not just open up a can of worms. We just went live and this post gave me a rude awakening. What is an effective easy to follow protocol for backup and recovery in mysql! Nick from Toronto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: InnoDB error 5
Am 21.11.2013 13:51, schrieb Paul Halliday: Had a system crash this morning and I can't seem to get mysql back up and running. This is the error: InnoDB: Progress in percent: 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 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 2013-11-21 08:47:26 1570 [ERROR] InnoDB: Tried to read 16384 bytes at offset 589824. Was only able to read -1. 2013-11-21 08:47:26 802808c00 InnoDB: Operating system error number 5 in a file operation. InnoDB: Error number 5 means 'Input/output error'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html 2013-11-21 08:47:26 802808c00 InnoDB: File operation call: 'read' returned OS error 105. 2013-11-21 08:47:26 802808c00 InnoDB: Cannot continue operation. I followed that link but it doesn't tell me anything outside of what is above. Can I fix this? i would look in the *system logs* because this pretty sure comes from the underlying operating system and is *not* mysql specific which is also in the message statet with returned OS error 105 http://lxr.free-electrons.com/source/include/uapi/asm-generic/errno-base.h#L8 signature.asc Description: OpenPGP digital signature
Re: InnoDB problem.
What's the MySQL error log have to say? - Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.com To: Johan De Meersman vegiv...@tuxera.be Sent: Tuesday, 23 July, 2013 3:39:55 PM Subject: Re: InnoDB problem. Yep, I do backup of /home/mysql/ib* files too :D What it occurs is that even with ibdata1, ib_logfile0 and ib_logfile1 in it's due place, MySQL (provided by xampp) shows me the following messages, when trying to open InnoDB tables: SHOW FULL FIELDS FROM `my_innodb_table` ; #1286 - Unknown table engine 'InnoDB' skin-innodb is commented but either way InnoDB engine are not shown when I execute show engines command. 2013/7/22 Johan De Meersman vegiv...@tuxera.be -- Att. Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67 -- Unhappiness is discouraged and will be corrected with kitten pictures.
Re: InnoDB problem.
Eek. No immediate clue here, but maybe someone else does - so please keep the list in CC at all times :-p Random question: were the files backed up from a different version? I'd expect some kind of warning about that in the logs, really, but you never know. - Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.com To: Johan De Meersman vegiv...@tuxera.be Sent: Tuesday, 23 July, 2013 6:34:47 PM Subject: Re: InnoDB problem. The error log: 130723 10:04:23 [ERROR] Plugin 'InnoDB' init function returned error. 130723 10:04:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 130723 10:04:23 [Note] Event Scheduler: Loaded 0 events 130723 10:04:23 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for connections. Version: '5.1.41' socket: '' port: 3306 Source distribution 2013/7/23 Johan De Meersman vegiv...@tuxera.be -- Att. Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67 -- Unhappiness is discouraged and will be corrected with kitten pictures.
RE: InnoDB problem.
Did you change innodb_log_file_size? -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Tuesday, July 23, 2013 9:57 AM To: Luis H. Forchesatto; mysql list Subject: Re: InnoDB problem. Eek. No immediate clue here, but maybe someone else does - so please keep the list in CC at all times :-p Random question: were the files backed up from a different version? I'd expect some kind of warning about that in the logs, really, but you never know. - Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.com To: Johan De Meersman vegiv...@tuxera.be Sent: Tuesday, 23 July, 2013 6:34:47 PM Subject: Re: InnoDB problem. The error log: 130723 10:04:23 [ERROR] Plugin 'InnoDB' init function returned error. 130723 10:04:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 130723 10:04:23 [Note] Event Scheduler: Loaded 0 events 130723 10:04:23 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for connections. Version: '5.1.41' socket: '' port: 3306 Source distribution 2013/7/23 Johan De Meersman vegiv...@tuxera.be -- Att. Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67 -- Unhappiness is discouraged and will be corrected with kitten pictures.
Re: InnoDB problem.
2013/7/23 Rick James rja...@yahoo-inc.com Did you change innodb_log_file_size? innodb_log_file_size error always appears in the logs...he only posted a few lines of his log...but I guess (or I want to believe) he's gone through the whole log before starting the thread :-) Manuel -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Tuesday, July 23, 2013 9:57 AM To: Luis H. Forchesatto; mysql list Subject: Re: InnoDB problem. Eek. No immediate clue here, but maybe someone else does - so please keep the list in CC at all times :-p Random question: were the files backed up from a different version? I'd expect some kind of warning about that in the logs, really, but you never know. - Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.com To: Johan De Meersman vegiv...@tuxera.be Sent: Tuesday, 23 July, 2013 6:34:47 PM Subject: Re: InnoDB problem. The error log: 130723 10:04:23 [ERROR] Plugin 'InnoDB' init function returned error. 130723 10:04:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 130723 10:04:23 [Note] Event Scheduler: Loaded 0 events 130723 10:04:23 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for connections. Version: '5.1.41' socket: '' port: 3306 Source distribution 2013/7/23 Johan De Meersman vegiv...@tuxera.be -- Att. Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67 -- Unhappiness is discouraged and will be corrected with kitten pictures. -- Manuel Aróstegui Systems Team tuenti.com
RE: InnoDB problem.
Either change it back, or delete the log files so that they will be built in the new size. (Backup the entire tree, just in case.) From: Manuel Arostegui [mailto:man...@tuenti.com] Sent: Tuesday, July 23, 2013 1:05 PM To: Rick James Cc: Johan De Meersman; Luis H. Forchesatto; mysql list Subject: Re: InnoDB problem. 2013/7/23 Rick James rja...@yahoo-inc.commailto:rja...@yahoo-inc.com Did you change innodb_log_file_size? innodb_log_file_size error always appears in the logs...he only posted a few lines of his log...but I guess (or I want to believe) he's gone through the whole log before starting the thread :-) Manuel -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.bemailto:vegiv...@tuxera.be] Sent: Tuesday, July 23, 2013 9:57 AM To: Luis H. Forchesatto; mysql list Subject: Re: InnoDB problem. Eek. No immediate clue here, but maybe someone else does - so please keep the list in CC at all times :-p Random question: were the files backed up from a different version? I'd expect some kind of warning about that in the logs, really, but you never know. - Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.commailto:luisforchesa...@gmail.com To: Johan De Meersman vegiv...@tuxera.bemailto:vegiv...@tuxera.be Sent: Tuesday, 23 July, 2013 6:34:47 PM Subject: Re: InnoDB problem. The error log: 130723 10:04:23 [ERROR] Plugin 'InnoDB' init function returned error. 130723 10:04:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 130723 10:04:23 [Note] Event Scheduler: Loaded 0 events 130723 10:04:23 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for connections. Version: '5.1.41' socket: '' port: 3306 Source distribution 2013/7/23 Johan De Meersman vegiv...@tuxera.bemailto:vegiv...@tuxera.be -- Att. Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67 -- Unhappiness is discouraged and will be corrected with kitten pictures. -- Manuel Aróstegui Systems Team tuenti.comhttp://tuenti.com
Re: InnoDB problem.
- Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.com Subject: InnoDB problem. Permission and owner of the table files (.frm files) are ok, since it recognizes MyISAM tables (they have the same permission). Oops. You should always read the fine manual. You took file-level backups, yes? Did they include the ibdata1 and similar files? Those contain innodb's dictionary - and in default installs also all the actual tables. The database/* files only contain the .frm, for innodb. If I'm right, you haven't got a backup at all. I'm crossing my fingers that I'm wrong... -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Innodb innodb_buffer_pool_size?
Hi claudio, The best value for innodb buffer pool size is considered to be 60 to 70 percent of ram. So in your case, this must be around 4 gb. That would work better. Regards. Vikas -Original Message- From: Claudio Nanni claudio.na...@gmail.com Sent: 16-05-2013 14:12 To: Rafał Radecki radecki.ra...@gmail.com Cc: mysql@lists.mysql.com mysql@lists.mysql.com Subject: Re: Innodb innodb_buffer_pool_size? Hi Rafal, I am trying to set the best value for innodb_buffer_pool_size. My system has 6GB of ram. My question: how to tell if my innodb_buffer_pool_size is ok? If this is a MySQL dedicated server, In your case I would set it to 2GB-3GB. You will have the whole data in RAM now and for some time. Does Buffer pool hit rate 1000 / 1000 mean that I can lower it? No. It means it is doing fine. And if you don't need RAM for anything else why lower it? Does Free buffers 0 mean that I should make it larger? No. InnoDB will always try to allocate each block in the buffer pool. Eventually what else to check? (a) mysql SELECT engine,sum(data_length)/1024/1024 as DATA_MB,sum(INDEX_LENGTH)/1024/1024 as INDEX_MB FROM information_schema.tables GROUP BY engine; To check the real size of the dataset. (b) Note: mysql-server-5.0.58 you should upgrade to latest 5.0.96 Regards Claudio
Re: Innodb innodb_buffer_pool_size?
Am 16.05.2013 11:01, schrieb Vikas Shukla: The best value for innodb buffer pool size is considered to be 60 to 70 percent of ram. So in your case, this must be around 4 gb. That would work better. hwo would a innodb_buffer_pool larger than the whole database make anything better? the best value is as large as the expected dataset, more is wasting system ressources -Original Message- From: Claudio Nanni claudio.na...@gmail.com Sent: 16-05-2013 14:12 To: Rafał Radecki radecki.ra...@gmail.com Cc: mysql@lists.mysql.com mysql@lists.mysql.com Subject: Re: Innodb innodb_buffer_pool_size? Hi Rafal, I am trying to set the best value for innodb_buffer_pool_size. My system has 6GB of ram. My question: how to tell if my innodb_buffer_pool_size is ok? If this is a MySQL dedicated server, In your case I would set it to 2GB-3GB. You will have the whole data in RAM now and for some time. Does Buffer pool hit rate 1000 / 1000 mean that I can lower it? No. It means it is doing fine. And if you don't need RAM for anything else why lower it? Does Free buffers 0 mean that I should make it larger? No. InnoDB will always try to allocate each block in the buffer pool. Eventually what else to check? (a) mysql SELECT engine,sum(data_length)/1024/1024 as DATA_MB,sum(INDEX_LENGTH)/1024/1024 as INDEX_MB FROM information_schema.tables GROUP BY engine; To check the real size of the dataset. (b) Note: mysql-server-5.0.58 you should upgrade to latest 5.0.96 Regards Claudio -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development 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
RE: Innodb innodb_buffer_pool_size?
Sorry I failed to specify ghatna is only applicable when the database is quiet large. Vikas -Original Message- From: Reindl Harald h.rei...@thelounge.net Sent: 16-05-2013 14:43 To: mysql@lists.mysql.com mysql@lists.mysql.com Subject: Re: Innodb innodb_buffer_pool_size? Am 16.05.2013 11:01, schrieb Vikas Shukla: The best value for innodb buffer pool size is considered to be 60 to 70 percent of ram. So in your case, this must be around 4 gb. That would work better. hwo would a innodb_buffer_pool larger than the whole database make anything better? the best value is as large as the expected dataset, more is wasting system ressources -Original Message- From: Claudio Nanni claudio.na...@gmail.com Sent: 16-05-2013 14:12 To: Rafał Radecki radecki.ra...@gmail.com Cc: mysql@lists.mysql.com mysql@lists.mysql.com Subject: Re: Innodb innodb_buffer_pool_size? Hi Rafal, I am trying to set the best value for innodb_buffer_pool_size. My system has 6GB of ram. My question: how to tell if my innodb_buffer_pool_size is ok? If this is a MySQL dedicated server, In your case I would set it to 2GB-3GB. You will have the whole data in RAM now and for some time. Does Buffer pool hit rate 1000 / 1000 mean that I can lower it? No. It means it is doing fine. And if you don't need RAM for anything else why lower it? Does Free buffers 0 mean that I should make it larger? No. InnoDB will always try to allocate each block in the buffer pool. Eventually what else to check? (a) mysql SELECT engine,sum(data_length)/1024/1024 as DATA_MB,sum(INDEX_LENGTH)/1024/1024 as INDEX_MB FROM information_schema.tables GROUP BY engine; To check the real size of the dataset. (b) Note: mysql-server-5.0.58 you should upgrade to latest 5.0.96 Regards Claudio -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development 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
Re: Innodb innodb_buffer_pool_size?
Am 16.05.2013 10:11, schrieb Rafał Radecki: I am trying to set the best value for innodb_buffer_pool_size. My system has 6GB of ram. innodb_buffer_pool_size = 1024M Size of innodb tables: du -h /mysql/ibdata1 1.8G/mysql/ibdata1 in the best case innodb_buffer_pool_size as big as innodb-data signature.asc Description: OpenPGP digital signature
Re: Innodb innodb_buffer_pool_size?
Am 16.05.2013 13:31, schrieb Ilya Kazakevich: hwo would a innodb_buffer_pool larger than the whole database make anything better? the best value is as large as the expected dataset, more is wasting system ressources You also may need space for adaptive indexes, locks etc so it is recommended to have pool size about 110% of your data. yes but that is far away from 4 GB as a reply to du -h /mysql/ibdata1 1.8G /mysql/ibdata1 which never got shrinked even if your real data are only half of it after deletions 60-70% of RAM is a naive not to say dumb reccomendation, especially in times where a ordinary workstation has 16 GB memory and it will bring you no benefit while it may lead in troubles if whatever process allocates a huge amount of memory sooner or later and the system starts to swap or OOM killer get in action and is killing your mysqld as first process signature.asc Description: OpenPGP digital signature
Re: Innodb innodb_buffer_pool_size?
Hi Rafal, I am trying to set the best value for innodb_buffer_pool_size. My system has 6GB of ram. My question: how to tell if my innodb_buffer_pool_size is ok? If this is a MySQL dedicated server, In your case I would set it to 2GB-3GB. You will have the whole data in RAM now and for some time. Does Buffer pool hit rate 1000 / 1000 mean that I can lower it? No. It means it is doing fine. And if you don't need RAM for anything else why lower it? Does Free buffers 0 mean that I should make it larger? No. InnoDB will always try to allocate each block in the buffer pool. Eventually what else to check? (a) mysql SELECT engine,sum(data_length)/1024/1024 as DATA_MB,sum(INDEX_LENGTH)/1024/1024 as INDEX_MB FROM information_schema.tables GROUP BY engine; To check the real size of the dataset. (b) Note: mysql-server-5.0.58 you should upgrade to latest 5.0.96 Regards Claudio
Re: InnoDB interaction between secondary and primary keys.
Thank you Rick! -- Jeremy Chase http://twitter.com/jeremychase On Wed, Jan 30, 2013 at 4:24 PM, Rick James rja...@yahoo-inc.com wrote: The hint of a change is in here (search for secondary): http://jorgenloland.blogspot.co.uk/2012/12/favorite-mysql-56-features-optimizer.html -Original Message- From: Rick James Sent: Wednesday, January 30, 2013 1:08 PM To: 'Jeremy Chase'; mysql@lists.mysql.com Subject: RE: InnoDB interaction between secondary and primary keys. secondarykey and redundantkey are redundant with each other -- in all versions of InnoDB. One expert said that redundant key would have two copies of `1`,`2`. I think he is wrong. I believe the two are the same in size. There is a subtle change in 5.6 that _may_ make a _few_ queries work better with redundantkey. I prefer to specify as many fields in the key as make sense for the SELECT(s), then let the engine add any more fields as needed to fill out the PK. That is, I might say (3,4) or (3,4,1) or (3,4,1,2) or (3,4,2), etc, depending on the queries. When looking up a row(s) by a secondary key, the engine first drills down the secondary BTree, finds the PK(s), then drills down the PRIMARY BTree. -Original Message- From: Jeremy Chase [mailto:jeremych...@gmail.com] Sent: Wednesday, January 30, 2013 11:25 AM To: mysql@lists.mysql.com Subject: InnoDB interaction between secondary and primary keys. Hello, I've been working with a secondary index and would like some clarification about how the primary columns are included. So, in the following example, is the secondaryKey effectively the same as redundantKey? CREATE TABLE `example` ( `1` int(10) unsigned NOT NULL, `2` int(10) unsigned NOT NULL, `3` int(10) unsigned NOT NULL, `4` int(10) unsigned NOT NULL, PRIMARY KEY (`1`,`2`), KEY `secondaryKey` (`3`, `4`) KEY `redundantKey` (`3`, `4`, `1`, `2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 Background: http://dev.mysql.com/doc/refman/5.5/en/innodb-index- types.html In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. Thank you! Jeremy
RE: InnoDB interaction between secondary and primary keys.
secondarykey and redundantkey are redundant with each other -- in all versions of InnoDB. One expert said that redundant key would have two copies of `1`,`2`. I think he is wrong. I believe the two are the same in size. There is a subtle change in 5.6 that _may_ make a _few_ queries work better with redundantkey. I prefer to specify as many fields in the key as make sense for the SELECT(s), then let the engine add any more fields as needed to fill out the PK. That is, I might say (3,4) or (3,4,1) or (3,4,1,2) or (3,4,2), etc, depending on the queries. When looking up a row(s) by a secondary key, the engine first drills down the secondary BTree, finds the PK(s), then drills down the PRIMARY BTree. -Original Message- From: Jeremy Chase [mailto:jeremych...@gmail.com] Sent: Wednesday, January 30, 2013 11:25 AM To: mysql@lists.mysql.com Subject: InnoDB interaction between secondary and primary keys. Hello, I've been working with a secondary index and would like some clarification about how the primary columns are included. So, in the following example, is the secondaryKey effectively the same as redundantKey? CREATE TABLE `example` ( `1` int(10) unsigned NOT NULL, `2` int(10) unsigned NOT NULL, `3` int(10) unsigned NOT NULL, `4` int(10) unsigned NOT NULL, PRIMARY KEY (`1`,`2`), KEY `secondaryKey` (`3`, `4`) KEY `redundantKey` (`3`, `4`, `1`, `2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 Background: http://dev.mysql.com/doc/refman/5.5/en/innodb-index- types.html In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. Thank you! Jeremy
RE: InnoDB interaction between secondary and primary keys.
The hint of a change is in here (search for secondary): http://jorgenloland.blogspot.co.uk/2012/12/favorite-mysql-56-features-optimizer.html -Original Message- From: Rick James Sent: Wednesday, January 30, 2013 1:08 PM To: 'Jeremy Chase'; mysql@lists.mysql.com Subject: RE: InnoDB interaction between secondary and primary keys. secondarykey and redundantkey are redundant with each other -- in all versions of InnoDB. One expert said that redundant key would have two copies of `1`,`2`. I think he is wrong. I believe the two are the same in size. There is a subtle change in 5.6 that _may_ make a _few_ queries work better with redundantkey. I prefer to specify as many fields in the key as make sense for the SELECT(s), then let the engine add any more fields as needed to fill out the PK. That is, I might say (3,4) or (3,4,1) or (3,4,1,2) or (3,4,2), etc, depending on the queries. When looking up a row(s) by a secondary key, the engine first drills down the secondary BTree, finds the PK(s), then drills down the PRIMARY BTree. -Original Message- From: Jeremy Chase [mailto:jeremych...@gmail.com] Sent: Wednesday, January 30, 2013 11:25 AM To: mysql@lists.mysql.com Subject: InnoDB interaction between secondary and primary keys. Hello, I've been working with a secondary index and would like some clarification about how the primary columns are included. So, in the following example, is the secondaryKey effectively the same as redundantKey? CREATE TABLE `example` ( `1` int(10) unsigned NOT NULL, `2` int(10) unsigned NOT NULL, `3` int(10) unsigned NOT NULL, `4` int(10) unsigned NOT NULL, PRIMARY KEY (`1`,`2`), KEY `secondaryKey` (`3`, `4`) KEY `redundantKey` (`3`, `4`, `1`, `2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 Background: http://dev.mysql.com/doc/refman/5.5/en/innodb-index- types.html In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. Thank you! Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Innodb, MySQL 5.5.28 - Would an incorrect setting in my.cnf cause mysqld to randomly crash on high load?
Nothing looks bad. 96G for the buffer_pool is bigger than I have experienced, but I know of no reason for it to fail (given that you have 128GB of RAM). -Original Message- From: Tom [mailto:livefortheda...@gmail.com] Sent: Wednesday, November 21, 2012 5:17 PM To: mysql@lists.mysql.com Subject: Innodb, MySQL 5.5.28 - Would an incorrect setting in my.cnf cause mysqld to randomly crash on high load? We have a high-end server, 128GB RAM, 32 Core , Xeon, SSD RAID 10 - running Ubuntu 12.04 with MySQL 5.5.28 . Doing random imports to large InnoDB tables, over 50+ gigs, randomly after a few hours of heavy load, mysql does a Signal 11 and crashes. We have tried to move hardware. Doing a full dump (but not a restore yet) gives no issues. Usually on corrupted tables, a dump would fail no? Below is the crash log and my.cnf . --- 12:45:4 UTC - 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=536870912 read_buffer_size=131072 max_used_connections=324 max_threads=200 thread_count=308 connection_count=308 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 965187 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x7fc7eb1b5040 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 = 7fadf6abfe60 thread_stack 0x3 /usr/sbin/mysqld(my_print_stacktrace+0x29)[0x7fc758522759] /usr/sbin/mysqld(handle_fatal_signal+0x483)[0x7fc7583e9ae3] /lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7fc75713bcb0] /usr/sbin/mysqld(+0x6671b0)[0x7fc75863a1b0] /usr/sbin/mysqld(+0x61d6b9)[0x7fc7585f06b9] /usr/sbin/mysqld(+0x630d12)[0x7fc758603d12] /usr/sbin/mysqld(+0x6319c2)[0x7fc7586049c2] /usr/sbin/mysqld(+0x631d85)[0x7fc758604d85] /usr/sbin/mysqld(+0x626e7d)[0x7fc7585f9e7d] /usr/sbin/mysqld(+0x633cea)[0x7fc758606cea] /usr/sbin/mysqld(+0x6347e2)[0x7fc7586077e2] /usr/sbin/mysqld(+0x624426)[0x7fc7585f7426] /usr/sbin/mysqld(+0x610871)[0x7fc7585e3871] /usr/sbin/mysqld(+0x5d4cb0)[0x7fc7585a7cb0] /usr/sbin/mysqld(+0x5b7c9c)[0x7fc75858ac9c] /usr/sbin/mysqld(_ZN7handler21read_multi_range_nextEPP18st_key_multi_ra nge+0x24)[0x7fc7583e9fe4] /usr/sbin/mysqld(_ZN18QUICK_RANGE_SELECT8get_nextEv+0x3c)[0x7fc7584a3c8 c] /usr/sbin/mysqld(+0x4e9195)[0x7fc7584bc195] /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x71)[0x7fc7582f 1741] /usr/sbin/mysqld(+0x32f025)[0x7fc758302025] /usr/sbin/mysqld(_ZN4JOIN4execEv+0x4a5)[0x7fc758311155] /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_E S2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_sele ct_lex+0x130)[0x7fc75830d000] /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x17c)[0x 7fc758312f5c] /usr/sbin/mysqld(+0x2f66b4)[0x7fc7582c96b4] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x16d8)[0x7fc7582d1118] /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x10f)[0x7fc758 2d5daf] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x13 80)[0x7fc7582d7200] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1bd)[0x7fc75837b7a d] /usr/sbin/mysqld(handle_one_connection+0x50)[0x7fc75837b810] /lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a)[0x7fc757133e9a] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fc756864cbd] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7faa4c18e440): is an invalid pointer Connection ID (thread ID): 2286 Status: NOT_KILLED 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. 121120 12:48:48 [Note] Plugin 'FEDERATED' is disabled. 121120 12:48:48 InnoDB: The InnoDB memory heap is disabled 121120 12:48:48 InnoDB: Mutexes and rw_locks use GCC atomic builtins 121120 12:48:48 InnoDB: Compressed tables use zlib 1.2.3.4 121120 12:48:48 InnoDB: Initializing buffer pool, size = 96.0G 121120 12:48:56 InnoDB: Completed initialization of buffer pool 121120 12:48:57 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 1341738337497 121120 12:48:58 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
Re: Innodb, MySQL 5.5.28 - Would an incorrect setting in my.cnf cause mysqld to randomly crash on high load?
Hi Tom, I am assuming nothing relevant shows up in dmesg, right? I have experienced random crashes like that and most of them turned to be HW issues - hard disk and memory banks related. Is it a HW RAID? Have you tried looking at the controller logs? (Megacli). And yes, corrupted tables would fail when restoring them (or even when backuping them). Good luck! Manuel 2012/11/26, Rick James rja...@yahoo-inc.com: Nothing looks bad. 96G for the buffer_pool is bigger than I have experienced, but I know of no reason for it to fail (given that you have 128GB of RAM). -Original Message- From: Tom [mailto:livefortheda...@gmail.com] Sent: Wednesday, November 21, 2012 5:17 PM To: mysql@lists.mysql.com Subject: Innodb, MySQL 5.5.28 - Would an incorrect setting in my.cnf cause mysqld to randomly crash on high load? We have a high-end server, 128GB RAM, 32 Core , Xeon, SSD RAID 10 - running Ubuntu 12.04 with MySQL 5.5.28 . Doing random imports to large InnoDB tables, over 50+ gigs, randomly after a few hours of heavy load, mysql does a Signal 11 and crashes. We have tried to move hardware. Doing a full dump (but not a restore yet) gives no issues. Usually on corrupted tables, a dump would fail no? Below is the crash log and my.cnf . --- 12:45:4 UTC - 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=536870912 read_buffer_size=131072 max_used_connections=324 max_threads=200 thread_count=308 connection_count=308 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 965187 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x7fc7eb1b5040 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 = 7fadf6abfe60 thread_stack 0x3 /usr/sbin/mysqld(my_print_stacktrace+0x29)[0x7fc758522759] /usr/sbin/mysqld(handle_fatal_signal+0x483)[0x7fc7583e9ae3] /lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7fc75713bcb0] /usr/sbin/mysqld(+0x6671b0)[0x7fc75863a1b0] /usr/sbin/mysqld(+0x61d6b9)[0x7fc7585f06b9] /usr/sbin/mysqld(+0x630d12)[0x7fc758603d12] /usr/sbin/mysqld(+0x6319c2)[0x7fc7586049c2] /usr/sbin/mysqld(+0x631d85)[0x7fc758604d85] /usr/sbin/mysqld(+0x626e7d)[0x7fc7585f9e7d] /usr/sbin/mysqld(+0x633cea)[0x7fc758606cea] /usr/sbin/mysqld(+0x6347e2)[0x7fc7586077e2] /usr/sbin/mysqld(+0x624426)[0x7fc7585f7426] /usr/sbin/mysqld(+0x610871)[0x7fc7585e3871] /usr/sbin/mysqld(+0x5d4cb0)[0x7fc7585a7cb0] /usr/sbin/mysqld(+0x5b7c9c)[0x7fc75858ac9c] /usr/sbin/mysqld(_ZN7handler21read_multi_range_nextEPP18st_key_multi_ra nge+0x24)[0x7fc7583e9fe4] /usr/sbin/mysqld(_ZN18QUICK_RANGE_SELECT8get_nextEv+0x3c)[0x7fc7584a3c8 c] /usr/sbin/mysqld(+0x4e9195)[0x7fc7584bc195] /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x71)[0x7fc7582f 1741] /usr/sbin/mysqld(+0x32f025)[0x7fc758302025] /usr/sbin/mysqld(_ZN4JOIN4execEv+0x4a5)[0x7fc758311155] /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_E S2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_sele ct_lex+0x130)[0x7fc75830d000] /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x17c)[0x 7fc758312f5c] /usr/sbin/mysqld(+0x2f66b4)[0x7fc7582c96b4] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x16d8)[0x7fc7582d1118] /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x10f)[0x7fc758 2d5daf] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x13 80)[0x7fc7582d7200] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1bd)[0x7fc75837b7a d] /usr/sbin/mysqld(handle_one_connection+0x50)[0x7fc75837b810] /lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a)[0x7fc757133e9a] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fc756864cbd] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7faa4c18e440): is an invalid pointer Connection ID (thread ID): 2286 Status: NOT_KILLED 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. 121120 12:48:48 [Note] Plugin 'FEDERATED' is disabled. 121120 12:48:48 InnoDB: The InnoDB memory heap is disabled 121120 12:48:48 InnoDB: Mutexes and rw_locks use GCC atomic builtins 121120 12:48:48 InnoDB: Compressed tables use zlib 1.2.3.4 121120 12:48:48 InnoDB: Initializing buffer pool, size = 96.0G 121120 12:48:56 InnoDB: Completed initialization of buffer pool 121120 12:48:57
Re: InnoDB corrupt after power failure
2012/10/4 Andrew Miklas and...@pagerduty.com Hi guys, I recently had a data corruption issue with InnoDB. MySQL was shut down improperly (power failure), and when the system came back up, MySQL refused to start. On inspection of the logs (see below), it looks like the tablespace became seriously corrupted. In the end, I had to rebuild the slave using mysqldump. I'm curious what happened here, since I thought InnoDB wasn't supposed to become corrupted on an improper shutdown. One possibility that we were exploring was that the filesystem journal setting was incorrect. We were using ext3 with the journal set to writeback mode. Is this a known bad config with InnoDB? Hey Andrew, it shouldn't be a biggie if you have a BBU. Do you guys use HW RAID + BBU? What's your innodb_flush_log_at_trx_commit value? Have you tried playing with innodb_force_recovery option to try to get the server started at least? That way you might be able to identify which table(s) is/are the corrupted one and the one(s) preventing the whole server from booting up. Manuel
Re: InnoDB corrupt after power failure
Hi Manuel, Thanks for the fast reply. On Oct 4, 2012, at 12:05 AM, Manuel Arostegui wrote: snip it shouldn't be a biggie if you have a BBU. Do you guys use HW RAID + BBU? We've checked with our hosting provider, and the database was indeed stored on a BBU RAID. What's your innodb_flush_log_at_trx_commit value? mysql show variables like 'innodb_flush_log_at_trx_commit'\G *** 1. row *** Variable_name: innodb_flush_log_at_trx_commit Value: 1 1 row in set (0.00 sec) Have you tried playing with innodb_force_recovery option to try to get the server started at least? That way you might be able to identify which table(s) is/are the corrupted one and the one(s) preventing the whole server from booting up. As the affected machine was just a read only slave, it was easier for me to get things back into service by just reloading off the master. Unfortunately, I didn't think to keep the corrupted ibd files for later debugging. At this point, I'm more trying to figure out if there's something wrong with the DB or host config. There was effectively no data loss, but I'm worried we might have data loss or availability issues if this error crops up on our master server. Thanks, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: InnoDB corrupt after power failure
I hope you turned OFF caching on the drives, themselves. The BBU should be the single place that caches and is trusted to survive a power outage. -Original Message- From: Andrew Miklas [mailto:and...@pagerduty.com] Sent: Thursday, October 04, 2012 2:16 PM To: Manuel Arostegui Cc: mysql@lists.mysql.com Subject: Re: InnoDB corrupt after power failure Hi Manuel, Thanks for the fast reply. On Oct 4, 2012, at 12:05 AM, Manuel Arostegui wrote: snip it shouldn't be a biggie if you have a BBU. Do you guys use HW RAID + BBU? We've checked with our hosting provider, and the database was indeed stored on a BBU RAID. What's your innodb_flush_log_at_trx_commit value? mysql show variables like 'innodb_flush_log_at_trx_commit'\G *** 1. row *** Variable_name: innodb_flush_log_at_trx_commit Value: 1 1 row in set (0.00 sec) Have you tried playing with innodb_force_recovery option to try to get the server started at least? That way you might be able to identify which table(s) is/are the corrupted one and the one(s) preventing the whole server from booting up. As the affected machine was just a read only slave, it was easier for me to get things back into service by just reloading off the master. Unfortunately, I didn't think to keep the corrupted ibd files for later debugging. At this point, I'm more trying to figure out if there's something wrong with the DB or host config. There was effectively no data loss, but I'm worried we might have data loss or availability issues if this error crops up on our master server. Thanks, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: InnoDB corrupt after power failure
Hi Rick, On Oct 4, 2012, at 2:40 PM, Rick James wrote: I hope you turned OFF caching on the drives, themselves. The BBU should be the single place that caches and is trusted to survive a power outage. The DB server in question is running in a virtualized environment, so the array shows up as a SCSI device inside our VM. I can't use hdparm to directly check whether the disks are doing write caching, but our hosting provider assures us that once data is sent to the virtual SCSI device from inside the VM, it will be persisted to disk even if there's a power failure. I'm a bit suspicious of a recent change we did to switch our ext3 journals from data=ordered to data=writeback. The ext3 docs say a crash+recovery can cause incorrect data to appear in files which were written shortly before the crash [1]. As a result, if a tablespace were extended just before the power failure, it might be possible that when MySQL restarts, it will see random data at the end of the tablespace. It seems like this could happen even if the disks are BBU / not write caching, because the increase of the ibd's file size in the inode and the zeroing out of the new blocks assigned to the file are not atomic with respect to one another. Is the InnoDB recovery process OK with this scenario? Has anyone else seen corruption problems with data=writeback? -- Andrew [1] http://lxr.linux.no/linux+v3.5.2/Documentation/filesystems/ext3.txt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: InnoDB corrupt after power failure
On Linux, XFS is preferred. Noop or Deadline, not CFQ is preferred. I don't know if any of this has any impact on the crash you describe. I am quite suspicious of VMs. -Original Message- From: Andrew Miklas [mailto:and...@pagerduty.com] Sent: Thursday, October 04, 2012 3:21 PM To: Rick James Cc: Manuel Arostegui; mysql@lists.mysql.com Subject: Re: InnoDB corrupt after power failure Hi Rick, On Oct 4, 2012, at 2:40 PM, Rick James wrote: I hope you turned OFF caching on the drives, themselves. The BBU should be the single place that caches and is trusted to survive a power outage. The DB server in question is running in a virtualized environment, so the array shows up as a SCSI device inside our VM. I can't use hdparm to directly check whether the disks are doing write caching, but our hosting provider assures us that once data is sent to the virtual SCSI device from inside the VM, it will be persisted to disk even if there's a power failure. I'm a bit suspicious of a recent change we did to switch our ext3 journals from data=ordered to data=writeback. The ext3 docs say a crash+recovery can cause incorrect data to appear in files which were written shortly before the crash [1]. As a result, if a tablespace were extended just before the power failure, it might be possible that when MySQL restarts, it will see random data at the end of the tablespace. It seems like this could happen even if the disks are BBU / not write caching, because the increase of the ibd's file size in the inode and the zeroing out of the new blocks assigned to the file are not atomic with respect to one another. Is the InnoDB recovery process OK with this scenario? Has anyone else seen corruption problems with data=writeback? -- Andrew [1] http://lxr.linux.no/linux+v3.5.2/Documentation/filesystems/ext3.txt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: InnoDB vs. other storage engines
Hi! Manuel == Manuel Arostegui man...@tuenti.com writes: Manuel 2012/9/19 Mark Haney ma...@abemblem.com I hope this doesn't end in some kind of flame war. I'm looking to optimize my tables (and performance in general) of the DB my web app is using. I'm tweaking things a little at a time, but I'm curious as to what the rest of the MySQL list thinks about changing my storage engine from InnoDB to something else so I can optimize the tables on a regular basis. Is it worth the effort? Any caveats? Manuel Hi Mark, Manuel I would depend on what your workload would be. Mostly writes, mostly reads, Manuel how many writes/reads do you expect etc. Manuel The best approach, from my point of view, would be, firstly, tune your Manuel MySQL server (if you've not done it yet) before getting into engine/tables Manuel optimizations which can be more complicated. InnoDB is a great engine, but not suitable for everything. Depending on your usage, moving some tables to another engine may help. Here is some suggestions (in no particular order): - If you want to have small footprint but don't need commit, foreign keys or explicite rollback then ARIA is an option. http://kb.askmonty.org/en/aria-formerly-known-as-maria/ - Duplicating some data in the MEMORY engine may also be beneficially. - If your problem is a lot of write, then you should take a look at Tokutek. It's an engine that is optimized for a lot of inserts. http://www.tokutek.com/products/tokudb-for-mysql/ - If you want to utilize a lot of computers to analyze BIG data then ScaleDB (http://www.scaledb.com) or InfiniDB (http://infinidb.org/) may be an option. Good luck and please post/blog about your experiences! Regards, Monty Creator of MySQL and MariaDB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: InnoDB vs. other storage engines
2012/09/19 13:44 -0700, Rick James http://mysql.rjweb.org/doc.php/myisam2innodb Also, InnoDB enforces foreign-key constraints, MyISAM not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: InnoDB vs. other storage engines
2012/9/19 Mark Haney ma...@abemblem.com I hope this doesn't end in some kind of flame war. I'm looking to optimize my tables (and performance in general) of the DB my web app is using. I'm tweaking things a little at a time, but I'm curious as to what the rest of the MySQL list thinks about changing my storage engine from InnoDB to something else so I can optimize the tables on a regular basis. Is it worth the effort? Any caveats? Hi Mark, I would depend on what your workload would be. Mostly writes, mostly reads, how many writes/reads do you expect etc. The best approach, from my point of view, would be, firstly, tune your MySQL server (if you've not done it yet) before getting into engine/tables optimizations which can be more complicated. Manuel.
RE: InnoDB vs. other storage engines
No flames from me; I stay out of that religious war. However, the general consensus is to move to InnoDB. So, here are the gotchas. Most are non-issues; a few might bite you, but can probably be dealt with: http://mysql.rjweb.org/doc.php/myisam2innodb -Original Message- From: Manuel Arostegui [mailto:man...@tuenti.com] Sent: Wednesday, September 19, 2012 12:51 PM To: Mark Haney Cc: mysql mailing list Subject: Re: InnoDB vs. other storage engines 2012/9/19 Mark Haney ma...@abemblem.com I hope this doesn't end in some kind of flame war. I'm looking to optimize my tables (and performance in general) of the DB my web app is using. I'm tweaking things a little at a time, but I'm curious as to what the rest of the MySQL list thinks about changing my storage engine from InnoDB to something else so I can optimize the tables on a regular basis. Is it worth the effort? Any caveats? Hi Mark, I would depend on what your workload would be. Mostly writes, mostly reads, how many writes/reads do you expect etc. The best approach, from my point of view, would be, firstly, tune your MySQL server (if you've not done it yet) before getting into engine/tables optimizations which can be more complicated. Manuel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: InnoDB and Memory Allocation
Johnny Withers wrote: I hope someone can help me out here. I'm having trouble with some new servers and memory allocation. Some basic specs on the servers: 32GB total mem 2GB swap 64-bit RHEL 64-bit mysqld overcommit_memory=2 mysql fails to start with 14GB innodb_buffer_pool_size mysql will start with 12GB buffer pool setting When overcommit_memory is set to 0, mysql starts with 26GB buffer pool. This is due to a system-wide limitation on the amount of memory that can be allocated by processes that the Linux kernel imposes when overcommit is disabled. The limit is (amount of swap) + (percentage of physical memory), and the default percentage is 50. So the limit in your case is 2G + 16G == 18G for all processes together; 14G for innodb_buffer_pool_size is too much. You can increase the amount of swap, or increase the percentage (via /proc/sys/vm/overcommit_ratio, which isn't as well-known, though it is in the kernel documentation). Hope that helps. Charles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: InnoDB and Memory Allocation
Wow, I hope this hasn't been hanging out in the mysql list server for a year. I completely forgot about this problem. Thanks for the info though. I've been running this server with overcommit_memory=0 and a 42GB buffer pool for a while now. Thanks again! On Fri, May 4, 2012 at 9:38 AM, Charles Cazabon charlesc-mysql@pyropus.ca wrote: Johnny Withers wrote: I hope someone can help me out here. I'm having trouble with some new servers and memory allocation. Some basic specs on the servers: 32GB total mem 2GB swap 64-bit RHEL 64-bit mysqld overcommit_memory=2 mysql fails to start with 14GB innodb_buffer_pool_size mysql will start with 12GB buffer pool setting When overcommit_memory is set to 0, mysql starts with 26GB buffer pool. This is due to a system-wide limitation on the amount of memory that can be allocated by processes that the Linux kernel imposes when overcommit is disabled. The limit is (amount of swap) + (percentage of physical memory), and the default percentage is 50. So the limit in your case is 2G + 16G == 18G for all processes together; 14G for innodb_buffer_pool_size is too much. You can increase the amount of swap, or increase the percentage (via /proc/sys/vm/overcommit_ratio, which isn't as well-known, though it is in the kernel documentation). Hope that helps. Charles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: Innodb Table Gets Locked while Drop
There is almost no reason to use LOCK TABLES on InnoDB tables. I prefer to have auto_commit=1, then use explicit BEGIN and COMMIT for clumps of statements that need ACID. I never use auto_commit=0. Following those two rules, your issue with innodb_table_locks being on goes away. On 4/5/12 11:58 PM, Adarsh Sharma wrote: Dear All, Today i faced an issue while issuing commands on my innodb tables. When I issued any drop table or alter table command my query gets into waiting state for more than 15-20 minutes. Mysql Version :-5.5.4 community I researched on the issue and find that every command try to get lock on the table before executing query on it. After some R n D , i find and innodb_table_lock parameter in my.cnf. After turning it off, my commands are executed with no issue. What are the pros cons of this modification why it tries to lock tables, can anyons have some idea about the issue. Thanks -- Rick James - MySQL Geek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: innodb space free decreasing by more then the amount of data we're adding
; 2011/11/22 12:44 -0800, Sean Sidelko We just moved a large amount of data off one of our db servers to another one (75gb of data). One thing I've noticed is that over the last couple days the free innodb space has been decreasing by over 2.5 gb a day while we've only been adding 400 mb of data a day to the db server. I'm at a loss as to why the innodb free space would be decreasing like it has been. This is on a MySql 5.0 install. Index? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: InnoDB free - What does it really mean?
Hi, The comment is just telling you how much free space is in your InnoDB datafile(s). When that approaches 0, InnoDB will add the data file. Image that there's a box, say it Innodb tablespace, this box is consist of your data, and innodb free is the same as the (capacity of your box - usage) To increase the size simply add innodb data file. MySQL documentation: The data files (tables) that you define in an InnoDB form the tablespace of InnoDB. The tablespace consists of database pages with a default size of 16KB. The pages are grouped into extents of 64 consecutive pages (i.e. 1024Kb). InnoDB allocates space starting from the first data file (table). InnoDB can add a large segment up to 4 extents at a time to ensure good sequentiality of data (4096Kb). Thanks, On Thu, Nov 10, 2011 at 10:03 AM, Rozeboom, Kay [DAS] kay.rozeb...@iowa.gov wrote: In the show table status output, there is comment field labeled InnoDB free. Can someone explain what kind of free space is counted in this figure? Is it space that is not currently part of any segment? Does it include empty pages within segments? Does it include unused space within pages? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: InnoDB free - What does it really mean?
it refers to free innodb tablespace. From: Rozeboom, Kay [DAS] kay.rozeb...@iowa.gov To: mysql@lists.mysql.com mysql@lists.mysql.com Sent: Thursday, November 10, 2011 10:03 AM Subject: InnoDB free - What does it really mean? In the show table status output, there is comment field labeled InnoDB free. Can someone explain what kind of free space is counted in this figure? Is it space that is not currently part of any segment? Does it include empty pages within segments? Does it include unused space within pages? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov
Re: InnoDB “log sequence in the future!” crashing, won't start
Log sequence in the future means that, for whatever reason, the update in the data pages happened but update in the Innodb's log didn't.The InnoDB by itself, without backups, is not protected against media failures, and this happens to be just that. Innodb_force_recovery is not really a regular recovery method, it is more like a last resort attempt to salvage the database and its ability to save the data is more or less luck. On Nov 6, 2011, at 2:35 AM, Reindl Harald wrote: Am 06.11.2011 06:05, schrieb Kevin Wang: I stopped mysql only to find that it wouldn't come back up, /etc/init.d/mysql start only outputs . . . . . . failed. I've narrowed it down to an issue with InnoDB. The database starts when innodb_force_recovery = 5 and nothing lower. When I check table for my MyISAM tables, they check fine, but the connection is dropped when I attempt to do so for my InnoDB tables with force_recovery set. mysql.err and mysql.log are empty. Here are the relevant logs from syslog when I attempt to start with innodb_force_recovery set to zero: http://pastebin.com/jzhEuWFu and here's my my.cnf file: http://pastebin.com/qn6huZ09 why do you care about starting mysql as long the systme is whining the whole time over a dying harddisk? What did you think Unrecovered read error and Medium error try to tell you? this are kernel messages saying: hopefully you have a backup and if not now is learning lesson for the future -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB “log sequence in the future!” crashing, won't start
Am 06.11.2011 06:05, schrieb Kevin Wang: I stopped mysql only to find that it wouldn't come back up, /etc/init.d/mysql start only outputs . . . . . . failed. I've narrowed it down to an issue with InnoDB. The database starts when innodb_force_recovery = 5 and nothing lower. When I check table for my MyISAM tables, they check fine, but the connection is dropped when I attempt to do so for my InnoDB tables with force_recovery set. mysql.err and mysql.log are empty. Here are the relevant logs from syslog when I attempt to start with innodb_force_recovery set to zero: http://pastebin.com/jzhEuWFu and here's my my.cnf file: http://pastebin.com/qn6huZ09 why do you care about starting mysql as long the systme is whining the whole time over a dying harddisk? What did you think Unrecovered read error and Medium error try to tell you? this are kernel messages saying: hopefully you have a backup and if not now is learning lesson for the future signature.asc Description: OpenPGP digital signature
Re: InnoDB #sql files
Am 04.11.2011 19:12, schrieb Ian Rubado: Hi there, I had the same issue as you posted about at the bottom of: http://bugs.mysql.com/bug.php?id=20867 I was curious if you ever found a solution. I ended up converting tables to MyIsam and flushing my innodb files to resolve. no, this bullshit files are staying here since summer 2009 and converting a production dbmail-database to myisam would be the same as destroy it signature.asc Description: OpenPGP digital signature
Re: Innodb as its default storage engine for Mysql 5.0 / 5.1
Yes, InnoDB is the default storage engine for MySQL as of MySQL 5.5.MyISAM and InnoDB has its own features. InnoDB probably the best RDBMS out there. InnoDB is default engine might be due nowadays most of the application required fully ACID-compliant modes, self recovery from a crash, and many more features... 2011/5/25 Halász Sándor h...@tbbs.net 2011/05/25 10:53 +0200, Reindl Harald if there is no good reason i will never enable innodb because MyISAM is enough for most web-apps And also MyISAM supports auto-increment in a lesser part of a primary key and InnoDB not--but although it is of interest, I have not tryed it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Innodb as its default storage engine for Mysql 5.0 / 5.1
On 2011-05-25 10:35:45 Brent Clark wrote: I think I read somewhere that Mysql 5.5 is defaulting on Innodb as its default storage engine. The question I would like to ask is. For those still running Mysql 5.0 / 5.1. Have any of you set the mysql default variable to be / use Innodb? Yes, I have, it's one of the first things I usually do/did in a new 5.0/1 setup, and it works out OK, allthough there are major improvements in 5.5 for InnoDB, so an upgrade from 5.0/1 to 5.5 may result in a lot less resource usage. It's just a setting, can be set globally or in a session: http://dev.mysql.com/doc/refman/5.1/en/server- options.html#option_mysqld_default-storage-engine -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Innodb as its default storage engine for Mysql 5.0 / 5.1
Am 25.05.2011 10:35, schrieb Brent Clark: Hiya I think I read somewhere that Mysql 5.5 is defaulting on Innodb as its default storage engine. The question I would like to ask is. For those still running Mysql 5.0 / 5.1. Have any of you set the mysql default variable to be / use Innodb? depends on the use for dedicated dbmail-instaces, yes because there is only InnoDB/UTF8 used on all other machines independent of the mysql-version like below and i am happy that i never use default-configs because the change in 5.5 would kill the mysqld if you disabeld innodb-storage-engine :-) character-set-server = latin1 collation-server = latin1_german1_ci default-storage-engine = myisam __ summary: if there is no good reason i will never enable innodb because MyISAM is enough for most web-apps and dirty things like stop the server and restore a database from a backup with rsync the folder can not be done with InnoDB signature.asc Description: OpenPGP digital signature
Re: Innodb as its default storage engine for Mysql 5.0 / 5.1
2011/05/25 10:53 +0200, Reindl Harald if there is no good reason i will never enable innodb because MyISAM is enough for most web-apps And also MyISAM supports auto-increment in a lesser part of a primary key and InnoDB not--but although it is of interest, I have not tryed it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: innodb system variable
Hi All innodb_rollback_on_timeout=1 Specifies when there is transaction open by session and not committed, If such session is inactive for long time, MySQL by default kicks out such session and transaction perform by session would be rollback innodb_lock_wait_timeout=600 Specify wait for lock on row up to provided seconds, if it is time out, it not try to process transaction further, but roll back will not happen, You need to start you transaction from point it was timeout, else need to be rollback explicitly and re run transaction or wait till session timeout and re run transaction --Anupam K From: Mohan L l.mohan...@gmail.com To: mysql@lists.mysql.com Sent: Tue, 29 March, 2011 12:54:12 PM Subject: innodb system variable Dear All, I have the following two system variable set in my MySQL configuration file under mysqld section. But I am not fully understand what the two variable internally does. innodb_rollback_on_timeout=1 innodb_lock_wait_timeout=600 Any help will be appreciated. Thanks for Your Time Mohan L
Re: innodb buffer pool allocation question
Hi, This is far more complicated than that. The buffer pool caches innodb pages. Not only data and indexes are stored on innodb pages. For example the undo log or the insert buffer are stored in innodb pages, therefore they are cached by the buffer pool. The simple answer is: in the buffer pool, the data, the indexes, and some other stuff are cached:). Since it caches innodb pages, it can cache subsets, it uses LRU toremove certain pages from the cache. Peter Boros On 02/23/2011 05:49 AM, Kyong Kim wrote: Does innodb buffer pool cache indexes and data in sub sets or in entirety? I've heard people mention the buffer pool allocation is dependent on the size of your tables and indexes. Kyong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
I skimmed over this thread and I think I can help clarify the innodb, rsync, and lvm situation. The basic issue with just running rsync on the files under a running mysqld is that the rsync will copy different parts of files at different points in time. This means that it could sync things to disk before and after rsync has read that part of a file which, when restored, will be corrupted. This is the worst case scenario. The next best thing is to run a FLUSH TABLES WITH READ LOCK (global read lock) and maintain the lock while the rsync is running. This works fine for myisam tables because all file activity to myisam tables is stopped while a global read lock is held. This isn't guaranteed to work with innodb. Internally to mysql flush tables with read lock only stops queries from acquiring write locks which let them modify tables. This won't make innodb hold still for a few different reasons. First a select query in innodb can actually modify data files. A select on a page with unmerged records from the insert buffer will cause innodb to merge those records before making the page available for selects. This causes some disk i/o. If this happens while rsync is running the resulting backup can be corrupted. So even while holding a global read lock and only running selects innodb can write data. The master thread(s) perform background tasks such as flushing dirty pages, merging the insert buffer and purging old records whenever innodb feels like there is spare i/o capacity. These threads don't know how to hold still during a global read lock and can corrupt a backup if it were taken with rsync. The safest way to create a backup without using something like XtraBackup is to get a snapshot at the filesystem level or below that at the block device level. This is effectively what LVM does. When you create a LVM snapshot it freezes that device at a single point in time. When you restore the backup innodb goes through it's recovery procedure as if the power went out. Some possible solutions to this were discussed on the internals list a few years ago. I'm not sure what has been implemented since then. The list thread is here: http://lists.mysql.com/internals/35527 -Eric On Fri, Jan 28, 2011 at 1:59 PM, Robinson, Eric eric.robin...@psmnv.com wrote: * flush atbles * rsync while mysqld is running * stop mysqld * second rsync Unless we can verify 100% that there is a safe way to do it without shutting down MySQL, then I'm sure the approach you described above is the one we will end up with. Thanks for your input. -- Eric Robinson Disclaimer - January 28, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: InnoDB and rsync
You need to quiesce the InnoDb background threads. One technique is mentioned here: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp aces.html Just refreshing this topic a bit. Can anyone confirm that FLUSH TABLES WITH READ LOCK is sufficient to quiesce the InnoBD background threads per Shawn's message above? -- Eric Robinson Disclaimer - January 28, 2011 This email and any files transmitted with it are confidential and intended solely for mysql@lists.mysql.com,Shawn Green (MySQL). If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
FLUSH TABLES WITH READ LOCK does work consistently on MyISAM and my experience confirms this. I do remember reading something on this list eons ago that asserted that it is not necessarily effective on InnoDB due to it's multi-versioning.. uncommited transactions might be caught in an inconsistent state. In one extreme instance, having a few terabytes of data across several instances (on distinct hosts), I was required to do a full-refactoring data migration with an absolute limitation on allowable downtime. Among the technique which I used (and I can't take credit for this one) was to use rsync on the live server for innodb files (this phase took a very long time, but did not interfere with operations). The result of this phase was, as you would expect, a set a seriously broken files which were notheless very similar to the correct files. When that phase was complete, I shut the server down and did another rsync. It required perhaps a minute or 2, but the result was 100% clean innodb data files which satisfied my downtime limitations. FLUSH TABLES WITH READ LOCK might suffice if all transactions are completed/rolled-back but I would stil advise that you scan SHOW ENGINE INNODB STATUS but I would carefully experiment with that. As for maat-kit, don't let the disclaimers discourage you. If you read the disclaimers carefully on any product (at least those released with the benefit(?) of legal advice), you would have a hard time trusting any of it with your enterprise. The maat-kit team (and Baron Schwartz in particular) and quite simply the *best* MySQL engineering team out there, with the possible exception of the vendor. I would not hesitate to trust them with my data. - michael dykman On Fri, Jan 28, 2011 at 11:04 AM, Robinson, Eric eric.robin...@psmnv.com wrote: You need to quiesce the InnoDb background threads. One technique is mentioned here: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp aces.html Just refreshing this topic a bit. Can anyone confirm that FLUSH TABLES WITH READ LOCK is sufficient to quiesce the InnoBD background threads per Shawn's message above? -- Eric Robinson Disclaimer - January 28, 2011 This email and any files transmitted with it are confidential and intended solely for mysql@lists.mysql.com,Shawn Green (MySQL). If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: InnoDB and rsync
In one extreme instance, having a few terabytes of data across several instances (on distinct hosts), I was required to do a full-refactoring data migration with an absolute limitation on allowable downtime. Among the technique which I used (and I can't take credit for this one) was to use rsync on the live server for innodb files (this phase took a very long time, but did not interfere with operations). The result of this phase was, as you would expect, a set a seriously broken files which were notheless very similar to the correct files. When that phase was complete, I shut the server down and did another rsync. It required perhaps a minute or 2, but the result was 100% clean innodb data files which satisfied my downtime limitations. FLUSH TABLES WITH READ LOCK might suffice if all transactions are completed/rolled-back but I would stil advise that you scan SHOW ENGINE INNODB STATUS but I would carefully experiment with that. You just described almost the exact procedure that I described at the beginning of this thread, except I use MyISAM so my question was whether the same technique could work with InnoDB. It sounds like it very well could if combined with SHOW ENGINE INNODB STATUS. I will definitely test it to be sure. As for maat-kit, don't let the disclaimers discourage you. If you read the disclaimers carefully on any product (at least those released with the benefit(?) of legal advice), you would have a hard time trusting any of it with your enterprise. The maat-kit team (and Baron Schwartz in particular) and quite simply the *best* MySQL engineering team out there, with the possible exception of the vendor. I would not hesitate to trust them with my data. I will definitely look at it again. Thanks. --Eric Disclaimer - January 28, 2011 This email and any files transmitted with it are confidential and intended solely for Michael Dykman,mysql@lists.mysql.com,Shawn Green (MySQL). If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
Am 28.01.2011 17:04, schrieb Robinson, Eric: Just refreshing this topic a bit. Can anyone confirm that FLUSH TABLES WITH READ LOCK is sufficient to quiesce the InnoBD background threads per Shawn's message above? Damned start your brain, read documentations and hear what peopole say http://dev.mysql.com/doc/refman/5.1/en/alter-table-problems.html If you use ALTER TABLE on a transactional table or if you are using Windows or OS/2, ALTER TABLE unlocks the table if you had done a LOCK TABLE on it. This is done because InnoDB and these operating systems cannot drop a table that is in use. And you will build your business on a hotcopy with external tools beause you do not trust replication? laughable! Do what you want, but dont come back and cry if all goes down You were told in which way you can use rsync with minimum downtime or that replication can be used to stop only salves for a short time and you are acting like a child mama i will do what i said the whole time signature.asc Description: OpenPGP digital signature
RE: InnoDB and rsync
And you will build your business on a hotcopy with external tools beause you do not trust replication? laughable! Do what you want, but dont come back and cry if all goes down You were told in which way you can use rsync with minimum downtime or that replication can be used to stop only salves for a short time and you are acting like a child mama i will do what i said the whole time Our current model has been working well since 2006. We will be careful to verify the reliability of any proposed changes. Have a great day! -- Eric Robinson Disclaimer - January 28, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
Am 28.01.2011 22:30, schrieb Robinson, Eric: Our current model has been working well since 2006. We will be careful to verify the reliability of any proposed changes. Have a great day! this is ok because MyISAM is so simple that you can even without any flushes make a copy while the server is running and after a repair table on the destination machine all tables are useable With InnoDB it is much difficult because table spaces Eeven with innodb_file_per_table there are dependencies of the table-files and ibdata1 in the main datadir If there is only a minimal problem it is possible that mysqld will not start and if this is a time where you need your backup really you would like to die :-) Even if there would be documentaed ways to rsync while the server is running i would never ever do that because it is so hot that a simple mysql-bug in a later release could break it temporarliy and you would not notice this, so you sgould simply go a safe way * flush atbles * rsync while mysqld is running * stop mysqld * second rsync With this steps you can copy real big databases with a minimum downtime, to reduce this again select a local folder as target if your disks are fast and after mysqld is runnign again you can sync this copy to another machine without stress signature.asc Description: OpenPGP digital signature
RE: InnoDB and rsync
* flush atbles * rsync while mysqld is running * stop mysqld * second rsync Unless we can verify 100% that there is a safe way to do it without shutting down MySQL, then I'm sure the approach you described above is the one we will end up with. Thanks for your input. -- Eric Robinson Disclaimer - January 28, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
On Wed, Jan 26, 2011 at 6:58 AM, Robinson, Eric eric.robin...@psmnv.comwrote: You need to quiesce the InnoDb background threads. One technique is mentioned here: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp aces.html Look for the section talking about clean backups. Now we're talkin. I'll check it out. I read that section but it is not at all clear (1) how one quiesces the InnoDB background threads, or (2) if there is a way to keep them quiesced while the backup is in progress. From what I see there: You can make a clean backup .ibd file using the following method: 1. Stop all activity from the *mysqld*http://dev.mysql.com/doc/refman/5.5/en/mysqld.htmlserver and commit all transactions. 2. Wait until SHOW ENGINE INNODB STATUShttp://dev.mysql.com/doc/refman/5.5/en/show-engine.htmlshows that there are no active transactions in the database, and the main thread status of InnoDB is Waiting for server activity. Then you can make a copy of the .ibd file. I would assume that flush tables with read lock would work for 1. and then you wait for 2. to happen. Probably shouldn't take very long, especially in the quiet moments. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
RE: InnoDB and rsync
You need to quiesce the InnoDb background threads. One technique is mentioned here: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp aces.html Look for the section talking about clean backups. Now we're talkin. I'll check it out. I read that section but it is not at all clear (1) how one quiesces the InnoDB background threads, or (2) if there is a way to keep them quiesced while the backup is in progress. From what I see there: You can make a clean backup .ibd file using the following method: 1. Stop all activity from the mysqld http://dev.mysql.com/doc/refman/5.5/en/mysqld.html server and commit all transactions. 2. Wait until SHOW ENGINE INNODB STATUS http://dev.mysql.com/doc/refman/5.5/en/show-engine.html shows that there are no active transactions in the database, and the main thread status of InnoDB is Waiting for server activity. Then you can make a copy of the .ibd file. I would assume that flush tables with read lock would work for 1. and then you wait for 2. to happen. Probably shouldn't take very long, especially in the quiet moments. Can anyone confirm that FLUSH TABLES WITH READ LOCK is sufficient to quiesce the InnoDB background threads? When Googling this, I'm seeing posts saying that even after a flush lock, InnoDB keeps making changes to certain files (metadata?) and that's why it is never really safe (according to them) to copy the files from a running instance of MySQL. Call me persistent (or just annoyingly thick-headed) but I am not fully satisfied their explanations. --Eric Disclaimer - January 26, 2011 This email and any files transmitted with it are confidential and intended solely for Johan De Meersman,Shawn Green (MySQL),Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
Re: InnoDB and rsync
Am 25.01.2011 05:37, schrieb Robinson, Eric: Is there a way to safely backup an InnoDB database using rsync? Not without stop mysqld Foregt it, do not try it and stop searching if you do not waste time If you understand how innodb works you will see that this is not possible by design your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? you can get a fresgh backup of the master with the following method but nobody do that daily - this is only for init/reinit a salve * rsync on master in another local folder while running * stop master * remove bin-logs * second rsync to get last changes * start master * rsync to final destination When I'm all done, I have 240+ slave servers in perfect sync with their masters, each having a 100% identical binary copy of its master's database. Since these copies are truly identical, they can be used for a second layer of backup to other media. why in the world do you not leave the slaves in peace and backup them to another media because you can stop them as long you want for a consistent backup and after starting the last changes from the master are applied Right now we have a very efficient and reliable way to backup 240+ separate instances of MySQL with MyISAM tables. The databases range in size from .5GB to 16GB. During this time, users can still access the system, so our customers can work 24x7. In the process, we also refresh 240+ slave instances with a perfect byte-for-byte replica of the master databases. The whole thing takes about 30 minutes. Here's how we do it. Late at night when the number of users on the system is low, we do the following for each of the 240+ instances of MySQL... 1. Shut down the slave and remove all replication-related log files. 2. Perform an rsync of the master's data directory to the slave. Users may be making changes to tables during this rsync. 3. Issue a FLUSH TABLES WITH READ LOCK on the master followed by a RESET MASTER. 4. Perform a second rsync of the data directory from the master to the slave to copy any user changes that happened during step 2. This usually completes in a few seconds, often less than 1. If any users were trying to insert records at this exact moment, their application may appear to pause very briefly. 5. Start the slave. When I'm all done, I have 240+ slave servers in perfect sync with their masters, each having a 100% identical binary copy of its master's database. Since these copies are truly identical, they can be used for a second layer of backup to other media. Like I said, the whole process takes about 30 minutes because the rsync algorithm only copies the byte-level changes. IS THERE ANY WAY TO SET UP SOMETHING THIS EASY AND EFFICIENT USING INNODB? I've been reading about InnoDB hot copy and other approaches, but none of them seem to work as well as the approach I have worked out with MyISAM. Unfortunately, my software wants to force us to switch to InnoDB, so I'm really stuck. If we have to switch to InnoDB and we cannot come up with a method for doing fast, rsync-style backups, it will probably mean huge, costly, and unnecessary changes to our infrastructure. Any help will be GREATLY appreciated. -- Eric Robinson Disclaimer - January 24, 2011 This email and any files transmitted with it are confidential and intended solely for mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- Mit besten Grüßen, 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/ signature.asc Description: OpenPGP digital signature
RE: InnoDB and rsync
your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. For this reason, most people agree that it is not safe to rely on the slave server as the source for your backups. My solution efficiently corrects row drift and makes sure the slaves are 100% binary replicas of the slaves, which can then be trusted as backup sources. The whole thing is very fast and there is no downtime for users, who can continue to work 24x7. I fail to see how this is crippled. why in the world do you not leave the slaves in peace and backup them to another media because you can stop them as long you want for a consistent backup and after starting the last changes from the master are applied See my comment above. (But also we cannot stop them as long as we want because the slaves are used for running reports. Using my approach, each slave is down for about 30 seconds. The masters are not brought down at all.) If you understand how innodb works you will see that this is not possible by design I'm starting to worry that you may be right. I know FLUSH TABLES WITH READ LOCK does not work as expected with InnoDB, but is there really no way to put InnoDB into a state where all changes have been flushed to disk and it is safe to rsync the directory? Is stopping the service really the only way? (And even if I stop the service, is rsync totally safe with InnoDB?) -- Eric Robinson Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
2011/1/25 Robinson, Eric eric.robin...@psmnv.com: your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. ... sounds interesting; have you got any document explaining this phenomenon? AFAIK, the things that (silently) break replication are: - non-deterministic functions in statement-based replication - hand-made updates on the slave db is this enough to justify a *daily* resync?! However, this could be a solution for your problem (maybe) http://www.pythian.com/news/5113/video-building-a-mysql-slave-and-keeping-it-in-sync/ if you watch the movie, at ~40 minutes, you can see a slide What causes slave to get out of sync... Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
On Tue, Jan 25, 2011 at 3:00 PM, Robinson, Eric eric.robin...@psmnv.comwrote: your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The Interesting. I never heard of that, and can't, at first glance, seem to find a lot of useful things on Google. Could you explain what you mean ? The one thing I can think of, would be the fact that your rows are not guaranteed to be in the same disk blocks, or even necessarily in the same data block of your file. This in itself doesn't really pose a problem for backups, though, afaik ? I'm starting to worry that you may be right. I know FLUSH TABLES WITH READ LOCK does not work as expected with InnoDB, but is there really no It doesn't, exactly, no; but afaik no actual data will be written. Some metadata may not be fully sync, but I do not believe a lot could happen that the recovery when you start your slave can't fix. Still, the issue is there. really the only way? (And even if I stop the service, is rsync totally safe with InnoDB?) As a stopped MySQL can't update the files or keep data in memory, that should be safe, yes. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: InnoDB and rsync
Am 25.01.2011 15:00, schrieb Robinson, Eric: your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. There is nothing drifting and nobody cares if the files on both servers are binary identical, the data must be consistent and it is binlog-format = ROW For this reason, most people agree that it is not safe to rely on the slave server as the source for your backups. sorry but these people have no plan My solution efficiently corrects row drift and makes sure the slaves are 100% binary replicas of the slaves jesus christ nobody cares if they are binary replica as long as the data is consistent and ident I fail to see how this is crippled. It is crippled because you do not understand the sense of replication if you reinit it every day See my comment above. (But also we cannot stop them as long as we want because the slaves are used for running reports. so start another slave on the machine with his own socket for backups, i have running on all dedicated backup-servers two instances - one is useable r/w and the other one without tcp is the replication-slave, every hour the salve is stopped and datadir mirrored to the r/w-instance Using my approach, each slave is down for about 30 seconds. The masters are not brought down at all. and if you running a clean solution the salves are never down but is there really no way to put InnoDB into a state where all changes have been flushed to disk and it is safe to rsync the directory? no, it is a database and not designed for access from external software as long as the database is running Is stopping the service really the only way? yes, and not only for innodb try to copy oracle, postgresql, ms-sql :-) if you do not stop the service you can be sure that the backup is not useable or missing data, even if there would exist a mode sync all to disk nobody would officially support copy datafiles while the service is running, even with myisam nobody will do that And even if I stop the service, is rsync totally safe with InnoDB? why not? the server is down and you copy the whole datadir what can be unsafe there? signature.asc Description: OpenPGP digital signature
Re: InnoDB and rsync
jesus christ nobody cares if they are binary replica as long as the data is consistent and ident Actually, I can see this being an issue if you're using LVM snapshot backups or another similar technique - if the datafiles aren't all identical you won't be able to restore to any machine from a single backup. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: InnoDB and rsync
On 1/25/2011 8:00 AM, Robinson, Eric wrote: your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. For this reason, most people agree that it is not safe to rely on the slave server as the source for your backups. My solution efficiently corrects row drift and makes sure the slaves are 100% binary replicas of the slaves, which can then be trusted as backup sources. The whole thing is very fast and there is no downtime for users, who can continue to work 24x7. I fail to see how this is crippled. Why don't you use a Maatkit solution like mk-checksum to ensure that your slaves have identical data with the master? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: InnoDB and rsync
Why don't you use a Maatkit solution like mk-checksum to ensure that your slaves have identical data with the master? I looked at Maatkit a year or so ago. It looked pretty interesting, but then I started reading the disclaimers carefully and they scared the bejeepers out of me. Warnings about data corruption and whatnot. I'll check it out again. (I was actually looking for it this morning but could not remember the name, so thanks for the reminder.) -- Eric Robinson Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for st...@internetretailconnection.com,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
Am 25.01.2011 15:56, schrieb Johan De Meersman: jesus christ nobody cares if they are binary replica as long as the data is consistent and ident Actually, I can see this being an issue if you're using LVM snapshot backups or another similar technique - if the datafiles aren't all identical you won't be able to restore to any machine from a single backup. Where exactly do you see any problem? * the master writes his bin-log in row format * the slaves writes his relay-log and managing inserts updates * lvm makes a snapshot of relay-log / datafiles There is none and if there could be one LVM must be broken because a snapshot has to be consistent So if you stop the slave, make the snahpshot and start the slave again there are all buffers written to the vfs-layer and the snapshot must have a defined state. Only if the db-server is running and have some data in memory cache you could have any troubles and that is why not copy the files as long the server is running Nobody out there makes a copy of database files while the server is running, really nobody! -- Mit besten Grüßen, 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/ signature.asc Description: OpenPGP digital signature
RE: InnoDB and rsync
-Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Tuesday, January 25, 2011 9:56 AM To: Reindl Harald Cc: Robinson, Eric; mysql@lists.mysql.com Subject: Re: InnoDB and rsync jesus christ nobody cares if they are binary replica as long as the data is consistent and ident Actually, I can see this being an issue if you're using LVM snapshot backups or another similar technique - if the datafiles aren't all identical you won't be able to restore to any machine from a single backup. [JS] I don't get it. Isn't this like saying that you can't substitute a dictionary with a red cover for a dictionary with a blue cover? If you do a complete LVM restore, then you have complete and (hopefully) consistent copy of your files, even if the volume images were not identical; in fact, they almost certainly will NOT be identical at the disk-image level. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- 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: InnoDB and rsync
There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. ... sounds interesting; have you got any document explaining this phenomenon? AFAIK, the things that (silently) break replication are: - non-deterministic functions in statement-based replication - hand-made updates on the slave db is this enough to justify a *daily* resync?! I'm definitely no expert on this. All I know is that we used to frequently experience situations where queries to the slaves would return different recordsets than the same queries to the masters. Yet by all other indications the servers were in sync. All the replication threads were running and the row counts were identical, but the data in the rows was sometimes different. I asked about this in the list and the answers I got back were that the phenomenon was called row drift and was fairly well known and not always easy (or sometimes even possible) to eliminate because of bad programming practices in some off-the-shelf applications. At that time, the consensus in the list was that it was not safe to trust replication slaves for backup purposes. That's when I came up with the idea of doing an rsync every night, which creates a slave that is 100% reliable for using as a backup source and also eliminates problems with row-drift. Since we started using that technique, we don't get calls from users complaining that their reports are showing bogus totals and such. Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Mattia Merzi,Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: InnoDB and rsync
nobody cares if they are binary replica as long as the data is consistent and ident Like I said, I'm no expert on this, but my approach seems like the only way to 100% absolutely sure that the data on the slave is in fact consistent and identical to the data on tha master. so start another slave on the machine with his own socket for backups You say that like it doesn't mean a huge amount of additional work, expense, and complexity. We currently have 240+ master MySQL instances and are adding them at a rate of several per week. Based on everything you've said so far, I still prefer my solution. I just need a way to make the same thing work with InnoDB. -- Eric Robinson Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
Am 25.01.2011 16:56, schrieb Robinson, Eric: You say that like it doesn't mean a huge amount of additional work, expense, and complexity. We currently have 240+ master MySQL instances and are adding them at a rate of several per week. 240 mysql-servers? why there is no consolidation? Based on everything you've said so far, I still prefer my solution. I just need a way to make the same thing work with InnoDB. this is simply impossible -- Mit besten Grüßen, 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/ signature.asc Description: OpenPGP digital signature
RE: InnoDB and rsync
240 mysql-servers? why there is no consolidation? I said 240+ mysql *instances*, not servers. It's actually just 3 physical servers (not counting standby cluster nodes). just need a way to make the same thing work with InnoDB. this is simply impossible That is very unfortunate. Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
Am 25.01.2011 18:38, schrieb Robinson, Eric: 240 mysql-servers? why there is no consolidation? I said 240+ mysql *instances*, not servers. It's actually just 3 physical servers (not counting standby cluster nodes). 240 mysql-instances on 3 physical hosts? what crazy setup is this please? sorry but your smallest problem is really innodb per rsync just need a way to make the same thing work with InnoDB. this is simply impossible That is very unfortunate. The whole world can work with replication-slaves and you are the only one who installing an endless count of mysql-services instead a hand of large instances - i would think that not all others are ghost-drivers and nobody outside mysql would ever think of backup a running db-server signature.asc Description: OpenPGP digital signature
RE: InnoDB and rsync
240 mysql-instances on 3 physical hosts? what crazy setup is this please? Processors average 90% idle, peaks are low, iowait is low, the system is not swapping, response time is good, and our users are happy all around the country. What is crazy about that? The whole world can work with replication-slaves and you are the only one who installing an endless count of mysql-services instead a hand of large instances I don't know how the rest of the world does it, but we have been doing it like this since 2006 and it has worked great and we have never regretted having multiple instances of mysql. In fact, it is really great because we can maintain each customer's service individually, stop and start mysql without affecting other customers, turn query logs on and off for each customer, customize performance parameters, and so on. I can maintain a customer's database right in the middle of a production day and the other customers won't even notice! It has been great being able to do all these things. outside mysql would ever think of backup a running db-server Then you're just not Googling very well. :-) --Eric Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
On 1/25/2011 10:45, Robinson, Eric wrote: There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. ... sounds interesting; have you got any document explaining this phenomenon? AFAIK, the things that (silently) break replication are: - non-deterministic functions in statement-based replication - hand-made updates on the slave db is this enough to justify a *daily* resync?! I'm definitely no expert on this. All I know is that we used to frequently experience situations where queries to the slaves would return different recordsets than the same queries to the masters. Yet by all other indications the servers were in sync. All the replication threads were running and the row counts were identical, but the data in the rows was sometimes different. I asked about this in the list and the answers I got back were that the phenomenon was called row drift and was fairly well known and not always easy (or sometimes even possible) to eliminate because of bad programming practices in some off-the-shelf applications. At that time, the consensus in the list was that it was not safe to trust replication slaves for backup purposes. That's when I came up with the idea of doing an rsync every night, which creates a slave that is 100% reliable for using as a backup source and also eliminates problems with row-drift. Since we started using that technique, we don't get calls from users complaining that their reports are showing bogus totals and such. I suspect that your queries were not as deterministic as you thought they were. Do you have a sample of a query that produced different results between the master and the slave? We shouldn't need the results, just the query. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
On 1/25/2011 09:00, Robinson, Eric wrote: ... I'm starting to worry that you may be right. I know FLUSH TABLES WITH READ LOCK does not work as expected with InnoDB, but is there really no way to put InnoDB into a state where all changes have been flushed to disk and it is safe to rsync the directory? Is stopping the service really the only way? (And even if I stop the service, is rsync totally safe with InnoDB?) You need to quiesce the InnoDb background threads. One technique is mentioned here: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html Look for the section talking about clean backups. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: InnoDB and rsync
On 1/25/2011 10:45, Robinson, Eric wrote: There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. ... sounds interesting; have you got any document explaining this phenomenon? AFAIK, the things that (silently) break replication are: - non-deterministic functions in statement-based replication - hand-made updates on the slave db is this enough to justify a *daily* resync?! I'm definitely no expert on this. All I know is that we used to frequently experience situations where queries to the slaves would return different recordsets than the same queries to the masters. Yet by all other indications the servers were in sync. All the replication threads were running and the row counts were identical, but the data in the rows was sometimes different. I asked about this in the list and the answers I got back were that the phenomenon was called row drift and was fairly well known and not always easy (or sometimes even possible) to eliminate because of bad programming practices in some off-the-shelf applications. At that time, the consensus in the list was that it was not safe to trust replication slaves for backup purposes. That's when I came up with the idea of doing an rsync every night, which creates a slave that is 100% reliable for using as a backup source and also eliminates problems with row-drift. Since we started using that technique, we don't get calls from users complaining that their reports are showing bogus totals and such. I suspect that your queries were not as deterministic as you thought they were. Do you have a sample of a query that produced different results between the master and the slave? We shouldn't need the results, just the query. Sorry, no. The software is a canned medical application so we cannot easily inspect the queries that could have been causing the problem. Even though we could capture them in various ways (sniffer, proxy, query logs) it would not be easy to isolate the culprits out of the tens of thousands issued every day. And it was a year or more ago. We have not had the problem since we started rsyncing. :-) Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Shawn Green (MySQL),Mattia Merzi,Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: InnoDB and rsync
I'm starting to worry that you may be right. I know FLUSH TABLES WITH READ LOCK does not work as expected with InnoDB, but is there really no way to put InnoDB into a state where all changes have been flushed to disk and it is safe to rsync the directory? Is stopping the service really the only way? (And even if I stop the service, is rsync totally safe with InnoDB?) You need to quiesce the InnoDb background threads. One technique is mentioned here: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp aces.html Look for the section talking about clean backups. Now we're talkin. I'll check it out. Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Shawn Green (MySQL),Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: InnoDB and rsync
You need to quiesce the InnoDb background threads. One technique is mentioned here: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp aces.html Look for the section talking about clean backups. Now we're talkin. I'll check it out. I read that section but it is not at all clear (1) how one quiesces the InnoDB background threads, or (2) if there is a way to keep them quiesced while the backup is in progress. Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Robinson, Eric,Shawn Green (MySQL),Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
I suspect the same trick might work with InnoDB (with pretty much the same caveats), but you'd be best off setting innodb-file-per-table - I'm sure you've already seen that the large datafiles are a hindrance to smooth rsyncing :-) Make sure to test extensively, though. On Tue, Jan 25, 2011 at 5:37 AM, Robinson, Eric eric.robin...@psmnv.comwrote: Is there a way to safely backup an InnoDB database using rsync? Right now we have a very efficient and reliable way to backup 240+ separate instances of MySQL with MyISAM tables. The databases range in size from .5GB to 16GB. During this time, users can still access the system, so our customers can work 24x7. In the process, we also refresh 240+ slave instances with a perfect byte-for-byte replica of the master databases. The whole thing takes about 30 minutes. Here's how we do it. Late at night when the number of users on the system is low, we do the following for each of the 240+ instances of MySQL... 1. Shut down the slave and remove all replication-related log files. 2. Perform an rsync of the master's data directory to the slave. Users may be making changes to tables during this rsync. 3. Issue a FLUSH TABLES WITH READ LOCK on the master followed by a RESET MASTER. 4. Perform a second rsync of the data directory from the master to the slave to copy any user changes that happened during step 2. This usually completes in a few seconds, often less than 1. If any users were trying to insert records at this exact moment, their application may appear to pause very briefly. 5. Start the slave. When I'm all done, I have 240+ slave servers in perfect sync with their masters, each having a 100% identical binary copy of its master's database. Since these copies are truly identical, they can be used for a second layer of backup to other media. Like I said, the whole process takes about 30 minutes because the rsync algorithm only copies the byte-level changes. IS THERE ANY WAY TO SET UP SOMETHING THIS EASY AND EFFICIENT USING INNODB? I've been reading about InnoDB hot copy and other approaches, but none of them seem to work as well as the approach I have worked out with MyISAM. Unfortunately, my software wants to force us to switch to InnoDB, so I'm really stuck. If we have to switch to InnoDB and we cannot come up with a method for doing fast, rsync-style backups, it will probably mean huge, costly, and unnecessary changes to our infrastructure. Any help will be GREATLY appreciated. -- Eric Robinson Disclaimer - January 24, 2011 This email and any files transmitted with it are confidential and intended solely for mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Innodb table space questions
reply inline On Mon, Jan 17, 2011 at 9:30 PM, Angela liu yyll2...@yahoo.com wrote: Folks : two questions: 1. can Innodb create per database table space , not per table table space? No. The only available options are creating a global tablespace which can be many files or a file per table. 2. can we store table on specific tablespace like Oracle or DB2 when creating table? You can only choose to store a table in it's own tablespace or in the global one. Many thanks. -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Innodb table space questions
Thanks first, I checked MySQL 5.1 manual, looks like , 'create tablespace' is ok only with NDB and NDBCLUSTER, NOT INNODB And I did not find 'create tablespace' in MySQL 5.5 manual:( looks like MySQL5.5 does not offer 'create tablespace' anymore. --- On Mon, 1/17/11, Eric Bergen eric.ber...@gmail.com wrote: From: Eric Bergen eric.ber...@gmail.com Subject: Re: Innodb table space questions To: Angela liu yyll2...@yahoo.com Cc: mysql@lists.mysql.com Date: Monday, January 17, 2011, 10:09 PM reply inline On Mon, Jan 17, 2011 at 9:30 PM, Angela liu yyll2...@yahoo.com wrote: Folks : two questions: 1. can Innodb create per database table space , not per table table space? No. The only available options are creating a global tablespace which can be many files or a file per table. 2. can we store table on specific tablespace like Oracle or DB2 when creating table? You can only choose to store a table in it's own tablespace or in the global one. Many thanks. -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net
RE: Innodb can't start
Once you get innodb corruption like this, you generally have to try to dump all your data, shutdown mysql, wipe out all innodb tables and files, then restart mysql reimport: It gives the link http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html, to help explain how to start innodb and get your data out. Regards, Gavin Towey -Original Message- From: Julien Jabouin [mailto:chatlumo@gmail.com] Sent: Sunday, October 31, 2010 5:27 AM To: mysql@lists.mysql.com Subject: Innodb can't start Hello, I have a database with tables in innodb and from today database can't be start. I don't know what to do, if your can help, this my mysql log error : Oct 31 13:18:16 myserver mysqld[13681]: 101031 13:18:16 [Note] /usr/sbin/mysqld: Normal shutdown Oct 31 13:18:16 myserver mysqld[13681]: Oct 31 13:18:16 myserver mysqld[13681]: 101031 13:18:16 InnoDB: Starting shutdown... Oct 31 13:18:18 myserver mysqld[13681]: 101031 13:18:18 InnoDB: Shutdown completed; log sequence number 6 2573408134 Oct 31 13:18:18 myserver mysqld[13681]: 101031 13:18:18 [Note] /usr/sbin/mysqld: Shutdown complete Oct 31 13:18:18 myserver mysqld[13681]: Oct 31 13:18:18 myserver mysqld_safe[14191]: ended Oct 31 13:18:19 myserver mysqld_safe[14258]: started Oct 31 13:18:19 myserver mysqld[14261]: 101031 13:18:19 InnoDB: Started; log sequence number 6 2573408134 Oct 31 13:18:19 myserver mysqld[14261]: 101031 13:18:19 [Note] /usr/sbin/mysqld: ready for connections. Oct 31 13:18:19 myserver mysqld[14261]: Version: '5.0.51a-24+lenny3' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian) Oct 31 13:18:20 myserver /etc/mysql/debian-start[14295]: Upgrading MySQL tables if necessary. Oct 31 13:18:20 myserver /etc/mysql/debian-start[14300]: Looking for 'mysql' in: /usr/bin/mysql Oct 31 13:18:20 myserver /etc/mysql/debian-start[14300]: Looking for 'mysqlcheck' in: /usr/bin/mysqlcheck Oct 31 13:18:20 myserver /etc/mysql/debian-start[14300]: This installation of MySQL is already upgraded to 5.0.51a, use --force if you still need to run mysql_upgrade Oct 31 13:18:20 myserver /etc/mysql/debian-start[14307]: Checking for insecure root accounts. Oct 31 13:18:20 myserver /etc/mysql/debian-start[14311]: Triggering myisam-recover for all MyISAM tables Oct 31 13:18:22 myserver mysqld[14261]: InnoDB: Database page corruption on disk or a failed Oct 31 13:18:22 myserver mysqld[14261]: InnoDB: file read of page 178137. Oct 31 13:18:22 myserver mysqld[14261]: InnoDB: You may have to recover from a backup. Oct 31 13:18:22 myserver mysqld[14261]: 101031 13:18:22 InnoDB: Page dump in ascii and hex (16384 bytes): Oct 31 13:18:22 myserver mysqld[14261]: len 16384; hex 4eafb5eb0002b7d900061aaac14145bf000200ba800500aa00020002000303b0f391000179fbc3213332c3213272010002001b696e66696d756d0004000b73757072656d756d0010001100168000124367e422b00026000265530019001680001245cdc328ce1ead000265540021ffc680001247f7abe58f3de20006329 ... ... ... Oct 31 13:18:22 myserver mysqld[14261]: 000 Oct 31 13:18:22 myserver mysqld[14261]: 007000632e150a1b1aaac141; asc N AE y!32 !2r infimum supremum Cg eS E ( eT
Re: InnoDB Crash
Hey Willy - Install the new binaries and start mysql with new binary as basedir and see whether innodb has enabled or not. Check the error log why the innodb is getting disabled, make a copy of it here too. On Tue, Oct 12, 2010 at 2:57 PM, Willy Mularto sangpr...@gmail.com wrote: Hi List, Last night accidentally one of my InnoDB table crash. And cause client can not connect to MySQL, it always said cannot connect to socket, even the daemon is launched. I tried to set innodb_force_recovery from 0 to 6 and only number 3 bring back the connection. After that I dump the data and drop the table. I recreate it as MyISAM and inject the dumped data. After that I stop MySQL and remove innodb_force_recovery and restart. And clients start complaining can not connect. Then I enable innodb_force_recovery again. I tried to create a new InnoDB table test and MySQL complaint cannot create the table due to the engine type is not supported. The question is how to solve this problem? How to bring back InnoDB to my server without reinstall the OS or MySQL itself? I have also tried to drop the database and remove the data folder from mysql data dir and recreate the database but still no luck. Thanks for any help. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: InnoDB Crash
I got the solution. I stop the ib* files in MySQL datadir and start the server. Now everything is back to normal. Thanks. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ On Oct 12, 2010, at 11:00 PM, Suresh Kuna wrote: Hey Willy - Install the new binaries and start mysql with new binary as basedir and see whether innodb has enabled or not. Check the error log why the innodb is getting disabled, make a copy of it here too. On Tue, Oct 12, 2010 at 2:57 PM, Willy Mularto sangpr...@gmail.com wrote: Hi List, Last night accidentally one of my InnoDB table crash. And cause client can not connect to MySQL, it always said cannot connect to socket, even the daemon is launched. I tried to set innodb_force_recovery from 0 to 6 and only number 3 bring back the connection. After that I dump the data and drop the table. I recreate it as MyISAM and inject the dumped data. After that I stop MySQL and remove innodb_force_recovery and restart. And clients start complaining can not connect. Then I enable innodb_force_recovery again. I tried to create a new InnoDB table test and MySQL complaint cannot create the table due to the engine type is not supported. The question is how to solve this problem? How to bring back InnoDB to my server without reinstall the OS or MySQL itself? I have also tried to drop the database and remove the data folder from mysql data dir and recreate the database but still no luck. Thanks for any help. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: innodb backup
I'm interested in InnoDB backups. Does anyone use PHPMyAdmin ? I've a MySQL server on a shared hosting server. Cheers Neil On Sat, Oct 9, 2010 at 3:21 AM, short.cut...@yahoo.com.cn wrote: Hello, Is there any good document for backup of InnoDB? includes the increment backup and full backup. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Re: innodb backup
Hi, On Sat, Oct 9, 2010 at 4:21 AM, short.cut...@yahoo.com.cn wrote: Hello, Is there any good document for backup of InnoDB? includes the increment backup and full backup. There is an overview of backups here http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html XtraBackup supports incremental backups for InnoDB/XtraDB. http://www.percona.com/software/percona-xtrabackup/ Ewen Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ewen.fort...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB Buffer Pool Status
Willy Mularto wrote: Hi, I got this result on InnoDB Buffer Pool Status: Free pages1 Dirty pages 2,040 Pages containing data 31,359 Pages to be flushed 457,083,205 Busy pages1,408 Read requests 31,348,288,497 Write requests7,913,407,934 Read misses 39,736,110 Write waits 0 Read misses in % 0.13 % Write waits in % 0.00 % I see there are millions of Read misses. What's that mean? And how to tuning up my server to get faster, stable, and reliable? Many thanks for any response. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ I'm a novice myself, so I can't offer much in the way of wise advice. I can, however, point you to a neat script that might give you some useful pointers. [ https://launchpad.net/mysql-tuning-primer ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB Buffer Pool Status
On Tue, Sep 21, 2010 at 12:49 PM, Willy Mularto sangpr...@gmail.com wrote: Hi, I got this result on InnoDB Buffer Pool Status: Free pages 1 Dirty pages 2,040 Pages containing data 31,359 Pages to be flushed 457,083,205 Busy pages 1,408 Read requests 31,348,288,497 Write requests 7,913,407,934 Read misses 39,736,110 Write waits 0 Read misses in %0.13 % Write waits in %0.00 % I see there are millions of Read misses. What's that mean? Nothing much, in and of itself. Divide by your uptime, and you'll get a vaguely meaningful number. Keep track of the counter over time, and you may see something actually useful. And how to tuning up my server to get faster, stable, and reliable? Many thanks for any response. By understanding how it works, what it does, and what the different counters and variables mean. There's no magic trick for it - start by investing time in reading the documentation; play with test systems; and if you've got the dough, get mysql-sanctioned training. Nothing in life is free. If it doesn't cost money, maybe you have to spend time. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: InnoDB Tablespace
On Mon, Aug 2, 2010 at 8:35 PM, Johnny Withers joh...@pixelated.net wrote: Now when i run the same show table status command, the comment field says: InnoDB free: 6144 kB Is that telling me that I only have 6MB of storage left even though I increased the table space by 8GB? I seem to recall - but am on holiday, and thus way too lazy to actually check - that the free refers to the amount of free space in the files (so should go up when you delete records, and so on), not amount of total disk space you are still allowed to fill. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: InnoDB Tablespace
About the above - it is saying 6144 KB so it is 6.1 GB. Are you sure? I would think 6144KB = 6.144 MB, or 6144 * 1000 = 6,144,000 bytes. I think since InnoDB, by default, extends the table space by 8MB increments, this is reporting the free space in this increment. How can I tell total remaining space so I can adjust and/or add new table space before it runs out of space next time? I have another server with a different config line, however, the last innodb file specified is also max 16G and when i run show table status on it, it reports 3983360 kB free, which i would assume is 3.9 GB? Could this be because it's filling up space in one of the files before the last auto-extending file, which these files are fixed sizes? Both servers are 5.0.77-log. One server is Cent OS, the other is RHEL. I'm very confused here. JW On Mon, Aug 2, 2010 at 1:53 PM, Suresh Kuna sureshkumar...@gmail.comwrote: Hey john, Yes you can add it but safe to keep auto-extend at the end and monitor the disk space as well. Now when i run the same show table status command, the comment field says: InnoDB free: 6144 kB Is that telling me that I only have 6MB of storage left even though I increased the table space by 8GB? About the above - it is saying 6144 KB so it is 6.1 GB. On Tue, Aug 3, 2010 at 12:05 AM, Johnny Withers joh...@pixelated.netwrote: I recently ran out of table space on a production server that had the following configuration line: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:8G Before I changed this line and restarted the server, I ran SHOW TABLE STATUS LIKE 'table' on one of the databases and the comment filed said: InnoDB Free: 3NNN kB (I don't remember the exact number, but know it started with 3 and had 4 digits. I modified the configuration line above to: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:16G Now when i run the same show table status command, the comment field says: InnoDB free: 6144 kB Is that telling me that I only have 6MB of storage left even though I increased the table space by 8GB? Also, If I wanted to add another file to this file_path variable, can I just add it to the end like so: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:16G; *ibdata7:16G* Or will that cause MySQL to complain the file size isn't correct the next time it starts? Thanks for any help! -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Thanks Suresh Kuna MySQL DBA -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: InnoDB Tablespace
Hi Johnny, Sorry about that - i just overlooked and the simple way to calculate the sizes is to query the information_schema table called tables for data and index sizes. On Tue, Aug 3, 2010 at 8:55 PM, Johnny Withers joh...@pixelated.net wrote: About the above - it is saying 6144 KB so it is 6.1 GB. Are you sure? I would think 6144KB = 6.144 MB, or 6144 * 1000 = 6,144,000 bytes. I think since InnoDB, by default, extends the table space by 8MB increments, this is reporting the free space in this increment. How can I tell total remaining space so I can adjust and/or add new table space before it runs out of space next time? I have another server with a different config line, however, the last innodb file specified is also max 16G and when i run show table status on it, it reports 3983360 kB free, which i would assume is 3.9 GB? Could this be because it's filling up space in one of the files before the last auto-extending file, which these files are fixed sizes? Both servers are 5.0.77-log. One server is Cent OS, the other is RHEL. I'm very confused here. JW On Mon, Aug 2, 2010 at 1:53 PM, Suresh Kuna sureshkumar...@gmail.comwrote: Hey john, Yes you can add it but safe to keep auto-extend at the end and monitor the disk space as well. Now when i run the same show table status command, the comment field says: InnoDB free: 6144 kB Is that telling me that I only have 6MB of storage left even though I increased the table space by 8GB? About the above - it is saying 6144 KB so it is 6.1 GB. On Tue, Aug 3, 2010 at 12:05 AM, Johnny Withers joh...@pixelated.netwrote: I recently ran out of table space on a production server that had the following configuration line: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:8G Before I changed this line and restarted the server, I ran SHOW TABLE STATUS LIKE 'table' on one of the databases and the comment filed said: InnoDB Free: 3NNN kB (I don't remember the exact number, but know it started with 3 and had 4 digits. I modified the configuration line above to: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:16G Now when i run the same show table status command, the comment field says: InnoDB free: 6144 kB Is that telling me that I only have 6MB of storage left even though I increased the table space by 8GB? Also, If I wanted to add another file to this file_path variable, can I just add it to the end like so: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:16G; *ibdata7:16G* Or will that cause MySQL to complain the file size isn't correct the next time it starts? Thanks for any help! -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Thanks Suresh Kuna MySQL DBA -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Thanks Suresh Kuna MySQL DBA
Re: InnoDB Tablespace
Hey john, Yes you can add it but safe to keep auto-extend at the end and monitor the disk space as well. Now when i run the same show table status command, the comment field says: InnoDB free: 6144 kB Is that telling me that I only have 6MB of storage left even though I increased the table space by 8GB? About the above - it is saying 6144 KB so it is 6.1 GB. On Tue, Aug 3, 2010 at 12:05 AM, Johnny Withers joh...@pixelated.netwrote: I recently ran out of table space on a production server that had the following configuration line: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:8G Before I changed this line and restarted the server, I ran SHOW TABLE STATUS LIKE 'table' on one of the databases and the comment filed said: InnoDB Free: 3NNN kB (I don't remember the exact number, but know it started with 3 and had 4 digits. I modified the configuration line above to: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:16G Now when i run the same show table status command, the comment field says: InnoDB free: 6144 kB Is that telling me that I only have 6MB of storage left even though I increased the table space by 8GB? Also, If I wanted to add another file to this file_path variable, can I just add it to the end like so: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:16G; *ibdata7:16G* Or will that cause MySQL to complain the file size isn't correct the next time it starts? Thanks for any help! -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Thanks Suresh Kuna MySQL DBA
Re: Innodb Choosing Random Index
optimize / analyze table in each of the hosts is not a good option for me , this is an in-production set-up with minimal number of boxes in rotation . On Sun, Jul 11, 2010 at 5:57 PM, arijit bhattacharyya new2mys...@gmail.comwrote: Hi , i am running into trouble due to wrong index chosen by mysql in some particular type of queries . This is happening in a critical production environment where we have deployment in two different colocations . I am seeing that a paticular query is using one index in one set of hosts another index in another set of hosts . We are not using 'use index' clause to explicitly mention the index due to some limitations . But wondering based on what mysql is using different indexes in different hosts . This is really surprising , since dataset table structures are exactly same in all the hosts . Other than changing the code to force using 'use index' , is there any other way to resolve it ? And what's the exact reason behind this ? Just to have mysql choose the correct index always will simply solve my problem . Thanks .
Re: Innodb Choosing Random Index
You you send us explain of that query. On Sun, Jul 11, 2010 at 6:31 PM, arijit bhattacharyya new2mys...@gmail.comwrote: optimize / analyze table in each of the hosts is not a good option for me , this is an in-production set-up with minimal number of boxes in rotation . On Sun, Jul 11, 2010 at 5:57 PM, arijit bhattacharyya new2mys...@gmail.comwrote: Hi , i am running into trouble due to wrong index chosen by mysql in some particular type of queries . This is happening in a critical production environment where we have deployment in two different colocations . I am seeing that a paticular query is using one index in one set of hosts another index in another set of hosts . We are not using 'use index' clause to explicitly mention the index due to some limitations . But wondering based on what mysql is using different indexes in different hosts . This is really surprising , since dataset table structures are exactly same in all the hosts . Other than changing the code to force using 'use index' , is there any other way to resolve it ? And what's the exact reason behind this ? Just to have mysql choose the correct index always will simply solve my problem . Thanks . -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Innodb Choosing Random Index
On Sun, Jul 11, 2010 at 7:07 PM, Leonardo Leonardo new2mys...@gmail.comwrote:
Here is the structure of the Table T1 ( ENGINE=InnoDB ) -
`c1` varchar(128) NOT NULL default '',
`c2` int(11) NOT NULL default '0',
`c3` varchar(32) NOT NULL default '',
`c4` blob,
`c5` double default NULL,
`c6` varchar(255) default NULL,
`c7` enum('BLOB','NUMERIC','STRING') NOT NULL default 'BLOB',
KEY `key1` (`c1`,`c2`,`c3`,`c5`),
KEY `key2` (`c1`,`c2`,`c3`,`c6`),
The query is as below -
SELECT * FROM T1 WHERE (c1 = '$string1' AND c2 IN ($int1, $int2 , $int3,
$int4 , $int5, $int6 , $int7 , $int8, $int9, $int10 ))
Mysql is choosing key1 key2 in different situations .
On Sun, Jul 11, 2010 at 6:39 PM, Prabhat Kumar aim.prab...@gmail.comwrote:
You you send us explain of that query.
On Sun, Jul 11, 2010 at 6:31 PM, arijit bhattacharyya
new2mys...@gmail.com wrote:
optimize / analyze table in each of the hosts is not a good option for me
,
this is an in-production set-up with minimal number of boxes in rotation
.
On Sun, Jul 11, 2010 at 5:57 PM, arijit bhattacharyya
new2mys...@gmail.comwrote:
Hi , i am running into trouble due to wrong index chosen by mysql in
some
particular type of queries . This is happening in a critical production
environment where we have deployment in two different colocations . I
am
seeing that a paticular query is using one index in one set of hosts
another index in another set of hosts . We are not using 'use index'
clause
to explicitly mention the index due to some limitations . But wondering
based on what mysql is using different indexes in different hosts .
This is
really surprising , since dataset table structures are exactly same
in all
the hosts .
Other than changing the code to force using 'use index' , is there any
other way to resolve it ? And what's the exact reason behind this ?
Just to
have mysql choose the correct index always will simply solve my problem
.
Thanks .
--
Best Regards,
Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com
My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Innodb Choosing Random Index
In the last episode (Jul 11), Leonardo Leonardo said:
On Sun, Jul 11, 2010 at 7:07 PM, Leonardo Leonardo
new2mys...@gmail.comwrote:
Here is the structure of the Table T1 ( ENGINE=InnoDB ) -
`c1` varchar(128) NOT NULL default '',
`c2` int(11) NOT NULL default '0',
`c3` varchar(32) NOT NULL default '',
`c4` blob,
`c5` double default NULL,
`c6` varchar(255) default NULL,
`c7` enum('BLOB','NUMERIC','STRING') NOT NULL default 'BLOB',
KEY `key1` (`c1`,`c2`,`c3`,`c5`),
KEY `key2` (`c1`,`c2`,`c3`,`c6`),
The query is as below -
SELECT * FROM T1 WHERE (c1 = '$string1' AND c2 IN ($int1, $int2 , $int3,
$int4 , $int5, $int6 , $int7 , $int8, $int9, $int10 ))
Mysql is choosing key1 key2 in different situations .
InnoDB estimates index cardinality on every query by examining a few random
disk blocks in each index. Depending on which pages are examined, mysql
might decide one index is better then the other even if everything else is
the same. Try running EXPLAIN SELECT on the same query a few times and see
if the optimizer picks different indexes. If you're running a new enough
version of mysql (5.1.38 or newer), you can change the
innodb_stats_sample_pages variable to raise the number of pages from the
default of 8 (try 16). That will make the estimate more accurate and
hopefully mysql will pick the right index consistently.
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_stats_sample_pages
Another solution might be to create another index on (c1,c2), since that
index is the most efficient one for your particular query. MySQL will
always pick that index over the other two.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Innodb buffer pool size
Machiel, I'm going to disagree strongly with the previous advice you got. You should NOT configure the buffer pool to be larger than the amount of RAM you have. If part of the buffer pool is swapped out, then swapping it back in is MUCH worse than re-fetching the page. InnoDB doesn't know the difference between in-memory and swapped out. (That's the point of virtual memory; it's invisible to the program.) It assumes that a memory access is fast. If it turns out not to really be a memory access, but instead is a disk access to swap something in, then everything goes very badly. If you search for buffer pool size on mysqlperformanceblog.com, you will get good advice. You should also get a copy of High Performance MySQL, Second Edition. (I'm the lead author.) In short: ignore advice about ratios, and ignore advice about the size of your data. Configure the buffer pool to use the amount of memory available, subtracting what's required for the OS and other things on the computer to run effectively. - Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
