Search Results for: innodb delete

Innodb locking and Foreign Keys

Today I was working with application which uses Innodb and foreign keys and got into locking problems possibly due to foreign keys, so I did a little investigation on that matter. Interesting enough it looks like most people do not think about foreign keys overhead in terms of locking. The overhead about checking referenced table […]

SHOW INNODB STATUS walk through

Many people asked me to publish a walk through SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS output and how to use this info to improve MySQL Performance. To start with basics SHOW INNODB STATUS is command which prints out a lot of internal Innodb performance counters, statistics, information about […]

Choosing proper innodb_log_file_size

If you’re doing significant amount of writes to Innodb tables decent size of innodb_log_file_size is important for MySQL Performance. However setting it too large will increase recovery time, so in case of MySQL crash or power failure it may take long time before MySQL Server is operational again. So how to find the optimal combination […]

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. MySQL has pluginable architecture which divides work between mysql common code (parser, optimizer) and storage engine. From storage engine’s point […]

My Innodb Feature wishes

At Users Conference Heikki did good presentation about Innodb planned features. I did not see some of big and tiny wishes listed so I was making notes. Here is what I’d like to see Packed indexes. In many cases then difference in Performace with MyISAM and Innodb is huge for read only workload it is […]

Recovery after DROP & CREATE

In a very popular data loss scenario a table is dropped and empty one is created with the same name. This is because¬† mysqldump in many cases generates the “DROP TABLE” instruction before the “CREATE TABLE”:

If there were no subsequent CREATE TABLE the recovery would be trivial. Index_id of the PRIMARY index of […]

Fractal Tree library as a Key-Value store

As you may know, Tokutek is now part of Percona and I would like to explain some internals of TokuDB and TokuMX – what performance benefits they bring, along with further optimizations we are working on. However, before going into deep details, I feel it is needed to explain the fundamentals of Key-Value store, and […]

Getting EXPLAIN information from already running queries in MySQL 5.7

When a new version of MySQL is about to be released we read a lot of blog posts about the performance and scalability improvements. That’s good but sometimes we miss some small features that can help us a lot in our day-to-day tasks. One good example is the blog post that Aurimas wrote about a […]