Planet MySQL

Planet MySQL - https://planet.mysql.com
  1. Use Case: Multi-master MySQL for e-Commerce Sites

    For this next blog in our Continuent MySQL Use Case series, we’re diving into a sub-series on the topic of ‘multi-master MySQL’. We’ll cover three (3) multi-master MySQL use cases as part of this sub-series focusing first on e-commerce to start with, and then following up with use cases from financial services and telecommunications. Multi-master replication for MySQL typically means that a user can write to any master node knowing that the write will be eventually consistent for all nodes in the cluster; unlike regular MySQL replication, where writes have to be applied to the sole master to ensure that it will be synched to all the slaves. The First Multi-master Customer The first Continuent multi-master customer is a leading fashion e-commerce company with sites servicing customers across the globe. More specifically, it has four multi-brand online stores and several online flagship stores serving millions of customers in 180 countries around the world. The Challenge The challenge this customer faced had to do with managing a single sign-on e-commerce portal for their millions of worldwide customers as well as their need to reduce contention in a single location by balancing traffic to each cluster based on the source website. The Solution Multi-master Tungsten Clustering topology (active/active) – Two or more master MySQL clusters In the Tungsten multi-master cluster topology (active/active), there is one writeable master node per cluster, across two or more clusters. All writes are directed to an available (usually local) master by the Tungsten Connectors / Proxies serving the client requests. The Connectors/Proxies are able to use any other site in the event of a local outage; both sites are write-able at all times, and each cluster replicates from all other member clusters. Our customer’s current Tungsten Clustering deployment consists of two (2) 3-node MySQL clusters configured in a multi-master topology. The Tungsten MySQL clusters are situated in two (2) separate data centers, geographically located in different sites, providing remote redundancy and disaster recovery. They also utilize Tungsten Replicator to replicate out in real-time from each cluster for management reporting. The Benefits Multi-master, Availability, Disaster Recovery About Tungsten Clustering Tungsten Clustering allows enterprises running business-critical MySQL database applications to cost-effectively achieve continuous operations with commercial-grade high availability (HA), geographically redundant disaster recovery (DR) and global scaling. To find out more, visit our Tungsten Clustering product page.
  2. Presentation: An overview to window function In MySQL 8.0

    MySQL has come up with window function in latest GA MySQL 8.0 . It is a major leap in SQL for MySQL. This presentation provides an overview to window function in MySQL 8.0. Window functions in MySQL 8.0 from Mydbops Window Function in MySQL 8.0
  3. How to Restore a Single MySQL Table Using mysqldump?

    Mysqldump is the most popular logical backup tool for MySQL. It is included in the MySQL distribution, so it’s ready for use on all of the MySQL instances.  Logical backups are not, however, the fastest nor the most space-efficient way of backing up MySQL databases, but they have a huge advantage over physical backups.  Physical backups are usually all or nothing type of backups. While it might be possible to create partial backup with Xtrabackup (we described this in one of our previous blog posts), restoring such backup is tricky and time-consuming.  Basically, if we want to restore a single table, we have to stop the whole replication chain and perform the recovery on all of the nodes at once. This is a major issue - these days you rarely can afford to stop all of the databases.  Another problem is the table level is the lowest granularity level you can achieve with Xtrabackup: you can restore a single table but you cannot restore part of it. Logical backup, though, can be restored in the way of running SQL statements, therefore it can easily be performed on a running cluster and you can (we wouldn’t call it easily, but still) pick which SQL statements to run so you can do a partial restore of a table.  Let’s take a look at how this can be done in the real world. Restoring a Single MySQL Table Using mysqldump At the beginning, please keep in mind that partial backups do not provide a consistent view of the data. When you take backups of separate tables, you cannot restore such backup to a known position in time (for example, to provision the replication slave) even if you would restore all of the data from the backup. Having this behind us, let’s proceed. We have a master and a slave: Dataset contains of one schema and several tables: mysql> SHOW SCHEMAS; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sbtest | | sys | +--------------------+ 5 rows in set (0.01 sec) mysql> SHOW TABLES FROM sbtest; +------------------+ | Tables_in_sbtest | +------------------+ | sbtest1 | | sbtest10 | | sbtest11 | | sbtest12 | | sbtest13 | | sbtest14 | | sbtest15 | | sbtest16 | | sbtest17 | | sbtest18 | | sbtest19 | | sbtest2 | | sbtest20 | | sbtest21 | | sbtest22 | | sbtest23 | | sbtest24 | | sbtest25 | | sbtest26 | | sbtest27 | | sbtest28 | | sbtest29 | | sbtest3 | | sbtest30 | | sbtest31 | | sbtest32 | | sbtest4 | | sbtest5 | | sbtest6 | | sbtest7 | | sbtest8 | | sbtest9 | +------------------+ 32 rows in set (0.00 sec) Now, we have to take a backup. There are several ways in which we can approach this issue. We can just take a consistent backup of the whole dataset but this will generate a large, single file with all the data. To restore the single table we would have to extract data for the table from that file. It is of course possible, but it is quite time-consuming and it’s pretty much manual operation that can be scripted but if you do not have proper scripts in place, writing ad hoc code when your database is down and you are under heavy pressure is not necessarily the safest idea. Instead of that we can prepare backup in a way that every table will be stored in a separate file: root@vagrant:~/backup# d=$(date +%Y%m%d) ; db='sbtest'; for tab in $(mysql -uroot -ppass -h127.0.0.1 -e "SHOW TABLES FROM ${db}" | grep -v Tables_in_${db}) ; do mysqldump --set-gtid-purged=OFF --routines --events --triggers ${db} ${tab} > ${d}_${db}.${tab}.sql ; done Please note that we set --set-gtid-purged=OFF. We need it if we’d be loading this data later to the database. Otherwise MySQL will attempt to set @@GLOBAL.GTID_PURGED, which will, most likely, fail. MySQL would as well set SET @@SESSION.SQL_LOG_BIN= 0; which is definitely not what we want. Those settings are required if we’d make a consistent backup of the whole data set and we’d like to use it to provision a new node. In our case we know it is not a consistent backup and there is no way we can rebuild anything from it. All we want is to generate a dump that we can load on the master and let it replicate to slaves. That command generated a nice list of sql files that can be uploaded to the production cluster: root@vagrant:~/backup# ls -alh total 605M drwxr-xr-x 2 root root 4.0K Mar 18 14:10 . drwx------ 9 root root 4.0K Mar 18 14:08 .. -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest10.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest11.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest12.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest13.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest14.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest15.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest16.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest17.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest18.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest19.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest1.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest20.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest21.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest22.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest23.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest24.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest25.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest26.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest27.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest28.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest29.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest2.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest30.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest31.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest32.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest3.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest4.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest5.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest6.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest7.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest8.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest9.sql When you would like to restore the data, all you need to do is to load the SQL file into the master node: root@vagrant:~/backup# mysql -uroot -ppass sbtest < 20200318_sbtest.sbtest11.sql Data will be loaded into the database and replicated to all of the slaves. How to Restore a Single MySQL Table Using ClusterControl? Currently ClusterControl does not provide an easy way of restoring just a single table but it is still possible to do it with just a few manual actions. There are two options you can use. First, suitable for small number of tables, you can basically create schedule where you perform partial backups of a separate tables one by one: Here, we are taking a backup of sbtest.sbtest1 table. We can easily schedule another backup for sbtest2 table: Alternatively we can perform a backup and put data from a single schema into a separate file: Now you can either find the missing data by hand in the file, restore this backup to a separate server or let ClusterControl do it: You keep the server up and running and you can extract the data that you wanted to restore using either mysqldump or SELECT … INTO OUTFILE. Such extracted data will be ready to be applied on the production cluster.   Tags:  MySQL mysqldump restore backup
  4. Advanced Query Analysis in Percona Monitoring and Management with Direct ClickHouse Access

    In my Webinar on Using Percona Monitoring and Management (PMM) for MySQL Troubleshooting, I showed how to use direct queries to ClickHouse for advanced query analysis tasks. In the followup Webinar Q&A, I promised to describe it in more detail and share some queries, so here it goes. PMM uses ClickHouse to store query performance data which gives us great performance and a very high compression ratio. ClickHouse stores data in column-store format so it handles denormalized data very well. As a result, all query performance data is stored in one simple “metrics” table: Column Name Comment queryid hash of query fingerprint service_name Name of service (IP or hostname of DB server by default) database PostgreSQL: database schema MySQL: database; PostgreSQL: schema username client user name client_host client IP or hostname replication_set Name of replication set cluster Cluster name service_type Type of service service_id Service identifier environment Environment name az Availability zone region Region name node_model Node model node_id Node identifier node_name Node name node_type Node type machine_id Machine identifier container_name Container name container_id Container identifier labels.key Custom labels names labels.value Custom labels values agent_id Identifier of agent that collect and send metrics agent_type qan-agent-type-invalid = 0 qan-mysql-perfschema-agent,= 1 qan-mysql-slowlog-agent,= 2 qan-mongodb-profiler-agent,= 3 qan-postgresql-pgstatements-agent,= 4 Agent Type that collect of metrics: slowlog,perf schema,etc. period_start Time when collection of bucket started period_length Duration of collection bucket fingerprint mysql digest_text; query without data example One of query example from set found in bucket example_format EXAMPLE_FORMAT_INVALID = 0 EXAMPLE = 1 FINGERPRINT = 2 Indicates that collect real query examples is prohibited is_truncated Indicates if query examples is too long and was truncated example_type EXAMPLE_TYPE_INVALID = 0 RANDOM = 1 SLOWEST = 2 FASTEST = 3 WITH_ERROR = 4 Indicates what query example was picked up example_metrics Metrics of query example in JSON format. num_queries_with_warnings How many queries was with warnings in bucket warnings.code List of warnings warnings.count Count of each warnings in bucket num_queries_with_errors How many queries was with error in bucket errors.code List of Last_errno errors.count Count of each Last_errno in bucket num_queries Amount queries in this bucket m_query_time_cnt The statement execution time in seconds was met. m_query_time_sum The statement execution time in seconds. m_query_time_min Smallest value of query_time in bucket m_query_time_max Biggest value of query_time in bucket m_query_time_p99 99 percentile of value of query_time in bucket m_lock_time_cnt m_lock_time_sum The time to acquire locks in seconds. m_lock_time_min m_lock_time_max m_lock_time_p99 m_rows_sent_cnt m_rows_sent_sum The number of rows sent to the client. m_rows_sent_min m_rows_sent_max m_rows_sent_p99 m_rows_examined_cnt m_rows_examined_sum Number of rows scanned – SELECT. m_rows_examined_min m_rows_examined_max m_rows_examined_p99 m_rows_affected_cnt m_rows_affected_sum Number of rows changed – UPDATE m_rows_affected_min m_rows_affected_max m_rows_affected_p99 m_rows_read_cnt m_rows_read_sum The number of rows read from tables. m_rows_read_min m_rows_read_max m_rows_read_p99 m_merge_passes_cnt m_merge_passes_sum The number of merge passes that the sort algorithm has had to do. m_merge_passes_min m_merge_passes_max m_merge_passes_p99 m_innodb_io_r_ops_cnt m_innodb_io_r_ops_sum Counts the number of page read operations scheduled. m_innodb_io_r_ops_min m_innodb_io_r_ops_max m_innodb_io_r_ops_p99 m_innodb_io_r_bytes_cnt m_innodb_io_r_bytes_sum Similar to innodb_IO_r_ops m_innodb_io_r_bytes_min m_innodb_io_r_bytes_max m_innodb_io_r_bytes_p99 m_innodb_io_r_wait_cnt m_innodb_io_r_wait_sum Shows how long (in seconds) it took InnoDB to actually read the data from storage. m_innodb_io_r_wait_min m_innodb_io_r_wait_max m_innodb_io_r_wait_p99 m_innodb_rec_lock_wait_cnt m_innodb_rec_lock_wait_sum Shows how long (in seconds) the query waited for row locks. m_innodb_rec_lock_wait_min m_innodb_rec_lock_wait_max m_innodb_rec_lock_wait_p99 m_innodb_queue_wait_cnt m_innodb_queue_wait_sum Shows how long (in seconds) the query spent either waiting to enter the InnoDB queue or inside that queue waiting for execution. m_innodb_queue_wait_min m_innodb_queue_wait_max m_innodb_queue_wait_p99 m_innodb_pages_distinct_cnt m_innodb_pages_distinct_sum Counts approximately the number of unique pages the query accessed. m_innodb_pages_distinct_min m_innodb_pages_distinct_max m_innodb_pages_distinct_p99 m_query_length_cnt m_query_length_sum Shows how long the query is. m_query_length_min m_query_length_max m_query_length_p99 m_bytes_sent_cnt m_bytes_sent_sum The number of bytes sent to all clients. m_bytes_sent_min m_bytes_sent_max m_bytes_sent_p99 m_tmp_tables_cnt m_tmp_tables_sum Number of temporary tables created on memory for the query. m_tmp_tables_min m_tmp_tables_max m_tmp_tables_p99 m_tmp_disk_tables_cnt m_tmp_disk_tables_sum Number of temporary tables created on disk for the query. m_tmp_disk_tables_min m_tmp_disk_tables_max m_tmp_disk_tables_p99 m_tmp_table_sizes_cnt m_tmp_table_sizes_sum Total Size in bytes for all temporary tables used in the query. m_tmp_table_sizes_min m_tmp_table_sizes_max m_tmp_table_sizes_p99 m_qc_hit_cnt m_qc_hit_sum Query Cache hits. m_full_scan_cnt m_full_scan_sum The query performed a full table scan. m_full_join_cnt m_full_join_sum The query performed a full join (a join without indexes). m_tmp_table_cnt m_tmp_table_sum The query created an implicit internal temporary table. m_tmp_table_on_disk_cnt m_tmp_table_on_disk_sum The querys temporary table was stored on disk. m_filesort_cnt m_filesort_sum The query used a filesort. m_filesort_on_disk_cnt m_filesort_on_disk_sum The filesort was performed on disk. m_select_full_range_join_cnt m_select_full_range_join_sum The number of joins that used a range search on a reference table. m_select_range_cnt m_select_range_sum The number of joins that used ranges on the first table. m_select_range_check_cnt m_select_range_check_sum The number of joins without keys that check for key usage after each row. m_sort_range_cnt m_sort_range_sum The number of sorts that were done using ranges. m_sort_rows_cnt m_sort_rows_sum The number of sorted rows. m_sort_scan_cnt m_sort_scan_sum The number of sorts that were done by scanning the table. m_no_index_used_cnt m_no_index_used_sum The number of queries without index. m_no_good_index_used_cnt m_no_good_index_used_sum The number of queries without good index. m_docs_returned_cnt m_docs_returned_sum The number of returned documents. m_docs_returned_min m_docs_returned_max m_docs_returned_p99 m_response_length_cnt m_response_length_sum The response length of the query result in bytes. m_response_length_min m_response_length_max m_response_length_p99 m_docs_scanned_cnt m_docs_scanned_sum The number of scanned documents. m_docs_scanned_min m_docs_scanned_max m_docs_scanned_p99 m_shared_blks_hit_cnt m_shared_blks_hit_sum Total number of shared blocks cache hits by the statement m_shared_blks_read_cnt m_shared_blks_read_sum Total number of shared blocks read by the statement. m_shared_blks_dirtied_cnt m_shared_blks_dirtied_sum Total number of shared blocks dirtied by the statement. m_shared_blks_written_cnt m_shared_blks_written_sum Total number of shared blocks written by the statement. m_local_blks_hit_cnt m_local_blks_hit_sum Total number of local block cache hits by the statement m_local_blks_read_cnt m_local_blks_read_sum Total number of local blocks read by the statement. m_local_blks_dirtied_cnt m_local_blks_dirtied_sum Total number of local blocks dirtied by the statement. m_local_blks_written_cnt m_local_blks_written_sum Total number of local blocks written by the statement. m_temp_blks_read_cnt m_temp_blks_read_sum Total number of temp blocks read by the statement. m_temp_blks_written_cnt m_temp_blks_written_sum Total number of temp blocks written by the statement. m_blk_read_time_cnt m_blk_read_time_sum Total time the statement spent reading blocks m_blk_write_time_cnt m_blk_write_time_sum Total time the statement spent writing blocks I provided the whole table structure here as it includes a description for many columns. Note not all columns will contain data for all database engines in all configurations, and some are not yet used at all. Before we get to queries let me explain some general design considerations for this table. We do not store performance information for every single query; it is not always available to begin with (for example, if using MySQL Performance Schema). Even if it was available with modern database engines capable of serving 1M+ QPS, it would still be a lot of data to store and process. Instead, we aggregate statistics by “buckets”  which can be seen as sort key in the “metrics” table: ORDER BY (queryid,service_name,database,schema,username,client_host,period_start) You can think about Sort Key as similar to Clustered  Index in MySQL. Basically, for every period (1 minute by default) we store information for every queried, service_name, database, schema, username, and client_host combination. Period_Start   is stored in the UTC timezone. QueryID – is a  hash which identifies unique query pattern, such as “select c from sbtest1 where id=?” Service_Name is the name of the database instance  Database  – is the database or Catalog.  We use it in PostgreSQL terminology, not MySQL one Schema – this is Schema, which also can be referred to as Database in MySQL  UserName –  The Database level  User Name, which ran this given query. Client_Host –  HostName or IP of the Client This data storage format allows us to provide a very detailed workload analysis, for example, you can see if there is a difference in performance profile between different schemas, which is very valuable for many applications that use the “tenant per schema” approach. Or you can see specific workloads that different users generate on your database fleet.  Another thing you may notice is that each metric for each grouping bucket stores several statistical values, such as:  `m_query_time_cnt` Float32 COMMENT 'The statement execution time in seconds was met.', `m_query_time_sum` Float32 COMMENT 'The statement execution time in seconds.', `m_query_time_min` Float32 COMMENT 'Smallest value of query_time in bucket', `m_query_time_max` Float32 COMMENT 'Biggest value of query_time in bucket', `m_query_time_p99` Float32 COMMENT '99 percentile of value of query_time in bucket', The  _cnt  value is the number of times this metric was reported.  Every Query should have query_time available but many other measurements may not be available for every engine and any configuration.  The _sum value is the sum for the metric among all _cnt  queries. So if you want to compute _avg you should divide   _sum by _cnt. _min, _max and _p99  store the minimum, maximum, and 99 percentile value. How to Access ClickHouse To access ClickHouse on PMM Server you should run the “clickhouse-client”  command line tool. If you’re deploying MySQL with Docker you can just run: docker exec -it pmm2-server clickhouse-client Where pmm2-server is the name of the container you’re using for PMM. Run  “use pmm”  to select the current schema to PMM. Query Examples ClickHouse uses SQL-like language as its query language. I call it SQL-like as it does not implement SQL standard fully, yet it has many additional and very useful extensions. You can find the complete ClickHouse Query Language reference here.  # Number of Queries for the period select sum(num_queries) from metrics where period_start>'2020-03-18 00:00:00'; # Average Query Execution Time for Last 6 hours select avg(m_query_time_sum/m_query_time_cnt) from metrics where period_start>subtractHours(now(),6); # What are most frequent query ids ? Also calculate total number of queries in the same query select queryid,sum(num_queries) cnt from metrics where period_start>subtractHours(now(),6) group by queryid with totals order by cnt desc limit 10; # How do actual queries look for those IDs ? select any(example),sum(num_queries) cnt from metrics where period_start>subtractHours(now(),6) group by queryid order by cnt desc limit 10 \G # queries hitting particular host select any(example),sum(num_queries) cnt from metrics where period_start>subtractHours(now(),6) and node_name='mysql1' group by queryid order by cnt desc limit 10 \G # slowest instances of the queries select any(example),sum(num_queries) cnt, max(m_query_time_max) slowest from metrics where period_start>subtractHours(now(),6) group by queryid order by slowest desc limit 10 \G # Query pattern which resulted in the largest temporary table created select example, m_tmp_table_sizes_max from metrics where period_start>subtractHours(now(),6) order by m_tmp_table_sizes_max desc limit 1 \G # Slowest Queries Containing Delete in the text select any(example),sum(num_queries) cnt, max(m_query_time_max) slowest from metrics where period_start>subtractHours(now(),6) and lowerUTF8(example) like '%delete%' group by queryid order by slowest desc limit 10 \G I hope this gets you started! If you create some other queries which you find particularly helpful, please feel free to leave them in the comments for others to enjoy!
  5. Which Cloud Provider Performs Better for My Mysql Workload?

    More and more people are nowadays thinking of cloud migration. The question of “Which cloud provider performs better for my MySQL workload?” is really common but cannot always be easily answered. However, there are ways to come up with an answer. This question also applies when thinking of moving to any provider, not necessarily a cloud one or DBaaS. The Problem The most reliable conclusion can be found if you have a testing environment that is fully identical and can produce the same amount of traffic compared to your production version. In this case, the comparison should be straightforward as what you have to do is point your testing environment against the under-evaluation (cloud) providers, and evaluate the differences in performance. But this is not always easy, as many organizations do not have such environments or it’s not always possible to replay all traffic. The Idea In this post, if you don’t already have a testing environment that can produce production-like traffic, I am going to show you one methodology that will help you decide which provider offers better performance for your workload. First of all, what we need to take into consideration is that finding the most suitable provider to migrate to depends on a few different factors such as: Pricing Business requirements Features and tools provided Limitations that may apply Technologies supported High Availability And so on… There are already many blog posts describing that, and some time ago I wrote one with a high-level overview of RDS and Aurora called “When Should I Use Amazon Aurora and When Should I use RDS MySQL?“. Assuming that you have already evaluated everything, and came across multiple available options, you may now have to make your final decision; but this is not easy as you don’t really know which of these providers performs better for your workload. I’m pretty sure that you already know some benchmarking tools such as sysbench, mysqlslap, tpcc-mysql, etc. Searching around you may find out that there are multiple whitepapers and benchmarks available. And as you most probably already know, one of the major factors when performing a benchmark is the workload, and the workload for every environment is different. For example, you may come to two totally different benchmark results if you replay different workloads. One benchmark may say that provider X is better but another benchmark may say that provider Y is better. One benchmark may say that provider X provides 20% better performance but another may say that both providers provide the same performance. And this is not strange, because assuming that hardware between the providers is similar, any actual benchmark relies on the workload. The scenario I am going to present is really simple and based on a single host accepting full traffic, read and write. We will compare how queries collected on the current production master are behaving when replayed against different providers. The logic is similar for every topology, so you could easily do any adjustments to match your environment. Please be aware that enabling slow logs may introduce some overhead due to the increased IO needs for writing the logs, so enable them with caution. It may also introduce increased needs for disk capacity (depending on the number of logs captured). The log file should ideally reside on a separate partition or on a partition that is not used by the main function of the server. Prerequisites Let’s say that we have to choose between two providers, provider A and provider B. For doing the performance evaluation, we will need the following: Three hosts with identical hardware specs. One located at your current provider, another hosted at provider A, and the last one hosted at provider B. Hardware specs do not necessarily need to match your current masters but should be the ones you are attempting to move to. MySQL/Percona Server for MySQL/MariaDB (depends on what you currently have) – same version and configuration to your production host – installed on all three hosts above The same exact dataset for all three hosts above A sample of queries ran against your master A tool that will replay these queries against the three hosts above Tasks one, two, and three should be straightforward to do on your own so I will only focus on the remaining ones. To collect the queries performed against your master, you need to enable slog logs and set an appropriate sampling (if applicable). The suggested settings are: slow_query_log = ON long_query_time = 0 log_output = FILE (or empty which defaults to FILE) log_slow_rate_limit = 100. To allow us to collect more queries this should be further lowered after initial configuration. This configuration variable applies to Percona Server for MySQL and MariaDB only. If you are using Percona Monitoring and Management Query Analytics, the settings above may already be enabled so you may only have to temporarily adjust them. In any case, you should cross-check if this is the case and adjust as needed because your settings may not match the ones suggested. With the settings above you are going to log every single query (long_query_time=0) with a sampling of 1/100 (log_slow_rate_limit = 100). Sampling can be further adjusted i.e. 1 so we can capture all queries. There is no magic number for how many slow logs to collect or what the better timeframe is to collect slow logs. It depends on your workload and we usually prefer a peak time, i.e. the busiest window during a normal business day, a 24-hour window, or times when reoccurring normal jobs like end of month jobs, daily batch processing, billing, etc., so we can collect as many logs as possible. The Benchmark Having the slow logs, we need to replay them and collect the metrics. We will replay them using the pt-upgrade tool which is part of percona-toolkit. What you need to have in mind is that pt-upgrade does not offer a concurrency option, so all queries will be sequentially replayed. Please be sure that, while performing the pt-upgrade operations, none of the three hosts of the above setup is taking any traffic other than the one we are going to generate, as otherwise, the results will not be reliable. Steps to replay traffic: We first need to run pt-query-digest on the slow logs we collected (let’s say slow.log). The file that we are going to run pt-query-digest against is the one specified by slow_query_log_file configuration variable. time pt-query-digest --sample 50 --no-report --output slowlog slow.log > slow_log_50_samples.log This way, we will export 50 samples per query, which is enough. The slow_log_50_samples.log will now be used for the benchmark. On every host, we need to run pt-upgrade twice. The first execution will ensure that our InnoDB buffer pool has been warmed up while the second one will be the actual execution. For the purposes of this blog, I’m going to share some results from a benchmarking that I recently did. On the testing instance on our current provider: time pt-upgrade h=localhost --max-examples=1 --save-results upc_RO_1 slow_log_50_samples.log And again specifying a new directory, time pt-upgrade h=localhost --max-examples=1 --save-results upc_RO_2 slow_log_50_samples.log where slow_log_50_samples.log is the processed slow logs that we collected. With the example above we’ve replayed all the queries and we saved the results to a folder named “upc_RO_2” (we are mostly interested in the second execution which is after warming up the InnoDB buffer pool). We now need to do exactly the same for the two hosts that we are evaluating. Run one time to warm the InnoDB buffer pool and another for the actual benchmark. This time we won’t use the slow logs as a source, but rather the results from the first execution. For further details on how pt-upgrade works and all the available options, please check the official pt-upgrade page. On provider A: time pt-upgrade upc_RO_2/ h=localhost --max-examples=1 1> GCP_RO_1_results.out 2> GCP_RO_1_results.err And again: time pt-upgrade upc_RO_2/ h=localhost --max-examples=1 1> GCP_RO_2_results.out 2> GCP_RO_2_results.err On provider B: time pt-upgrade upc_RO_2/ h=localhost --max-examples=1 1> EC2_RO_1_results.out 2> EC2_RO_1_results.err And again: time pt-upgrade upc_RO_2/ h=localhost --max-examples=1 1> EC2_RO_2_results.out 2> EC2_RO_2_results.err The files we should focus on are: GCP_RO_2_results.out which is the comparison between provider A and the current provider EC2_RO_2_results.out which is the comparison between provider B and the current provider The output in each file should end with something like that. Let’s see how it looks for provider B (EC2_RO_2_results) #----------------------------------------------------------------------- # Stats #----------------------------------------------------------------------- failed_queries 60 not_select 0 queries_filtered 0 queries_no_diffs 36149 queries_read 36830 queries_with_diffs 621 queries_with_errors 0 Which shows that: 36830 queries were read in total 60 queries failed to be executed 36149 queries had no diffs 621 queries had diffs 621 queries out of 36830 queries is almost 1.6%. These queries (1.6%) include both queries that have returned a different set of rows or performing worse so actual degradation might be even less than 1.6%. So what we know is that at least 98.4% of the queries are performing exactly the same or better than our current provider. Please note that only queries returning a different set of data or degradation will be reported, as this is what we are actually interested in, so you won’t see something like “queries performing better” in the output above. Queries that failed and queries with diffs should be further evaluated. These may help us uncover any incompatibilities or problems due to non-supported queries. To evaluate these queries you should get familiar with how pt-upgrade works. Trying to give some hints only as this is not the goal of this post, the “queries_with_diffs” queries may report differences for various reason such as: Queries are expected to report different output due to the difference in the hostname. monitoring queries performance_schema information_schema use of DATETIME functions such as NOW(), CURDATE(). different order (missing order by) or order by column has similar values including NULL for multiple rows Queries_with_diffs also includes the queries which are worse-performing. Doing a grep for “increase” will help you reveal the queries performing worse, i.e.: $ fgrep -i increase EC2_RO_2_results.out 0.002259 vs. 0.029961 seconds (13.3x increase) 0.007984 vs. 0.084002 seconds (10.5x increase) 0.005101 vs. 0.051768 seconds (10.1x increase) 0.000886 vs. 0.009347 seconds (10.5x increase) 0.010519 vs. 0.118369 seconds (11.3x increase) So, in this case, we have only five examples of queries performing worse. These queries can be found in the EC2_RO_2_results.out file which can be further reviewed. What you now need to do is repeat the evaluation above for the results for provider A (GCP_RO_2_results.out). After reviewing this as well, it should be straightforward for you to identify which provider offers better performance. For the scenario above, we compared how our current provider is performing compared to provider A and provider B. Following the same logic, you can make as many combinations as you want. For example, you can compare the current provider to another single provider only, compare provider A to provider B, add another provider C, etc. Assuming that you performed the READ ONLY testing and you are interested in WRITE performance as well, you can now do a WRITE testing. The only adjustments you’d need to do is: Add –no-read-only to the pt-upgrade options Perform only once against each node as there is no need to warm the InnoDB buffer pool Select a different folder to save the results and a different filename for the statistics For your evaluation, you should follow the same strategy as you did for the READ ONLY testing. *** Please note that WRITE testing is destructive to the dataset operation so you must be sure that you are not performing this against your master or any other valuable dataset. While doing a WRITE testing, all queries that you have captured on your master will be replayed and the subsequent write fails may fail. For example, you may have captured an “insert” query but the record may already exist in the snapshot of data you’ve used for the performance testing. So when trying to run this insert again, it may violate a PK. Conclusion When planning a migration you should always evaluate performance using your own workload as every workload is unique. pt-upgrade can help you identify incompatibilities, queries that may fail when replayed against different hosts, or performance degradation. It’s a great tool that can help you evaluate different providers and compare performance using your own workload.

Upcoming Events

Visitors

We have 86 guests and no members online