In my previous post, I talked about a funny replication breakage that I experienced with MariaDB. So what about different versions of MySQL... > SELECT version(); +------------+ | version() | +------------+ | 5.6.35-log | +------------+ 1 row in set (0.00 sec) > SELECT * FROM test_jfg; +----+--------+-------------+ | id | status
10 Older Entries »
By default, MySQL 5.7 is much “stricter” than older versions of MySQL. That can make your application fail. To temporarily fix this, change the
(same as in MySQL 5.6):
mysql> set global SQL_MODE="NO_ENGINE_SUBSTITUTION";
MySQL 5.7, dates and default values
in MySQL 5.7 is:
That makes MySQL operate in “strict” mode for transactional tables.
“Strict …[Read more]
Generated Columns is a feature released on MySQL 5.7. They can be used during
CREATE TABLE or
ALTER TABLE statements.
It is a way of storing data without actually sending it through
UPDATE clauses in SQL.
The database resolves what the data will be.
There are two types of Generated Columns: Virtual and Stored. They work with:
- mathematical expressions (
- built-in functions (
- literals (“2”, “new”, 0)
Besides that, they can be indexed but they don’t allow …[Read more]
This blog updates Stephane Combaudon’s blog on MySQL performance tuning, and covers MySQL 5.7 performance tuning immediately after installation.
A few years ago, Stephane Combaudon wrote a blog post on Ten MySQL performance tuning settings after installation that covers the (now) older versions of MySQL: 5.1, 5.5 and 5.6. In this post, I will look into what to tune in MySQL 5.7 (with a focus on InnoDB).
The good news is that MySQL 5.7 has significantly better default values. Morgan Tocker created a page with a complete list of features in MySQL 5.7, and is a great reference point. For example, the …[Read more]
The MySQL keynote at Oracle Open World 2016 announced the immediate availability of the MySQL Cloud Service, part of the larger Oracle Cloud offering. You can evaluate this now with a trial copy at cloud.oracle.com/mysql. MySQL server product manager Morgan Tocker gave two presentations at the event including a deep dive session.
This is the first release of the MySQL cloud service. As with all first releases there are some highlights and some pipeline features. All major cloud providers have MySQL offerings. AWS RDS (traditional, MAZ and Aurora) GCP Cloud SQL and Azure …[Read more]
Disclaimer: this post takes into consideration that strict mode is enabled on the server
used to store strings.
VARCHAR stores varying length
CHAR always use the same exact size no matter
the size of the string. For example, CHAR(4) will always
store 4 bytes, whereas VARCHAR(4) will store up to 5
bytes. See documentation.
When we create a table like this one:
We put inside the parentheses the length of the field in
characters for the
VARCHAR field. However, the
maximum size in bytes of the field will depend on the
COLLATION of the table. You
can also specify a different collation for a column.
- latin1: 1 to 2 bytes per …
Recently, I was discussing with one of my colleagues about how insert statement performs for MySQL partitioned tables. General prediction is that it should be slower than for non-partitioned tables, but how much that we didn’t know. So, I thought let’s test with different types of partitions (i.e range, list and hash) and also with different number of partitions and check how’s performance. As people says, MySQL 5.7 is must faster than old one, so I also tested partitions with it.
So, I took simple table with 3.2M records on Centos 6.7 VM (4 core with 2GB RAM) with default my.cnf settings and then created tables for range, list and hash partitioning with 5,25,50 and 100 partitions. i.e with 5 partition (range and list), the table structures were like
CREATE TABLE emp_range_5( id int, fname varchar (30), lname varchar (30), hired_date date not null, separated_date date not null, job_code int, store_id int ) …[Read more]
In a previous post, I presented an Unexpected Memory Consumption for Bulk Index Creation in InnoDB. This was triggered by an increased innodb_sort_buffer_size and as stated in another post: "the sorting algorithm does not scale well with large sort buffers". In this post, I will present why it does not scale well and I will suggest solutions.
This post also answers feedback request for the
In this post, we’ll discuss some insight I’ve gained regarding severe performance regression in MySQL 5.7 crash recovery.
Working on different InnoDB log file sizes in my previous post:
I tried to understand how we can make InnoDB crash recovery faster, but found a rather surprising 5.7 crash recovery regression.
Basically, crash recovery in MySQL 5.7 is two times slower, due to this issue: https://bugs.mysql.com/bug.php?id=80788. InnoDB now performs the log scan twice, compared to a single scan in MySQL 5.6 (no surprise that there is performance degradation).
Fortunately, there is a proposed patch for MySQL 5.7, so I hope it will be improved soon.
As for general crash …[Read more]
Researching a performance issue, we came to a startling discovery:
MySQL 5.7 + binlogs is by default 37-45% slower than MySQL 5.6 + binlogs when otherwise using the default MySQL settings
Test server MySQL versions used:
i7, 8 threads, SSD, Centos 7.2.1511
--no-defaults --log-bin=mysql-bin --server-id=2
Sysbench version 0.5, 4 threads, socket file connection
sysbench --test=/usr/share/doc/sysbench/tests/db/parallel_prepare.lua --oltp-auto-inc=off --mysql-engine-trx=yes --mysql-table-engine=innodb --oltp_table_size=1000000 --oltp_tables_count=1 --mysql-db=test --mysql-user=root --db-driver=mysql --mysql-socket=/path_to_socket_file/your_socket_file.sock prepare …[Read more]
10 Older Entries »