Shlomi NoachAnnouncing orchestrator-agent (13.10.2014, 12:59 UTC)

orchestrator-agent is a side-kick, complementary project of orchestrator, implementing a daemon service on one's MySQL hosts which communicates with and accepts commands from orchestrator, built with the original purpose of providing an automated solution for provisioning new or corrupted slaves.

It was built by Outbrain, with Outbrain's specific use case in mind. While we release it as open source, only a small part of its functionality will appeal to the public (this is why it's not strictly part of the orchestrator project, which is a general purpose, wide-audience solution). Nevertheless, it is a simple implementation of a daemon, such that can be easily extended by the community. The project is open for pull-requests!

A quick breakdown of orchestrator-agent is as follows:

  • Executes as a daemon on linux hosts
  • Interacts and invokes OS commands (via bash)
  • Does not directly interact with a MySQL server running on that host (does not connect via mysql credentials)
  • Expects a single MySQL service on host
  • Can control the MySQL service (e.g. stop, start)
  • Is familiar with LVM layer on host
  • Can take LVM snapshots, mount snapshots, remove snapshots
  • Is familiar with the MySQL data directory, disk usage, file system
  • Can send snapshot data from a mounted snapshot on a running MySQL host
  • Can prepare data directory and receive snapshot data from another host
  • Recognizes local/remote datacenters
  • Controlled by orchestrator, two orchestrator-agents implement an automated and audited solution for seeding a new/corrupted MySQL host based on a running server.

Offline, hard-copy backups aside, Outbrain implements quick backups via LVM snapshots. Some slaves (depending on strength, datacenter etc.) take the role of snapshot servers. These slaves take an LVM snapshot once per day, and keep it open unmounted. At any given time we might have multiple open snapshots on a snapshot server. At a few minutes notice, we are able to restore MySQL to yesterday's, the day before, two days before, ... state. This only takes mounting of the desired snapshot and starting MySQL over the mounted volume.

We've in fact put this method to practice in at least one major occasion last year, that proved it to be of significant worth: we were able to fix a compromise to some dataset within minutes of action.

But we also use these snapshots on a daily basis, putting them constantly to practice (hence validating this backup method routinely): we provision new MySQL servers based on those snapshots. A new or corrupted server can be seeded with a snapshot data (preferably from a host in same datacenter). We don't need to perform a backup now. We can take the one from tonight.

There is some fuss around this: need to verify MySQL not running on target host, clean up the target host, choose the best source host, choose the best snapshot on source host, mount it, deliver the data, accept the data, do post-copy cleanup, start the MySQL service on target host, ... Or we might choose to actually do take a fresh, immediate snapshot from a server not configured as snapshot server... We used to do all these by shell scripts, but realized this will not sustain us.

Orchestrator-agent was invented with this particular use case in mind, and orchestrator was extended to support these operations. Orchestrator can act as the controller of a seed/provisioning operation, getting info from/to orchestrator-agent and coordinating two agents to send/receive the data.

The agent still relies on some external commands (via bash) to implement some functionality; to some extent, we have broken down complex shell scripts then wrapped again, now controlled and audited by the agent; but this will slowly change as we move more code into orchestrator-agent itself.

Should you use orchestrator-agent?

If, like us, you use LVM snapshots, at least as a partial form of backup - bingo!

Otherwise, orchestrator-agent can assist in stopping/starting the MySQL service, tailing the error log; simple stuff like that -- probably too much fuss for such basic operations.

But it can be easily extended to support other forms of backup (though do note that orchestrator-agent does not and should not perform scheduled backups, merely manipulate them); so if you like to contribute code into orchestrator-agent, please let us know! It would be nice to have a solution that works for multiple use cases.

Get it

orchestrator-agent is released under the Apache 2.0 license, and is free to use.

Pre-compiled, RPM and deb packages can be found on the releases page.

 

Link
Daniël van EedenDisabling old_passwords=1 (12.10.2014, 09:15 UTC)
It is possible to disallow users from using old_passwords=1. This can be done by adding 'maximum-old_passwords=0' to your my.cnf

This prevents users from generating passwords hashes in pre-4.1 format. In MySQL 5.7 old_passwords=1 is already disabled, so this is only useful on 5.6 and earlier.

Be aware that this also restricts old_passwords=2 which is needed for sha256 hashes.

mysql> select @@old_passwords;
+-----------------+
| @@old_passwords |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.00 sec)

mysql> set old_passwords=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect old_passwords value: '1' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@old_passwords;
+-----------------+
| @@old_passwords |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.00 sec)

