Showing entries 1 to 10 of 93
10 Older Entries »
Displaying posts with tag: mysql performance (reset)
On MySQL and Intel Optane performance

Recently, Dimitri published the results of measuring MySQL 8.0 on Intel Optane storage device. In this blog post, I wanted to look at this in more detail and explore the performance of MySQL 8, MySQL 5.7 and Percona Server for MySQL using a similar set up. The Intel Optane is a very capable device, so I was puzzled that Dimitri chose MySQL options that are either not safe or not recommended for production workloads.

Since we have an Intel Optane in our labs, I wanted to run a similar benchmark, but using settings that we would recommend our customers to use, namely:

  • use innodb_checksum
  • use innodb_doublewrite
  • use binary logs with sync_binlog=1
  • enable (by default) Performance …
[Read more]
What is MySQL partitioning ?

MySQL partitioning makes data distribution of individual tables (typically we recommend partition for large & complex I/O table for performance, scalability and manageability) across multiple files based on partition strategy / rules. In very simple terms, different portions of table are stored as separate tables in different location to distribute I/O optimally. The user defined division of data by some rule is known as partition function, In MySQL we partition data by RANGE of values / LIST of values / internal hashing function / linear hashing function. By restricting the query examination on the selected partitions by matching rows increases the query performance by multiple times compared to the same query on a non partitioned table, This methodology is also called partition pruning (trimming of unwanted partitions), Please find below example of partition pruning:

[Read more]
Chunk Change: InnoDB Buffer Pool Resizing

Since MySQL 5.7.5, we have been able to resize dynamically the InnoDB Buffer Pool. This new feature also introduced a new variable — innodb_buffer_pool_chunk_size — which defines the chunk size by which the buffer pool is enlarged or reduced. This variable is not dynamic and if it is incorrectly configured, could lead to undesired situations.

Let’s see first how innodb_buffer_pool_size , innodb_buffer_pool_instances  and innodb_buffer_pool_chunk_size interact:

The buffer pool can hold several instances and each instance is divided into chunks. There is some information that we need to take into account: the number of instances can go from 1 to 64 and the total amount of chunks should not exceed 1000.

So, for a server with 3GB RAM, a buffer pool of 2GB with 8 instances and chunks at default value (128MB) we are going to get 2 chunks per instance:

This means that there will be 16 chunks.

[Read more]
How to use mysqlpump for faster MySQL logical backup ?

MySQL 5.7.8 introduced much improved version of mysqldump, It’s called “mysqlpump”, mysqlpump is much faster than mysqldump with parallel threads capabilities, There are many other compelling reasons for choosing mysqlpump over mysqldump, This blog is about how mysqlpump can be used for good. mysqlpump is relatively a new utility of MySQL and we are confident that Oracle MySQL will invest more to make mysqlpump efficient, we haven’t recommended mysqlpump in production for any of our customers till date, considering several concerns. The following below are mysqlpump features we are really excited about:

  • Supports parallel MySQL logical backup, The resource usage efficiency and high performance backups (we love it !)
  • Much better orchestration possible – You can backup selected databases, tables, stored programs and user accounts etc.
  • By default mysqlpump will not backup performance_schema, sys schema, …
[Read more]
Things to remember when you migrate from MyISAM to InnoDB

Occasionally we have customers with MyISAM storage engine approaching us to migrate their database to InnoDB, MyISAM is great if you are just an application like web content management system with no multi-user concurrency challenges but what if you are building an highly transactional data web property ? InnoDB is much preferred for such situations, InnoDB provides Row-level locking (Oracle like) for consistent reads on an multi-user concurrent user high performance database application. InnoDB also guarantees maximum data integrity by supporting FOREIGN KEY, We captured below few interesting points to remember while migrating your database from MyISAM to InnoDB :

  • Data of InnoDB tables is stored in *.ibd files, deleting those files will permanently corrupt your database
  • InnoDB tables consumes more storage space than MyISAM tables .
  • Unlike MyISAM, InnoDB is a transactional database engine. In any typical MyISAM …
