As promised, here are slides about MySQL 8.0 Performance from my talks at Oracle Open World 2018 and Percona LIVE Europe 2018 -- all is combined into a single PDF file to give you an overall summary about what we already completed, where we're going in the next updates within our "continuous release", and what kind of performance issues we're digging right now.. ;-))
Also, I'd like to say that both Conferences were simply awesome, and it's great to see a constantly growing level of skills of all MySQL Users attending these Conferences ! -- hope you'll have even more fun with MySQL 8.0 now ;-))
10 Older Entries »
This article is inspired by Percona blog post comparing MySQL 8.0 and
Percona Server 5.7 on IO-bound workload with Intel Optane
storage. There are several claims made by Vadim based on a single
test case, which is simply unfair. So, I'll try to clarify this
all based on more test results and more tech details..
But before we start, some intro :
InnoDB Parallel Flushing -- was introduced with MySQL 5.7 (as a single-thread flushing could no more follow), and implemented as dedicated parallel threads (cleaners) which are involved in background once per second to do LRU-driven flushing first (in case there is no more or too low amount of free pages) and then REDO-driven flushing (to flush …
MySQL replication has evolved a lot in 5.6 ,5.7 and 8.0. This presentation focus on the changes made in parallel replication. It covers MySQL 8.0. It was presented at Mydbops database meetup on 04-08-2016 in Bangalore.
In Percona Monitoring and Management (PMM) 1.13 we have adopted Prometheus 2, and with this comes a dramatic improvement in resource usage, along with performance improvements!
What does it mean for you? This means you can have a significantly larger number of servers and database instances monitored by the same PMM installation. Or you can reduce the instance size you use to monitor your environment and save some money.
Let’s look at some stats!
We can see an approximate 5x and 8x reduction of CPU usage on these two PMM Servers. Depending on the workload, we see CPU usage reductions to range between 3x and 10x.
There is also less disk write bandwidth required:
On this instance, the bandwidth reduction is “just” 1.5x times. …[Read more]
More than 2 months passed since my previous review of active MySQL bug reports I
am subscribed to, so it's time to describe what I was interested
in this summer.
Let's start with few bug reports that really surprised me:
- Bug #91893 - "LOAD DATA INFILE throws error with NOT NULL column defined via SET". The bug was reported yesterday and seem to be about a regression in MySQL 8.0.12 vs older versions. At least I have no problem to use such a way to generate columns for LOAD DATA with MariaDB 10.3.7.
- Bug #91847 - "Assertion `thread_ids.empty()' failed.". As usual, Roel Van de Paar …
The main focus of a previous blog post was the performance of MyRocks when using fast SSD devices. However, I figured that MyRocks would be beneficial for use in cloud workloads, where storage is either slow or expensive.
In that earlier post, we demonstrated the benefits of MyRocks, especially for heavy IO workloads. Meanwhile, Mark wrote in his blog that the CPU overhead in MyRocks might be significant for CPU-bound workloads, but this should not be the issue for IO-bound workloads.
In the cloud the cost of resources is a major consideration. Let’s review the annual cost for the processing and storage …[Read more]
MySQL 8.0.10 GPL came out with more changes and advanced features. We have changes on InnoDB physical file layout ( MySQL data directory ) too. This blog will provide the information about the MySQL 8 InnoDB physical files.
MySQL system tables are completely InnoDB now ?
Yes, Previously, we don’t have too many InnoDB tables on MySQL system tables. We have the innodb_index_stats, innodb_table_stats, slave_master_info, slave_relay_log_info and slave_worker_info in MySQL 5.7 as InnoDB tables. But, now all the MySQL System tables were converted to InnoDB from MySQL 8.0 . It helps in the transactional DDL’s .
Below are the list of InnoDB physical files on MySQL 8.0 .
- .ibd …
MySQL 8.0 has come with a list of new features for DBA’s ,we will discuss the new feature in MySQL 8.0 which supports Descending index.Prior to MySQL 8.0 (i.e MySQL 5.6 and 5.7) creating desc index syntax was supported but desc keyword was ignored, Now in MySQL 8.0 release descending index is extended are supported.
What is index?
- Indexes play an important role in performance optimization and they are used frequently to speed up access to particular data and reduce disk I/O operations .
- To understand index easily you can imagine a book,every book has an index with content referring to a page number.If you want to search something in a book you first refer to the index and get the page number and then get the information in the page,like this the indexes in MySQL will tell you the row with matching data.
MySQL semisynchronous replication provides improved data integrity because when a commit returns successfully, it’s known that the data exists in at least two places – the master and its slave. In this blog post, we review some of the MySQL hosting configurations that influence the data integrity and performance aspects of semisynchronous replication. We’ll be using InnoDB storage engine and GTID-based replication in a 3-node replica set (master and 2 slaves), which will ensure there is redundancy in the slaves. This means that if there are issues with one slave, we can fall back on the other.
Configurations Applicable to Both Master and Slave Nodes
This post is mainly inspired by findings from the previous
testing of MySQL 8.0 on TPCC workload(s) and observations
from IO-bound Sysbench OLTP on Optane -vs- SSD. But
also by several "urban myths" I'm often hearing when discussing
with users about their IO-bound OLTP performance problems :
Myth #1 : "if I'll double the number of my storage drives -- I'll get x2 times better TPS !"
- this was mostly true during "HDD era", and again..
- (ex.: a single thread app doing single random IO reads from a single HDD will not go faster by doing the same from 2x HDD -- similar like single thread workload will not run faster on 8CPU cores -vs- 2CPU cores, etc.)
- all depends …
10 Older Entries »