Time-based Flushing: This wouldn't seem like it should be a separate category, but because the behavior seems to be so different between the two cache solutions, I'm listing it here. The vCache manual indicates that "flush period" specifies the time after which dirty blocks will be written to the backing store, whereas FlashCache has a setting called "fallow_delay", defined in the documentation as the time period before "idle" dirty blocks are cleaned …
…65481 explains that the HUP signal also flushes tables in addition to logs. Flushing tables can impact running queries.
Disable MySQL slow logs during rotation
Flushing logs takes time. Meanwhile, queries are still being executed. To prevent MySQL from filling the slow log buffer, we disable the MySQL slow logs temporarily during log rotation.
Putting it all together
Here is a logrotate configuration file for a slow log that illustrates these best practices: /var/mysql/slow_query.log …
- Nikos D.: Fixing 'SQL exception: Incorrect string value' on TeamCity with MySQL database
- Scout ~ The Blog: Making Scout feel young again: our 4-part tonic
- Videos matching: rubyonrails: How to Install Ruby on Rails 1.9.3 on Windows 7 with RadRail and MySQL Server
- High Scalability: Stuff The Internet Says On Scalability For July 27, 2012
- Brightbox Ruby Blog: New Relic Agent vulnerability
- Global Nerdy: Further Into FizzBuzz!
Automatic Flushing: The Rails 3.1 Plan
Yehuda Katz , a member of the Ruby On Rails core team, put this great post together on one of the performance optimizations that the team is looking at implementing in Rails 3.1 - specifically, automatic flushing. This follow up post from hemju , helps explain what flushing is and provides a few graphs that demonstrate its impact:
Flushing is a technique that basically streams an HTTP response. So instead …
- Riding Rails - home: Rails 3.0: It's ready!
- EdgeCase - Home: Obtiva Craftsman Swap, Day Five
- Brightbox Ruby Blog: Rails 3 has landed!
- Bamboo Blog: Ruby divide at RubyKaigi 2010 and what can you do as a Rubyist? (Part 1)
- Trevor Turk: Links for 9-19-10
- Katz Got Your Tongue?: Threads (in Ruby): Enough Already
Automatic Flushing: The Rails 3.1 Plan
- Topics: Ruby on Rails
- MySQL Performance Blog: Why Swapping is bad for MySQL Performance ?
- Improvement to Buffer Pool Flushing
- Subquery Optimizations
- More efficient Optimizer
Replication
- Optimized ROW Based Replication
- Multi-Threaded Slave
- Global Transaction Identifiers
- Crash Safe Slave and Binlog
- Replication Event Checksums
- Time Delayed Replication
- Server UUID
- Improved Logging for Row based Replication
- Replication Utilities for Failover and Admin
Transparency
- Many new INFORMATION SCHEMA Tables
- Axon Flux // A Ruby on Rails Blog: Impressive tech talk on Scaling MySQL at Facebook
- High Scalability: Facebook at 13 Million Queries Per Second Recommends: Minimize Request Variance
- A Fresh Cup: Double Shot #578
- Pennysmalls: Much faster Rails tests if you use MyISAM
- igvita.com: Schema-Free MySQL vs NoSQL
- Junior developer: Rails test data not getting rolled back
We raised topic of problems with flushing in InnoDB several times, some links:
InnoDB Flushing theory and solutions
MySQL 5.5.8 in search of stability
This was not often recurring problem so far, however in my recent experiments, I observe it in very simple sysbench workload on hardware which can be considered as typical nowadays.
Hardware: HP ProLiant DL380 G6 , with 72GB of RAM and RAID10 on 8 disks.
I took sysbench multi-tables workload, with 20 tables, …
- MySQL Performance Blog: Adaptive flushing in MySQL 5.6
- MySQL Performance Blog: Looking for InnoDB/XtraDB hacker
- On the Path: Tuning MySQL
- Simple thoughts - articles: The Internal Storage Mechanism For Globalize for Rails Model Translations
- A Fresh Cup: Double Shot #578
- oldmoe: 101 Reasons Why PostgreSQL is a better fit for Rails than MySQL
…usually not much additional work. It's ensuring consistency and durability that is expensive. Flushing it to disk adds an fsync call for every transaction. And the server performs an XA transaction between InnoDB and the binary log. This adds more fsync calls, and causes mutex contention, and prevents group commit, and probably other things that aren't coming to mind now.
The performance reduction can be an order of magnitude or more.
What's the solution? I'm …
- Simple thoughts - articles: The Internal Storage Mechanism For Globalize for Rails Model Translations
- Rails Envy » Home: RailsLab: Scaling Your Database - Part 2
- igvita.com: Measuring & Optimizing I/O Performance
- Junior developer: Rails test data not getting rolled back
- JJinuxLand: MySQL: Disk Usage Can Never Shrink When Using InnoDB
- Signal vs. Noise: Keeping your slave warm
…single entry level hard drive to tens of thousands of writes/sec for high end SSD card. Flushing can be done using multiple threads (in XtraDB and Innodb Plugin at least) so it scales well with multiple hard drives. The second important variable is your workload, especially how dirty pages would line up on the hard drive. If there are a lot of sequential pages which are dirty Innodb will be able to use larger size IOs - up to 1MB flushing dirty pages which can be a lot faster …
- MySQL Performance Blog: The relationship between Innodb Log checkpointing and dirty Buffer pool pages
- Pennysmalls: Much faster Rails tests if you use MyISAM
- A Fresh Cup: Double Shot #578
- JJinuxLand: MySQL: Disk Usage Can Never Shrink When Using InnoDB
- Rails Envy » Home: RailsLab: Scaling Your Database - Part 2
- Simple thoughts - articles: The Internal Storage Mechanism For Globalize for Rails Model Translations
…get a list of tables and columns in the current database for tab-completion. Note also that " Flushing tables" is a misnomer -- connection 5 is not flushing tables yet. It's waiting to get the lock.
Flushing tables
After the FLUSH TABLES WITH READ LOCK command finally acquires the lock, it must begin flushing data. This does not apply to all storage engines. However, MyISAM does not attempt to flush its own data to the disk during normal processing. It relies on the operating …
- Simple thoughts - articles: The Internal Storage Mechanism For Globalize for Rails Model Translations
- Rails Envy » Home: RailsLab: Scaling Your Database - Part 2
Flushing dirty pages or even discarding the page will cause extra IO slowing things down.
Skewing up all Algorithms The database internals algorithms are tuned for things being in memory and if they start dealing with data which is on disk they just often stop working with any reasonable level of efficiently - when database deals with on disk data it often uses different set of algorithms which are optimized to limit number of IOs or make them more sequential. Most of these were designed …