Planet MySQL

1. Webinar Wednesday, January 24, 2018: Differences between MariaDB and MySQL

   Join Percona’s Chief Evangelist, Colin Charles as he presents Differences Between MariaDB and MySQL on Wednesday, January 24, 2018, at 7:00 am PDT / 10:00 am EDT (UTC-7). Register Now Tags: MariaDB, MySQL, Percona Server for MySQL, DBA, SysAdmin, DevOpsExperience Level: Novice MariaDB and MySQL. Are they syntactically similar? Where do these two query languages differ? Why would I use one over the other? MariaDB is on the path of gradually diverging from MySQL. One obvious example is the internal data dictionary currently under development for MySQL 8.0. This is a major change to the way metadata is stored and used within the server. MariaDB doesn’t have an equivalent feature. Implementing this feature could mark the end of datafile-level compatibility between MySQL and MariaDB. There are also non-technical differences between MySQL and MariaDB, including: Licensing: MySQL offers their code as open-source under the GPL, and provides the option of non-GPL commercial distribution in the form of MySQL Enterprise. MariaDB can only use the GPL because they derive their work from the MySQL source code under the terms of that license. Support services: Oracle provides technical support, training, certification and consulting for MySQL, while MariaDB has their own support services. Some people will prefer working with smaller companies, as traditionally it affords them more leverage as a customer. Community contributions: MariaDB touts the fact that they accept more community contributions than Oracle. Part of the reason for this disparity is that developers like to contribute features, bug fixes and other code without a lot of paperwork overhead (and they complain about the Oracle Contributor Agreement). However, MariaDB has its own MariaDB Contributor Agreement — which more or less serves the same purpose. Colin will take a look at some of the differences between MariaDB and MySQL and help answer some of the common questions our Database Performance Experts get about the two databases. Register for the webinar now. Colin Charles, Chief Evangelist Colin Charles is the Chief Evangelist at Percona. He was previously on the founding team for MariaDB Server in 2009, worked in MySQL since 2005, and been a MySQL user since 2000. Before joining MySQL, Colin worked actively on the Fedora and OpenOffice.org projects. He’s well-known within many open source communities and speaks on the conference circuit.

2. Understanding – Group Replication and InnoDB Cluster

   In this blog post, I’d like to show some relationships of “Group Replication” and “InnoDB Cluster“.  My hope is that I will be able to draw meaningful correlations and attempt to simplify our way of thinking about these setups. The MySQL development team released the first GA release of Group Replication in MySQL 5.7.17, which turns… Read More »

3. About USE/FORCE/IGNORE INDEX

   USE/FORCE/IGNORE INDEX syntax, or index hints, are nice shortcuts to make sure that MySQL will (or will not) use a certain index. But it comes with some drawbacks: USE/FORCE INDEX will not allow to use an index not mentioned in the list This could be by design, though in the case of USE INDEX it sounds weird to me. Why? Because if none of the indexes mentioned in the list is usable, a full table scan will happen. Why is this a problem? Because in the real world queries are generated dynamic and evolve over time. Today’s optimisations could be tomorrow’s wrong hints. I had a case of a wrong USE INDEX preventing the use of the primary key. Produces an error if the index doesn’t exist Again, this could be by design, but in the case of IGNORE INDEX this seems to me not ideal. A warning would be much better. Even better, I’d like to have this behaviour governed by a variable. Why is this a problem? Because indexes can be deleted. Maybe it’s because queries change, maybe it’s because they were wrong from the start (possibly not even created by a DBA). But then, dropping an index can generate errors for existing applications. Unfortunately, IMHO, documenting the usage of such hints is too difficult. Federico

