… temporary table, secondary indexes are updated by inserting individual rows rather than sorting; OPTIMIZE TABLE could be faster if secondary… test > dump_unoptimized.sql $ mysqldump -uroot test –innodb-optimize-keys > dump_optimized.sql $ time mysql -uroot test < dump_unoptimized.sql real 2m52.785s…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… 20 nodes Distributed set processing (theory) What is SQL? As you probably know, SQL stands for “structured query language”. It isn’t…: `origin_airport_id` * storage node result set merge optimization enabled: ON DUPLICATE KEY UPDATE `origin_airport_id`=VALUES(`origin_airport_id`), `count…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
… Cache” which is quite helpful for MySQL Performance optimization tasks but there are number of things you…are constantly being invalidated from query cache by table updates, this means number of queries in cache and…query_cache_type to “DEMAND” and use only SQL_CACHE hint for queries which you want …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… uses the changelogs collected by FlexCDC to update the snapshot to reflect the changes that …Refresh type Aggregation Outer join All SQL functions Built using SQL Requires FlexCDC COMPLETE Y Y Y …refresh will be more expensive. This is an optimization which is required to efficiently refresh MV …
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 3 Comments
… series describing Flexviews. Update: This series has a part 2 and part 3. The output of any SQL query is logically an… discarded. Views are not generally considered to be a performance optimization because: The contents of the result set are computed each… contents from scratch. The contents of the result set are updated periodically, not each time the view is accessed. If multiple…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… data possible to save work. Avoid operations that update huge chunks of aggregated data at once. …s still another reason to use InnoDB/XtraDB tables… Optimize For I/O It is pretty much inevitable: … parallelization, so ETL jobs written to rely on SQL tend to get really bogged down. In contrast…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…MySQL thread id 30898, query id 100626 localhost root Updating update iz set pad=’a’ where i=2 *** (1) WAITING…8079, query id 728899 localhost root Sending data select sql_calc_found_rows * from b limit 5 Trx …can check if your innodb_log_buffer_size is optimal – if you see more than 30% of log buffer…
Post: Announcing Percona Server 5.1.66-14.2
January 15, 2013 By Leave a Comment
…tracking has been implemented. HandlerSocket has been updated to version 1.1.0 (rev. 83d8f3af176e1698acd9eb3ac5174700ace40fe0…any non-temporary table) can inject arbitrary SQL into the binary log and …Laurynas Biveinis). When mysqldump was used with –innodb-optimize-keys, it did not handle composite …
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
…indexes slow down insert operations, the MySQL optimizer sometimes appears to become rather overwhelmed by…using an ORM like SQL Alchemy and want to avoid writing sql directly in application … not null, last_updated timestamp not null default current_timestamp on update current_timestamp, foreign key…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…_results cd shard-query/ $ ./run_query < queries.sql | tee raw |./pivot_results & [1] 12359 $ tail -f …-threads=16 innodb-recovery-stats innodb-recovery-update-relay-log innodb-replication-delay=# innodb-rollback… over response time, get a good plan optimizer-prune-level=0 partition=ON port=3306…
