…tables regularly or reducing table cache reduces memory consumption to be withing the reason. Connection Related Allocations Another set of buffers correspond to…to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table…data is read from disk, in fact half the amount of data is read from disk 2.2G vs 5G. However, there is one…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… on the InnoDB table: SELECT non_key_column FROM tbl WHERE key…-optimization Now let’s move on to the benchmarks, to see the difference …1.89G 1.53G Innodb_data_reads 120552 123872 100551 103011…one MRR related variable introduced in MySQL 5.6 and MariaDB 5.5 Handler_mrr_init and another additional one…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…from the same team within Percona. However, the audit itself is generally a single person, with another…to look at MySQL Assuming I don’t see anything noteworthy here, I’ll move on to the MySQL…to convert MyISAM tables to…table. Queries can be rewritten. Indexes and data types and table…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…to point out that the results shown in this blog post are from the queries that I have gathered from one…to download the “slow query log” to another…to gather more data about the underlying tables involved and the query execution plan used by MySQL…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 7 Comments
…another child F, and below F we have a subtree D -> E (see illustration): We want to move…from the same table in a single query in MySQL. But we can use MySQL‘s multi-table DELETE syntax, to…
Post: A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 7 Comments
…to recover data. Basically, while doing a transfer from one SAN to another, something went wrong and they lost the ibdata1 file, where all the table meta-data…echo “moving the datafile in place” cp /mnt/tables/$CURRENT_TABLENAME.ibd /var/lib/mysql/recover/ chown mysql.mysql /var/lib/mysql/recover/$…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 7 Comments
…s stock MySQL you cannot move your ibd files freely from one server to another or from one database to another. The reason is that the table definition …going to delete some data from one table. In this case we’re going to delete the salary information from the user 10008: mysql> SELECT * FROM …
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
…to think of any harm that may happen here due to the way this variable is used. Moving on. ==9090== Possible data…table_state) (sql_show.cc:6238) ==9090== by 0x6A5E0C: JOIN::exec() (sql_select.cc:1863) ==9090== by 0x6A7D72: mysql_select(THD*, Item***, TABLE…
