Re: next-key lock
Geetanjali, There is a difference between next-key locking, gap locking and locking reads. Next-key locking and gap-locking are used with normal Selects statement in Innodb, whereas locking reads wont release a lock on the whole column until transaction completed, and not just selected values. May be you can try your example with SELECT... LOCK IN SHARE MODE; Cheers!!! Akshay Suryawanshi On Fri, Aug 29, 2014 at 11:22 AM, geetanjali mehra mailtogeetanj...@gmail.com wrote: Thanks for your reply. I read those docs. Still my doubt is at the same stage. Please clarify the same to me. Should not other sessions be allowed to insert the rows beyond that range.? As far as I understand, Innodb brought the concept of next-key locks so as to prevent phantom problem. So, it is clear to me that issuing the below query Select * from new where c1 between 12 and 17 for update; will not allow other sessions to insert any value between 12 and 17. But if i am trying to insert 20 from other session, it is not allowed. Why this is so? The session is hanging. Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Trainer and Database Security Specialist On Thu, Aug 28, 2014 at 2:26 AM, shawn l.green shawn.l.gr...@oracle.com wrote: 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
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
Replication problem
Replication novice I have a master server at the office and a replication server at home. This setup has been working for a couple of years. Occasionally the replication server gets out of sync (usually following a internet problem and the vpn going down.) I just stop the slave, make sure there is nothing going to the master (when the office is closed), copy the database, transfer the file, load the backup, and start the slave and all is well. This time there was not a communications problem of which I am aware. The slave status said the slave_IO_state was Waiting for master to send event but it was not replicating. I did the usual now it is not updating the replication database (transactions made on the master do not show on the slave - using phpMyAdmin on both servers) BUT show master status shows the correct log file and the position is incrementing AND show slave status shows the same master log file and the same position as the master. So, looking at the status info it seems to be running fine, but the transactions do not appear to appear on the slave. I seek suggestions how to 1) find out what goes wrong when the vpn goes down, and 2) (much more important now) how to find out whether or not the slave is actually replicating or not. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
Whilst there are a few possibilities, check on the master that your binary logs are being written to. Another possible reason could be filtering. On 29 Aug 2014 21:36, william drescher will...@techservsys.com wrote: Replication novice I have a master server at the office and a replication server at home. This setup has been working for a couple of years. Occasionally the replication server gets out of sync (usually following a internet problem and the vpn going down.) I just stop the slave, make sure there is nothing going to the master (when the office is closed), copy the database, transfer the file, load the backup, and start the slave and all is well. This time there was not a communications problem of which I am aware. The slave status said the slave_IO_state was Waiting for master to send event but it was not replicating. I did the usual now it is not updating the replication database (transactions made on the master do not show on the slave - using phpMyAdmin on both servers) BUT show master status shows the correct log file and the position is incrementing AND show slave status shows the same master log file and the same position as the master. So, looking at the status info it seems to be running fine, but the transactions do not appear to appear on the slave. I seek suggestions how to 1) find out what goes wrong when the vpn goes down, and 2) (much more important now) how to find out whether or not the slave is actually replicating or not. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
Hello guys, some points to check here: 1-) Is the master server configured with sync_binlog=1 ? 2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading events from master, is the Exec_Master_Log_Pos incrementing or not? 3-) Why are you reconfiguring all the replication just because the link went down? Cheers, -- *WB* 2014-08-29 17:46 GMT-03:00 Andrew Moore eroomy...@gmail.com: Whilst there are a few possibilities, check on the master that your binary logs are being written to. Another possible reason could be filtering. On 29 Aug 2014 21:36, william drescher will...@techservsys.com wrote: Replication novice I have a master server at the office and a replication server at home. This setup has been working for a couple of years. Occasionally the replication server gets out of sync (usually following a internet problem and the vpn going down.) I just stop the slave, make sure there is nothing going to the master (when the office is closed), copy the database, transfer the file, load the backup, and start the slave and all is well. This time there was not a communications problem of which I am aware. The slave status said the slave_IO_state was Waiting for master to send event but it was not replicating. I did the usual now it is not updating the replication database (transactions made on the master do not show on the slave - using phpMyAdmin on both servers) BUT show master status shows the correct log file and the position is incrementing AND show slave status shows the same master log file and the same position as the master. So, looking at the status info it seems to be running fine, but the transactions do not appear to appear on the slave. I seek suggestions how to 1) find out what goes wrong when the vpn goes down, and 2) (much more important now) how to find out whether or not the slave is actually replicating or not. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
You can paste the show slave status\G here for us to review and on Master, show global variables like 'log-bin%'; show master status ( 3 to 4 times continuously ) On Fri, Aug 29, 2014 at 5:11 PM, wagnerbianchi.com m...@wagnerbianchi.com wrote: Hello guys, some points to check here: 1-) Is the master server configured with sync_binlog=1 ? 2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading events from master, is the Exec_Master_Log_Pos incrementing or not? 3-) Why are you reconfiguring all the replication just because the link went down? Cheers, -- *WB* 2014-08-29 17:46 GMT-03:00 Andrew Moore eroomy...@gmail.com: Whilst there are a few possibilities, check on the master that your binary logs are being written to. Another possible reason could be filtering. On 29 Aug 2014 21:36, william drescher will...@techservsys.com wrote: Replication novice I have a master server at the office and a replication server at home. This setup has been working for a couple of years. Occasionally the replication server gets out of sync (usually following a internet problem and the vpn going down.) I just stop the slave, make sure there is nothing going to the master (when the office is closed), copy the database, transfer the file, load the backup, and start the slave and all is well. This time there was not a communications problem of which I am aware. The slave status said the slave_IO_state was Waiting for master to send event but it was not replicating. I did the usual now it is not updating the replication database (transactions made on the master do not show on the slave - using phpMyAdmin on both servers) BUT show master status shows the correct log file and the position is incrementing AND show slave status shows the same master log file and the same position as the master. So, looking at the status info it seems to be running fine, but the transactions do not appear to appear on the slave. I seek suggestions how to 1) find out what goes wrong when the vpn goes down, and 2) (much more important now) how to find out whether or not the slave is actually replicating or not. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- -- Thanks Suresh Kuna MySQL Database Consutant MongoDB DBA Hadoop Admin
