… drop table performance with large Innodb Buffer Pool size. As such I wonder how much memory do we really use to run MySQL Server these days ? [poll id="2"] Feel free to select multiple sizes if you have multiple kinds of instances… will be frozen so we do not get results from multiple years mixed.
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
… automatically table generated names are not really helpful for multiple column indexes – you can’t easily see what is the index MySQL trying…) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `i` int(11) default NULL… complicated. If you use auto generated index names you may drop the wrong indexes as part of upgrade process just because…
Post: MySQL Upgrade Webinar Questions Followup
June 28, 2012 By 7 Comments
…tables are on track to be depreciated in future MySQL versions also MyISAM tables are essentially at the state they were in MySQL…. Another thing to mention having multiple instances of different MySQL versions on the same box …, so it should be good as drop in replacement with no data conversion …
Post: Table locks in SHOW INNODB STATUS
June 8, 2010 By 1 Comment
… 368 MySQL thread id 8, query id 164 localhost root TABLE LOCK table `…table. None of this is right. In fact Innodb uses Multiple Granularity Locking and there is always lock taken on the whole table…table – for example to drop the table you need to lock it exclusively. So do not worry intention table…
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…
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…
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 …
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…
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…
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…
