June 18, 2013

Post: Improved InnoDB fast index creation

… important implications: when the data does not fit in the buffer pool, fast index creation provides even better performance as compared to… pages to the buffer pool. A benchmark is worth a thousand words, so let’s repeat the last test with innodb_buffer_pool_sizeset to…, a small buffer pool had a huge impact on ALTER TABLE with the optimization disabled, and absolutely no effect on the optimized case, which…

Post: Choosing innodb_buffer_pool_size

My last post about Innodb Performance Optimization got a lot of comments choosing proper innodb_buffer_pool_size and indeed I oversimplified things a bit… other needs for Innodb Buffer Pool. This of course assumes your database is large so you need large buffer pool, if not – setting buffer pool a bit…

Post: Is there room for more MySQL IO Optimization?

….5.28-29.1 on it with 6GB allocated for innodb_buffer_pool which with other memory needs leaves roughly 1GB for OS… | 2×2048 KB I have and old table which was not optimized for prolonged period of time, so it reflect natural state… 30GB in size including indexes which is way more than buffer pool size and OS cache which is available. I run CHECK…

Post: How well does your table fits in innodb buffer pool ?

innodb_buffer_pool_pages_index GROUP BY index_id) bp JOIN innodb_sys_indexes ON id = index_id JOIN innodb_sys_tables ON table_id = innodb… tune buffer pool invalidation strategy, for example play with innodb_old_blocks_pct and innodb_old_blocks_time actually observing data stored in buffer pool rather… warmed up first as well as how buffer pool is affected by batch jobs, alter tables, optimize table etc – the lasting impact these…

Post: MySQL Users Conference - Innodb

… did number of interesting benchmarks stress testing other areas in Innodb than buffer-pool which had it locking relaxed in latest MySQL 5… patches to make Innodb IO scheduling more aggressive which is important for serious IO subsystems. Many Innodb IO settings were optimized on systems…

Post: Innodb Double Write

… term log file allocated inside Innodb tablespace – it contains space for 100 pages. When Innodb flushes pages from Innodb buffer pool it does so by… writes are executed and use multiple devices in parallel. This optimization could be used without doublewrite though, it was just implemented…

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

… here is to note that the InnoDB buffer pool for my 5.6 instance is 128MB…innodb‘; mysql> set global innodb_optimize_fulltext_only=1; mysql> select * from information_schema.innodb_ft_config; +—————————+——–+ | KEY | VALUE | +—————————+——–+ | optimize

Post: How InnoDB handles REDO logging

… double write buffer to ensure consistent page writes. Why log? Since InnoDB tries to keep the working set in memory (InnoDB Buffer Pool), therefore the… background processing of dirty pages can then apply algorithms to optimize those writes during checkpoint. What else? There is of course…

Post: Read Buffers, mmap, malloc and MySQL Performance

buffers are allocated from the pools rather than mmap. This though will at least increase memory consumptions as deallocating of these large buffers… use large pages. These already can be used for Innodb Buffer Pool and Key Buffer but could be used for other large allocations. Finally… terms of which buffer size should be used, even for sake of speeding up the reads and more optimal resource allocation – if…

Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1

…=128 MyISAM / InnoDB: libexec/mysqld –no-defaults –user=root –key-buffer-size=1500M –innodb-buffer-pool-size=1500M –innodb-log-file-size=100M –innodb-thread-concurrency=8… scales pretty bad and there is a big room for optimization. READ_PK_RANGE Query: SELECT min(dob) FROM $tableName WHERE…