…_column FROM tbl WHERE key_column=x This query will roughly be evaluated in following steps, without MRR: SELECT key_column, pk_column FROM tbl WHERE key_column… reporting high numbers for Innodb_buffer_pool_read_ahead which shows that the access pattern was sequential and hence InnoDB decided…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…see MySQL memory consumption plotted. I use “VSZ” columns from “ps” output on Linux. It helps me to…as you can query temporary tables too: mysql> select sum(data_length+index_length) from information_schema….I do as it is least intrusive. Run SHOW ENGINE INNODB STATUS and look for memory information…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…ahead when the buffers are sized appropriately, which shows that the IO access pattern has been …query on MariaDB 5.5 is slow as compared to MySQL 5.6. Next interesting thing are the last two columns…tests to see what specific types of queries would benefit from Hash Join as compared to Nested Loop …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By Leave a Comment
… Query OK, 0 rows affected (0.49 sec) mysql> alter table employees add column age smallint unsigned not null default 0; Query …mysql-bin.00022 only starting from position 336796712 and skipping the DROP statement at position 336797160: mysql> show binary logs; +——————+———–+ | Log_…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… MySQL conference, and Timour and Sergei from Monty Program will present a …with external auth systems Dynamic columns and virtual columns (they’re different) offer flexibility…SHOW PROCESSLIST Group commit for the binary log makes the server much faster with the binary log enabled Faster queries…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…output ranks the queries and shows the top queries with largest impact – longest sum of run time which typically (not always) shows queries causing …to instead of selecting every column from all the tables involved in the query, probably selecting only the needed columns which could exclude the…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
… dimension table tables to the staging tables, omitting the dimension columns from the projection, instead replacing them with the dimension keys: select… LIMIT 10; The next queries show how performance is improved when Shard-Query adds parallelism when “subqueries in the from clause” are used. There…
Comment: Feature Idea: Finding columns which query needs to access
June 7, 2009 By 16 Comments
… = mysql_query($sql); $sql=”insert into test select * from $table limit 1″; $result = mysql_query($sql); // Here we get all of the columns from the table and create an index on each one. $sql=”show columns from test”; $result = mysql_query…
Post: Testing MySQL column stores
August 16, 2010 By 15 Comments
… of such a problem would be “select avg(char_column) from table” where the column contains a mix of ascii data, such as… find significant defects in Infobright that would be “show stoppers” for typical OLAP analysis queries. These tests were intended to approximate what… get accurate results for all of the queries on InfiniDB. In particular the query “select count(*) from carsales.sales_fact” when run on…
