… queries for a star schema. A star schema presents some unique challenge to the database optimizer. The SSB benchmark consists of…_log_buffer_size=32M innodb_buffer_pool_instances=16 metadata_locks_hash_instances=32 table_open_cache_instances=8 sort_buffer…
Post: Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
… node by simply by running “FLUSH TABLES WITH READ LOCK“, or perhaps by “LOCK TABLE”, in which case flow control will kick… the size of the transactions, which translates into number of unique key lookups into the certification index, which is a hash… flow control if I execute a FLUSH TABLES WITH READ LOCK on node3: [root@node3 ~]# myq_status wsrep Wsrep Cluster Node…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… operations, the number of unique pages the query accessed, the length of time query waited for row locks, etc). So you can… # Files: slow-query.log.1 # Overall: 20.08k total, 167 unique, 16.04 QPS, 0.01x concurrency ________ # Time range: 2011-12… is the SELECT wp_options query, this is basically a unique way of identifying the query and simply implies that this…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… | 0.000004 | | init | 0.000008 | | Opening tables | 0.000118 | | System lock | 0.000007 | | setup | 0.000027 | | creating table | 0.002255 | | After… when this optimization is not applicable: UNIQUE indexes in ALTER TABLE are ignored to enforce uniqueness where necessary when copying the data…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… counter values called “Traditional”. This one uses a special table lock called AUTO-INC that remains until the end of the… AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB; Insert a value using a… AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… > exception however. For example Innodb tables do internal full table > lock if auto_increment is used so using values generated elseware… order) the primary key you specify (an auto_increment), a unique key, or one it creates for you. It’s been… implications. Remember that if you don’t create either a unique key or a column with an auto_increment primary key…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
… table with an auto-incrementing integer PK and a secondary UNIQUE KEY. The queries being run against this table were almost… NULL, last_modified TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY(id), UNIQUE KEY(username) ) ENGINE=InnoDB; Now consider the following sequence of… out to be. 1500 queries per second all trying to lock and update the same row is not going to end…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… READ COMMITTED, then InnoDB normally does not lock the ‘gaps’, and it does not lock rows whose latest committed version does not… key constraint need not form a primary key or a unique constraint: it is sufficient if the referenced columns form a… a foreign key not reference an entire primary key or unique constraint (something that is mandatory in every other RDBMS that…
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(…_id` int(10) unsigned NOT NULL, PRIMARY KEY (`entry_id`), UNIQUE KEY `jy_id` (`jy_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1… remains. But the problem is gone if I change the unique key (jy_id) to be index (jy_id).
Comment: Database problems in MySQL/PHP Applications
November 6, 2006 By 13 Comments
…_extract #binlog-ignore-db=vehicles #binlog-ignore-db=voiceshot # required unique id between 1 and 2^32 – 1 # defaults to 1… # (commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 – 1 # (and different from…_size = 32M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql…
