-
Fun with Bugs #45 - On Some Bugs Fixed in MySQL 5.7.15
Oracle released MySQL 5.7.15 recently, earlier than expected. The reason for this "unexpected" release is not clear to me, but it could happen because of a couple of security related internal bug reports that got fixed:"It was possible to write log files ending with .ini or .cnf that later could be parsed as option files. The general query log and slow query log can no longer be written to a file ending with .ini or .cnf. (Bug #24388753) Privilege escalation was possible by exploiting the way REPAIR TABLE used temporary files. (Bug #24388746)"Let me concentrate on the most important fixes to bugs and problems reported by Community users. First of all, in MySQL 5.7.15 one can just turn off InnoDB deadlock detection using the new innodb_deadlock_detect dynamic server variable. Domas had explained the positive effect of this more than 6 years ago in his post. Some improvements to the way deadlock detection worked in MySQL happened in frames of fix for the Bug #49047 long time ago, but this time Oracle just implemented a way to disable check and rely on InnoDB lock wait timeout instead.Other InnoDB-related fixes to problems reported in public bugs database include:Bug #82073 - "Crash with InnoDB Encryption, 5.7.13, FusionIO & innodb_flush_method=O_DIRECT". It was reported by my colleague from MariaDB, Chris Calender, and verified by other my colleague from MariaDB, Jan Lindström. Probably Bugs Verification Team in Oracle just had no access to proper hardware to verify this.Bug #79378 - "buf_block_align() makes incorrect assumptions about chunk size". This bug was reported by Alexey Kopytov, who had provided a patch.There were several fixes to replication-related bugs:Bug #81675 - "mysqlbinlog does not free the existing connection before opening new remote one". It was reported by Laurynas Biveinis from Percona, who had also provided a patch, and verified by Umesh.Bug #80881 - "MTR: binlog test suite failed to cleanup (contribution)". This fix to the binlog test suit was contributed by Daniel Black and verified by Umesh.Bug #79867 - "unnecessary using temporary for update". This bug was reported by Zhang Yingqiangwho had also contributed a patch (that was not used after all, according to the comment from Oracle developer). It was verified by Umesh. Some more bugs from other categories were also fixed:Bug #82125 - "@@basedir sysvar value not normalized if set through the command line/INI file". It was reported by Georgi Kodinov from Oracle. It's funny that there is a typo in the release notes when this fix is described (pay attention to slashes):"If the basedir system variable was set at server startup from the command line or option file, the value was not normalized (on Windows, / was not replaced with /)"Bug #82097 is private. I can not say anything about it in addition to this:"kevent statement timer subsystem deinitialization was revised to avoid a mysqld hang during shutdown on OS X 10.12."I can repeat, though, my usual statement that in most cases making bugs private is a wrong thing to do. I feel myself personally insulted every time when I see that fixed bug report remains private.Bug #81666 - "The MYSQL_SERVER define not defined du to spelling error in plugin.cmake". It was reported by Magnus Blåudd who had provided a patch also.Bug #81587 - "Combining ALTER operations triggers table rebuild". This bug was reported by Daniël van Eeden and verified by Umesh.Bug #68972 - "Can't find temporary table". This bug (that could happen in a stored procedure or when prepared statements are used) was reported by Cyril Scetbon and verified by Miguel Solorzano.Bug #82019 - "Is client library supposed to retry EINTR indefinitely or not". It was reported by Laurynas Biveinis from Percona, who had also contributed patches later. This bug was verified formally by Sinisa Milivojevic.To summarize, you should consider upgrade to MySQL 5.7.15 for sure if you use FusionIO or want to be able to disable InnoDB deadlock detection entirely, or if you consider security-related fixes in this release really important (I don't). Otherwise just check other fixes that could impact you positively, or just wait for 5.7.16...
-
Don’t Spin Your Data, Use SSDs!
This blog post discussed the advantages of SSDs over HDDs for database environments.
For years now, I’ve been telling audiences for my MySQL Performance talk the following: if you are running an I/O-intensive database on spinning disks you’re doing it wrong. But there are still a surprising number of laggards who aren’t embracing SSD storage (whether it’s for cost or reliability reasons).
Let’s look at cost first. As I write this now (September 2016), high-performance server-grade spinning hard drives run for about $240 for 600GB (or $0.40 per GB). Of course, you can get an 8TB archive drive at about same price (about $0.03 per GB), but it isn’t likely you’d use something like that for your operational database. At the same time, you can get a Samsung 850 EVO drive for approximately $300 (or $0.30 per GB), which is cheaper than the server-grade spinning drive!
While it’s not the best drive money can buy, it is certainly an order of magnitude faster than any spinning disk drive!
(I’m focusing on the cost per GB rather than the cost of the number of IOPS per drive as SSDs have overtaken HDDs years ago when it comes to IOPS/$.)
If we take a look at Amazon EBS pricing, we will find that Amazon has moved to SSD volumes by default as “General Purpose” storage (gp2). Prices for this volume type run about 2x higher per GB than high-performance HDD-based volumes (st1) and provisioned IOPs volumes. The best volumes for databases will likely run you 4x higher than HDD.
This appears to be a significant cost difference, but keep in mind you can get much more IOPS at much better latency from these volumes. They also handle IO spikes better, which is very important for real workloads.
Whether we’re looking at a cloud or private environment, it is wrong just to look at the cost of the storage alone – you must look at the whole server cost. When using an SSD, you might not need to buy a RAID card with battery-backed-up (BBU) cache, as many SSDs have similar functions built in.
(For some entry-level SSDs, there might be an advantage to purchasing a RAID with BBU, but it doesn’t affect performance nearly as much as for HDDs. This works out well, however, as entry level SSDs aren’t going to cost that much to begin with and won’t make this setup particularly costly, relative to a higher-end SSD.)
Some vendors can charge insane prices for SSDs, but this is where you should negotiate and your alternative vendor choice powers.
Some folks are concerned they can’t get as much storage per server with SSDs because they are smaller. This was the case a few years back, but not any more. You can find a 2TB 2.5” SSD drive easily, which is larger than the available 2.5” spinning drives. You can go as high as 13TB in the 2.5” form factor
There is a bit of challenge if you’re looking at the NVMe (PCI-E) cards, as you typically can’t have as many of those per server as you could using spinning disks, but the situation is changing here as well with the 6.4TB SX300 from Sandisk/FusionIO or the PM1725 from Samsung. Directly attached storage provides extremely high performance and 10TB-class sizes.
To get multiple storage units together, you can use hardware RAID, software RAID, LVM striping or some file systems (such as ZFS) can take care of it for you.
Where do we stand with SSD reliability? In my experience, modern SSDs (even inexpensive ones) are pretty reliable, particularly for online data storage. The shelf life of unpowered SSDs is likely to be less than HDDs, but we do not really keep servers off for long periods of time when running database workloads. Most SSDs also do something like RAID internally (it’s called RAIN) in addition to error correction codes that protect your data from a full single flash chip.
In truth, focusing on storage-level redundancy is overrated for databases. We want to protect most critical applications from complete database server failure, which means using some form of replication, storing several copies of data. In this case, you don’t need bulletproof storage on a single server – just a replication setup where you won’t lose the data and any server loss is easy to handle. For MySQL, solutions like Percona XtraDB Cluster come handy. You can use external tools such as Orchestrator or MHA to make MySQL replication work.
When it comes to comparing SSD vs. HDD performance, whatever you do with SSDs they will likely still perform better than HDDs. Your RAID5 and RAID6 arrays made from SSDs will beat your RAID10 and RAID0 made from HDDs (unless your RAID card is doing something nasty).
Another concern with SSD reliability is write endurance. SSDs indeed have a specified amount of writes they can handle (after which they are likely to fail). If you’re thinking about replacing HDDs with SSDs, examine how long SSDs would endure under a comparable write load.
If we’re looking at a high HDD write workload, a single device is likely to handle 200 write IOPS of 16KB (when running InnoDB). Let’s double that. That comes to 6.4MB/sec, which gives us 527GB/day (doing this 24/7). Even with the inexpensive Samsung 850 Pro we get 300TB of official write endurance – enough for 1.5 years. And in reality, drives tend to last well beyond their official specs.
If you don’t like living on the edge, more expensive server-grade storage options have much better endurance. For example, 6.4TB SX300 offers almost 100x more endurance at 22 Petabytes written.
In my experience, people often overestimate how many writes their application performs on a sustained basis. The best approach is to do the math, but also monitor the drive status with a SMART utility or vendor tool. The tools can alert you in advance when drive wears out.
Whatever your workload is, you will likely find an SSD solution that offers you enough endurance while significantly exceeding the performance of an HDD-based solution.
Finally, there is a third and very important component of SSD reliability for operational database workloads: not losing your data during a power failure. Many “consumer-grade” SSDs come with drive write cache enabled by default, but without proper power loss protection. This means you can lose some writes during a power failure, causing data loss or database corruption.
Disabling write cache is one option, though it can severely reduce write performance and does not guarantee data won’t be lost. Using enterprise-grade SSDs from a reputable vendor is another option, and testing SSDs yourself might be a good idea if you’re on a budget.
Conclusion
When it comes to operational databases, whether your workload is on-premises or in the cloud, Don’t spin your data – use SSD. There are choices and options for almost any budget and every workload.
-
Ansible Dependencies for Docker Containers
I recently had the opportunity to test out Ansible’s ability to interact with docker containers. Some might ask why we would want Ansible to connect to running containers. Afterall, we can build the containers to our liking using ansible-container, or even mundane tools such as Docker’s Dockerfile. Also, we can link configuration files at runtime to override the container’s settings where appropriate.The point, though, is to leverage Ansible’s capability as an orchestration tool. As a very basic example, assume that you have plays for your non-docker environment to ensure MySQL users exist. How do you do that with Docker containers?You have a few options:Assume you have users with appropriate privileges that can connect remotely, you can execute the Ansible plays locally to connect to MySQL over the Docker network. You might need a few tweaks to the playbooks to connect remotely instead of locally and add module dependencies to your Ansible control machine.You might subscribe to the idea of running basic services such as SSH on your Docker containers, so Ansible would be able to connect just the same.If you run Ansible 2.0+, you can leverage Ansible’s docker connection driver, adding to the list of existing drivers such as SSH, winrm, and paramiko.Using the Docker connection driverIn any event, my test images do not have SSH. I try to keep them as slim as possible, or use upstream images such as the officially supported MySQL Docker image.And they do not allow remote login to users with ability to create users and grant privileges.Using the Docker connection driver leverages `docker exec` to connect to Docker containers. To connect to my container, it should be just as simple as specifying the connection driver to be docker with `-c docker`.Unfortunately, this resulted in the following unhelpful error:
$ ansible --version
ansible 2.1.0.0
$ ansible all -i mysqla, -c docker -m command -a 'uptime'
mysqla | FAILED | rc=0 >>
MODULE FAILURE
Verbose logging was not very helpful either:
$ ansible all -i mysqla, -c docker -m command -a 'uptime' -vvvv
Loaded callback minimal of type stdout, v2.0
<mysqla> ESTABLISH DOCKER CONNECTION FOR USER: root
<mysqla> EXEC ['/usr/local/bin/docker', 'exec', '-i', 'mysqla', ...]
<mysqla> PUT ... TO ...
<mysqla> EXEC ['/usr/local/bin/docker', 'exec', '-i', 'mysqla', ...]
mysqla | FAILED | rc=0 >>
MODULE FAILURE
Remember the Dependencies!The answer comes from remembering which dependencies are necessary for Ansible to manage remote hosts: primarily Python. Installing Python on my container results in a successful execution:
root@8425c734000a:/# apt update && apt-get install -y python
$ ansible all -i mysqla, -c docker -m command -a 'uptime'
mysqla | SUCCESS | rc=0 >>
19:25:49 up 9 days, 1:48, 0 users, load average: 0.01, 0.05, 0.02
ConclusionAnsible enables you to orchestrate your Docker containers starting with Ansible 2.0 using the new docker connection driver. This is very useful if you keep your containers slim and don’t run services such as SSH.But regardless of the connection method, if you do keep your containers slim, it is very possible you do not have the basic Ansible dependencies installed. The dependencies are primarily python and any module-specific dependencies required to manage those containers, such as python-mysqldb.Be sure to install them if you want your Ansible plays to be able to execute successfully against your Docker containers. Happy orchestrating!
-
Basic Housekeeping for MySQL Indexes
In this blog post, we’ll look at some of the basic housekeeping steps for MySQL indexes.
We all know that indexes can be the difference between a high-performance database and a bad/slow/painful query ride. It’s a critical part that needs deserves some housekeeping once in a while. So, what should you check? In no particular order, here are some things to look at:
1. Unused indexes
With sys schema, is pretty easy to find unused indexes: use the schema_unused_indexes view.mysql> select * from sys.schema_unused_indexes;
+---------------+-----------------+-------------+
| object_schema | object_name | index_name |
+---------------+-----------------+-------------+
| world | City | CountryCode |
| world | CountryLanguage | CountryCode |
+---------------+-----------------+-------------+
2 rows in set (0.01 sec)This view is based on the performance_schema.table_io_waits_summary_by_index_usage table, which will require enabling the Performance Schema, the events_waits_current consumer and the wait/io/table/sql/handler instrument. PRIMARY (key) indexes are ignored.
If you don’t have them enabled, just execute these queries:update performance_schema.setup_consumers set enabled = 'yes' where name = 'events_waits_current';
update performance_schema.setup_instruments set enabled = 'yes' where name = 'wait/io/table/sql/handler';Quoting the documentation:
“To trust whether the data from this view is representative of your workload, you should ensure that the server has been up for a representative amount of time before using it.”
And by representative amount, I mean representative:
Do you have a weekly job? Wait at least one week
Do you have monthly reports? Wait at least one month
Don’t rush!
Once you’ve found unused indexes, remove them.
2. Duplicated indexes
You have two options here:
pt-duplicate-key-checker
the schema_redundant_indexes view from sys_schema
The pt-duplicate-key-checker is part of Percona Toolkit. The basic usage is pretty straightforward:[root@e51d333b1fbe mysql-sys]# pt-duplicate-key-checker
# ########################################################################
# world.CountryLanguage
# ########################################################################
# CountryCode is a left-prefix of PRIMARY
# Key definitions:
# KEY `CountryCode` (`CountryCode`),
# PRIMARY KEY (`CountryCode`,`Language`),
# Column types:
# `countrycode` char(3) not null default ''
# `language` char(30) not null default ''
# To remove this duplicate index, execute:
ALTER TABLE `world`.`CountryLanguage` DROP INDEX `CountryCode`;
# ########################################################################
# Summary of indexes
# ########################################################################
# Size Duplicate Indexes 2952
# Total Duplicate Indexes 1
# Total Indexes 37Now, the schema_redundant_indexes view is also easy to use once you have sys schema installed. The difference is that it is based on the information_schema.statistics table:mysql> select * from schema_redundant_indexesG
*************************** 1. row ***************************
table_schema: world
table_name: CountryLanguage
redundant_index_name: CountryCode
redundant_index_columns: CountryCode
redundant_index_non_unique: 1
dominant_index_name: PRIMARY
dominant_index_columns: CountryCode,Language
dominant_index_non_unique: 0
subpart_exists: 0
sql_drop_index: ALTER TABLE `world`.`CountryLanguage` DROP INDEX `CountryCode`
1 row in set (0.00 sec)Again, once you find the redundant index, remove it.
3. Potentially missing indexes
The statements summary tables from the performance schema have several interesting fields. For our case, two of them are pretty important: NO_INDEX_USED (means that the statement performed a table scan without using an index) and NO_GOOD_INDEX_USED (“1” if the server found no good index to use for the statement, “0” otherwise).
Sys schema has one view that is based on the performance_schema.events_statements_summary_by_digest table, and is useful for this purpose: statements_with_full_table_scans, which lists all normalized statements that have done a table scan.
For example:mysql> select * from world.CountryLanguage where isOfficial = 'F';
55a208785be7a5beca68b147c58fe634 -
746 rows in set (0.00 sec)
mysql> select * from statements_with_full_table_scansG
*************************** 1. row ***************************
query: SELECT * FROM `world` . `Count ... guage` WHERE `isOfficial` = ?
db: world
exec_count: 1
total_latency: 739.87 us
no_index_used_count: 1
no_good_index_used_count: 0
no_index_used_pct: 100
rows_sent: 746
rows_examined: 984
rows_sent_avg: 746
rows_examined_avg: 984
first_seen: 2016-09-05 19:51:31
last_seen: 2016-09-05 19:51:31
digest: aa637cf0867616c591251fac39e23261
1 row in set (0.01 sec)The above query doesn’t use an index because there was no good index to use, and thus was reported. See the explain output:mysql> explain select * from world.CountryLanguage where isOfficial = 'F'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: CountryLanguage
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 984
Extra: Using whereNote that the “query” field reports the query digest (more like a fingerprint) instead of the actual query.
In this case, the CountryLanguage table is missing an index over the “isOfficial” field. It is your job to decide whether it is worth it to add the index or not.
4. Multiple column indexes order
It was explained before that Multiple Column index beats Index Merge in all cases when such index can be used, even when sometimes you might have to use index hints to make it work.
But when using them, don’t forget that the order matters. MySQL will only use a multi-column index if at least one value is specified for the first column in the index.
For example, consider this table:mysql> show create table CountryLanguageG
*************************** 1. row ***************************
Table: CountryLanguage
Create Table: CREATE TABLE `CountryLanguage` (
`CountryCode` char(3) NOT NULL DEFAULT '',
`Language` char(30) NOT NULL DEFAULT '',
`IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
`Percentage` float(4,1) NOT NULL DEFAULT '0.0',
PRIMARY KEY (`CountryCode`,`Language`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1A query against the field “Language” won’t use an index:mysql> explain select * from CountryLanguage where Language = 'English'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: CountryLanguage
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 984
Extra: Using whereSimply because it is not the leftmost prefix for the Primary Key. If we add the “CountryCode” field, now the index will be used:mysql> explain select * from CountryLanguage where Language = 'English' and CountryCode = 'CAN'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: CountryLanguage
type: const
possible_keys: PRIMARY,CountryCode
key: PRIMARY
key_len: 33
ref: const,const
rows: 1
Extra: NULLNow, you’ll have to also consider the selectivity of the fields involved. Which is the preferred order?
In this case, the “Language” field has a higher selectivity than “CountryCode”:mysql> select count(distinct CountryCode)/count(*), count(distinct Language)/count(*) from CountryLanguage;
+--------------------------------------+-----------------------------------+
| count(distinct CountryCode)/count(*) | count(distinct Language)/count(*) |
+--------------------------------------+-----------------------------------+
| 0.2368 | 0.4644 |
+--------------------------------------+-----------------------------------+So in this case, if we create a multi-column index, the preferred order will be (Language, CountryCode).
Placing the most selective columns first is a good idea when there is no sorting or grouping to consider, and thus the purpose of the index is only to optimize where lookups. You might need to choose the column order, so that it’s as selective as possible for the queries that you’ll run most.
Now, is this good enough? Not really. What about special cases where the table doesn’t have an even distribution? When a single value is present way more times than all the others? In that case, no index will be good enough. Be careful not to assume that average-case performance is representative of special-case performance. Special cases can wreck performance for the whole application.
In conclusion, we depend heavily on proper indexes. Give them some love and care once in a while, and the database will be very grateful.
All the examples were done with the following MySQL and Sys Schema version:mysql> select * from sys.version;
+-------------+-----------------+
| sys_version | mysql_version |
+-------------+-----------------+
| 1.5.1 | 5.6.31-77.0-log |
+-------------+-----------------+
-
MySQL Replication Troubleshooting: Q & A
In this blog, I will provide answers to the Q & A for the MySQL Replication Troubleshooting webinar.
First, I want to thank everybody for attending the August 25 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:
Q: Hi Sveta. One question: how is it possible to get N previous events using the SHOW BINLOG EVENTS command? For example, the position is 999 and I want to analyze the previous five events. Is it possible?
A: Not, there is no such option. You cannot get the previous five events using SHOW BINLOG EVENTS. However, you can use mysqlbinlog with the option --stop-position and tail its output.
Q: We are having issues with inconsistencies over time. We also have a lot of “waiting for table lock” statuses during high volume usage. Would changing these tables to InnoDB help the replicated database remain consistent?
A: Do you use MyISAM? Switching to InnoDB might help, but it depends on what types of queries you use. For example, if you often use the LOCK TABLE command, that will cause a "waiting for table lock" error for InnoDB too. Regarding data consistency between the master and slave, you need to use row-based replication.
Q: For semi-sync replication, what’s the master’s behavior when the master never received ACK from any of the slaves?
A: It will timeout after rpl_semi_sync_master_timeout milliseconds, and then switch to asynchronous replication.
Q: We’re using MySQL on r3.4xlarge EC2 instances (16 CPU). We use RBR. innodb_read_io_threads and innodb_write_io_threads =4. We often experience lags. Would increasing these to eight offer better IO for slaves? What other parameters could boost slave IO?
A: Yes, an increased number of IO threads would most likely improve performance. Other parameters that could help are similar to the ones discussed in “InnoDB Troubleshooting” and “Introduction to Troubleshooting Performance: What Affects Query Execution?” webinars. You need to pay attention to InnoDB options that affect IO (innodb_thread_concurrency, innodb_flush_method, innodb_flush_log_at_trx_commit, innodb_flush_log_at_timeout ) and general IO options, such as sync_binlog .
Q: How many masters can I have working together?
A: What do you mean by “how many masters can [you] have working together”? Do you mean circular replication or a multi-master setup? In any case, the only limitation is hardware. For a multi-master setup you should ensure that the slave has enough resources to process all requests. For circular replication, ensure that each of the masters in the chain can handle the increasing number of writes as they replicate down the chain, and do not lead to permanently increasing slave lags.
Q: What’s the best way to handle auto_increment?
A: Follow the advice in the user manual: set auto_increment_offset to a unique value on each of servers,auto_increment_increment to the number of servers and never update auto-incremented columns manually.
Q: I configured multi threads replication. Sometimes the replication lag keeps increasing while the slave was doing “invalidating query cache entries(table)”. How should I do to fine tune it?
A: The status "invalidating query cache entries(table)" means that the query cache is invalidating entries, and has been changed by a command currently being executed by the slave SQL thread. To avoid this issue, you need to keep the query cache small (not larger than 512 MB) and de-fragment it from time to time using the FLUSH QUERY CACHE command.
Q: Sometimes when IO is slow and during lag we see info: Reading event from the relay log “Waiting for master to send event” — How do we troubleshoot to get more details.
A: The "Waiting for master to send event" state shows that the slave IO thread sent a request for a new event, and is waiting for the event from the master. If you believe it hasn’t received the event in a timely fashion, check the error log files on both the master and slave for connection errors. If there is no error message, or if the message doesn’t provide enough information to solve the issue, use the network troubleshooting methods discussed in the “Troubleshooting hardware resource usage” webinar.
Save
|