I've been working on the CIS security benchmark for Oracle MySQL
5.6. There already is a benchmark document for earlier versions
of MySQL.
The benchmark document helps you to ensure you have a secure
configuration for MySQL.
Center for Internet Security (CIS) is a nonprofit
organization.
The Call For Participation is now open.
If you are already a CIS member then you can download the
document here. If you don't have an account you can
register for free.
This is the next chapter of the story started in my previous article and related to the updated results on LinkBench workload
published by MariaDB..
Keeping in mind that the obtained results are completely opposite
from both sides, I've started to investigate then the same
LinkBench-150GB 64 concurrent users workload from the "most
favorable" possible test conditions on the same 40cores-HT server
as in my previous article:
- InnoDB Buffer Pool (BP) = 150G (so, all the data may remain in memory)
- innodb_flush_log_at_trx_commit=2 (so, REDO write are not flushed once per second and not on every transaction)
- no …
MySQL upgrades are necessary tasks and we field a variety of questions here at Percona Support regarding MySQL upgrade best practices. This post highlights recommended ways to upgrade MySQL in different scenarios.
Why are MySQL upgrades needed? The reasons are many and include: Access to new features, performance benefits, bug fixes…. However, MySQL upgrades can be risky if not tested extensively beforehand with your application because the process might break it, prevent the application from functioning properly – or performance issues could arise following the upgrade. Moreover, I suggest keeping an eye on new releases of MySQL and Percona Server – check what has changed in the …
[Read more]At Twitter, MySQL is an important part of our storage story. We can have a typical database node with thousands of open connections. It works mostly fine because generally only a few of these connections actively working inside the server. Given the scale at which we have to operate spikes happen – mostly at unexpected times and with unexpected ferocity. The challenge is how to control concurrency within MySQL server during spikes.
Historically we have been using innodb_thread_concurrency to control number of active threads within the server. In Twitter MySQL-5.5 we introduced the idea of Query Throttling where incoming requests are throttled if the number of active threads goes beyond a configurable threshold. These solutions focus on maintaining the health of the system. However, they don’t help us to scale to the workload spikes. With …
[Read more]So, I’ve been looking around for a while (and a few times now) for any good resources that cover a bunch of MySQL architecture and technical details aimed towards the technically proficient but not MySQL literate audience. I haven’t really found anything. I mean, there’s the (huge and very detailed) MySQL manual, there’s the MySQL Internals manual (which is sometimes only 10 years out of date) and there’s various blog entries around the place. So I thought I’d write something explaining roughly how it all fits together and what it does to your system (processes, threads, IO etc).(Basically, I’ve found myself explaining this enough times in the past few years that I should really write it down and just point people to my blog). I’ve linked to things for more reading. You should probably …
[Read more]It’s been a little while since I blogged on MySQL on POWER (last time was thinking that new releases would be much better for running on POWER). Well, I recently grabbed the MySQL 5.6.20 source tarball and had a go with it on a POWER8 system in the lab. There is good news: I now only need one patch to have it function pretty flawlessly (no crashes). Unfortunately, there’s still a bit of an odd thing with some of the InnoDB mutex code (bug filed at some point soon).
But, with this one patch applied, I was getting okay sysbench results and things are looking good.
Now just to hope the MySQL team applies my other patches that improve things on POWER. To be honest, I’m a bit disappointed many of them have sat there for this long… it doesn’t help build a development community when patches can sit for months without either …
[Read more]Today we released TokuDB® v7.5, the latest version of Tokutek’s storage engine for MySQL and MariaDB.
I’ll be publishing two blogs next week to go into more details about our new “Read Free Replication”, but here are high level descriptions of the most important new features.
- Read Free Replication
- TokuDB replication slaves can now be configured to process the binary logs with virtually no read IO. This is accomplished via two new server parameters: one to allow the skipping of uniqueness checks (for inserts and updates), the other to eliminate read-before-write behavior (for updates and deletes). The two other conditions are that the slave must be in read-only mode and replication must be row based.
- Hot Backup Now Supports Multiple Directories (Enterprise Edition)
- The original implementation of our Hot Backup functionality was only capable of …
Over the past few months, I’ve seen an increase in the following use case while working on performance and schema review engagements:
I need to store exponentially increasing amounts of data and analyze all of it in real-time.
This is also known simply as: “We have big data.” Typically, this data is used for user interaction analysis, ad tracking, or other common click stream applications. However, it can also be seen in threat assessment (ddos mitigation, etc), financial forecasting, and other applications as well. While MySQL (and other OLTP systems) can handle this to a degree, it is by no means a forte. Some of the pain points include:
- Cost of rapidly increasing, expensive disk storage (OLTP disks need to be fast == $$)
- Performance decrease as the data size increases …
September 18, 2014 By Severalnines
Galera Cluster is a popular choice for achieving high availability using synchronous replication. Though if you are planning to run huge sites with many DB objects (tables), a few tweaks are necessary.
Yes, you might have been successful in loading your 1000s of databases and 1000s of tables, but what happens if you have a node failure and Galera recovery fails?
In this blog post we will show you how to determine one common error related to the open_files_limit that MySQL imposes, and also to spot another potential pitfall.
Open_files_limit
If you are using wsrep_sst_method=xtrabackup or wsrep_sst_method=xtrabackup-v2 then you will find a log file in the data directory of the donor node. This log file is called innobackup.backup.log.
140912 19:10:15 innobackupex: Done. …[Read more]
I recently encountered a situation in which after running Percona Toolkit’s pt-table-checksum on a customer system, 95% of the table on the MySQL master was different on the MySQL slave. Although this table was not a critical part of the infrastructure, from time to time, writes to the table from the master would break replication. Additionally, this table has about 6 million rows, and running pt-table-sync would take sometime. Rebuilding the slave from backup of the master would not be an easy option as well since the slave acts as an archive where it has a lot more data than the master.
So how did we solve it? With pt-online-schema-change and a NOOP ALTER.
pt-online-schema-change --alter 'ENGINE=INNODB' D=dbname,t=tblname
How is it …
[Read more]