June 19, 2013

Post: Beware the Innodb Table Monitor

… big spike in io-wait on the server during the time I ran the Innodb Table monitor (it was just for a minute) and he was very glad we waited until after hours.  The server itself… for each table, it calls dict_update_statistics and appears to force table statistics to be regenerated for each table It’s possible I…

Post: INSERT INTO ... SELECT Performance with Innodb tables.

Innodb tables probably got use to the fact Innodb tables perform non locking reads, meaning unless you use… statement and possibly result in different data than on master. Locking rows in the source table while …(it has to be done outside of MySQL Server) as otherwise the script will fail second time…

Post: MySQL Server Memory Usage

… more important “theoretically possible” maximum it provides have nothing to do with real memory consumptions. In fact typical server with 8GB of… have very large memory consumption Innodb Table Cache. Innodb has its own table cache in which meta data about each table accessed from the start…

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

InnoDB* table with a single VARCHAR column named “value”. OK, sounds easy enough: mysql: SHOW CREATE TABLE innodb_myisam_stopword\G *************************** 1. row *************************** Table: innodb

Post: Heikki Tuuri answers to Innodb questions, Part II

… dedicated server is: set innodb_flush_method=O_DIRECT in my.cnf, to prevent double buffering. Configure as much memory as possible to InnoDB…. Q37: We have a fairly large Innodb table (150GB) that is showing poor performance for full table scans. using O_DIRECT, we are… for Heikki – it should be possible to implement online reorganize which locks and reorganizes small portions of table online. Q38: Seems that…

Post: Real-Life Use Case for "Barracuda" InnoDB File Format

… recent posts Vadim already gave some information about possible benefits from using new InnoDB file format but in this post I’d… table customer has on this server is one huge innodb table with a set of TEXT fields. We’ve been trying to optimize this server before by playing with various innodb parameters…

Post: MySQL Users Conference - Innodb

Innodb user when I found out ALTERing Innodb tables may take a lot of time. Fast Index creation will allow Innodb…to watch out for. So these are all possibilities but are we expected to have the …Innodb IO settings were optimized on systems with 1-2 SATA drives so they are far from optimal for server

Post: Improved InnoDB fast index creation

… Percona Server supports the new option –innodb-optimize-keys which does just that, i.e. it tries to optimize dumps of InnoDB tables by first creating the table with only the clustered index and adding the secondary indexes after the data dump when possible

Post: Innodb vs MySQL index counts

… an Innodb table make a copy of the .frm file do an ALTER TABLE to…the table was opened and not on every table access.  So, it was a possibilityInnodb Data dictionary. The first recommendation I got was to simply use the INFORMATION_SCHEMA.INNODB_SYS_INDEXES table, which exists in Percona Server

Post: MySQL 5.5.8 and Percona Server: being adaptive

… an optimization for hard drives, since InnoDB tries to combine writing as many pages as possible into a single sequential write, which…-tables server_id=1 local_infile=1 datadir=/mnt/fio320 innodb_buffer_pool_size=24G innodb_data_file_path=ibdata1:10M:autoextend innodb_file_per_table