…run the same query twice, you get the same result, regardless other… to insert another value on the second session: transaction2 > START TRANSACTION; transaction2 >… gap lock preventing the insertion of data to other sessions. How to …size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 3…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…mysql> show processlist; +——-+——+———–+———-+———+——+————————-+—————————–+———–+—————+———–+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
…of garbage collection. Support for Delayed Invalidation Many MySQL application do not need absolutely up to date data, something which is couple of seconds…rows used from query syntax may be hard. Query Cache Storage Engines MySQL …1Gb networks. Plus if we can get MySQL protocol to support such light …
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…of MySQL process in 1/100 of the second. (This is pretty idle test system). So 347 and 5303 correspond to 3.47 seconds of… 0 ROWS_FETCHED: 485139 ROWS_UPDATED: 0 TABLE_ROWS…of CPU. With Percona Server, enabled full query logging and log_slow_verbosity=full you can also get great amount of related data…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
…get the table row format from the Information Schema: mysql (information_schema) > SELECT ROW… from_date [...] The second method has the…output of our example: ~/data-recovery# cat data/salaries…rows With the data in our hands the last step is to import it in our database: mysql (employees) > LOAD DATA…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 11 Comments
… MySQL slave transaction_isolation=READ-COMMITTED You can get FlexCDC directly out of the Flexviews SVN. I suggest that you just grab all of… OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 And delete data in a second transaction: mysql> delete from test.demo where c1=1; Query OK, 1 row affected (0.00…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… data is stored inside of a histogram in the set. The cardinality and the distribution is always known. Second, computation on the compressed data… table. mysql> delete from data where val=16; Query OK, 1 row affected (3.14 sec) mysql> select val, count(distinct id) from data where…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…) Data dictionary Here is a quick example of the list of tables used by the above view, as stored in the data dictionary: mysql> select…: mysql> call flexviews.refresh( -> flexviews.get_id(‘demo’, -> ‘dashboard_customer_sales’),’BOTH’,NULL); Query OK, 0 rows affected (7.01 sec) The second one…
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
… that key. It then uses data from that random row to look up a second piece of information via email address. Because… am still getting a really solid # of hits in memcached, but the # of writes in MySQL coupled with the still large # of reads takes its… via that key. It then uses data from that random row to look up a second piece of information via email address. Because the…