Link
Sergey PetruniaMariaDB 10.1: Better query optimization for ORDER BY … LIMIT (10.10.2014, 23:47 UTC)

For some reason, we’ve been getting a lot of issues with ORDER BY optimization recently. The fixes have passed Elena Stepanova’s scrutiny and I’ve pushed them to MariaDB 10.1. Now, MariaDB’s ORDER BY ... LIMIT optimizer:

  • Doesn’t make stupid choices when several multi-part keys and potential range accesses are present (MDEV-6402)
  • Always uses “range” and (not full “index” scan) when it switches to an index to satisfy ORDER BY … LIMIT (MDEV-6657)
  • Tries hard to be smart and use cost/number of records estimates from other parts of the optimizer (MDEV-6384, MDEV-465, MySQL Bug#36817)
  • Takes full advantage of InnoDB’s Extended Keys feature when checking if filesort() can be skipped (MDEV-6796)

so, if you are struggling with poor optimization of ORDER BY ... LIMIT queries, help might be underway.

I have to admit that optimizations for ORDER BY... LIMIT are still far from perfect (both in MySQL and in MariaDB). The code is old and ugly and having spent some time debugging it, I can easily believe that it still does a poor job in some cases. The good part is that now we have some fresh knowledge of the ORDER BY … LIMIT optimization code, as well as RQG scripts for testing ORDER BY .. LIMIT queries. If you have any issues with ORDER BY LIMIT optimization, we will appreciate to see bugs filed for them.

Link
Chris CalenderMariaDB 5.5.40 Overview and Highlights (10.10.2014, 22:46 UTC)

MariaDB 5.5.40 was recently released (it is the latest MariaDB 5.5), and is available for download here:

https://downloads.mariadb.org/mariadb/5.5.40/

This is a maintenance release, and so there are not too many big changes of note, just a number of normal bug fixes. However, there are a few items worth mentioning:

If interested, the official MariaDB 5.5.40 release notes are here:

https://mariadb.com/kb/en/mariadb/development/release-notes/mariadb-5540-release-notes/

And the full list of fixed bugs and changes in MariaDB 5.5.40 can be found here:

https://mariadb.com/kb/en/mariadb/development/changelogs/mariadb-5540-changelog/

Hope this helps.

 

Link
Sergey PetruniaA discovery - Index Condition Pushdown can cause a slowdown after all (10.10.2014, 21:56 UTC)

MariaDB 5.5 and then MySQL 5.6 got Index Condition Pushdown (ICP) optimization (initially coded by yours truly). The idea of ICP is simple: after reading the index record, check the part of WHERE condition that can be computed using index columns, and only then read the table record. That way, we avoid reading table rows that don’t satisfy index condition:

It seems apparent that ICP can never make things slower. The WHERE clause has to be checked anyway, and not reading certain records can only make things faster.

That was what I thought, too, until recently Joffrey Michaie observed the contrary “in the wild”: we’ve got a real-world case where using Index Condition Pushdown was slower than not using it: MDEV-6713. The slowdown was about 20%, both on MariaDB and MySQL.

From what I could investigate so far, the slowdown is caused by these three factors working together:

  • a VARCHAR(255) column in the index. MySQL’s in-memory data representation for VARCHARs is not space efficient. If a column is defined as VARCHAR(255), any value will occupy the entire 255 bytes.
  • InnoDB’s row prefetch cache. When InnoDB detects that one is reading a lot of rows from an index, it pre-fetches index records and stores them in an internal cache. The cache uses the inefficient in-memory data representation.
  • Design of MySQL’s Item classes. One can’t evaluate an expression over a table record that is in InnoDB prefetch cache. Expression evaluation functions expect to find column values in the table’s “primary location”, internally known as table->record[0]. In order for ICP to check the condition, index columns have to be copied to table->record[0], first.

I hope we will be able to investigate this problem and post more about it soon. For now, the news is that ICP can cause a slowdown, when the index has big VARCHAR columns.

Link
Peter ZaitsevMySQL compression: Compressed and Uncompressed data size (10.10.2014, 14:34 UTC)

MySQL has information_schema.tables that contain information such as “data_length” or “avg_row_length.” Documentation on this table however is quite poor, making an assumption that those fields are self explanatory – they are not when it comes to tables that employ compression. And this is where inconsistency is born. Lets take a look at the same table containing some highly compressible data using different storage engines that support MySQL compression:

TokuDB:

mysql> select * from information_schema.tables where table_schema='test' G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: comp
TABLE_TYPE: BASE TABLE
ENGINE: TokuDB
VERSION: 10
ROW_FORMAT: tokudb_zlib
TABLE_ROWS: 40960
AVG_ROW_LENGTH: 10003
DATA_LENGTH: 409722880
MAX_DATA_LENGTH: 9223372036854775807
INDEX_LENGTH: 0
DATA_FREE: 421888
AUTO_INCREMENT: NULL
CREATE_TIME: 2014-10-10 07:59:05
UPDATE_TIME: 2014-10-10 08:01:20
CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
1 row in set (0.00 sec)

Archive:

mysql> select * from information_schema.tables where table_schema='test' G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: comp
TABLE_TYPE: BASE TABLE
ENGINE: ARCHIVE
VERSION: 10
ROW_FORMAT: Compressed
TABLE_ROWS: 40960
AVG_ROW_LENGTH: 12
DATA_LENGTH: 501651
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: NULL
UPDATE_TIME: 2014-10-10 08:08:24
CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
1 row in set (0.01 sec)

InnoDB:

mysql> select * from information_schema.tables where table_schema='test' G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: comp
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Compressed
TABLE_ROWS: 40660
AVG_ROW_LENGTH: 4168
DATA_LENGTH: 169480192
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 1572864
AUTO_INCREMENT: NULL
CREATE_TIME: 2014-10-10 08:33:22
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
CHECKSUM: NULL
CREATE_OPTIONS: row_format=COMPRESSED KEY_BLOCK_SIZE=4
TABLE_COMMENT:
1 row in set (0.00 sec)

From this we can see what Archive and Innodb show the COMPRESSED values for DATA_LENGTH and AVG_ROW_LENGTH while TokuDB shows uncompressed one (as of TokuDB 7.5.1) shipped with Percona Server 5.6.21-69.

The problem here is not only a lack of consistency but also what we need to know about BOTH numbers. We often need to know the uncompressed value to understand how much data there is really in the table, but also how much space it takes on the disk. Comparing these also can help us to understand the compression ratio (or on the contrary expansion due to storage overhead and indexes).

Looking at Information_Schema tables available I can’t find any way to find how much uncompressed data is stored in the Innodb (or Archive) table. The simple trick I can use is running the query along those lines: SELECT SUM(LENGTH(col1)+LENGTH(col2)…) FROM T – This would slightly overestimate the length converting numbers and dates to strings but it is good enough for most purposes.

TokuDB though, while providing uncompressed information in Information_schema TABLES table, allows you to get the information of real data storage on disk from its own information schema tables:

mysql> select * from information_schema.TokuDB_fractal_tree_info where table_schema='test' and table_name='comp' limit 10 G
*************************** 1. row ***************************
dictionary_name: ./test/comp-main
internal_file_name: ./_test_sql_147e_292e_main_2c20c08_1_1b_B_0.tokudb
bt_num_blocks_allocated: 125
bt_num_blocks_in_use: 125
bt_size_allocated: 1880088
bt_size_in_use: 1502232
table_schema: test
table_name: comp
table_dictionary_name: main
*************************** 2. row ***************************
dictionary_name: ./test/comp-status
internal_file_name: ./_test_sql_147e_292e_status_2c20bdd_1_1b.tokudb
bt_num_blocks_allocated: 4
bt_num_blocks_in_use: 4
bt_size_allocated: 8704
bt_size_in_use: 512
table_schema: test
table_name: comp
table_dictionary_name: status
2 rows in set (0.01 sec)

This shows us that this table is really consisting of 2 files each “bt_size_allocated” bytes in length. These numbers are close to what you will see on the disk but not exactly. I see these files are taking 1886208 and 16384 files, respectfully. I wish there would be an exact l

Truncated by Planet PHP, read more at the original (another 1810 bytes)

Link
Jean-Jerome SchmidtDatabase Automation - Private DBaaS for MySQL, MariaDB and MongoDB with ClusterControl (9.10.2014, 14:02 UTC)
October 9, 2014
By Severalnines

Installing, configuring, deploying databases and performing repetitive administrative tasks are all part of a DBA’s or sysadmin’s job. This can get pretty repetitive and overwhelming if you are part of a centralized IT team, running multiple databases for your organization’s different departments, or a managed hosting provider responsible for setting up and operating databases for external clients. One way to get out of this ‘manual, repetitive task’ business is through a Database as a Service (DBaaS).

DBaaS is a way of delivering database functionality as a service to one or more consumers. A DBaaS platform would provide automated procedures for database deployment, monitoring, backups, recovery/repair, scaling, security/multi-tenancy, etc. This type of automation is especially useful where agility is needed, e.g. for systems that require elasticity by scaling out or scaling back at short notice, or for temporary deployments associated with dev/test/QA. Now that you’ve automated the repetitive stuff, you can start using your time and skills to optimize your schemas and configurations, help developers write better queries that scale, and work on system architecture or strategic database initiatives.

In this post, we’ll have a look at how enterprise companies or managed hosters can use ClusterControl to implement a DBaaS for MySQL, MariaDB and MongoDB. 

 

Multitenancy - Organizations, Users, Roles and Clusters 

 

Some basics first, let’s have a look at how ClusterControl handles multiple users and clusters. ClusterControl has an admin module through which an admin can create users, specify their roles and the organization each user belongs to. 

By default, ClusterControl provides three types of roles:

  • Super Admin - Sees all clusters/DBs that are registered with ClusterControl. The Super Admin can also create organizations and users. Only the Super Admin can transfer a cluster from one organization to another.
  • Admin - Belongs to a specific organization, and sees all clusters registered in that organization.
  • User - Belongs to a specific organization, and only sees the cluster(s) that she registered.

It is also possible to create custom roles with specific access control, for more fine-grained access to functionality. 

As a roundup, here is how the different entities relate to each other:

 

More information can be found in the ClusterControl User Guide.

 

Example Organizational Structure

 

Let’s assume we have an organization with separate departments, each having their own applications and databases. An IT department is responsible for running all the databases, but the Marketing and HR departments also have their own developers who need access to their respective databases.

Department

Description

Truncated by Planet PHP, read more at the original (another 6346 bytes)

Link
Oli SennhauserMigration between MySQL/Percona Server and MariaDB (8.10.2014, 19:40 UTC)

This week we did some migrations from MariaDB 10.0 to Percona Server 5.6 at the IT department of a big German bank.

We were perfectly aware that since version 10.0 the MariaDB code base started to diverge slightly away from the MySQL and Percona Server code base which are still pretty close to each other.

Because of the Percona Server option enforce_storage_engine we wanted to do this migration.

We stopped the MariaDB 10.0 server replaced the binaries by the Percona Server 5.6 binaries and started the Percona 5.6 server again. After successfully starting the instance we found some error messages in the MySQL error log. By running the mysql_upgrade command some of the problems were fixed but not all of them. Still left problems were:

  • The MariaDB binary logs provoked some error messages for the Percona Server:
    [ERROR] Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 25, event_type: -93
    [Warning] Error reading GTIDs from binary log: -1
    [ERROR] Incorrect definition of table mysql.db: expected column 'User' at position 2 to have type char(16), found type char(80).
    [ERROR] Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have type char(77), found type char(141).
    [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set...
    
    A purge of the binary logs solved this issue.
  • The tables mysql.event, mysql.innodb_table_stats and mysql.innodb_index_stats where not fixed by mysql_upgrade (a bug to fix for Percona and MySQL/Oracle?). We had to replace those tables manually by copying from an other already working Percona 5.6 Server.

Later in the FromDual technology labs we investigated further and tried the other way from Percona Server 5.6 to MariaDB 10.0. In this direction we found some other errors in the MySQL error log which also where not completely resolved by the mysql_upgrade utility:

  • The mysql.innodb_table_stats and mysql.innodb_index_stats tables where recreated manually (here a bug to fix for the MariaDB people?).
  • All error messages from tables affected by the following message:
    InnoDB: in InnoDB data dictionary has unknown flags 40/50/52.
    
    could be silenced by a run of the OPTIMIZE TABLE command (which can become quite expensive for very big tables).

Sidegrades from MySQL 5.6 to Percona Server 5.6 and back did not provoke any error message written to the MySQL log files. Sidegrades from MariaDB 10.0 to MySQL 5.6 and vice versa behaved exactly the same as MariaDB 10.0 to Percona Server 5.6 and back.

from/to: MySQL 5.6 MariaDB 10.0 Percona Server 5.6
MySQL 5.6 - 2 tables, OPTIMIZE OK
MariaDB 10.0 binlog, 3 tables - binlog, 3 tables
Percona Server 5.6 OK

Truncated by Planet PHP, read more at the original (another 1407 bytes)

Link
Peter ZaitsevMySQL Replication: ‘Got fatal error 1236′ causes and cures (8.10.2014, 07:00 UTC)

MySQL Replication: 'Got fatal error 1236' causes and curesMySQL replication is a core process for maintaining multiple copies of data – and replication is a very important aspect in database administration. In order to synchronize data between master and slaves you need to make sure that data transfers smoothly, and to do so you need to act promptly regarding replication errors to continue data synchronization. Here on the Percona Support team, we often help customers with replication broken-related issues. In this post I’ll highlight the top most critical replication error code 1236 along with the causes and cure. MySQL replication error “Got fatal error 1236” can be triggered by multiple reasons and I will try to cover all of them.

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event ‘binlog.000201′ at 5480571

This is a typical error on the slave(s) server. It reflects the problem around max_allowed_packet size. max_allowed_packet refers to single SQL statement sent to the MySQL server as binary log event from master to slave. This error usually occurs when you have a different size of max_allowed_packet on the master and slave (i.e. master max_allowed_packet size is greater then slave server). When the MySQL master server tries to send a bigger packet than defined on the slave server,  the slave server then fails to accept it and hence the error. In order to alleviate this issue please make sure to have the same value for max_allowed_packet on both slave and master. You can read more about max_allowed_packet here.

This error usually occurs when updating a huge number of rows on the master and it doesn’t fit into the value of slave max_allowed_packet size because slave max_allowed_packet size is lower then the master. This usually happens with queries “LOAD DATA INFILE” or “INSERT .. SELECT” queries. As per my experience, this can also be caused by application logic that can generate a huge INSERT with junk data. Take into account, that one new variable introduced in MySQL 5.6.6 and later slave_max_allowed_packet_size which controls the maximum packet size for the replication threads. It overrides the max_allowed_packet variable on slave and it’s default value is 1 GB. In this post, “max_allowed_packet and binary log corruption in MySQL,”my colleague Miguel Angel Nieto explains this error in detail.

Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

This error occurs when the slave server required binary log for replication no longer exists on the master database server. In one of the scenarios for this, your slave server is stopped for some reason for a few hours/days and when you resume replication on the slave it fails with above error.

When you investigate you will find that the master server is no longer requesting binary logs which the slave server needs to pull in order to synchronize data. Possible reasons for this include the master server expired binary logs via system variable expire_logs_days – o

Truncated by Planet PHP, read more at the original (another 6552 bytes)

Link
Chris CalenderMariaDB 10.0.14 Overview and Highlights (8.10.2014, 00:54 UTC)

MariaDB 10.0.14 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.0.14/

This is the fifth GA release of MariaDB 10.0, and 15th overall release of MariaDB 10.0.

This is primarily a bug-fix release. (MariaDB 10.0 is the current stable series of MariaDB. It is an evolution of the MariaDB 5.5 with several entirely new features not found anywhere else and with backported and reimplemented features from MySQL 5.6.)

Here are the main items of note:

  1. TokuDB upgraded to 7.5.0
  2. XtraDB upgraded to 5.6.20-68.0
  3. InnoDB upgraded to 5.6.20
  4. Spider upgraded to 3.2.11
  5. SphinxSE upgraded to 2.1.9
  6. The Feedback plugin now includes statistics on collation usage.
  7. Error log has a flood protection that is activated after 10 identical unsafe warnings and disables them for the next 5 minutes.
  8. Many fixes and optimizations for the Power8 platform.
  9. As per the MariaDB Deprecation Policy, this will be the last release of MariaDB 10.0 for both Ubuntu 13.10 “Saucy” and Mint 16 “Petra”.
  10. With the recent release of CentOS 7 and RHEL 7, we are pleased to now provide packages for both distributions. Instructions for how to enable the repositories can be found by visiting the “Installing MariaDB with YUM” page and the repository configuration tool.
  11. Crash in GROUP_CONCAT(IF () ORDER BY 1) (MDEV-6743)
  12. Server crashes in my_hash_first if shutdown is performed when FLUSH LOGS is running (MDEV-6616)
  13. Slave replicating using GTID doesn’t recover correctly when master crashes in the middle of transaction (MDEV-6462)
  14. MariaDB crash on Power8 when built with advance tool chain (MDEV-6450)

As always, it’s great to see all of the fixes alone from TokuDB, XtraDB, InnoDB, Spider, and Sphinx (and thus if you’re relying on any of these technologies, I would consider upgrading). The Power8 enhancements are very nice to see also! (If you’re running Power8 and looking for performance improvements, then you should definitely look into upgrading.) The crash fixes are mostly obscure, but double-check them, just in case they might affect you currently, and if so, then plan to upgrade.

You can read more about the 10.0.14 release here:

https://mariadb.com/kb/en/mariadb-10014-release-notes/

And if interested, you can review the full list of changes in 10.0.14 (changelogs) here:

https://mariadb.com/kb/en/mariadb-10014-changelog/

Hope this helps.

 

Link
LinksRSS 0.92   RDF 1.
Atom Feed