June 19, 2013

Post: Is Synchronous Replication right for your app?

May 14, 2013 By 18 Comments

…application workload: [In a Galera cluster] a given row can’t be modified more than once…Our unit of locking in Innodb is a single row (well, the PRIMARY KEY index entry for …standard MySQL replication from this instance, since MySQL replication is asynchronous. What about semi-sync MySQL replication?…

Filed Under: MySQL, Percona Software, XtraDB Cluster Tagged With: , , , , , ,

Post: Galera Flow Control in Percona XtraDB Cluster for MySQL

May 2, 2013 By 5 Comments

… mechanism, unlike anything you find in standard async MySQL replication. It is my belief…in this case might mean a long wait before flow control gets relaxed again. However, this was recently modified… fc_limit does however keep the certification index smaller in memory. It keeps the certification …

Filed Under: Insight for DBAs, Percona Live, XtraDB Cluster Tagged With: , , ,

Post: The Optimization That (Often) Isn't: Index Merge Intersection

December 14, 2012 By 6 Comments

…index_merge_intersection. It’s a dynamically-adjustable setting and can be modified globally or per-session: SET [GLOBAL|SESSION] optimizer_switch=”index…INDEX() and suddenly start believing that they can always outsmart the optimizer by manually manipulating indexes in…

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size

June 27, 2012 By 4 Comments

… performance with fast index creation and without it with different buffer pool sizes. Results are pretty interesting. I used modified Sysbench table for these tests because original table as initially created only has index on column K which…*SATA disks in RAID10. I used 10mil row table which would look as following in terms of data and index size: mysql> show table…

Filed Under: Insight for DBAs, MySQL

Post: Troubleshooting MySQL Memory Usage

March 21, 2012 By 5 Comments

…in most cases you do not know where exactly memory is allocated. This was not much of the problem in MySQL…Modified…

Filed Under: Insight for DBAs, MySQL

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

… Search in MySQL 5.6: Part 2, The Queries![/caption] This is part 2 in a 3 part series. In … InnoDB index, and tried again. mysql: SELECT id, full_name, MATCH(full_name, details) AGAINST (‘”Thomas B Smith”‘ IN … FULLTEXT KEY, you can use the “WITH PARSER” modifier to specify the name of a UDF which …

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Getting History of Table Sizes in MySQL

December 8, 2010 By 13 Comments

…, `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL, `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL, `DATA_…to keep it completely inside MySQL you can create appropriate event in MySQL 5.1+ Unless you…large number of MySQL servers, especially in Sharded environment it makes sense to modify the script so…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By 7 Comments

…in 587MB of raw data, mostly in one  table (lineorder). Each of the 13 queries were executed serially in a single connection I modified…able to move to the hot list.  In MySQL 5.6 the default value of …in the buffer pool and the other secondary indexes have been pushed out of the buffer pool. mysql…

Filed Under: MySQL Tagged With: , , , , ,

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

… than updating indexes by insertion. Let’s see if having more secondary indexes in the table makes any difference: mysql> SET expand_fast_index_creation=OFF; Query OK, 0 rows affected (0.00 sec) mysql> ALTER… in set (0.00 sec) mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t MODIFY…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: Connecting orphaned .ibd files

May 13, 2011 By 19 Comments

…_SPACE_OR_CHKSUM 4 /* in < MySQL-4.0.14 space id the page belongs to (== 0) but in later versions the ‘new’ checksum… command will create respective records in SYS_TABLES and SYS_INDEXES. 2. Now let’s modify SPACE in InnoDB dictionary. MySQL must be stopped at this point. There is a tool ibdconnect in Percona…

Filed Under: MySQL
« Older Posts