… is the buffer size. In MySQL 5.6 the buffer size used by MRR can be controlled by the variable read_rnd… different variable to control the MRR buffer size mrr_buffer_size. Both buffer sizes default to 256K in MySQL 5.6 and…, sum(l_extendedprice * (1 – l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where…
Post: Looking for RAID Controller without Battery Learning problems ?
January 5, 2012 By 7 Comments
…MySQL Performance. Here are couple of links (1,2). It is good to see though there are some controllers…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
…year has tens of millions of flights: mysql> select count(*) from ontime_one.ontime_fact; +———–+ | count(*) | +———–+ |…. Query details: — Q1 SELECT DayOfWeek, count(*) AS c from ontime_fact JOIN dim_date using… as the degree of parallelism is controlled by the number of shards. — Q9 …
Post: How InnoDB handles REDO logging
February 3, 2011 By 18 Comments
…is the mechanism that provides the A (Atomic) C (Consistent)Â and D (Durability) in ACID. … sectors. Percona Server with XtraDB allows you to control the transaction log record size, as better …all, your transaction will never complete. Both the MySQL configuration and hardware will affect the speed …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…. The refresh method you select effectively controls the minimum amount of time it… flexviews.add_table(@mvid,’demo’,'customers’,’c‘,’USING (customer_id) ‘); CALL flexviews.add… | <-- too high +------------------+ 1 row in set (0.68 sec) mysql> select sum(total_lines) from dashboard_customer_sales ;…
Post: MySQL 5.0, 5.1 and Innodb Plugin CPU Efficiency
December 3, 2008 By 15 Comments
… different MySQL versions in terms of their CPU efficiently in TPC-C like Workload. We did it couple of weeks ago so MySQL 5.0.67, MySQL 5.1.29… Core Xeon E5310, CentOS 5, Data stored on ramfs. We controlled number of cores used with /sys/devices/system/cpu/cpuX…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
… table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a… 2Billion rows has been modified, whichever comes first. ./row/row0mysql.c:row_update_statistics_if_needed If you or an application…. Luckily, this can be controlled with Percona Server with the variable innodb_stats_auto_update (from MySQL Community 5.1.17 a…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…sort buffers to be allocated. You can’t control that or calculate it at a global level….( select * from ( select 1 as a ) as b ) as c; You can have many of those per query…* 100); Query OK, 0 rows affected (2.34 sec) mysql> select get_lock(@a, 1); +—————–+ | get_lock(@a, 1) | …
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… | 1 | i | A | 245900 | NULL | NULL | | BTREE | | | antest_innodb | 1 | c | 1 | c | A | 245900 | NULL | NULL | YES | BTREE | | | antest_innodb | 1…. MyISAM has a special variable which controls if NULLs should be considered equal when computing stats: mysql> show variables like “myisam_stats…
Post: The performance effects of new patches
September 9, 2008 By 14 Comments
… between transactions (no thinking time, no keying time), it uses MySQL C API and the server side prepared statement. The server has… following parameters are added. innodb_read_ahead (default 3) This controls [enable/disable] of read-ahead. 3: normal 2: enable linear… value innodb_buf_flush_burst (default 100) * Burst value These control the number of blocks flushed at once. (flushing: writing modified…