[Read more]
MariaDB 10.3 support Oracle mode sequences

Sequences are used to requesting unique values on demand, The best use case of sequences is to have a unique ID. , that can be used across multiple tables. In some cases sequences are really helpful to have an identifier before an actual row is inserted. With the normal way of having an automatically incrementing identifier, the identifier value will only be available after insert of the row and the identifier will only be unique inside its own table. MariaDB Server 10.3 follows the standard and includes compatibility with the way Oracle does sequences introduced in Oracle Database Server on top of the standard.

Simple steps to create a sequence in MariaDB 10.3 onwards, a create statement is used:

MariaDB [MDB101]> CREATE SEQUENCE Seq1_100
    -> START WITH 100
    -> INCREMENT BY 1;
Query OK, 0 rows affected (0.015 sec)

This creates a sequence that starts at 100 and is incremented with 1 every time a …

[Read more]
MySQL 8.0 Data Dictionary

We are all familiar with “.frm” files since the earliest days of MySQL, The community has been continuously requesting for replacement of file-system based metadata for several good reasons, So with MySQL 8.0 “.frm” files are gone for ever, Going forward MySQL stores table metadata in the data dictionary tables which uses InnoDB storage engine. This blog is about MySQL 8.0 data dictionary and how it creates value for MySQL going forward:

How file based metadata management used to work in the past (before MySQL 8.0) ? 

  • Every table in MySQL will have corresponding .frm file, This .frm file stores information like column names and data-types in the binary format, In addition to the .frm file, there are .trn, .trg and .par files to support triggers, trigger namespace and partitioning .

What are major bottlenecks faced due to the usage of file based metadata management …

[Read more]
A friendly comparison of InnoDB and MyRocks Performance

In this blog post, we have multiple OLTP performance benchmarking scenarios using sysbench 1.0.14 on InnoDB and MyRocks. InnoDB and MyRocks (RocksDB with MySQL) are definitely not to supplement each other, They actually compliment well with respective advantages, Let me quickly explain how InnoDB and MyRocks can benefit you when used wisely, Again this blog post is not to show who (InnoDB or MyRocks) is better ? We regularly benchmark both of these storage engines before recommending to our customers on what is best suited for their database infrastructure operations ? so we would like to share our thoughts on this post.

How InnoDB and MyRocks are different ?

  • MyRocks supports only READ-COMMITTED isolation level, There is no REPEATABLE-READ isolation level like InnoDB so no gap locking like InnoDB, We have written detailed blog on InnoDB transaction isolation levels …
[Read more]
Call for Questions: Webinar with MySQL Benchmarking Experts

If you attended my latest Troubleshooting MySQL Concurrency Issues with Load Testing Tools webinar you learned how I exploit benchmarking tools to replicate locking issues and crashes. But I told you nothing about proper use of these tools: for reliable benchmarks. I did not tell you that for a reason… I am just a Support Engineer and not a benchmarking expert.

And I have a dream. I want to invite world famous MySQL benchmarking experts for a Percona webinar and ask them for their secrets. I have a pretty long list of my own questions, but in order to make 1-hour webinar productive, we need to concentrate on just a few of the hottest ones. Therefore I need your help.

Please add your questions into the comments field of this blog post. Then we will choose the most important and/or most popular of …

[Read more]
Troubleshooting MySQL Concurrency Issues with Load Testing Tools Webinar: Q & A

In this blog, I will provide answers to the Q & A for the Troubleshooting MySQL Concurrency Issues with Load Testing Tools webinar.

First, I want to thank everybody for attending my May 23, 2018, webinar on troubleshooting tools for MySQL. The recording and slides for the webinar are available here. Below is the list of your questions that I was unable to answer fully during the webinar.

Q: What do you recommend for benchmarking NDB cluster? Which should be used and how?

A: The issue with benchmarking NDB cluster is not the tool choice, …

[Read more]
Showing entries 1 to 10 of 93
10 Older Entries »