サクサク読めて、アプリ限定の機能も多数!
トップへ戻る
体力トレーニング
yoshinorimatsunobu.blogspot.com
In MySQL ecosystems, it has been a very common practice to shard MySQL databases by application keys, and to manage multiple small sized MySQL instances. Main reason of the sharding was that a single server could not handle so much data. 10 years ago, typical commodity servers had only 16GB RAM, and typical storage configuration was RAID10 with 6~12 SATA HDDs. There was no affordable flash storage
Long time ago I wrote a blog post that we started using Semi-Synchronous replication in production at Facebook. We are running our own MySQL facebook-5.6 branch, which added many replication (including Semisync) patches. Some of them were also merged with WebScaleSQL. Recently I heard from people from community that they were suffering from Semisync performance issues in official MySQL 5.6. I took
After intensive testing and hack, we started using Semi-Synchronous MySQL Replication at Facebook production environments. Semi-Synchronous Replication itself was ready since MySQL 5.5 (GA was released 3.5 years ago!), but I'm pretty sure not many people have used in production so far. Here are summary of our objective, enhancements and usage patterns. If you want to hear more in depth, please fee
I released MHA version 0.56 today. Downloads are available here. MHA 0.56 includes below features. Supporting MySQL 5.6 GTID. If GTID and auto position is enabled, MHA automatically does failover with GTID SQL syntax, not using traditional relay log based failover. You don't need any explicit configuration within MHA to use GTID based failover. Supporting MySQL 5.6 Multi-Threaded slave Supporting
At Percona Live, Steaphan Greene and I will talk about MySQL 5.6 and WebScaleSQL at Facebook. 2 April 1:20PM - 2:10PM @ Ballroom E http://www.percona.com/live/mysql-conference-2014/sessions/mysql-56-facebook-2014-edition In addition to that, I have two more talks this year. Performance Monitoring at Scale 3 April 2:00PM - 2:50PM @ Ballroom G http://www.percona.com/live/mysql-conference-2014/sessio
There is a relatively new and platform dependent flushing function called sync_file_range(). Some databases (not MySQL) use sync_file_range() internally. Recently I investigated stall issues caused by buffered write and sync_file_range(). I learned a lot during investigation but I don't think these behaviors are well known to the public. Here I summarize my understandings. Understanding difference
Many people think buffered write (write()/pwrite()) is fast because it does not do disk access. But this is not always true. Buffered write sometimes does disk access by itself, or waits for some disk accesses by other threads. Here are three common cases where write() takes longer time (== causing stalls). 1. Read Modify Write Suppose the following logic. Opening aaa.dat without O_DIRECT/O_SYNC,
At Facebook, we have upgraded most of MySQL database tiers to 5.6, except very few tiers that have a special requirement -- very fast single threaded replication speed. As Oli mentioned, single threaded performance is worse in 5.6. The regression is actually not visible in most cases. For remote clients, the performance regression is almost negligible because network latency is longer than 5.1->5.
At MySQL Connect 2013, I talked about how we used MySQL 5.6 at Facebook, and explained some of new features we added to our Facebook MySQL 5.6 source tree. In this post, I'm going to talk about how we made full table scan faster in InnoDB. Faster full table scan in InnoDB In general, almost all queries from applications are using indexes, and reading very few rows (0..1 on primary key lookups and
Here is another micro-benchmark to prove that Performance Schema overhead is so serious. non-persistent connections from remote machine, 100 concurrency, MySQL 5.6.13 performance_schemaconnections per second 039525.62 1(default)26304.53 Benchmark command is the same as what I published long time ago. I understand almost all applications don't need 20000~30000 connections per second per instance, b
This year I have two sessions and one panel discussion. - Practical failover design - automated, semi-automated and manual failover (24 April 11:10am - 12:00pm) - Practices for reducing MySQL database size (25 April 12:50pm - 13:40pm) - MySQL at Facebook: lots and lots of small data (25 April 1:50pm - 2:40pm, together with Mark, Harrison and Domas) At HA session I'll speak about lessons learned at
One of my favorite performance improvements in 5.6 is "faster non-persistent connections". I couldn't find this information from release notes, but non-persistent connection is really faster in 5.6. Here are sysbench micro-benchmark results. I tested from 100 concurrent clients, running remotely. Clients simply connected and disconnected repeatedly. versionconnections per second 5.1.6520712 5.5.28
I was asked from a few people about my opinion of the Github's recent service outage. As a creator of MHA, I have lots of MySQL failover experiences. Here are my points about failover design. Most of them duplicate with Robert's points. - "Too Many Connections" is not a reason to start automated failover - Do not repeat failover I know some unsuccessful failover stories that "1. failover happens b
In the recent few weeks I have spent some time for creating yet another slave prefetching tool named "Replication Booster (for MySQL)", written in C/C++ and using Binlog API. Now I'm happy to say that I have released an initial version at GitHub repository. The objective of Replication Booster is same as mk-slave-prefetch: avoiding or reducing replication delay under disk i/o bound workloads. This
I'm excited to see the new features in MySQL 5.6.3. Replication enhancements such as parallel SQL threads, crash safe slave and binlog checksum have been desired for years. I really appreciate that MySQL development team has released 5.6.3 in timely manner. In this blog entry, I'd like to pick up one of my most favorite performance enhancements in MySQL 5.6.3: "network performance improvements". T
I'm happy to announce that MHA for MySQL (Master High Availability Manager and tools for MySQL) version 0.52 has been released. Downloads are available here. This is a stable release. I have been using MHA on our(DeNA's) cross-datacenter environments. We had tens of failover opportunities and MHA worked successfully in all cases. Here are release notes. * multi-master configuration is supported Se
After publishing MySQL MHA in the end of July, I received a few requests for supporting multi-master configuration. So I spent time for extending MHA for supporting multi-master, and now it's published at a separated GitHub branch (a new development tree). I'll take some time for people to evaluate the new feature, and after verifying it's stable enough, I'll merge it to the main branch and will r
I have published "MySQL MHA" that fully automates MySQL master failover. You can also get commercial support from SkySQL. Let's try MHA today! Today I'm happy to announce that I have released MySQL-MHA: MySQL Master High Availability manager and tools as an open source software (GPL v2 license). The below is a part of documentation of MHA. I'm glad if you are interested in MHA. A primary objective
A few days ago Oracle offered me a position of Oracle ACE Director, and I accepted it. Thanks Oracle. I'm glad to see that Oracle has evaluated my activities and we can have a good relationships each other. Oracle has been great for MySQL so far and I'd be happy to keep contributions. Since I live in Tokyo and my native language is Japanese, I usually do community activities in Japanese (speaking
Some kinds of large tables such as chat messages, blog entries, etc have the following characteristics. * huge number of records, huge data and index size * insert and select mostly * select from only recent data * select by secondary index (i.e. user_id) * secondary index entries are inserted in random order What are optimal ways to handle these tables? The below single large table does not perfo
Sometimes I want to trace transactions that have taken long time to execute. If a transaction is holding row locks for 30 seconds, all other clients requesting locks for the same rows will be blocked for up to 30 seconds (or will get Lock Wait Timeout errors if you set innodb_lock_wait_timeout lower). This is serious. The important step to solve such issues is identifying what kinds of queries are
I am excited to see that MySQL 5.5 GA (5.5.8) has been finally released . All of my ex-colleagues at MySQL/Oracle have done amazing jobs. I was also positively surprised that 5.5 was released on schedule:). So far I have felt that Oracle is leading MySQL development and product management very well. 5.5 has lots of practical features not only for web services providers, but also for enterprise use
I have seen that 1 to 1 relationship is sometimes used for MySQL(InnoDB) to avoid significant performance slowdown. To understand the performance difference, it is necessary to understand how InnoDB stores column values to data blocks. Suppose you have the following "diary" table (for storing blog entries).CREATE TABLE diary ( diary_id INT UNSIGNED AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, po
UPDATE: Oracle officially released memcached daemon plugin that talks with InnoDB. I'm glad to see that NoSQL+MySQL has become an official solution. It's still preview release but will be very promising. Let's try it to make it better! Most of high scale web applications use MySQL + memcached. Many of them use also NoSQL like TokyoCabinet/Tyrant. In some cases people have dropped MySQL and have sh
A couple of weeks ago, at a MySQL study group in Tokyo I presented about various kinds of MySQL hacking techniques, such as debugging with MS Visual Studio or gdb, tracing with DTrace, writing information schema plugins, accessing tables from UDF and storage engine API, and extending MySQL server code itself. This 90-minute session went successful. Nearly 100 people attended, including famous MySQ
iostat -x is very useful to check disk i/o activities. Sometimes it is said that "check %util is less than 100%" or "check svctm is less than 50ms", but please do not fully trust these numbers. For example, the following two cases (DBT-2 load on MySQL) used same disks (two HDD disks, RAID1) and reached almost 100% util, but performance numbers were very different (no.2 was about twice as fast as n
Writing small volume of data (Bytes-MBs) with sync (fsync()/fdatasync()/O_SYNC/O_DSYNC) is very common for RDBMS and is needed to guarantee durability. For transactional log files, sync happens per commit. For data files, sync happens at checkpoint etc. Typically RDBMS does syncing data very frequently. In this case, overwriting is much faster than appending for most filesystems/storages. Overwrit
I recently did a disk bound DBT-2 benchmarking on SSD/HDD (MySQL 5.4.0, InnoDB). Now I'm pretty confident that storing tables on SSD, redo/Binlog/SYSTEM-tablespace on HDD will be one of the best practices for the time being. This post is a detailed benchmarking report. (This post is very long and focusing on InnoDB only. If you are familiar with HDD/SSD/InnoDB architecture and understand what my b
次のページ
このページを最初にブックマークしてみませんか?
『Yoshinori Matsunobu's blog』の新着エントリーを見る
j次のブックマーク
k前のブックマーク
lあとで読む
eコメント一覧を開く
oページを開く