4. Replication Performance Enhancements in MySQL 8

   Although it feels like it was only yesterday that Oracle released version 5.7 of their acclaimed MySQL Community Server, version 8 is already available as a development milestone release (DMR). No, you didn’t sleep through a bunch of releases; MySQL is jumping several versions in its numbering due to 6.0 being dropped and 7.0 being reserved for the clustering version of MySQL. This new version boasts numerous changes (and bug fixes), one of the most exciting of which are replication enhancements. This blog will provide an overview of the new replication enhancements, including new replication timestamps, additional information reported by performance schema tables, and how replication delay has been reduced by updating the relationship between replication threads to make them more efficient. New Replication Timestamps The most common task when managing a replication process is to ensure that replication is, in fact, taking place and that there were no errors between the slave and the master. The primary statement for this is SHOW SLAVE STATUS, which provides status information on essential parameters of the slave threads. Hence, you must execute it on each slave. Here’s some sample output: mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: root Master_Port: 13000 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 1307 Relay_Log_File: slave-relay-bin.000003 Relay_Log_Pos: 1508 Relay_Master_Log_File: master-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes / * / * / * / Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: ETC... One of the many output fields is the Seconds_Behind_Master. While perfectly suitable for a simple master-slave setup, this metric is insufficient for more complex replication scenarios. The Seconds_Behind_Master metric has four main drawbacks: It only reports the delay between the slave and the top-most master. For instance, in a chained replication setup, the Seconds_Behind_Master reports the delay relative to the original master and does not provide any information regarding the lag between the slave and its nearest – i.e. immediate – master. It is relative to the original master’s time zone. As a result, server replication across time zones cause the measured delay to be offset by the time zone difference between the two servers. Lag is measured on a per-event basis, based on the statement’s execution start time. A more insightful measure would be per-transaction, from the time the transaction was actually committed on the master. The timestamp used to measure the replication lag offers a precision only up to the nearest second. MySQL 8 introduces two new timestamps that complement the Seconds_Behind_Master metric in circumventing the above issues. These are associated with the global transaction identifier (GTID) of each transaction (as opposed to each event), written to the binary log. A GTID is a unique identifier created and associated with each transaction committed on the server of origin (master). This identifier is unique not only to the server on which it originated but across all servers in a given replication setup. Being associated to a transaction, there is a 1-to-1 mapping between all transactions and all GTIDs. The two new timestamps are: original commit timestamp (OCT): the number of microseconds since epoch (i.e. POSIX time/ UNIX time/January 1, 1970/1970-01-01T00:00:00Z) when the transaction was written to the binary log of the original master immediate commit timestamp (ICT): the number of microseconds since epoch when the transaction was written to the binary log of the immediate master The output of mysqlbinlog displays the new timestamps in two formats: microseconds from epoch, and TIMESTAMP format in the user time zone (for better readability) This snippet from a slave’s binary log shows both timestamps: #170404 10:48:05 server id 1 end_log_pos 233 CRC32 0x016ce647 GTID last_committed=0 sequence_number=1 original_committed_timestamp=1491299285661130 immediate_commit_timestamp=1491299285843771 # original_commit_timestamp=1491299285661130 (2018-01-04 10:48:05.661130 WEST) # immediate_commit_timestamp=1491299285843771 (2018-01-04 10:48:05.843771 WEST) /*!80001 SET @@session.original_commit_timestamp=1491299285661130*//*!*/; SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1'/*!*/; # at 288 New Information Reported by Performance Schema Tables MySQL 8.0 added a few changes to the Performance Schema resulting in better performance and more metrics: It can instrument server errors It now supports indexes It adds new fields to the existing performance schema replication status tables. Let’s explore each of these in more detail. Instrumentation of Server Errors MySQL 8 saw the introduction of five new summary tables to assist in the in the instrumentation of server errors. These include: events_errors_summary_by_account_by_error events_errors_summary_by_host_by_error events_errors_summary_by_thread_by_error events_errors_summary_by_user_by_error events_errors_summary_global_by_error The error statistics are aggregated by error in all of the above tables. Moreover, each table, with the exception of events_errors_summary_global_by_error, stores errors related to a particular user, host, account, or thread; events_errors_summary_global_by_error contains errors for the entire server. Table Structure Each table contains the following fields: +-------------------+---------------------+------+-----+---------------------+ | Field | Type | Null | Key | Default | +-------------------+---------------------+------+-----+---------------------+ | ERROR_NUMBER | int(11) | YES | | NULL | | ERROR_NAME | varchar(64) | YES | | NULL | | SQL_STATE | varchar(5) | YES | | NULL | | SUM_ERROR_RAISED | bigint(20) unsigned | NO | | NULL | | SUM_ERROR_HANDLED | bigint(20) unsigned | NO | | NULL | | FIRST_SEEN | timestamp | YES | | 0000-00-00 00:00:00 | | LAST_SEEN | timestamp | YES | | 0000-00-00 00:00:00 | +-------------------+---------------------+------+-----+---------------------+ Note that: The FIRST_SEEN/LAST_SEEN columns indicate the first and last time a particular error was seen. The SUM_ERROR_RAISED column lists the number of times a particular error is raised. The SUM_ERROR_HANDLED column lists the number of times a particular error is handled. All errors which were handled in stored programs are counted/aggregated under SUM_ERROR_HANDLED. Meanwhile, SUM_ERROR_RAISED is the number of all other remaining errors which were raised but not handled. Hence, to see the number of times a particular error is encountered on the server, we could do the following: -- select from an unknown table: select * from mydb.unknown_table; ERROR 1146 (42S02): Table 'mydb.unknown_table' doesn't exist -- look up the error SELECT * from performance_schema.events_errors_summary_global_by_error where ERROR_NAME = 'ER_NO_SUCH_TABLE'; +--------------+------------------+-----------+------------------+-------------------+---------------------+--------------------+ | ERROR_NUMBER | ERROR_NAME | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN | +--------------+------------------+-----------+------------------+-------------------+---------------------+---------------------+ | 1146 | ER_NO_SUCH_TABLE | 42S02 | 1 | 0 | 2018-01-15 15:15:21 | 2018-01-15 15:15:21 | +--------------+------------------+-----------+------------------+-------------------+---------------------+---------------------+ Although this example is querying the global table, these error’s statistics could be retrieved aggregated by user/host/account/thread from their respective tables as well for more fine-grained statistics. Index Support Since its inclusion in MySQL 5.5, the Performance Schema has grown to 93 tables, some of which store a large amount of data. The added index support greatly increases the efficiency the performance_schema, resulting in a dramatic speed boost for many monitoring queries. The performance improvements from indexes can be very easily seen in many of the sys schema queries. For instance, with 1000 idle threads, the query “SELECT * FROM sys.session drops from 34.70 seconds down to 1.01 seconds – a 30x improvement! A total of 115 indexes have been added in the performance schema. Unlike the INFORMATION_SCHEMA, the performance schema exposes the data as a storage engine, rather than temporary tables. Whereas the latter are not able to expose indexes that may be utilized by the optimizer, storage engines can. Data access to the performance schema also uses the same (SQL) interface as regular tables, so that it is able to benefit from future improvements to the query optimizer. New Fields Added to Existing Performance Schema Replication Status Tables Beyond the new server error tables, existing performance_schema tables are also getting some extra fields to help detect and diagnose lag at several points. Each potential lag point in the replication stream maps to its own table: Lag Point Performance Schema Replication Status Table The connection thread that retrieves transactions from the master and queues them in the relay log. replication_connection_status: current status of the connection to the master The coordinator thread that reads the transactions from the relay log and schedules them to a worker queue (when multi-threaded slave mode [MTS] is enabled). replication_applier_status_by_coordinator: current status of the coordinator thread that only displays information when MTS is enabled The worker threads applying the transactions. replication_applier_status_by_worker: current status of the thread(s) applying transactions received from the master Nine additional fields were added to each table that store information about the last transaction, the corresponding thread processed, and the transaction that thread is currently processing. This information includes: the transaction’s GTID its OCT and ICT (retrieved from the slave’s relay log) the time the thread started processing it and in case of the last processed transaction, the time the thread finished processing it. New replication_connection_status Fields The new replication_connection_status fields report information on the last and current transaction the connection thread queued into the relay log. This includes when it: started queuing the transaction, i.e., when it read the first event from the master and queued it in the relay log for LAST_QUEUED_TRANSACTION, when it finished queuing the last event of the transaction in the relay log Here are the fields, along with sample data: Field                                                                                                                                                                      Sample Data————————————————————————————————————————————————————————– LAST_QUEUED_TRANSACTION aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP 2018-01-04 12:48:05.661130 LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP 2018-01-04 12:48:05.661130 LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP 2018-01-04 12:48:05.674003 LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP 2018-01-04 12:48:05.697760 QUEUEING_TRANSACTION QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP 0000-00-00 00:00:00.000000 New replication_applier_status_by_coordinator Fields When MTS is enabled, the replication_applier_status_by_coordinator table also reports which was the last transaction buffered by the coordinator thread to a worker’s queue, as well as the transaction it is currently buffering. The start timestamp refers to when this thread read the first event of the transaction from the relay log to buffer it to a worker’s queue, while the end timestamp refers to when the last event finished buffering to the worker’s queue. Here are its fields, along with sample data: Field                                                                                                                                                                      Sample Data————————————————————————————————————————————————————————– LAST_PROCESSED_TRANSACTION aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1 LAST_PROCESSED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP 2018-01-04 12:48:05.661130 LAST_PROCESSED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP 2018-01-04 12:48:05.661130 LAST_PROCESSED_TRANSACTION_START_BUFFER_TIMESTAMP 2018-01-04 12:48:05.674139 LAST_PROCESSED_TRANSACTION_END_BUFFER_TIMESTAMP 2018-01-04 12:48:05.819167 PROCESSING_TRANSACTION PROCESSING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP 0000-00-00 00:00:00.000000 PROCESSING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP 0000-00-00 00:00:00.000000 PROCESSING_TRANSACTION_START_BUFFER_TIMESTAMP 0000-00-00 00:00:00.000000 New replication_applier_status_by_worker Fields The table replication_applier_status_by_worker now contains details about the transactions applied by the applier thread, or, if MTS is enabled, by each worker.  The start timestamp refers to when the worker started applying the first event, whereas the end timestamp refers to when the last event of the transaction was applied. Here are its nine new fields, along with sample data: Field                                                                                                                                                                      Sample Data————————————————————————————————————————————————————————– LAST_PROCESSED_TRANSACTION aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1 LAST_APPLIED_TRANSACTION aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1 LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP 2018-01-04 12:48:05.661130 LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP 2018-01-04 12:48:05.661130 LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP 2018-01-04 12:48:05.822463 LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP 2018-01-04 12:48:05.948926 APPLYING_TRANSACTION APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_START_APPLY_TIMESTAMP 0000-00-00 00:00:00.000000 More Efficient Relationship between Replication Threads As of MySQL 8.0.1, updates to the replica server will make it more efficient than previous MySQL versions thanks to improvements in the relationship between the replication threads.  Although it’s still early to say with any precision just how the increased efficiency will translate into faster performance, preliminary testing showed a benefit of up to 65 percent. The core of MySQL replication on the replica side is composed of two threads (sometimes more): the connection: handles the connection with the master, retrieving the events and queuing them on the relay log. the applier: reads the queued events from the relay log and applies them to the replica The Old Arbitration Thread Model The relationship between connection and applier was problematic when both threads were dealing with the same relay log file, due to “arbitration”.  The relay log file could only be accessed by one thread at a time, resulting in mutual exclusion of the replication threads.  Hence, when the connection was writing to the relay log file, the applier was unable to read content to be applied and had to wait. Likewise, when the applier was reading from the relay log file, the connection was unable to write new content to it and went into an idle state.  The arbitration was necessary to prevent the applier from sending events that were only partially written to the relay log to workers. While sometimes beneficial on slaves with limited resources, this arbitration was also limiting the scalability of the multi-threaded slave (MTS) applier. The New Thread Model As of MySQL 8.0.1, the applier should almost never block the connection anymore, the exception being when the relay log has exceeded its size limit.   Likewise, the connection will not block the applier for already fully queued transaction parts.  To make this solution work, the connection thread keeps updated information about the position in the relay log file of the last fully queued event. The applier now reads from the log up to this position and waits for notification from the connection thread when it’s done writing to the relay log. Conclusion This blog provided an overview of MySQL 8.0’s exciting new replication enhancements, including: new replication timestamps: MySQL 8 introduces two new timestamps that complement the Seconds_Behind_Master metric.  These are associated with the global transaction identifier (GTID) of each transaction written to the binary log.   new information reported by performance schema tables: the Performance Schema in version 8.0 can now instrument server errors, supports indexes and adds new fields to the existing performance schema replication status tables. more efficient relationship between replication threads: as of MySQL 8.0.1, updates to the replica server will make it more efficient than previous MySQL versions thanks to improvements in the relationship between the replication threads. Preliminary testing showed a benefit of up to 65 percent! All of these changes will make for much-improved performance monitoring. Once MySQL 8 goes into production, we’ll get a chance to gauge just how much. The post Replication Performance Enhancements in MySQL 8 appeared first on Monyog Blog.

5. MariaDB 5.5.59 now available

   The MariaDB project is pleased to announce the immediate availability of MariaDB 5.5.59. This is a stable (GA) release. See the release notes and changelog for details. Download MariaDB 5.5.59 Release Notes Changelog What is MariaDB 5.5? MariaDB APT and YUM Repository Configuration Generator Thanks, and enjoy MariaDB! The post MariaDB 5.5.59 now available appeared first on MariaDB.org.