…have configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have…while true do date >> ps.log ps aux …There is a max_heap_table_… used by current MEMORY tables: mysql> select sum…have created which temporary tables (both in memory and not): mysql> select…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…, instead replacing them with the dimension keys: select dim_date.date_id, origin.airport_id as …can be confirmed by examining the contents of the date dimension: mysql> select count(*), min(FlightDate), max(FlightDate) from … improve the performance of queries which have to examine a large amount of …
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…by…have any indexes (not even primary keys…date NOT NULL, `date_id` smallint(6) NOT NULL, PRIMARY KEY (`date_id`), KEY `FlightDate` (`FlightDate`), KEY `Year` (`Year`,`Quarter`,`Month`,`DayOfWeek`), KEY `Quarter` (`Quarter`,`Month`,`DayOfWeek`), KEY…Max…select …mysql-inno.sock [mysqld] socket=/tmp/mysql…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…have happened since the backup was taken. Replaying the binary logs brings the database up-to-date by…prevent wide innodb primary keys on the MV. mysql> select mview$pk as…sec) *If you use MIN/MAX/COUNT_DISTINCT, a secondary… out-of-date and must be refreshed: mysql> select sum(total_lines) from …
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…commands: date uname …mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max…mysql.* tables, running mk-duplicate-key…Having found the desired information about the queries by…(rewriting non-SELECT queries…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…date DEFAULT NULL, `l_commitDATE` date DEFAULT NULL, `l_receiptDATE` date…by MySQL 5.5 is as follows: SELECT…have scaled down the time taken by MySQL…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…, GROUP BY, SUM, COUNT, MIN*, and MAX* are distributable…via ON DUPLICATE KEY UPDATE is the … does not have to have any idea of…but right now only MySQL storage nodes are …date AS `dim_date` ON (ontime_fact.date_id = dim_date.date_id) WHERE dim_date.Year IN (2008) GROUP BY 1 ORDER BY NULL [1] => SELECT…
Comment: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…have given the query & cnf details. Select…DATE`,`DESCRIPTION`,`OTHER_INFO`,`CATEGORY`,`REMARKS`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 CNF ——- [mysqld] socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max…
