Emergency

Search Results for: flush table with read lock

MySQL optimizer: ANALYZE TABLE and Waiting for table flush

The MySQL optimizer makes the decision of what execution plan to use based on the information provided by the storage engines. That information is not accurate in some engines like InnoDB and they are based …

Read More
 

Adaptive flushing in MySQL 5.6

As you may know, flushing in MySQL is an area of my interest, I wrote about it several times, i.e. https://www.percona.com/blog/2011/09/18/disaster-mysql-5-5-flushing/ https://www.percona.com/blog/2011/03/31/innodb-flushing-a-lot-of-memory-and-slow-disk/ https://www.percona.com/blog/2011/01/03/mysql-5-5-8-in-search-of-stability/ In MySQL 5.6 there was implemented a new flushing logic, so I …

Read More
 

DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL

Suppose you have turned on innodb_file_per_table (which means that each table has its own tablespace), and you have to drop tables in a background every hour or every day. If its once every day then …

Read More
 

Effect from innodb log block size 4096 bytes

In my post MySQL 5.5.8 and Percona Server: being adaptive I mentioned that I used innodb-log-block-size=4096 in Percona Server to get better throughput, but later Dimitri in his article MySQL Performance: Analyzing Percona’s TPCC-like Workload …

Read More
 

SHOW OPEN TABLES – what is in your table cache

One command, which few people realize exists is SHOW OPEN TABLES – it allows you to examine what tables do you have open right now:

Read More
 

Hacking to make ALTER TABLE online for certain changes

Suppose you want to remove auto_increment from 100G table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE huge_table CHANGE id id int(6) NOT NULL and then wait hours for table rebuild to …

Read More
 

How much overhead is caused by on disk temporary tables

As you might know while running GROUP BY and some other kinds of queries MySQL needs to create temporary tables, which can be created in memory, using MEMORY storage engine or can be created on …

Read More
 

PHP Large result sets and summary tables.

We’re working with web site preparing for massive growth. To make sure it handles large data sets as part of the process we work on generation test database of significant size as testing your application …

Read More
 

Can Innodb Read-Ahead reduce read performance ?

I ran into pretty interesting behavior today. We needed to dump and reload large database and we had pretty good IO subsystem so we started number of mysqldump processes in parallel. Unlike in other case …

Read More
 

InnoDB thread concurrency

InnoDB has a mechanism to regulate count of threads working inside InnoDB. innodb_thread_concurrency is variable which set this count, and there are two friendly variables innodb_thread_sleep_delay and innodb_concurrency_tickets. I’ll try to explain how it works. …

Read More