… need to change MySQL’s innodb_log_file_size parameter (see How to calculate a good InnoDB log file size), you can…. At this point you can verify that InnoDB is working, and then you can delete the old log files. The typical error message you’ll see in the client when InnoDB has refused…
Post: Innodb undo segment size and transaction isolation
January 12, 2011 By 3 Comments
…it is easy to check: mysql> select * from information_schema.innodb_rseg; +———+———-+———-+———+————+———–+ | rseg_id | space_id | zip_size | page_no | max…record in undo space so row can be deleted on rollback, update and delete however need to put the old row …
Post: Reasons for run-away main Innodb Tablespace
June 10, 2010 By 2 Comments
… Server the following can be helpful: mysql> select * from innodb_rseg; +———+———-+———-+———+————+———–+ | rseg_id | space_id | zip_size | page_no | …. In many cases doing such processes in chunks, updating/deleting may be thousands of rows per transaction may be …
Post: Limiting InnoDB Data Dictionary
February 11, 2009 By 13 Comments
… you can download it there http://mysqlperformanceblog.com/files/patches/innodb_dict_size_limit_standalone.patch. Currently patch is on testing… we consider it as used, if not – we can delete it from InnoDB data dictionary. So there is the trick – if you…, memory consumed by data dictionary may exceed innodb_dict_size_limit, as we can’t delete any table entry from it. To…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… not used you can enable innodb_locks_unsafe_for_binlog option, which will relax locks which Innodb sets on statement execution, which… unsafe fore replication and point in time recovery, so use innodb_locks_unsafe_for_binlog option with caution. Note disabling binary… in SHOW INNODB STATUS: —TRANSACTION 0 42304626, ACTIVE 14 sec, process no 29895, OS thread id 2894768 updating or deleting mysql tables…
Post: Choosing proper innodb_log_file_size
July 3, 2006 By 12 Comments
… you’re doing significant amount of writes to Innodb tables decent size of innodb_log_file_size is important for MySQL Performance… on recovery and why large innodb_log_file_size slows down recovery. During startup after crash Innodb scans log files to find… “undo” phase is size your transactions appropriately – so updates/inserts/deletes can be sized to affect limited number of rows. Good…
Post: Lost innodb tables, xfs and binary grep
November 9, 2010 By 12 Comments
… a dedicated partition on XFS file system Server was running innodb_file_per_table There was a production master and two… their copy of the data The important tables were all InnoDB Having a backup, customer has first attempted to restore from… went through various data loss scenarios. One of them was deleted files on xfs – we all agreed on few things: recovering…
Post: Can Innodb Read-Ahead reduce read performance ?
June 26, 2007 By 2 Comments
… ————– ROW OPERATIONS ————– 8 queries inside InnoDB, 3 queries in queue 12 read views open inside InnoDB Main thread process no. 3956, id 1157658976, state: sleeping Number of rows inserted 60790248, updated 11571576, deleted 0, read…, 0.00 deletes/s, 24860.96 reads/s The stats are rather interesting. There are 8 queries inside Innodb and we can…
Post: Innodb locking and Foreign Keys
December 12, 2006 By 2 Comments
… talk how locks seems to work with foreign keys in Innodb. I’ve only done a quick check so could be missing some details. All Innodb Foreign Key related operations happen on data modification. So for… will try to delete row in PARENT table lockup in CHILD table will be performed with row lock performed. Innodb is smart…
Post: InnoDB thread concurrency
June 5, 2006 By 4 Comments
…; delete_row; At start of each of these methods InnoDB checks count of already entered threads, and if this count is over innodb…_enter_innodb–; ENTER; } retry: if (entered_thread < innodb_thread_concurrency) { entered_threads++; thread->n_tickets_to_enter_innodb = innodb_concurrency_tickets; ENTER; } if (innodb_thread…
