June 20, 2013

Post: The Optimization That (Often) Isn't: Index Merge Intersection

December 14, 2012 By 6 Comments

… indexes on them, MySQL could sometimes make use of the multiple indexes. For instance, … 1. row *************************** id: 1 select_type: SIMPLE table: users type: index_merge possible_keys:… the “InnoDB rows examined” graph had dropped several orders of magnitude and CPU…

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By 7 Comments

…_blocks_time=1000 on MySQL 5.5.30 and testing multiple buffer pools on MySQL 5.5.30. Finally, MySQL 5.6.10 has… promised, here are the results from joining the tables in the other direction mysql> select straight_join sum(lo_extendedprice*lo_discount…_LastDayInMonthFl tinyint, D_HolidayFl tinyint, D_WeekDayFl tinyint ); DROP TABLE IF EXISTS lineorder; CREATE TABLE IF NOT EXISTS lineorder ( LO_OrderKey bigint not…

Filed Under: MySQL Tagged With: , , , , ,

Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5

April 4, 2012 By 4 Comments

…MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table… case you could be reading the same page multiple times into the buffer pool. So considering this …size were set to 6M, the query time dropped to ~40s. So the maximum possible benefit …

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: Find and remove duplicate indexes

June 20, 2012 By 9 Comments

… multiple indexes on the same column and MySQL won’t complain. Let’s see this example: mysql> alter table t add index(name); mysql> alter table… index [...] # To shorten this duplicate clustered index, execute: ALTER TABLE `test`.`t` DROP INDEX `name`, ADD INDEX `name` (`name`); # Key age ends…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…mysql: SHOW CREATE TABLE innodb_myisam_stopword\G *************************** 1. row *************************** Table: innodb_myisam_stopword Create Table: CREATE TABLE… might expect to get back multiple matches containing the words “James…1, dropped/rebuilt the InnoDB index, and tried again. mysql: …

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Comment: Can MySQL temporary tables be made safe for statement-based replication?

May 27, 2008 By 8 Comments

… that I have my code surrounded by START TRANSACTION; COMMIT; mysql> SHOW binlog events IN ‘bin-logs.000440′\G *************************** 1. row… that could potentially run this multiple times per session. Interestingly enough, we don’t care if the drop table statement isn’t rolled…

Filed Under: Insight for DBAs Tagged With:

Post: Beware of MyISAM Key Cache mutex contention

August 12, 2008 By 10 Comments

… tables at very high rate. Hundreds of millions rows are loaded daily into single MySQL…table and so in theory using multiple thread to do inserts in multiple tables would not be bound by table…multiple key caches . We use “keycache per table” this case and caused number of context switches to drop…

Filed Under: Insight for DBAs Tagged With: ,

Post: Side load may massively impact your MySQL Performance

November 13, 2011 By 7 Comments

…mysql –mysql-host=localhost –mysql-table-engine=innodb –mysql-db=test –oltp-table-name=md_cache_test_small –oltp-table-size=1100000 –mysql-user=msandbox –mysql-password=msandbox –mysql… sysbench OLTP on small table drops more than 150 times… likely going to have multiple accesses to the data…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: Why MySQL could be slow with large tables ?

June 9, 2006 By 141 Comments

… grows you may be surprised by serve drop in performance. This especially apples to index …1% of the table. The times for full table scan vs range scan by index: mysql> select count(pad…multiple servers to host portions of data set. Store portion of data you’re going to work with in temporary table…

Filed Under: Insight for DBAs Tagged With: ,

Post: MySQL Server Memory Usage

May 17, 2006 By 21 Comments

…memory MySQL will allocate for this operation. Note it is more complicated than once may think – multiple …if done to MyISAM tables. myisam_sort_buffer_size used for ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE commands. For …I’m quite sure I’ve missed something (drop me a note if you have something to…

Filed Under: Insight for DBAs Tagged With: , ,
« Older Posts
Newer Posts »