… Let me give you a simple example. Suppose you have the following query executed on the InnoDB table… ‘R’ and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment …bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…the second table. This means many point queries, say for example if table1 yields 1000 rows then …, lineitem FORCE INDEX (i_l_orderkey) where c_mktsegment = ‘AUTOMOBILE’ and c_custkey = o_custkey and l_orderkey …at the status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ join_buffer_size=…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… Here is example: SESSION1: mysql> show tables; +——————–+ | Tables_in_dumptest | +——————–+ | A | | B | | C | +——————–+ 3 rows in set (0.00 sec) mysql> …SESSION1: (before dump has completed) mysql> alter table C add i int not null; Query OK, 1 row affected (0….
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… SELECT queries you can be potentially waiting for hours for this statement to complete. Here is example how it can look: mysql> show… priority problem with pending WRITE query blocks any READ queries to execute on the table. mysql> show processlist; +——-+——+———–+———-+————+——+——————————+——————————+———–+—————+———–+ | Id | User | Host… | 0 | 1 | | 10291 | root | localhost | dumptest | Query | 74 | Waiting for global read lock | insert into C values (“a”,1) | 0 | 0 | 1…
Comment: InnoDB's gap locks
April 13, 2012 By 15 Comments
…example: “InnoDB provides REPEATABLE READ for read-only SELECT, but it behaves as if you use READ COMMITTED for all write queries… DEFAULT NULL, `c` char(20) DEFAULT NULL, KEY `i` (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql> select * from t; +——+——–+ | i | c | +——+——–+ | 1 | …
Post: NDB API examples, the trivia to make ndbapi_scan works!
November 15, 2009 By 4 Comments
… has example table: GARAGE. Dropping it… MySQL Cluster already has example table: GARAGE. Dropping it… MySQL Cluster already has example table: GARAGE. Dropping it… ^C What’s that? Wow… look at the create_table code… void create_table(MYSQL &mysql) { while (mysql_query(&mysql, “CREATE…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…query. convert.php This script takes as one argument the default database. Notice in the following example….add_table(@mvid,’demo’,'customers’,’c‘,’USING (customer_id) ‘); CALL flexviews…for querying: mysql> call flexviews.enable( -> flexviews.get_id(‘demo’,'dashboard_customer_sales’)); Query OK…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
Shard-Query is an open source tool kit which helps improve the performance of queries against a MySQL database by distributing the work… Shard-Query over Hive is that it works with existing MySQL data sets and queries. Another advantage is that it works with all MySQL… be added. Q9 is an example of such a query. . Query details: — Q1 SELECT DayOfWeek, count(*) AS c from ontime_fact JOIN dim_date…
Post: The two even more fundamental performance metrics
May 5, 2011 By 6 Comments
… analysis to the data, such as using the Erlang C function to predict how the queue time will vary … microseconds, maybe a bit longer. This is common in MySQL servers I work with when they’re running a … which we’d often like to have. What was the query, for example? With the text of the SQL in hand, …
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
… such as Sysbench and DBT2/TPC-C scale pretty well a they got …example I spotted the query pattern responsible for large portion of the load in the application by aggregating query log with mk-query…select,JOIN::exec,mysql_sel ect,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,…
