…large discrepancy in the sizes, is this an indicator that it may be a good opportunity to run OPTIMIZE…table‘s .ibd file looks like this: # cd /var/lib/mysql/databasename # ls -lh tablename* -rw-rw—- 1 mysql mysql…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join two or more tables….memory workload Now let’s see how effective are the join optimizations when the workload fits entirely … MariaDB 5.5 that is quite large as compared to MySQL 5.6 and that is ‘Handler_…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…TABLES WITH READ LOCK can be quite large because of the time it may take to…MySQL 5.5 FLUSH TABLES WITH READ LOCK does not work as optimally…to complete. Here is example how it can look: mysql> show processlist; …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… 1 MRR range scans had to be performed. As in the table above you can with default…to a bug in how counter is calculated inside MariaDB? Other Observations Sometimes both for MariaDB 5.5 and MySQL 5.6, the optimizer… to under a minute. The query time is reduced further when buffer size is set large…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 28 Comments
…large transaction caused by the single statement. We didn’t want to…optimized for rollbacks, it’s optimized for transactions that succeed and commit. Rollback can take an order of magnitude longer to…TABLE in 10k row chunks, by the way; I chose 1M because the rows were small). But how to… do time mysql…
Post: Economics of Performance Optimization
March 13, 2008 By 9 Comments
… such as building summary tables, significant schema changes, use of replication…how to optimize it another 10-15% may be a great idea. On higher end I’d even look to speak to…MySQL and LAMP and MySQL is one of them. Plus for large companies it may make sense to hire great experts and have them to…
Post: Is your MySQL Application having Busy IO by Oracle Measures ?
January 8, 2012 By 1 Comment
… MySQL can be used for free we do not need to optimize the …large table and data sizes in MySQL can be painful, lacking online operations to alter table…How many of us are running 5TB per MySQL instance ? 20TB ? Over 50TB ? I believe this is to continue to be significant difference between MySQL…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
…run a function on a large amount of input. Often times…MySQL client. This will be a linear operation because Shard-Query has no idea how to…BY NULL ) The other important optimization combines results from multiple queries…row in set (0.00 sec) How to factor numbers create table dataset ( id bigint auto…
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 table…TABLES or SHOW TABLE STATUS on many a large tables, this can affect your server especially if the random index dives had to…
Post: What do we optimize with mk-query-digest ?
December 15, 2009 By 2 Comments
…to optimize), it typically does so as transactions you’re trying to optimize…how critical this suffering is) – it is however perfectly possible to get bad performance with MySQL responsible for large…
