Re: purge thread
Hello Geetanjali, On 8/26/2014 1:16 AM, geetanjali mehra wrote: I want to understand how to tune innodb_max_purge_lag http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_max_purge_lag when history list length is high. Could anyone explain me. Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Trainer and Database Security Specialist This setting not related to the history value. It is there to help control the gap between these two values of the InnoDB status report Trx id counter 0 290328385 Purge done for trx's n:o 0 290315608 undo n:o 0 17 What is the current transaction compared what is the oldest transaction that still has aged copies of data left in the data area of the tablespace. The difference between those two values is the purge backlog 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: 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
Re: next-key lock
On 8/26/2014 1:12 AM, geetanjali mehra wrote: Hello to all, In repeatable read isolation level, when we issue: Select * from new where c1 between 12 and 17 for update; this range will be locked by innodb by using next-key locks. But, why is is preventing any other session to insert any value beyond that range; any value above the range and any value below the range. I am unable to understand this. I believe you are confusing gap locking (the space between the values) and next-key locking (the space after the range). http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html See also: http://dev.mysql.com/doc/refman/5.6/en/innodb-next-key-locking.html http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Trainer and Database Security Specialist 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
