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
innodb log sequence problem
Hello, We are facing a strange innodb related problem. Our client ran mysql 5.1 on WinXP having file_per_table disabled. OS crashed after 5 years continuous running and our client of course does not have any backup (big company with own IT department so we do not have acces to their system and IT policy). We received the mysql folders to see whether we can recover data somehow. We installed a new myql 5.1 instance. Changed my.ini in program files\mysql\msql server 5.1 either changed bin folder all in all. We changed data folder as well to the crashed on containing all database folders with all .frm files ib_logfile0, ib_logfile1 and ibdata1 as well. Trying to start mysql service log says the following: 50805 16:58:28 [Note] Plugin 'FEDERATED' is disabled. 150805 16:58:28 InnoDB: Initializing buffer pool, size = 47.0M 150805 16:58:28 InnoDB: Completed initialization of buffer pool InnoDB: Error: log file .\ib_logfile0 is of different size 0 10485808 bytes InnoDB: than specified in the .cnf file 0 25165824 bytes! 150805 16:58:28 [ERROR] Plugin 'InnoDB' init function returned error. 150805 16:58:28 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 150805 16:58:28 [ERROR] Unknown/unsupported table type: INNODB 150805 16:58:28 [ERROR] Aborting Strange is that ib_logfile0 is 10MB+48 bytes however as far as I know innodb_log_file_size system variable can only be added in M. We tryed to remove ib_logfile0 and ib_logfile1 from data folder and tryed to start the instance again. Logfiles were created but got the following error mesage in error log: 150805 17:02:29 [Note] Plugin 'FEDERATED' is disabled. 150805 17:02:30 InnoDB: Initializing buffer pool, size = 47.0M 150805 17:02:30 InnoDB: Completed initialization of buffer pool 150805 17:02:30 InnoDB: Log file .\ib_logfile0 did not exist: new to be created InnoDB: Setting log file .\ib_logfile0 size to 24 MB InnoDB: Database physically writes the file full: wait... 150805 17:02:30 InnoDB: Log file .\ib_logfile1 did not exist: new to be created InnoDB: Setting log file .\ib_logfile1 size to 24 MB InnoDB: Database physically writes the file full: wait... InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 150805 17:02:31 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... 150805 17:02:31 InnoDB: Error: space id and page n:o stored in the page InnoDB: read in are 960999566:544833488, should be 0:7! InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 7. InnoDB: You may have to recover from a backup. 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. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. InnoDB: Ending processing because of a corrupt database page. We tried to start with system variable innodb_force_recovery set 1-6 none of them helped. Does anyone have any idea how to recover? Many thanks in advance. Arpad Csepregi -- 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: Como monitorar o InnoDB Change Buffer
Please, ignore last message, wrong list :) -- *Wagner Bianchi, +55.31.8654.9510* Oracle ACE Director https://apex.oracle.com/pls/otn/f?p=19297:4:105567988301604::NO:4:P4_ID:4541, MySQL Certified Professional Percona MySQL Forum http://www.percona.com/forums/ Community V.I.P. Email: m...@wagnerbianchi.com Skype: wbianchijr Em 27 de maio de 2015 14:29, Wagner Bianchi wagnerbianch...@gmail.com escreveu: Pessoal, depois de solicitar para o time da Oracle a inclusão de informações de monitoramento do InnoDB Change Buffer, a adição de informação foi levada em consideração e compartilho com vocês: https://dev.mysql.com/doc/refman/5.5/en/innodb-insert-buffering.html https://dev.mysql.com/doc/refman/5.6/en/innodb-insert-buffering.html https://dev.mysql.com/doc/refman/5.7/en/innodb-insert-buffering.html Tome cuidado ao trabalhar esse subsistema, pois, o valor de innodb_change_buffering é um percentual do Buffer Pool que é destinado ao change buffer - principal objetivo aqui é diminuir a pressão por I/O em I/O-Bound workloads. Abraço!! -- *Wagner Bianchi, +55.31.8654.9510 %2B55.31.8654.9510* Oracle ACE Director https://apex.oracle.com/pls/otn/f?p=19297:4:105567988301604::NO:4:P4_ID:4541, MySQL Certified Professional Percona MySQL Forum http://www.percona.com/forums/ Community V.I.P. Email: m...@wagnerbianchi.com Skype: wbianchijr
Como monitorar o InnoDB Change Buffer
Pessoal, depois de solicitar para o time da Oracle a inclusão de informações de monitoramento do InnoDB Change Buffer, a adição de informação foi levada em consideração e compartilho com vocês: https://dev.mysql.com/doc/refman/5.5/en/innodb-insert-buffering.html https://dev.mysql.com/doc/refman/5.6/en/innodb-insert-buffering.html https://dev.mysql.com/doc/refman/5.7/en/innodb-insert-buffering.html Tome cuidado ao trabalhar esse subsistema, pois, o valor de innodb_change_buffering é um percentual do Buffer Pool que é destinado ao change buffer - principal objetivo aqui é diminuir a pressão por I/O em I/O-Bound workloads. Abraço!! -- *Wagner Bianchi, +55.31.8654.9510* Oracle ACE Director https://apex.oracle.com/pls/otn/f?p=19297:4:105567988301604::NO:4:P4_ID:4541, MySQL Certified Professional Percona MySQL Forum http://www.percona.com/forums/ Community V.I.P. Email: m...@wagnerbianchi.com Skype: wbianchijr
Re: MySQL 5.7 Innodb performans issue
Found the cause. sync_binlog was set to 1. I suspect that the default value is 5.5 was 0 and that is has changed to 1 sometime after that. Setting it to 0 boosted the performance back to normal (4x speed) and the HD LED indicated much lower stress on the hard disk. Found this after I found out what caused it: https://www.percona.com/blog/2009/01/21/beware-ext3-and-sync-binlog-do-not-play-well-together/ I suspect that this also apply to ext4, or? -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
Hi Jørn, Found this after I found out what caused it: https://www.percona.com/blog/2009/01/21/beware-ext3-and-sync-binlog-do-not-play-well-together/ I suspect that this also apply to ext4, or? I would go more specific and say that sync_binlog=1 does not play well with single-threaded workloads. MySQL 5.6 and above can group commit to the binary log (reducing a lot of IO if transactions arrive at the same time). I have an explanation on how it works here: http://www.tocker.ca/2014/12/30/an-easy-way-to-describe-mysqls-binary-log-group-commit.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL 5.7 Innodb performans issue
Hello (again I must say). Over a year ago I experienced a severe drop in the MySQL Innodb performance after ugrading to MySQL 5.6. I did not found any solution to that so I downgraded back to 5.5.33 and lived with in until recently. After a system disk crash I replaced the system disk with an identical disk, upgraded the test server to Fedora Core 21 and installed MySQL 5.7.7 by downloading RPM files. Now I'm facing the same problem with poor Innodb performance. How have I messured the performance? I got a data collector system that get new data from mails (stored as files). The system read the files, store data in the database and then prerform a lot of calulcations on the data. The performance is messured by how many jobs the system can process when putting months of mails into a queue at once. While I was using 5.5.53 it processed about 2000 jobs per minute with a peak up to 2500. Each time I test the system I use an initial database and the same mail files, so the input is always the same. With 5.7.7 the performance has dropped by a factor 4 - about 500-600 jobs per minute. I have played with the settings in my.cnf file but nothing seem to have any influence on the performance. So I'm using more or less the same settings for both version. Another thing I have noticed is that when running the test with MySQL 5.5.33, the hard disk LED was mostly dark. But now, it is flashing like h**l. So it seems like the problem has to do with the disk activity. I would be very glad to get some feedback on this that would bring my performance back to normal :) About the server: System disk (where the binlogs are stored) is a regular magnetic disk. All Innodb files are stored on a SSD disk mounted with defaults, nouser_xattr,noatime,data=writeback,barrier=0 The system got 32 Gb memory and have an AMD 8-core CPU (AMD FX-8120). This is my.cnf I'm currently using: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock explicit_defaults_for_timestamp = TRUE # Logging slow_query_log_file = /var/log/mysql-slow.log slow_query_log = 1 long_query_time = 10 log_queries_not_using_indexes = OFF skip-external-locking key_buffer_size = 384M max_allowed_packet = 32M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 max_connections = 50 join_buffer_size = 64M # Replication Master Server (default) # binary logging is required for replication log-bin=/var/mysql/mysql-bin server-id = 1 binlog_format=mixed # Innodb settings. innodb_open_files = 2048 open_files_limit= 8096 innodb_data_home_dir= /data/mysql/data innodb_data_file_path = ibdata1:20G;ibdata2:20G;ibdata3:20G;ibdata4:20G:autoextend innodb_file_per_table = 0 innodb_autoextend_increment = 256 innodb_log_group_home_dir = /data/mysql/data innodb_buffer_pool_size = 25G innodb_log_file_size= 300M innodb_log_files_in_group = 2 innodb_log_buffer_size = 128M innodb_flush_log_at_trx_commit = 1 innodb_support_xa = 0 innodb_flush_method = O_DIRECT innodb_lock_wait_timeout= 50 innodb_thread_concurrency = 16 innodb_fast_shutdown= 0 [mysql] no-auto-rehash -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
Can you share the SHOW ENGINE INNODB STATUS\G ? -- *Wagner Bianchi, +55.31.8654.9510* Oracle ACE Director https://apex.oracle.com/pls/otn/f?p=19297:4:105567988301604::NO:4:P4_ID:4541, MySQL Certified Professional Percona MySQL Forum http://www.percona.com/forums/ Community V.I.P. Email: m...@wagnerbianchi.com Skype: wbianchijr 2015-05-20 15:07 GMT-03:00 Jørn Dahl-Stamnes sq...@dahl-stamnes.net: Hello (again I must say). Over a year ago I experienced a severe drop in the MySQL Innodb performance after ugrading to MySQL 5.6. I did not found any solution to that so I downgraded back to 5.5.33 and lived with in until recently. After a system disk crash I replaced the system disk with an identical disk, upgraded the test server to Fedora Core 21 and installed MySQL 5.7.7 by downloading RPM files. Now I'm facing the same problem with poor Innodb performance. How have I messured the performance? I got a data collector system that get new data from mails (stored as files). The system read the files, store data in the database and then prerform a lot of calulcations on the data. The performance is messured by how many jobs the system can process when putting months of mails into a queue at once. While I was using 5.5.53 it processed about 2000 jobs per minute with a peak up to 2500. Each time I test the system I use an initial database and the same mail files, so the input is always the same. With 5.7.7 the performance has dropped by a factor 4 - about 500-600 jobs per minute. I have played with the settings in my.cnf file but nothing seem to have any influence on the performance. So I'm using more or less the same settings for both version. Another thing I have noticed is that when running the test with MySQL 5.5.33, the hard disk LED was mostly dark. But now, it is flashing like h**l. So it seems like the problem has to do with the disk activity. I would be very glad to get some feedback on this that would bring my performance back to normal :) About the server: System disk (where the binlogs are stored) is a regular magnetic disk. All Innodb files are stored on a SSD disk mounted with defaults, nouser_xattr,noatime,data=writeback,barrier=0 The system got 32 Gb memory and have an AMD 8-core CPU (AMD FX-8120). This is my.cnf I'm currently using: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock explicit_defaults_for_timestamp = TRUE # Logging slow_query_log_file = /var/log/mysql-slow.log slow_query_log = 1 long_query_time = 10 log_queries_not_using_indexes = OFF skip-external-locking key_buffer_size = 384M max_allowed_packet = 32M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 max_connections = 50 join_buffer_size = 64M # Replication Master Server (default) # binary logging is required for replication log-bin=/var/mysql/mysql-bin server-id = 1 binlog_format=mixed # Innodb settings. innodb_open_files = 2048 open_files_limit= 8096 innodb_data_home_dir= /data/mysql/data innodb_data_file_path = ibdata1:20G;ibdata2:20G;ibdata3:20G;ibdata4:20G:autoextend innodb_file_per_table = 0 innodb_autoextend_increment = 256 innodb_log_group_home_dir = /data/mysql/data innodb_buffer_pool_size = 25G innodb_log_file_size= 300M innodb_log_files_in_group = 2 innodb_log_buffer_size = 128M innodb_flush_log_at_trx_commit = 1 innodb_support_xa = 0 innodb_flush_method = O_DIRECT innodb_lock_wait_timeout= 50 innodb_thread_concurrency = 16 innodb_fast_shutdown= 0 [mysql] no-auto-rehash -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
On Wednesday, May 20, 2015, wagnerbianchi.com wrote: Can you share the SHOW ENGINE INNODB STATUS\G ? Sure, here it is. *** 1. row *** Type: InnoDB Name: Status: = 2015-05-20 20:29:56 0x7f9a4c189700 INNODB MONITOR OUTPUT = Per second averages calculated from the last 10 seconds - BACKGROUND THREAD - srv_master_thread loops: 204 srv_active, 0 srv_shutdown, 2320 srv_idle srv_master_thread log flush and writes: 2524 -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 5507 OS WAIT ARRAY INFO: signal count 5827 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 1901, rounds 3750, OS waits 1849 RW-excl spins 108, rounds 1784098, OS waits 8 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 0.00 mutex, 1.97 RW-shared, 16519.43 RW-excl, 0.00 RW-sx TRANSACTIONS Trx id counter 1826118 Purge done for trx's n:o 1826115 undo n:o 0 state: running but idle History list length 1793 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421804350357096, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421804350355272, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 1826117, ACTIVE (PREPARED) 0 sec mysql tables in use 1, locked 1 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 MySQL thread id 12, OS thread handle 140329332676352, query id 123121 localhost 127.0.0.1 dahls query end UPDATE washperiod SET washperiodstatus_id=1 WHERE washperiod_id=127687474 FILE I/O I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 104317 OS file reads, 6881 OS file writes, 6297 OS fsyncs 164.08 reads/s, 16384 avg bytes/read, 21.90 writes/s, 21.90 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf: size 1, free list len 0, seg size 2, 38 merges merged operations: insert 130, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 53118839, node heap has 400 buffer(s) 783.22 hash searches/s, 854.11 non-hash searches/s --- LOG --- Log sequence number 137891623643 Log flushed up to 137891623643 Pages flushed up to 137884471017 Last checkpoint at 137884471017 0 pending log flushes, 0 pending chkp writes 4586 log i/o's done, 21.90 log i/o's/second -- BUFFER POOL AND MEMORY -- Total large memory allocated 27485798400 Dictionary memory allocated 1342452 Buffer pool size 1638225 Free buffers 1533395 Database pages 104430 Old database pages 38704 Modified db pages 2007 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 14, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 104305, created 125, written 1448 164.08 reads/s, 0.30 creates/s, 0.00 writes/s Buffer pool hit rate 992 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 50.39/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 104430, unzip_LRU len: 0 I/O sum[0]:cur[8], unzip sum[0]:cur[0] -- INDIVIDUAL BUFFER POOL INFO -- ---BUFFER POOL 0 Buffer pool size 204775 Free buffers 188541 Database pages 16183 Old database pages 5993 Modified db pages 587 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 2, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 16136, created 47, written 257 18.00 reads/s, 0.20 creates/s, 0.00 writes/s Buffer pool hit rate 996 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 7.50/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 16183, unzip_LRU len: 0 I/O sum[0]:cur[1], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 204775 Free buffers 192460 Database pages 12269 Old database pages 4548 Modified db pages 179 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 12269, created 0, written 153
Re: MySQL 5.7 Innodb performans issue
Hi Jørn, - The data collector system processing jobs, is it multi threaded? Sorry, forgot about that. No, it is not multi threaded. It is a PHP bases system using several script running sequently in an infinite loop. Each script taking care of part of the job of processing the data. Newer MySQL releases are often more scalable (= works better for multi-threaded applications). On a single threaded basis though, as functionality is added it is difficult to always keep the same performance numbers. When you mentioned that there was a regression in 5.6 as well, it might be interesting to compare to the most recent 5.6. There were some single-threaded regression fixes after the GA release. - Do you have a sample schema + set of queries we could look at? (We pay close attention to regressions.) This will be BIG, since it it so many different queries and tables. Not sure what you mean by We pay close attention to regressions”. Bugs that introduce a loss of performance have a higher priority factor applied to them. We are very interested to hear clear cases where an operation takes more time in MySQL 5.7 versus earlier releases. In terms of your configuration: I would usually recommend assuming the default values for some of the settings you’ve specified (table_open_cache, sort_buffer_size, thread_cache_size, innodb_log_buffer_size, innodb_thread_concurrency..). A 25G buffer pool on a 32G server with some of your other buffers being quite large is something you may need to look into too. I know, but sofare no swapping is taking place. The test server is used for testing this system only. And in 99% of the time, only one client is using the SQL server. This makes some sense based on your workload being single threaded as well. It looks from show engine innodb status that your server is just starting up, and caches are empty, so versus a 5.5 server that has been running for a while it will likely be slower. What you may be able to do to track a specific set of statements that take longer in 5.7, is convert a mysqlbinlog to sql (mysqlbinlog mybinlogfile output.sql) and replay it on a 5.5 and 5.7 server. If you can pair it down a little from there, then the output.sql file can be uploaded to bugs.mysql.com and evaluated. Thanks! - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
On Wednesday, May 20, 2015, Morgan Tocker wrote: Hi Jørn, Wagner’s point about SHOW ENGINE INNODB STATUS is a good one. A couple of other questions about your workload: - The data collector system processing jobs, is it multi threaded? Sorry, forgot about that. No, it is not multi threaded. It is a PHP bases system using several script running sequently in an infinite loop. Each script taking care of part of the job of processing the data. - Do you have a sample schema + set of queries we could look at? (We pay close attention to regressions.) This will be BIG, since it it so many different queries and tables. Not sure what you mean by We pay close attention to regressions. In terms of your configuration: I would usually recommend assuming the default values for some of the settings you’ve specified (table_open_cache, sort_buffer_size, thread_cache_size, innodb_log_buffer_size, innodb_thread_concurrency..). A 25G buffer pool on a 32G server with some of your other buffers being quite large is something you may need to look into too. I know, but sofare no swapping is taking place. The test server is used for testing this system only. And in 99% of the time, only one client is using the SQL server. -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
I'd like to add to the Morgan's note that if you want to restrict the number of transactions inside InnoDB kernel to 16, you need at least configure the tickets... = http://www.pythian.com/blog/once-again-about-innodb-concurrency-tickets/ BTW, leave it as its default, IMHO, innodb_thread_concurrency=0, is better... -- *Wagner Bianchi, +55.31.8654.9510* Oracle ACE Director https://apex.oracle.com/pls/otn/f?p=19297:4:105567988301604::NO:4:P4_ID:4541, MySQL Certified Professional Percona MySQL Forum http://www.percona.com/forums/ Community V.I.P. Email: m...@wagnerbianchi.com Skype: wbianchijr 2015-05-20 15:25 GMT-03:00 Morgan Tocker morgan.toc...@oracle.com: Hi Jørn, Wagner’s point about SHOW ENGINE INNODB STATUS is a good one. A couple of other questions about your workload: - The data collector system processing jobs, is it multi threaded? - Do you have a sample schema + set of queries we could look at? (We pay close attention to regressions.) In terms of your configuration: I would usually recommend assuming the default values for some of the settings you’ve specified (table_open_cache, sort_buffer_size, thread_cache_size, innodb_log_buffer_size, innodb_thread_concurrency..). A 25G buffer pool on a 32G server with some of your other buffers being quite large is something you may need to look into too. - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
Additionally, take a look on this http://wagnerbianchi.com/blog/?p=982 as you're running mysql on SSD -- *Wagner Bianchi, +55.31.8654.9510* Oracle ACE Director https://apex.oracle.com/pls/otn/f?p=19297:4:105567988301604::NO:4:P4_ID:4541, MySQL Certified Professional Percona MySQL Forum http://www.percona.com/forums/ Community V.I.P. Email: m...@wagnerbianchi.com Skype: wbianchijr 2015-05-20 15:15 GMT-03:00 wagnerbianchi.com m...@wagnerbianchi.com: Can you share the SHOW ENGINE INNODB STATUS\G ? -- *Wagner Bianchi, +55.31.8654.9510 %2B55.31.8654.9510* Oracle ACE Director https://apex.oracle.com/pls/otn/f?p=19297:4:105567988301604::NO:4:P4_ID:4541, MySQL Certified Professional Percona MySQL Forum http://www.percona.com/forums/ Community V.I.P. Email: m...@wagnerbianchi.com Skype: wbianchijr 2015-05-20 15:07 GMT-03:00 Jørn Dahl-Stamnes sq...@dahl-stamnes.net: Hello (again I must say). Over a year ago I experienced a severe drop in the MySQL Innodb performance after ugrading to MySQL 5.6. I did not found any solution to that so I downgraded back to 5.5.33 and lived with in until recently. After a system disk crash I replaced the system disk with an identical disk, upgraded the test server to Fedora Core 21 and installed MySQL 5.7.7 by downloading RPM files. Now I'm facing the same problem with poor Innodb performance. How have I messured the performance? I got a data collector system that get new data from mails (stored as files). The system read the files, store data in the database and then prerform a lot of calulcations on the data. The performance is messured by how many jobs the system can process when putting months of mails into a queue at once. While I was using 5.5.53 it processed about 2000 jobs per minute with a peak up to 2500. Each time I test the system I use an initial database and the same mail files, so the input is always the same. With 5.7.7 the performance has dropped by a factor 4 - about 500-600 jobs per minute. I have played with the settings in my.cnf file but nothing seem to have any influence on the performance. So I'm using more or less the same settings for both version. Another thing I have noticed is that when running the test with MySQL 5.5.33, the hard disk LED was mostly dark. But now, it is flashing like h**l. So it seems like the problem has to do with the disk activity. I would be very glad to get some feedback on this that would bring my performance back to normal :) About the server: System disk (where the binlogs are stored) is a regular magnetic disk. All Innodb files are stored on a SSD disk mounted with defaults, nouser_xattr,noatime,data=writeback,barrier=0 The system got 32 Gb memory and have an AMD 8-core CPU (AMD FX-8120). This is my.cnf I'm currently using: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock explicit_defaults_for_timestamp = TRUE # Logging slow_query_log_file = /var/log/mysql-slow.log slow_query_log = 1 long_query_time = 10 log_queries_not_using_indexes = OFF skip-external-locking key_buffer_size = 384M max_allowed_packet = 32M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 max_connections = 50 join_buffer_size = 64M # Replication Master Server (default) # binary logging is required for replication log-bin=/var/mysql/mysql-bin server-id = 1 binlog_format=mixed # Innodb settings. innodb_open_files = 2048 open_files_limit= 8096 innodb_data_home_dir= /data/mysql/data innodb_data_file_path = ibdata1:20G;ibdata2:20G;ibdata3:20G;ibdata4:20G:autoextend innodb_file_per_table = 0 innodb_autoextend_increment = 256 innodb_log_group_home_dir = /data/mysql/data innodb_buffer_pool_size = 25G innodb_log_file_size= 300M innodb_log_files_in_group = 2 innodb_log_buffer_size = 128M innodb_flush_log_at_trx_commit = 1 innodb_support_xa = 0 innodb_flush_method = O_DIRECT innodb_lock_wait_timeout= 50 innodb_thread_concurrency = 16 innodb_fast_shutdown= 0 [mysql] no-auto-rehash -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
Hi Jørn, Wagner’s point about SHOW ENGINE INNODB STATUS is a good one. A couple of other questions about your workload: - The data collector system processing jobs, is it multi threaded? - Do you have a sample schema + set of queries we could look at? (We pay close attention to regressions.) In terms of your configuration: I would usually recommend assuming the default values for some of the settings you’ve specified (table_open_cache, sort_buffer_size, thread_cache_size, innodb_log_buffer_size, innodb_thread_concurrency..). A 25G buffer pool on a 32G server with some of your other buffers being quite large is something you may need to look into too. - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
On Wednesday, May 20, 2015, wagnerbianchi.com wrote: I'd like to add to the Morgan's note that if you want to restrict the number of transactions inside InnoDB kernel to 16, you need at least configure the tickets... = http://www.pythian.com/blog/once-again-about-innodb-concurrency-tickets/ BTW, leave it as its default, IMHO, innodb_thread_concurrency=0, is better... Changed it to 0 and restarted MySQL. Now the test will be running all night long... (a full test took 3+ weeks with 5.5 and I expect 12-14 weeks with the speed of 5.7). But as I wrote before, it seems like 5.7 causing a much higher disk load than 5.5 based on how the HD LED is flashing. It could be off for a long time (a second or even more) with 5.5 while now it is hardly off at all. On Wednesday, May 20, 2015, Morgan Tocker morgan.toc...@oracle.com wrote: It looks from show engine innodb status that your server is just starting up, and caches are empty, so versus a 5.5 server that has been running for a while it will likely be slower. It this is the case I have never experienced this behavior. The PHP scripts write output about everything that is happening. And the speed of the output tells me how fast things goes. With 5.5 the speed was much higher right from the start (after a reboot and starting up the test). -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to change character sets in InnoDB as fast as possible
Yes, normally convert from latin1 to binary, and from binary to utf8 (which would also be the recipe to convert actual utf8 data which accidentally ended up in latin1 columns to the proper definition without changing the content). I would not know why that would take overly long for you. An alternative if you have the disk space, and the table has no triggers, is using a tool like pt-online-schema change to avoid locking during the change (it creates a shadow table with the proper data and renames the tables once it is done). I am looking for a way to convert about 40GB of InnoDB tables from latin1 character set to utf8. As true conversion will take ages, I had the idea of just changing the character sets (and preferably collation, too) of the tables without actually converting the data. Conversion could be done manually later. From my side it is ok that the data is wrongly encoded in the tables for the time of manual conversion. The goal is to have the tables up and running as soon as possible. I tried to set the character sets with CHANGE, MODIFY, ALTER, but mysql always seems to convert the tables’ data. I read that character sets will be ignored if you convert to binary character sets, but still changing to this set takes ages. So my question is: Is there a way of changing an InnoDB table's character set and collation without letting mysql converting the data? What is the fastest way of changing the character sets, regardless of the method? If converting the data is the only way to go, I thought about converting several tables at the same time, but still this would require a down time of several hours, which is basically unacceptable. Is there no faster way to go? Thanks a lot for your ideas!
How to change character sets in InnoDB as fast as possible
Hi there, I am looking for a way to convert about 40GB of InnoDB tables from latin1 character set to utf8. As true conversion will take ages, I had the idea of just changing the character sets (and preferably collation, too) of the tables without actually converting the data. Conversion could be done manually later. From my side it is ok that the data is wrongly encoded in the tables for the time of manual conversion. The goal is to have the tables up and running as soon as possible. I tried to set the character sets with CHANGE, MODIFY, ALTER, but mysql always seems to convert the tables’ data. I read that character sets will be ignored if you convert to binary character sets, but still changing to this set takes ages. So my question is: Is there a way of changing an InnoDB table's character set and collation without letting mysql converting the data? What is the fastest way of changing the character sets, regardless of the method? If converting the data is the only way to go, I thought about converting several tables at the same time, but still this would require a down time of several hours, which is basically unacceptable. Is there no faster way to go? Thanks a lot for your ideas! Best, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Strange observation in OPTIMIZE TABLE command in InnoDB
Are both instances running the same MySQL version and release? Are they MASTER and SLAVE, actively replicating? Are the InnoDB configurations currently running on both servers the same? -- *WB* 2014-09-06 6:00 GMT-03:00 Ajay Garg ajaygargn...@gmail.com: Sorry, forgot to specify the engine. The table runs on InnoDB backend. Also, changed the subject to be more specific. On Sat, Sep 6, 2014 at 2:26 PM, Ajay Garg ajaygargn...@gmail.com wrote: Hi all. We are facing a very strange scenario. We have two mysql-instances running on the same machine, and they had been running functionally fine since about 6 years or so (catering to millions of records per day). However, since last few days, we were experiencing some elongated slowness on both the instances. So, we decided to OPTIMIZE TABLE slow_table on both the instances. We first ran the command on one instance. That speeded up things massively (select count(*) that was earlier taking 45 minutes was now running in less than 3 minutes). We then ran the command on the second instance. However, that seemed to have no effect. We ran the command again (on the same instance); again it had no effect. What could be the reason of this strange behavior? Both the instances run under fairly the same load, and both instances are mounted on the same partition (obviously, all the directories are different). Hoping for some light on this strange issue. Thanks and Regards, Ajay -- Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Strange observation in OPTIMIZE TABLE command in InnoDB
Sorry, forgot to specify the engine. The table runs on InnoDB backend. Also, changed the subject to be more specific. On Sat, Sep 6, 2014 at 2:26 PM, Ajay Garg ajaygargn...@gmail.com wrote: Hi all. We are facing a very strange scenario. We have two mysql-instances running on the same machine, and they had been running functionally fine since about 6 years or so (catering to millions of records per day). However, since last few days, we were experiencing some elongated slowness on both the instances. So, we decided to OPTIMIZE TABLE slow_table on both the instances. We first ran the command on one instance. That speeded up things massively (select count(*) that was earlier taking 45 minutes was now running in less than 3 minutes). We then ran the command on the second instance. However, that seemed to have no effect. We ran the command again (on the same instance); again it had no effect. What could be the reason of this strange behavior? Both the instances run under fairly the same load, and both instances are mounted on the same partition (obviously, all the directories are different). Hoping for some light on this strange issue. Thanks and Regards, Ajay -- Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: fragmentation in innodb index
Thanks to all, Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Trainer and Database Security Specialist On Fri, Aug 29, 2014 at 11:59 AM, Hartmut Holzgraefe hart...@skysql.com wrote: On 08/29/2014 08:15 AM, geetanjali mehra wrote: But after doing *optimize table*, the value inside data_free is still the same. If there is no change in the value of data_free, then what *optimize table* does here? Without seeing actual values I can only give an educated guess: * If you are not using innodb_file_per_table then data_free is for the whole table space, not just the individual table you're looking at. Even if that table was fragmented before running OPTIMIZE the overall unallocated storage space within the table space won't change much ... * If you have innodb_file_per_table=1 and you are seeing data_free values round about 4MB what you're seeing is simply pre-allocated space. See also the last paragraph on http://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html File-per-table tablespace files are auto-extending regardless of the value of innodb_autoextend_increment. The initial extensions are by small amounts, after which extensions occur in increments of 4MB. So unless you have innodb_file_per_table set and see data_free values substantially larger than 4MB there's nothing to worry about ... -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) SkySQL - The MariaDB Company | http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: fragmentation in innodb index
Could you please answer these questions also. What does data_free field from SHOW TABLE STATUS shows? When should we run optimize table for innodb tables? I read various blogs. They said data_free shows free space inside the innodb tables. But after doing *optimize table*, the value inside data_free is still the same. If there is no change in the value of data_free, then what *optimize table* does here? Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Trainer and Database Security Specialist On Thu, Aug 28, 2014 at 2:21 AM, shawn l.green shawn.l.gr...@oracle.com wrote: Hello Geetanjali, On 8/26/2014 1:11 AM, geetanjali mehra wrote: Hello to all, I want to know whether my innodb index is fragemented. Is it possible to know? Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Trainer and Database Security Specialist Just like every other piece of data stored in an InnoDB tablespace, the index data is stored in pages. At maximum capacity, 15KB of the 16KB assigned to each page can consist of data. At worst, about half of a 16K page will contain data. This is because each page is one leaf in a BTREE structure. If you add data to a page and you would exceed that 15K limit, we would need to split that page. That means that each page (the existing page and the one we just created) will now have a near-equal share of the data that was on the original page. That empty space is reused as much as possible to avoid another page split. If removing data from a table makes it possible to combine two adjacent leaves in the B-tree, we will. The page that once held the extra information is marked as 'available' and it can be filled with more index information later or filled with actual table data. A page is a page is a page. InnoDB decides what goes on a page. So... Is an index ever fragmented? No (depending on your definition of fragmentation) Will there ever be some free space within the index tree? Always. Can index pages be scattered (non-contiguous) within a tablespace file? Yes. Will rebuilding a table ensure that the index pages are made contiguous? No. Do these same answers apply to the actual data stored on a table? Yes. http://dev.mysql.com/doc/refman/5.6/en/innodb-file-defragmenting.html http://dev.mysql.com/doc/refman/5.6/en/innodb-file-space.html Does that help? -- 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: fragmentation in innodb index
On 08/29/2014 08:15 AM, geetanjali mehra wrote: But after doing *optimize table*, the value inside data_free is still the same. If there is no change in the value of data_free, then what *optimize table* does here? Without seeing actual values I can only give an educated guess: * If you are not using innodb_file_per_table then data_free is for the whole table space, not just the individual table you're looking at. Even if that table was fragmented before running OPTIMIZE the overall unallocated storage space within the table space won't change much ... * If you have innodb_file_per_table=1 and you are seeing data_free values round about 4MB what you're seeing is simply pre-allocated space. See also the last paragraph on http://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html File-per-table tablespace files are auto-extending regardless of the value of innodb_autoextend_increment. The initial extensions are by small amounts, after which extensions occur in increments of 4MB. So unless you have innodb_file_per_table set and see data_free values substantially larger than 4MB there's nothing to worry about ... -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) SkySQL - The MariaDB Company | http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: fragmentation in innodb index
Senior Oracle and MySQL DBA Corporate Trainer and Database Security Am I the only one worried about that line, then? -- 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: fragmentation in innodb index
On 8/29/2014 5:51 AM, Johan De Meersman wrote: Senior Oracle and MySQL DBA Corporate Trainer and Database Security Am I the only one worried about that line, then? yes. I welcome help from anyone willing. Expertise and willingness both are important. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: fragmentation in innodb index
Hello Geetanjali, On 8/26/2014 1:11 AM, geetanjali mehra wrote: Hello to all, I want to know whether my innodb index is fragemented. Is it possible to know? Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Trainer and Database Security Specialist Just like every other piece of data stored in an InnoDB tablespace, the index data is stored in pages. At maximum capacity, 15KB of the 16KB assigned to each page can consist of data. At worst, about half of a 16K page will contain data. This is because each page is one leaf in a BTREE structure. If you add data to a page and you would exceed that 15K limit, we would need to split that page. That means that each page (the existing page and the one we just created) will now have a near-equal share of the data that was on the original page. That empty space is reused as much as possible to avoid another page split. If removing data from a table makes it possible to combine two adjacent leaves in the B-tree, we will. The page that once held the extra information is marked as 'available' and it can be filled with more index information later or filled with actual table data. A page is a page is a page. InnoDB decides what goes on a page. So... Is an index ever fragmented? No (depending on your definition of fragmentation) Will there ever be some free space within the index tree? Always. Can index pages be scattered (non-contiguous) within a tablespace file? Yes. Will rebuilding a table ensure that the index pages are made contiguous? No. Do these same answers apply to the actual data stored on a table? Yes. http://dev.mysql.com/doc/refman/5.6/en/innodb-file-defragmenting.html http://dev.mysql.com/doc/refman/5.6/en/innodb-file-space.html Does that help? -- 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
fragmentation in innodb index
Hello to all, I want to know whether my innodb index is fragemented. Is it possible to know? Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Trainer and Database Security Specialist
backup of databases which have a mix of MyISAM- and InnoDB-tables
Hi, i've been already reading the documentation the whole day, but still confused and unsure what to do. We have two databases which are important for our work. So both are stored hourly. Now I recognized that each database has a mixture of MyISAM- and InnoDB-tables. A backup of this mix does not seem to be easy. Until now it was dumped using mysqldump --opt -u root --databases mausdb What I understand until now is that --opt is not necessary because it is default. It includes, among others, --lock-tables which is senseful for saving MyISAM-tables. For InnoDB-tables --single-transaction is useful. But both are mutually exclusive (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_single-transaction ). The dump of both take about 10 seconds. If the db is locked for that period I can live with. When I use --single-transaction only the InnoDB-tables are consistent. Using --lock-tables the MyISAM-tables are stored consistently. What is about --lock-tables in conjunction with InnoDB-tables ? Are they stored consistently ? Are they locked during the dumping ? As I said, I could live with a small lock period ( 30 sec). Would --lock-all-tables be better ? Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables (from the manpage). I can live with a global read lock for the duration of the whole dump. --lock-tables causes any pending transactions to be committed implicitly (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_single-transaction ). Is that a problem for the InnoDB tables ? Our system is: mysql-5.0.26-12.29.1 on a SLES 10 SP4 64 bit host. Bernd -- Bernd Lentes Systemadministration Institut für Entwicklungsgenetik Gebäude 35.34 - Raum 208 HelmholtzZentrum münchen bernd.len...@helmholtz-muenchen.de phone: +49 89 3187 1241 fax: +49 89 3187 2294 http://www.helmholtz-muenchen.de/idg Die Freiheit wird nicht durch weniger Freiheit verteidigt Helmholtz Zentrum München Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH) Ingolstädter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDir´in Bärbel Brumme-Bothe Geschäftsführer: Prof. Dr. Günther Wess, Dr. Nikolaus Blum, Dr. Alfons Enhsen Registergericht: Amtsgericht München HRB 6466 USt-IdNr: DE 129521671 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: backup of databases which have a mix of MyISAM- and InnoDB-tables
Am 22.08.2014 um 19:40 schrieb Lentes, Bernd: i've been already reading the documentation the whole day, but still confused and unsure what to do. We have two databases which are important for our work. So both are stored hourly. Now I recognized that each database has a mixture of MyISAM- and InnoDB-tables. A backup of this mix does not seem to be easy. Until now it was dumped using mysqldump --opt -u root --databases mausdb What I understand until now is that --opt is not necessary because it is default. It includes, among others, --lock-tables which is senseful for saving MyISAM-tables. For InnoDB-tables --single-transaction is useful. But both are mutually exclusive (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_single-transaction ). The dump of both take about 10 seconds. If the db is locked for that period I can live with. When I use --single-transaction only the InnoDB-tables are consistent. Using --lock-tables the MyISAM-tables are stored consistently. What is about --lock-tables in conjunction with InnoDB-tables ? Are they stored consistently ? Are they locked during the dumping ? As I said, I could live with a small lock period ( 30 sec). Would --lock-all-tables be better ? Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables (from the manpage). I can live with a global read lock for the duration of the whole dump. --lock-tables causes any pending transactions to be committed implicitly (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_single-transaction ). Is that a problem for the InnoDB tables ? Our system is: mysql-5.0.26-12.29.1 on a SLES 10 SP4 64 bit host why that complex? just setup replication because you have a lot of benefits: * in case your master crashs and the FS got damaged you have a real-time backup * for backups you can stop the slave, tar the whole datadir and start the slave * after it is restarted it pulls any change happened on the master due backup * the backup is likely smaller than verbose sql dumps * you do not need to care about table types and what not else signature.asc Description: OpenPGP digital signature
Re: backup of databases which have a mix of MyISAM- and InnoDB-tables
XTrabackup can handle both InnoDB and MyISAM in a consistent way while minimizing lock time on MyISAM tables ... http://www.percona.com/doc/percona-xtrabackup/2.1/ -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) SkySQL - The MariaDB Company | http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL InnoDB table row access
Hi,
I want to access data from an InnoDB table. I know that I can do this using
the corresponding handler and ha_rnd_next() or ha_index_next().
My problem is that the original MySQL code is outperforming my
implementation even on simple projection queries, even though I use the same
functions.
The entry point for my own execution is in the sql_parce.cc file in the
function int mysql_execute_command(THD *thd).
~line 2600 res= execute_sqlcom_select(thd, all_tables);
database: http://dev.mysql.com/doc/employee/en/index.html
query: select title from titles;
my code:
while (!tables-table-file-ha_rnd_next(tables-table-record[0]))
{
result-send_data(thd-lex-select_lex.item_list);
}
handler-ha_index_or_rnd_end();
return;
result is of the type select_send
To evaluate the query, using this simple loop, takes around 20 percent
longer than the original MySQL code. I have used a debugger to step through
the code, but the code being executed
seems to be the same. That's why I think MySQL is doing some kind of
optimization on the table but I can't figure out where. I have also tried to
let my code run later in the evaluation process
of MySQL but the result stays the same.
Best regards,
Tobias Krueger
Re: Optimizing InnoDB tables
Hi Johan, Thanks for your reply. Theorically the fragmented tables not offer the best performance to the InnoDB engine, that's correct or not? I don't know if is a problem or not, is a doubt/question for me. I'm not sure if is an atypical behaviour. Thanks in advance. Regards, Antonio.
Re: Optimizing InnoDB tables
*please* don't use reply-all on mailing-lists the list by definition distributes your message Am 30.06.2014 13:14, schrieb Antonio Fernández Pérez: Thanks for your reply. Theorically the fragmented tables not offer the best performance to the InnoDB engine, that's correct or not? practically it don't matter because the hot data should anways be in innodb_buffer_pool and so in memory and the fragmentation don't really matter as long it is not extremely you just can't have always unfragmented data because that would mean the must be space reserved left and right to fill growing data there how much space will you reserve and how will the holes impact performance if it comes to read data at startup anyways: a state of no single fragmentation is not possible and seeking for a solution because some tool displays data without any emotion is a fool with a tool still is a fool signature.asc Description: OpenPGP digital signature
Re: Optimizing InnoDB tables
Hi Andre, Thanks for your reply. I have checked the link and my configuration. Innodb_file_per_table is enabled and in data directory appears a set of files by each table. Any ideas? Thanks in advance. Regards, Antonio.
Re: Optimizing InnoDB tables
Am 27.06.2014 09:48, schrieb Antonio Fernández Pérez: Thanks for your reply. I have checked the link and my configuration. Innodb_file_per_table is enabled and in data directory appears a set of files by each table. Any ideas? ideas for what? * which files don't get shrinked (ls -lha) * which evidence do you have that they should * show create table * what *exactly* do you enter in your myscl client signature.asc Description: OpenPGP digital signature
Re: Optimizing InnoDB tables
Hi Reindl, Thanks for your attention. Following the previous mail, I have checked my MySQL's configuration and innodb_file_per_table is enabled so, I think that this parameter not affects directly to fragmented tables in InnoDB (In this case). I would like to know, if is possible, why after execute an analyze table command on some fragmented table, after that, appears fragmented again. Regards, Antonio.
Re: Optimizing InnoDB tables
Hello Antonio, On 6/27/2014 9:31 AM, Antonio Fernández Pérez wrote: Hi Reindl, Thanks for your attention. Following the previous mail, I have checked my MySQL's configuration and innodb_file_per_table is enabled so, I think that this parameter not affects directly to fragmented tables in InnoDB (In this case). I would like to know, if is possible, why after execute an analyze table command on some fragmented table, after that, appears fragmented again. Regards, Antonio. InnoDB operates by storing multiple rows on pages. Each page is 16K. Of that 1K is reserved for metadata (a tiny index showing where on a page each row sits, links to various other locations, checksums, ...) The remaining 15K can be used for your actual data. If you delete a row of data, that space on a page is made available but the page does not change size. It is always 16K. InnoDB stores data in the order of your PK. If you need to insert a new row between other rows on a 'full' page, then the page needs to split. This creates 2 new pages that are about 50% full. If two adjacent pages (A and B) become too 'empty' they can be combined into one page. This puts the data from both pages onto one of them (page A, for example). However page B remains empty and becomes available for any other purpose. Is that what you are calling 'fragmentation' ? -- 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: Optimizing InnoDB tables
- Original Message - From: Antonio Fernández Pérez antoniofernan...@fabergames.com Subject: Re: Optimizing InnoDB tables I would like to know, if is possible, why after execute an analyze table command on some fragmented table, after that, appears fragmented again. Simple question: why do you believe this is a problem? -- 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: Optimizing InnoDB tables
Hi again, I have enabled innodb_file_per_table (Its value is on). I don't have clear what I should to do ... Thanks in advance. Regards, Antonio.
Re: Optimizing InnoDB tables
- Original Message - From: Antonio Fernández Pérez antoniofernan...@fabergames.com Subject: Re: Optimizing InnoDB tables I have enabled innodb_file_per_table (Its value is on). I don't have clear what I should to do ... Then all new tables will be created in their own tablespace now. It's easy to convert an existing table, too, simply do alter table yourtable engine=innodb - but that will of course take a while on large tables. The problem, however, is that there is no way to shrink the main tablespace afterwards. Your tables will all be in their own space, but the ibdata1 will still be humoungous, even though it's close to empty. Don't just delete it, btw, as it still contains metadata. The only way to get rid of those, is to export ALL innodb tables, shut down mysqld, delete all innodb files (iblog0/1, ibdata1 etc, but also db/*.ibd and the associated db/*.frm files; then start the server (it'll recreate ibdata1 as specified in your my.cnf, so shrink there, too, if required) and then import the lot again. Note that, if you have the space, you don't *have* to do that - the huge ibdata1 file doesn't do any harm; but do consider that as your dataset grows over the years, it'll become more and more of a bother to actually do it. Make sure you have backups when attempting :-) -- 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: Optimizing InnoDB tables
Have a look at this: https://rtcamp.com/tutorials/mysql/enable-innodb-file-per-table/ -- Andre Matos andrema...@mineirinho.org On Jun 25, 2014, at 2:22 AM, Antonio Fernández Pérez antoniofernan...@fabergames.com wrote: Hi again, I have enabled innodb_file_per_table (Its value is on). I don't have clear what I should to do ... Thanks in advance. Regards, Antonio. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Optimizing InnoDB tables
Hi list, I was trying to optimize the InnoDB tables. I have executed the next query to detect what are the fragmented tables. SELECT TABLE_SCHEMA,TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA NOT IN (information_schema,mysql) AND Data_free 0 After that, I have seen that there are 49 fragmented tables. With one table, I have executed optimize table table_name; and analyze table table_name;. The result is the same, the table continuos fragmented. Any ideas? I have followed the mysqltuner recomendations ... Thanks in advance. Regards, Antonio.
Re: Optimizing InnoDB tables
Hi Antonio, como esta? What's the mysql version you're running? Have you tried to ALTER TABLE x ENGINE=InnoDB? -- WB, MySQL Oracle ACE Em 24/06/2014, às 08:03, Antonio Fernández Pérez antoniofernan...@fabergroup.es escreveu: Hi list, I was trying to optimize the InnoDB tables. I have executed the next query to detect what are the fragmented tables. SELECT TABLE_SCHEMA,TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA NOT IN (information_schema,mysql) AND Data_free 0 After that, I have seen that there are 49 fragmented tables. With one table, I have executed optimize table table_name; and analyze table table_name;. The result is the same, the table continuos fragmented. Any ideas? I have followed the mysqltuner recomendations ... Thanks in advance. Regards, Antonio. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Optimizing InnoDB tables
Hi Wagner, I'm running MySQL Percona Server 5.5.30 64Bits. No, I don't have tried to execute ALTER TABLE (Analyze with InnoDB tables do that, or not?). Thanks in advance. Regards, Antonio.
Re: Optimizing InnoDB tables
Hello Antonio, On 6/24/2014 7:03 AM, Antonio Fernández Pérez wrote: Hi list, I was trying to optimize the InnoDB tables. I have executed the next query to detect what are the fragmented tables. SELECT TABLE_SCHEMA,TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA NOT IN (information_schema,mysql) AND Data_free 0 After that, I have seen that there are 49 fragmented tables. With one table, I have executed optimize table table_name; and analyze table table_name;. The result is the same, the table continuos fragmented. Any ideas? I have followed the mysqltuner recomendations ... Thanks in advance. Regards, Antonio. It makes a huge difference if the tables you are trying to optimize have their own tablespace files or if they live inside the common tablespace. http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table -- 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: Optimizing InnoDB tables
Am 24.06.2014 21:07, schrieb shawn l.green: It makes a huge difference if the tables you are trying to optimize have their own tablespace files or if they live inside the common tablespace. http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table which is the most stupid default in case of innodb and only survivable without a lot of work for people who realize that *before* start operations and enable innodb_file_per_table from the very begin having defaults which can't be changed later without complete re-import of data and prevent from ever get disk space for long ago deleted data free is the most wrong thing a software developer can do signature.asc Description: OpenPGP digital signature
Re: Optimizing InnoDB tables
Hello Reindl, On 6/24/2014 3:29 PM, Reindl Harald wrote: Am 24.06.2014 21:07, schrieb shawn l.green: It makes a huge difference if the tables you are trying to optimize have their own tablespace files or if they live inside the common tablespace. http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table which is the most stupid default in case of innodb and only survivable without a lot of work for people who realize that *before* start operations and enable innodb_file_per_table from the very begin having defaults which can't be changed later without complete re-import of data and prevent from ever get disk space for long ago deleted data free is the most wrong thing a software developer can do The tables can be moved from the common tablespace into their own tablespace at any time after the option is enabled. The space they once occupied within the primary tablespace will remain and it will be marked as 'available' for any general purpose (such as the UNDO log) The only way to shrink the primary tablespace is, as you correctly described, through a dump/restore of your data. This process to resize the primary tablespace (such as to shrink it) must be followed precisely or problems will result. http://dev.mysql.com/doc/refman/5.6/en/innodb-data-log-reconfiguration.html -- 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: Big innodb tables, how can I work with them?
2014-05-15 14:26 GMT+02:00 Antonio Fernández Pérez antoniofernan...@fabergroup.es: Hi, I have in my server database some tables that are too much big and produce some slow query, even with correct indexes created. For my application, it's necessary to have all the data because we make an authentication process with RADIUS users (AAA protocol) to determine if one user can or not navigate in Internet (Depending on the time of all his sessions). So, with 8GB of data in one table, what are your advices to follow? Fragmentation and sharding discarted because we are working with disk arrays, so not apply. Another option is to delete rows, but in this case, I can't. For the other hand, maybe de only possible solution is increase the resources (RAM). Adding more RAM will only save you for a few weeks/months until the data isn't able to fit in memory any longer. You will face the same problem soon (if your data is and will be still growing). There will be a point where you just can't buy more and better hardware (actually you kinda can, but you will spend load of money and might end up with nice servers just doing nothing because they support more memory in their motherboard so you need to upgrade it too). You should give your application a thought and start considering noSQL/table sharding/partitioning/archiving. Maybe it is too late, but before needing another hardware upgrade, yo should've thought about a solution that would allow you keep growing without needing to spend all in hardware (unless you have unlimited money). Good luck! Manuel.
Re: Big innodb tables, how can I work with them?
- Original Message - From: Manuel Arostegui man...@tuenti.com Subject: Re: Big innodb tables, how can I work with them? noSQL/table sharding/partitioning/archiving. I keep wondering how people believe that NoSQL solutions magically don't need RAM to work. Nearly all of them slow down to a crawl, many even worse than an SQL database, as soon as the full or working set no longer fits in memory, too. Don't get me wrong - they have certain benefits and definite usecases, but it's time people stop presenting them as a magic bullet. They require understanding and work, just like any other technology. -- 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: Big innodb tables, how can I work with them?
2014-05-19 11:49 GMT+02:00 Johan De Meersman vegiv...@tuxera.be: - Original Message - From: Manuel Arostegui man...@tuenti.com Subject: Re: Big innodb tables, how can I work with them? noSQL/table sharding/partitioning/archiving. I keep wondering how people believe that NoSQL solutions magically don't need RAM to work. Nearly all of them slow down to a crawl, many even worse than an SQL database, as soon as the full or working set no longer fits in memory, too. Don't get me wrong - they have certain benefits and definite usecases, but it's time people stop presenting them as a magic bullet. They require understanding and work, just like any other technology. I was thinking about its distributed system as it might speed up reads :-) We do have a huge noSQL cluster here at work and it certainly needs lot of RAM. Manuel
Re: Big innodb tables, how can I work with them?
What kind of queries is this table serving? 8GB is not a huge amount of data at all and IMO it's not enough to warrant sharding. On Thu, May 15, 2014 at 1:26 PM, Antonio Fernández Pérez antoniofernan...@fabergroup.es wrote: Hi, I have in my server database some tables that are too much big and produce some slow query, even with correct indexes created. For my application, it's necessary to have all the data because we make an authentication process with RADIUS users (AAA protocol) to determine if one user can or not navigate in Internet (Depending on the time of all his sessions). So, with 8GB of data in one table, what are your advices to follow? Fragmentation and sharding discarted because we are working with disk arrays, so not apply. Another option is to delete rows, but in this case, I can't. For the other hand, maybe de only possible solution is increase the resources (RAM). Any ideas? Thanks in advance. Regards, Antonio.
Re: Big innodb tables, how can I work with them?
Hi, Thanks for your replies. In our case, we can't implement NOSQL solution. Thats requires modify/check all our application and all services (Including FreeRADIUS that I'm not sure if it's compatible). Andrew, I have heard about people that has a lot of data, more than me. I know that MySQL support this amount but in this case and thinking in the future, I have this problem with my architecture; how can I grow in database servers without delete rows in the tables. I have checked slow queries and now there aren't. These tables are serving queries from FreeRADIUS service. For example, SUMs, COUNTS, nomal SELECTs ... Always with a where condition. Excuse me, what is the meaning of IMO? Thanks. Regards, Antonio.
Big innodb tables, how can I work with them?
Hi, I have in my server database some tables that are too much big and produce some slow query, even with correct indexes created. For my application, it's necessary to have all the data because we make an authentication process with RADIUS users (AAA protocol) to determine if one user can or not navigate in Internet (Depending on the time of all his sessions). So, with 8GB of data in one table, what are your advices to follow? Fragmentation and sharding discarted because we are working with disk arrays, so not apply. Another option is to delete rows, but in this case, I can't. For the other hand, maybe de only possible solution is increase the resources (RAM). Any ideas? Thanks in advance. Regards, Antonio.
Re: Big innodb tables, how can I work with them?
Am 15.05.2014 14:26, schrieb Antonio Fernández Pérez: I have in my server database some tables that are too much big and produce some slow query, even with correct indexes created. For my application, it's necessary to have all the data because we make an authentication process with RADIUS users (AAA protocol) to determine if one user can or not navigate in Internet (Depending on the time of all his sessions). So, with 8GB of data in one table, what are your advices to follow? Fragmentation and sharding discarted because we are working with disk arrays, so not apply. Another option is to delete rows, but in this case, I can't. For the other hand, maybe de only possible solution is increase the resources (RAM) rule of thumbs is innodb_buffer_pool = database-size or at least as much RAM that frequently accessed data stays always in the pool signature.asc Description: OpenPGP digital signature
Re: SHOW CREATE TABLE suddenly slow on InnoDB?
Hey Brad. What version are you using? My immediate thought is to check if innodb_stats_on_metadata is off. If it is on, switch off and check your timings again. Regards On 17 Mar 2014 04:40, Brad Heller b...@cloudability.com wrote: Hey all, I'm trying to figure out how InnoDB executes a SHOW CREATE TABLE query so I can figure out what could possibly have made them suddenly slow down? mysql SHOW CREATE TABLE `my_table`; ... 1 row in set (37.48 sec) We tend to execute many of these statements concurrently, but it's never been a problem until recently. I upgraded the IO subsystem, and our statistics indicate that it's not maxing out IO (at least IOPS). This is problematic because the ORM we're using uses that to figure out the structure of our DB... *Brad Heller *| Director of Engineering | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability We're hiring! https://cloudability.com/jobs http://www.cloudability.com/jobs
Re: SHOW CREATE TABLE suddenly slow on InnoDB?
Hey Andrew, I'm on 5.5.27. Good thought. Just flipped that setting off and getting the same results. It pretty clearly seems to be InnoDB: If I create a HEAP table, I don't get this behavior. FWIW, I have (and always have had) innodb_file_per_table enabled, but my tablespace file is still gigantic (56GB)? *Brad Heller *| Director of Engineering | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability We're hiring! https://cloudability.com/jobshttp://www.cloudability.com/jobs On Mon, Mar 17, 2014 at 1:07 AM, Andrew Moore eroomy...@gmail.com wrote: Hey Brad. What version are you using? My immediate thought is to check if innodb_stats_on_metadata is off. If it is on, switch off and check your timings again. Regards On 17 Mar 2014 04:40, Brad Heller b...@cloudability.com wrote: Hey all, I'm trying to figure out how InnoDB executes a SHOW CREATE TABLE query so I can figure out what could possibly have made them suddenly slow down? mysql SHOW CREATE TABLE `my_table`; ... 1 row in set (37.48 sec) We tend to execute many of these statements concurrently, but it's never been a problem until recently. I upgraded the IO subsystem, and our statistics indicate that it's not maxing out IO (at least IOPS). This is problematic because the ORM we're using uses that to figure out the structure of our DB... *Brad Heller *| Director of Engineering | Cloudability.com | 541-231-1514| Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability We're hiring! https://cloudability.com/jobs http://www.cloudability.com/jobs
Re: SHOW CREATE TABLE suddenly slow on InnoDB?
Hi Brad, I'm trying to figure out how InnoDB executes a SHOW CREATE TABLE query so I can figure out what could possibly have made them suddenly slow down? mysql SHOW CREATE TABLE `my_table`; ... 1 row in set (37.48 sec) We tend to execute many of these statements concurrently, but it's never been a problem until recently. I upgraded the IO subsystem, and our statistics indicate that it's not maxing out IO (at least IOPS). This is problematic because the ORM we're using uses that to figure out the structure of our DB... I am going to guess that it could be contention trying to open the table. i.e. when you run into this issue and run SHOW PROCESSLIST, does it show any threads waiting in state “Opening tables”? (If you can paste a sanitized SHOW PROCESSLIST this helps a lot.) - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW CREATE TABLE suddenly slow on InnoDB?
Hey Morgan, That sounds right. Here's the process list (scrubbed) and the show engine innodb status. Notice that all of the SHOW CREATE TABLE aren't for hte same table, just got cleaned up that way. https://gist.github.com/bradhe/c9f00eaf93ac588b8339 We have the defaults for table_definition_cache and table_open_cache (400 each). *Brad Heller *| Director of Engineering | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability We're hiring! https://cloudability.com/jobshttp://www.cloudability.com/jobs On Mon, Mar 17, 2014 at 5:55 AM, Morgan Tocker morgan.toc...@oracle.comwrote: Hi Brad, I'm trying to figure out how InnoDB executes a SHOW CREATE TABLE query so I can figure out what could possibly have made them suddenly slow down? mysql SHOW CREATE TABLE `my_table`; ... 1 row in set (37.48 sec) We tend to execute many of these statements concurrently, but it's never been a problem until recently. I upgraded the IO subsystem, and our statistics indicate that it's not maxing out IO (at least IOPS). This is problematic because the ORM we're using uses that to figure out the structure of our DB... I am going to guess that it could be contention trying to open the table. i.e. when you run into this issue and run SHOW PROCESSLIST, does it show any threads waiting in state Opening tables? (If you can paste a sanitized SHOW PROCESSLIST this helps a lot.) - Morgan
Re: SHOW CREATE TABLE suddenly slow on InnoDB?
Hi Brad, That sounds right. Here's the process list (scrubbed) and the show engine innodb status. Notice that all of the SHOW CREATE TABLE aren't for hte same table, just got cleaned up that way. It shouldn't matter if they are for the same or different - in 5.5 there is one table open cache “instance” - so only one person can be opening or closing tables at a time. In 5.6 this is configurable to reduce contention: http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_open_cache_instances https://gist.github.com/bradhe/c9f00eaf93ac588b8339 We have the defaults for table_definition_cache and table_open_cache (400 each). I am going to guess and say that you may have a sharded environment with a large number of tables? Another solution that may work, is to increase these caches. In most cases it will work fine, but MPB has also blogged about the exception where you can get negative scalability (so many cache misses the cache can’t work effectively): http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/ I’m not sure how up-to-date the edge case issue is. But hopefully this gives you some starting points. (Others, feel free to chime in!) - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW CREATE TABLE suddenly slow on InnoDB?
Hey Morgan, We actually only have about 60 tables in that database. I've tried increasing the cache and open tables limits and get the same behavior. mysql select @@table_definition_cache, @@table_open_cache, @@innodb_file_per_table, @@innodb_open_files; +--++-+-+ | @@table_definition_cache | @@table_open_cache | @@innodb_file_per_table | @@innodb_open_files | +--++-+-+ | 4096 | 3000 | 1 | 300 | +--++-+-+ 1 row in set (0.10 sec) A few other tests I've tried: 1. Stand up a new machine, dump just the schema in to it, and run the test. Performs flawlessly, so it's probably just this machine/snapshot. 2. Stand up a snapshot of my existing machine, truncate the tables, optimize the truncated tables, and run the test. I get the bad behavior! Correct me if I'm wrong but it'd appear that there's just something fundamentally broken this machines' InnoDB ibdata file/data dictionary? All the contention comes out of the dictionary, but I'd expect the optimize to re-write the dictionary entries... *Brad Heller *| Director of Engineering | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability We're hiring! https://cloudability.com/jobshttp://www.cloudability.com/jobs On Mon, Mar 17, 2014 at 11:55 AM, Morgan Tocker morgan.toc...@oracle.comwrote: Hi Brad, That sounds right. Here's the process list (scrubbed) and the show engine innodb status. Notice that all of the SHOW CREATE TABLE aren't for hte same table, just got cleaned up that way. It shouldn't matter if they are for the same or different - in 5.5 there is one table open cache instance - so only one person can be opening or closing tables at a time. In 5.6 this is configurable to reduce contention: http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_open_cache_instances https://gist.github.com/bradhe/c9f00eaf93ac588b8339 We have the defaults for table_definition_cache and table_open_cache (400 each). I am going to guess and say that you may have a sharded environment with a large number of tables? Another solution that may work, is to increase these caches. In most cases it will work fine, but MPB has also blogged about the exception where you can get negative scalability (so many cache misses the cache can't work effectively): http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/ I'm not sure how up-to-date the edge case issue is. But hopefully this gives you some starting points. (Others, feel free to chime in!) - Morgan
Re: SHOW CREATE TABLE suddenly slow on InnoDB?
Hello Brad, On 3/17/2014 5:50 PM, Brad Heller wrote: Hey Morgan, We actually only have about 60 tables in that database. I've tried increasing the cache and open tables limits and get the same behavior. mysql select @@table_definition_cache, @@table_open_cache, @@innodb_file_per_table, @@innodb_open_files; +--++-+-+ | @@table_definition_cache | @@table_open_cache | @@innodb_file_per_table | @@innodb_open_files | +--++-+-+ | 4096 | 3000 | 1 | 300 | +--++-+-+ 1 row in set (0.10 sec) A few other tests I've tried: 1. Stand up a new machine, dump just the schema in to it, and run the test. Performs flawlessly, so it's probably just this machine/snapshot. 2. Stand up a snapshot of my existing machine, truncate the tables, optimize the truncated tables, and run the test. I get the bad behavior! Correct me if I'm wrong but it'd appear that there's just something fundamentally broken this machines' InnoDB ibdata file/data dictionary? All the contention comes out of the dictionary, but I'd expect the optimize to re-write the dictionary entries... If it's localized to the one machine, have you looked to ensure that your physical disks are all working properly? In the past, sudden decreases in disk response (such as loading and reading the .frm file) have happened to others due to a RAID element being dead but appearing alive because your RAID controller is simulating the content of the dead disk. Other potential sources of interference are: * File lock contention if you are using a networked share (or potentially even the network itself). * File lock contention from an anti-virus (or other file scanning) program * Automated backup software (shadow copy). Yours, -- 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: SHOW CREATE TABLE suddenly slow on InnoDB?
Hi Brad, We actually only have about 60 tables in that database. I've tried increasing the cache and open tables limits and get the same behaviour. Hmm.. Shawn’s guesses are probably better than mine then. A few other tests I've tried: 1. Stand up a new machine, dump just the schema in to it, and run the test. Performs flawlessly, so it's probably just this machine/snapshot. 2. Stand up a snapshot of my existing machine, truncate the tables, optimize the truncated tables, and run the test. I get the bad behavior! Correct me if I'm wrong but it'd appear that there's just something fundamentally broken this machines' InnoDB ibdata file/data dictionary? All the contention comes out of the dictionary, but I'd expect the optimize to re-write the dictionary entries… InnoDB data dictionary is always stored in ibdata1 + there is MySQL data dictionary stored in .frm files. I can’t think of a specific reason why accessing it could be slower until after a dump and restore. I believe that Performance Schema could be helpful here. This is a view that will work with 5.5: https://github.com/MarkLeith/dbahelper/blob/master/views/p_s/io_global_by_wait_by_bytes.sql (PS is not enabled by default in 5.5, but file IO is instrumented.. you just need to turn it on.) - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
SHOW CREATE TABLE suddenly slow on InnoDB?
Hey all, I'm trying to figure out how InnoDB executes a SHOW CREATE TABLE query so I can figure out what could possibly have made them suddenly slow down? mysql SHOW CREATE TABLE `my_table`; ... 1 row in set (37.48 sec) We tend to execute many of these statements concurrently, but it's never been a problem until recently. I upgraded the IO subsystem, and our statistics indicate that it's not maxing out IO (at least IOPS). This is problematic because the ORM we're using uses that to figure out the structure of our DB... *Brad Heller *| Director of Engineering | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability We're hiring! https://cloudability.com/jobshttp://www.cloudability.com/jobs
InnoDB error 5
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? Thanks. -- 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
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
#1341 [Com]: InnoDB ibdata1 never shrinks after data is removed
For 10 YEARS we have been asking for a way to reclaim the ibdata files (or even .MYD files) and finally someone from mysql/oracle replied. It's not great news, but at least they acknowledge and give some explanations. -Original Message- From: Bug Database [mailto:do-not-re...@mysql.com] Sent: Tuesday, July 23, 2013 2:30 PM Subject: #1341 [Com]: InnoDB ibdata1 never shrinks after data is removed View this bug at: http://bugs.mysql.com/1341 Updated by: James Day Reported by: Scott Ellsworth Category: Server: InnoDB Severity: S4 (Feature request) Status: Verified Version: All versions OS: Any Defect Class: D5 (Feature request) [23 Jul 21:29] James Day Ten years is because we know it is desired but it is a hard problem. To free the space takes moving lots of internal pointers and data. That is almost as much work as alter table, but in theory it could be done gradually in the background. But would be very complicated with high chance for bugs and performance problems due to the locking needed. With 5.6 the best combination that is implemented so far is: 1. Innodb_file_per_table, on by default in 5.6. Prevents data from going to shared tablespace. 2. Undo logs in their own tablespace, see http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html Separate Tablespaces for InnoDB Undo Logs . This will prevent undo logs from causing the shared tablespace to grow, but there is still no way to shrink the undo logs. 3. Shared tablespace then contains data dictionary, change buffer and statistics, so is much less likely to grow to big sizes. 4. For some workloads the compressed tables feature will help to reduce data sizes and hence the times. Still some way to go on performance of this for OLTP but it's way better in 5.6 than 5.5, in part due to many improvements suggested by Facebook. Best used in general for tables that don't have the highest change rates but do have big column sizes that compress well. This definitely does not solve all problems: 5. The ibd files can have free space and the only way to release that is slow alter table or truncate. Slow for big tables, not practical on a live server. But is practical on a system that has failover capability. Can take a window of slow failover time and do it during this window with a known risk that there might be slower failover than usual. If you do this, drop all the non-unique secondary indexes then add them back later, the fast index recreate will save you time and rebuild the indexes with less free space inside the pages. 6. The portable tablespaces feature in 5.6 could then be used to load the tablespaces into a new copy of Mysql with small shared tablespace and redo log. This still requires downtime, so still requires a failover solution, but it's far faster for big tables than mysqldump and reload. 7. Dropping tables should be faster in 5.6 and 5.5.20 or later but it's still going to be slower using innodb_file_per_table than having the tables in the shared tablespace. More practical for big tables that aren't dropped often than for small tables that are very regularly dropped. For the big/infrequent combination, most people already use innodb_file_per_table. The work on this in bug http://bugs.mysql.com/bug.php?id=51325 helps a lot but there's still scope for more. So we know it's desirable, some major improvements that help the workarounds, but still no way to make it practical online. For now, failover based workarounds are still the way to go. Not ideal, but at least doable. James Day, MySQL Senior Principal Support Engineer, Oracle Earlier comments can be viewed at http://bugs.mysql.com/1341 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
InnoDB problem.
Greetings. I've restored an MySQL backup from our MySQL server into another server. The backup includes InnoDB tables. After the import, MySQL recognized the innodb tables fine but when I try to do a check table ir returns that the table doesn't exists. Permission and owner of the table files (.frm files) are ok, since it recognizes MyISAM tables (they have the same permission). Innodb engine is enabled.. Which can cause the tables to appears as non existent, as far as they do really exist? -- Att.* *** Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67
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: best way to copy a innodb table
Well, the easy way to chunk the inserts is by use of limit. Here is what I used for one of my projects: Insert ignore into t1 (f1, f2, f3) Select f1, f2, f3 from t2 limit 100, 100 Inserts 1M records at a time starting from 1M th record in t2 and you can keep incrementing this offset as you progress. This will help in monitoring the table inserts and at the same time move chunks of records from source table. Enjoy! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: best way to copy a innodb table
Another technique to avoid impact to the source database is to create your target as MyISAM, pump your records into that (no ACID overhead) and at the end : ALTER mytable engine=InnoDb The alter can take awhile but it will impose no strain on the source server at all. On Tue, Jul 2, 2013 at 3:48 AM, Arjun na...@yahoo.com wrote: Well, the easy way to chunk the inserts is by use of limit. Here is what I used for one of my projects: Insert ignore into t1 (f1, f2, f3) Select f1, f2, f3 from t2 limit 100, 100 Inserts 1M records at a time starting from 1M th record in t2 and you can keep incrementing this offset as you progress. This will help in monitoring the table inserts and at the same time move chunks of records from source table. Enjoy! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: best way to copy a innodb table
On Thu, Dec 1, 2011 at 5:54 PM, Miguel Angel Nieto miguel.ni...@percona.com wrote: You should check pt-archiver. +1. It works very well for this type of job. - Perrin
RE: best way to copy a innodb table
The particular example given here is unsafe and slow. * Without an ORDER BY, you are not guaranteed that the chunks will be distinct. * If there are any INSERTs/DELETEs between chunk copies, you will get dups/missing rows for two reasons: the inserted/deleted rows, and the OFFSET is not quite right. * OFFSET requires walking over the skipped rows. As you get farther into the table, this takes longer. That is, you have an ORDER(N**2) operation, not what could be ORDER(N). * If replication is involved, 1M rows is a lot -- there will be noticeable delays where other replication activity is stalled. If you have an AUTO_INCREMENT PRIMARY KEY, then using WHERE id 100 AND id = 200 is a better approach -- Order(N), and chunks guaranteed to be distinct. Still, it is not immune from INSERTs/DELETEs. Replication is fixed by decreasing chunk size (and by avoiding OFFSET). -Original Message- From: Arjun [mailto:na...@yahoo.com] Sent: Tuesday, July 02, 2013 12:48 AM To: mysql@lists.mysql.com Subject: Re: best way to copy a innodb table Well, the easy way to chunk the inserts is by use of limit. Here is what I used for one of my projects: Insert ignore into t1 (f1, f2, f3) Select f1, f2, f3 from t2 limit 100, 100 Inserts 1M records at a time starting from 1M th record in t2 and you can keep incrementing this offset as you progress. This will help in monitoring the table inserts and at the same time move chunks of records from source table. Enjoy! -- 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: SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
SHOW GLOBAL STATUS LIKE 'Innodb%'; Then do some math -- usually dividing by Uptime. That will give you some insight in how hard the I/O is working, and how full the buffer_pool is. -Original Message- From: Rafał Radecki [mailto:radecki.ra...@gmail.com] Sent: Friday, June 21, 2013 4:59 AM To: mysql@lists.mysql.com Subject: SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs? Hi All. I've searched but with no luck... what do exactly these variables mean: 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs ? I am wondering if my innodb_buffer_pool setting is not to low. Does 'file reads' show number of times innodb files have been read into memory from server's start? What about file writes/fsyncs? Best regards, Rafal Radecki. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: UPDATE_TIME for InnoDB in MySQL 5.7
Yeah, why not flush them to disk on a clean shutdown, and periodically before that? -Original Message- From: Dotan Cohen [mailto:dotanco...@gmail.com] Sent: Sunday, June 23, 2013 10:39 AM To: mysql. Subject: UPDATE_TIME for InnoDB in MySQL 5.7 The MySQL 5.7 changelog mentions: Beginning with MySQL 5.7.2, UPDATE_TIME displays a timestamp value for the last UPDATE, INSERT, or DELETE performed on InnoDB tables. Previously, UPDATE_TIME displayed a NULL value for InnoDB tables. For MVCC, the timestamp value reflects the COMMIT time, which is considered the last update time. Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache. This is great news! However, I would in fact need the UPDATE_TIME to persist across database server resets. Is this feature being considered or discussed? Where might I find it online? Thank you to the MySQL team and to Oracle for filling in InnoDB;s missing features! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
As I can see the changes in these values are use by percona cacti monitoring templates to graph InnoDB I/O. Can anyone answer the question finally? ;) 2013/6/21 Hartmut Holzgraefe hart...@skysql.com On 21.06.2013 13:59, Rafał Radecki wrote: Hi All. I've searched but with no luck... what do exactly these variables mean: 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs ? these are the total number of reads/writes/fsyncs (number of system calls actually?) since the server started (or maybe last FLUSH call?) and not very meaningful by themselves without knowing the time span it took to come up to those counter values. The per second values on the following line are much more interesting. http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/ has a pretty good description of the SHOW ENGINE INNODB STATUS output, even though it is not too detailed in this specific section. -- Hartmut Holzgraefe hart...@skysql.com Principal Support Engineer (EMEA) SkySQL AB - http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
Hello Rafał, On 6/24/2013 4:26 AM, Rafał Radecki wrote: As I can see the changes in these values are use by percona cacti monitoring templates to graph InnoDB I/O. Can anyone answer the question finally? ;) 2013/6/21 Hartmut Holzgraefe hart...@skysql.com On 21.06.2013 13:59, Rafał Radecki wrote: Hi All. I've searched but with no luck... what do exactly these variables mean: 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs ? these are the total number of reads/writes/fsyncs (number of system calls actually?) since the server started (or maybe last FLUSH call?) and not very meaningful by themselves without knowing the time span it took to come up to those counter values. The per second values on the following line are much more interesting. I don't understand how Hartmut's answer was insufficient. The InnoDB engine must get data from the disk (reads), send data to the disk (writes), and occasionally tell the operating system that it must flush its buffers to disk to ensure durability (fsync). Why are you so interested in these numbers? -- Shawn Green MySQL 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
UPDATE_TIME for InnoDB in MySQL 5.7
The MySQL 5.7 changelog mentions: Beginning with MySQL 5.7.2, UPDATE_TIME displays a timestamp value for the last UPDATE, INSERT, or DELETE performed on InnoDB tables. Previously, UPDATE_TIME displayed a NULL value for InnoDB tables. For MVCC, the timestamp value reflects the COMMIT time, which is considered the last update time. Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache. This is great news! However, I would in fact need the UPDATE_TIME to persist across database server resets. Is this feature being considered or discussed? Where might I find it online? Thank you to the MySQL team and to Oracle for filling in InnoDB;s missing features! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: help: innodb database cannot recover
boah you *must not* remove ibdata1 it contains the global tablespace even with file_per_table ib_logfile0 and ib_logfile1 may be removed, but make sure you have a as cinsistent as possible backup of the whole datadir I removed ib_logfile0 and ib_logfile1 and restarted mysql with innodb_force_recovery=1, mysql keeps crashing and restart: thd: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = (nil) thread_stack 0x3 /usr/libexec/mysqld(my_print_stacktrace+0x2e) [0x84bbbae] /usr/libexec/mysqld(handle_segfault+0x4bc) [0x81eca1c] [0xf57fe400] [0xf57fe416] /lib/libc.so.6(gsignal+0x51) [0x45a7bb71] /lib/libc.so.6(abort+0x17a) [0x45a7d44a] /usr/libexec/mysqld(fil_io+0x377) [0x83ba177] /usr/libexec/mysqld() [0x83a257b] /usr/libexec/mysqld(buf_read_page+0x282) [0x83a3132] /usr/libexec/mysqld(buf_page_get_gen+0x351) [0x839c111] /usr/libexec/mysqld(btr_cur_search_to_nth_level+0x3c1) [0x838ca31] /usr/libexec/mysqld(row_search_index_entry+0x79) [0x840d3c9] /usr/libexec/mysqld() [0x840bf97] /usr/libexec/mysqld(row_purge_step+0x574) [0x840d1e4] /usr/libexec/mysqld(que_run_threads+0x535) [0x83fa815] /usr/libexec/mysqld(trx_purge+0x365) [0x8427e25] /usr/libexec/mysqld(srv_master_thread+0x75b) [0x842009b] /lib/libpthread.so.0() [0x45bf09e9] /lib/libc.so.6(clone+0x5e) [0x45b2dc2e] 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. 130620 00:47:21 mysqld_safe Number of processes running now: 0 130620 00:47:21 mysqld_safe mysqld restarted InnoDB: Error: tablespace size stored in header is 456832 pages, but InnoDB: the sum of data file sizes is only 262080 pages InnoDB: Cannot start InnoDB. The tail of the system tablespace is InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an InnoDB: inappropriate way, removing ibdata files from there? InnoDB: You can set innodb_force_recovery=1 in my.cnf to force InnoDB: a startup if you are trying to recover a badly corrupt database. 130620 0:47:22 [ERROR] Plugin 'InnoDB' init function returned error. 130620 0:47:22 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. if I set innodb_force_recovery=4 to restart mysql and then run mysqldump, i got the following error: mysqldump: Got error: 2013: Lost connection to MySQL server during query when using LOCK TABLES it looks that all data from innodb is messed up and gone forever even though *.frm is still there. Peter
Re: help: innodb database cannot recover
As a matter of dumb questions, what versions are the old and new mysqld; and are they running on the same platform (OS, 32/64 bit, ...) ? - Original Message - From: Peter one2001...@yahoo.com To: Reindl Harald h.rei...@thelounge.net, mysql@lists.mysql.com Sent: Friday, 21 June, 2013 10:04:27 AM Subject: Re: help: innodb database cannot recover I removed ib_logfile0 and ib_logfile1 and restarted mysql with innodb_force_recovery=1, mysql keeps crashing and restart: thd: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = (nil) thread_stack 0x3 /usr/libexec/mysqld(my_print_stacktrace+0x2e) [0x84bbbae] /usr/libexec/mysqld(handle_segfault+0x4bc) [0x81eca1c] [0xf57fe400] [0xf57fe416] /lib/libc.so.6(gsignal+0x51) [0x45a7bb71] /lib/libc.so.6(abort+0x17a) [0x45a7d44a] /usr/libexec/mysqld(fil_io+0x377) [0x83ba177] /usr/libexec/mysqld() [0x83a257b] /usr/libexec/mysqld(buf_read_page+0x282) [0x83a3132] /usr/libexec/mysqld(buf_page_get_gen+0x351) [0x839c111] /usr/libexec/mysqld(btr_cur_search_to_nth_level+0x3c1) [0x838ca31] /usr/libexec/mysqld(row_search_index_entry+0x79) [0x840d3c9] /usr/libexec/mysqld() [0x840bf97] /usr/libexec/mysqld(row_purge_step+0x574) [0x840d1e4] /usr/libexec/mysqld(que_run_threads+0x535) [0x83fa815] /usr/libexec/mysqld(trx_purge+0x365) [0x8427e25] /usr/libexec/mysqld(srv_master_thread+0x75b) [0x842009b] /lib/libpthread.so.0() [0x45bf09e9] /lib/libc.so.6(clone+0x5e) [0x45b2dc2e] 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. 130620 00:47:21 mysqld_safe Number of processes running now: 0 130620 00:47:21 mysqld_safe mysqld restarted InnoDB: Error: tablespace size stored in header is 456832 pages, but InnoDB: the sum of data file sizes is only 262080 pages InnoDB: Cannot start InnoDB. The tail of the system tablespace is InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an InnoDB: inappropriate way, removing ibdata files from there? InnoDB: You can set innodb_force_recovery=1 in my.cnf to force InnoDB: a startup if you are trying to recover a badly corrupt database. 130620 0:47:22 [ERROR] Plugin 'InnoDB' init function returned error. 130620 0:47:22 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. if I set innodb_force_recovery=4 to restart mysql and then run mysqldump, i got the following error: mysqldump: Got error: 2013: Lost connection to MySQL server during query when using LOCK TABLES it looks that all data from innodb is messed up and gone forever even though *.frm is still there. Peter -- 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: SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
On 21.06.2013 13:59, Rafał Radecki wrote: Hi All. I've searched but with no luck... what do exactly these variables mean: 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs ? these are the total number of reads/writes/fsyncs (number of system calls actually?) since the server started (or maybe last FLUSH call?) and not very meaningful by themselves without knowing the time span it took to come up to those counter values. The per second values on the following line are much more interesting. http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/ has a pretty good description of the SHOW ENGINE INNODB STATUS output, even though it is not too detailed in this specific section. -- Hartmut Holzgraefe hart...@skysql.com Principal Support Engineer (EMEA) SkySQL AB - http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
Hi All. I've searched but with no luck... what do exactly these variables mean: 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs ? I am wondering if my innodb_buffer_pool setting is not to low. Does 'file reads' show number of times innodb files have been read into memory from server's start? What about file writes/fsyncs? Best regards, Rafal Radecki.
help: innodb database cannot recover
Hello, I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole database directory) from one crashed machine to another. I find that I cannot start database to get the database data any more. I tried innodb_force_recovery=1 or innodb_force_recovery=4, it doesn't help. Here is the error message I got: 130620 00:47:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql InnoDB: Error: tablespace size stored in header is 456832 pages, but InnoDB: the sum of data file sizes is only 262080 pages InnoDB: Cannot start InnoDB. The tail of the system tablespace is InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an InnoDB: inappropriate way, removing ibdata files from there? InnoDB: You can set innodb_force_recovery=1 in my.cnf to force InnoDB: a startup if you are trying to recover a badly corrupt database. 130620 0:47:08 [ERROR] Plugin 'InnoDB' init function returned error. 130620 0:47:08 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 130620 0:47:09 [Note] Event Scheduler: Loaded 0 events 130620 0:47:09 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.52' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution InnoDB: Error: trying to access page number 10929 in space 0, InnoDB: space name ./ibdata1, InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10. InnoDB: If you get this error at mysqld startup, please check that InnoDB: your my.cnf matches the ibdata files that you have in the InnoDB: MySQL server. 130620 0:47:09 InnoDB: Assertion failure in thread 2985618288 in file fil/fil0fil.c line 4123 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html InnoDB: about forcing recovery. 130620 0:47:09 - mysqld got signal 6 ; 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. is there a way to start the database again? Thanks for your help in advance. Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: help: innodb database cannot recover
Am 20.06.2013 10:11, schrieb Peter: 130620 00:47:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql InnoDB: Error: tablespace size stored in header is 456832 pages, but InnoDB: the sum of data file sizes is only 262080 pages InnoDB: Cannot start InnoDB. The tail of the system tablespace is InnoDB: missing is there a way to start the database again? Thanks for your help in advance restore your backups that is one reason why replication exists to have a slave which a) does not die with the master at a complete crash and b) have a machine where offline backups could be pulled from without any downtime signature.asc Description: OpenPGP digital signature
Re: help: innodb database cannot recover
2013/6/20 Peter one2001...@yahoo.com Hello, I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole database directory) from one crashed machine to another. I find that I cannot start database to get the database data any more. How did you copy the database? Manuel
Re: help: innodb database cannot recover
Am 20.06.2013 15:18, schrieb Peter: 2013/6/20 Peter one2001...@yahoo.com Hello, I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole database directory) from one crashed machine to another. I find that I cannot start database to get the database data any more. How did you copy the database? Manuel I copy the files ib_logfile0 ib_logfile1 ibdata1 into /var/lib/mysql in linux and the whole database directory my_database_name into /var/lib/mysql/my_database_name, the same location as previous machine /var/lib/mysql/my_database_name and your my.cnf? you hardly can move innodb around without the same settings signature.asc Description: OpenPGP digital signature
Re: help: innodb database cannot recover
2013/6/20 Peter one2001...@yahoo.com Hello, I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole database directory) from one crashed machine to another. I find that I cannot start database to get the database data any more. How did you copy the database? Manuel I copy the files ib_logfile0 ib_logfile1 ibdata1 into /var/lib/mysql in linux and the whole database directory my_database_name into /var/lib/mysql/my_database_name, the same location as previous machine /var/lib/mysql/my_database_name Thanks. Peter
Re: help: innodb database cannot recover
2013/6/20 Peter one2001...@yahoo.com 2013/6/20 Peter one2001...@yahoo.com Hello, I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole database directory) from one crashed machine to another. I find that I cannot start database to get the database data any more. How did you copy the database? Manuel I copy the files ib_logfile0 ib_logfile1 ibdata1 into /var/lib/mysql in linux and the whole database directory my_database_name into /var/lib/mysql/my_database_name, the same location as previous machine /var/lib/mysql/my_database_name Was MySQL daemon stopped in both machines? It must be stopped in both of them. If you cannot afford stopping your mysql, you might want to take a look at xtrabackup: http://www.percona.com/doc/percona-xtrabackup/2.1/ Manuel.
Re: help: innodb database cannot recover
Am 20.06.2013 15:18, schrieb Peter: I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole database directory) from one crashed machine to another. I find that I cannot start database to get the database data any more. How did you copy the database? Manuel I copy the files ib_logfile0 ib_logfile1 ibdata1 into /var/lib/mysql in linux and the whole database directory my_database_name into /var/lib/mysql/my_database_name, the same location as previous machine /var/lib/mysql/my_database_name and your my.cnf? you hardly can move innodb around without the same settings the my.cnf file is same as follows:
Re: help: innodb database cannot recover
Am 20.06.2013 15:18, schrieb Peter: I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole database directory) from one crashed machine to another. I find that I cannot start database to get the database data any more. How did you copy the database? Manuel I copy the files ib_logfile0 ib_logfile1 ibdata1 into /var/lib/mysql in linux and the whole database directory my_database_name into /var/lib/mysql/my_database_name, the same location as previous machine /var/lib/mysql/my_database_name and your my.cnf? you hardly can move innodb around without the same settings sorry for the previous post missing the file. the my.cnf file is same in both machines.as follows: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Peter
