… locking cluster-wide, it uses optimistic locking. Initially a transaction on PXC behaves much like a regular Innodb transaction. It does pessimistic locking…Table: CREATE TABLE `autoinc` ( `i` int(10) unsigned NOT NULL AUTO_INCREMENT, `j` varchar(32) DEFAULT NULL, PRIMARY KEY (`i`) node2 mysql…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
Post: Instrumentation and the cost of Foreign Keys
September 20, 2010 By 9 Comments
…INT NOT NULL AUTO_INCREMENT, bogus_column char(32), PRIMARY KEY (id) ) ENGINE=InnoDB; CREATE TABLE child ( id INT NOT NULL AUTO_INCREMENT, parent_id…6 lock_trx_id: 87035 lock_mode: X lock_type: RECORD lock_table: `test`.`parent` lock_index: `PRIMARY` lock_space: 1300 lock_page: 3 lock_rec: 6 lock_…
Post: Announcing Percona Server 5.1.66-14.2
January 15, 2013 By Leave a Comment
… downgrade the requested exclusive (X) row locks to shared (S) locks, these S locks prevent X locks from being taken and block the real… –innodb-optimize-keys, it did not handle composite indexes correctly when verifying if the optimization is applicable with respect to AUTO_INCREMENT columns…
Comment: To UUID or not to UUID ?
October 27, 2008 By 28 Comments
… for InnoDB as well. We are using MySQL 5.0 and InnoDB, we got badly lock conflict when inserting rows into InnoDB tables with auto-increment…. the results for InnoDB are shown as below: inserting 100,000 rows into InnoDB tables, for a table with auto-increment, it tooks 254 sec; for a table without auto-increment but use UUID…
Comment: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
March 11, 2012 By 8 Comments
… mod http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html I have the same problem. 1. You have… is traditional lock mode == > funcionality of auto_increment like before 5.1.22 # =1 is default value – consecutive lock mode # =2 is interleaved lock mode , i don’t investigate what it do innodb_autoinc_lock_mode=0 2. restart…
Comment: MySQL Server Memory Usage
December 20, 2012 By 21 Comments
…test #replicate_do_db=crmdb #replicate_do_db=newcrmdb #auto-increment-increment = 3 #auto-increment-offset = 1 #log-slave-updates skip_name_resolve…= 131072 default-storage-engine = InnoDB #thread_concurrency = 32 innodb_buffer_pool_size = 4096M innodb_lock_wait_timeout = 120 innodb_use_sys_malloc = …
Comment: Some little known facts about Innodb Insert Buffer
December 14, 2010 By 12 Comments
Innodb locking problem with unique keys when inserting CREATE TABLE `male_id` ( `entry_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `jy_id` int(10) unsigned NOT NULL, PRIMARY KEY (`entry_id`), UNIQUE KEY `jy_id` (`jy_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1…
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
…. Not using auto_increment functionality This is right. With some exception however. For example Innodb tables do internal full table lock if auto_increment is used… OS and File System dependent, others correspond to Innodb storage engine or using innodb_file_per_table option in particular. 4. Not…
Comment: Product to try: MySQL/MariaDB-Galera 0.8
June 13, 2011 By 34 Comments
… auto_increment: I did not research much yet, but I see that Galera strictly requires innodb_autoinc_lock_mode=2 and tries to manage innodb_autoinc_lock_mode / auto_increment_offset – For deadlocks: I definitely…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… Max_data_length: 0 Index_length: 539279360 Data_free: 4194304 Auto_increment: NULL Create_time: 2011-05-10 04:26:14 Update… innodb-rollback-segments=16 innodb-stats-auto-update=0 innodb-stats-on-metadata=0 innodb-stats-sample-pages=256 innodb-stats-update-need-lock=0 innodb-status-file innodb…
