Hi,
Recently I have encountered a weird simple issue with starting Slave server.

So created user as tried to run change master:

2017-10-30 14:03:59 DEBUG    Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock --force test -e 
'select @@port'
2017-10-30 14:03:59 DEBUG    Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock --force test -e 
"CREATE USER 'repl'@'%' IDENTIFIED BY 'Baku12345'"
2017-10-30 14:03:59 DEBUG    Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot …

This small article is about nicely depicting memory usage prior to OOM killers show.

I have started MySQL with:

/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/bin/mysqld --no-defaults \
--innodb_buffer_pool_size=1G --innodb_log_file_size=1G --innodb_page_size=64K --early-plugin-load=keyring_file.so \ --keyring_file_data=/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/mysql-keyring/keyring \
--log-bin=mysql-bin --log-slave-updates --server-id=1 --gtid-mode=ON --enforce-gtid-consistency --binlog-format=row --core-file --basedir=/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64 \
--tmpdir=/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/data \
--datadir=/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/data \
--plugin-load-add=tokudb=ha_tokudb.so …

This post is about situation where comments in BUG reports are really valuable in term of learning and getting some internal view of going process.
Let’s take a look at -> http://bugs.mysql.com/bug.php?id=80183

The report is about how you can not drop general tablespace if it was a crash of MySQL instant while running alter statement.


mysql> create tablespace ts1 add datafile '/var/lib/mysql_tablespaces/ts1.ibd' engine=innodb;
Query OK, 0 rows affected (0,02 sec)

Kill -9 process while altering:


mysql> alter table sbtest1 tablespace ts1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL …

How it feels when you realize that, something going to be scary in term of question – “Wait, why? how?”
Let’s see:

I am trying to allocate 9G buffer pool size with 64 instances:

innodb_buffer_pool_instances=64
innodb_buffer_pool_size=9G

From error log it said that:

InnoDB: Initializing buffer pool, total size = 16G, instances = 64, chunk size = 128M

“Wait, why? how?” N1

From select it is also allocated 16G:

mysql> select @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
| 16.000000000000 |
+------------------------------------------+

Just continue to increase pool size:

If you give 17G:

[Note] InnoDB: Initializing buffer pool, …

If you are running ‘OPTIMIZE TABLE’ or altering ‘TABLESPACE’ just be careful not to kill MySQL service while running these commands
Here is a test case:
We have a table:

CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2427738 DEFAULT CHARSET=latin1 COMPRESSION='lz4'

After a while we want to ‘OPTIMIZE’ this table:

mysql> optimize table sbtest1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Uups MySQL process was killed/crashed.

[root@ps-5 mysql]# ls -l dbtest/
total 1092792
-rw-r-----. 1 mysql mysql 65 фев 4 03:43 db.opt
-rw-r-----. 1 mysql mysql …

If you have not be careful with General Tablespaces in MySQL 5.7.10, you must be at least from now.
I have reported several bugs related to General Tablespaces for eg.:

* #77814 -> Altering table twice,with same tablespace will increase general tablespace size (verfied)

* #77556 -> Segmentation fault while creating general tablespace in disk full Linux (fixed/closed)

* #77830 -> “ALGORITHM=INPLACE is not supported” no error with tablespace alter (open)

* #80070 -> allocated_size and file_size differ if create general tablespace outside datadir (verified)

* …

Today i want to share experience with latest Percona Server where TokuDB and TokuBakcup plugins have been added in main source.
So there is no other steps needed here to get and started.
But in recent discussion in our local community, there was a question: “How about installing MySQL on CentOS minimal”.
The reason was clear, because somebody got too many errors in fresh CentOS 7 minimal.

I want to share here full steps.
Please follow numbers:

1. Getting Source package:

[root@testing-host1 ~]# wget https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.27-75.0/source/tarball/percona-server-5.6.27-75.0.tar.gz

Unpacking:
tar -xvf percona-server-5.6.27-75.0.tar.gz

2. Compiling using CMAKE:

If you have read the documentation:

…

What about inserting data to MySQL 5.7.7-rc-debug with GTID/binary log enabled. It is OK until you hit disk full condition while inserting data. It will crash on the way and likely you will lose MySQL. Error log indicates related problem:

2015-07-29T07:20:05.865708Z 2 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file ./sls2/sales.ibd, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2015-07-29T07:20:05.895529Z 2 [Warning] InnoDB: 1048576 bytes should have been written. Only 557056 bytes written. Retrying again to write the remaining bytes.
2015-07-29T07:20:05.905149Z 2 [Warning] InnoDB: 491520 bytes should have been written. Only 106496 bytes written. Retrying again to write the remaining …

Dear community, maybe you have already tested, there are new added features in MySQL 5.7. See previous post -> About 5.7.7-rc BUGs

“General Tablespace” feature -> From now you can place tables to separate tablespaces. Prior, on our hands there were only “System TableSpace” and “InnoDB file per table” options. Today’s topic is altering table to use this “General Tablespace”.

Our sample table structure:

mysql> show create table t1;

    | Table | Create Table 
    | t1    | CREATE TABLE `t1` (
      `id` int(11) DEFAULT NULL,
      `c1` varchar(85) DEFAULT NULL
    ) /*!50100 TABLESPACE innodb_file_per_table */ ENGINE=InnoDB DEFAULT CHARSET=utf8 

Note that there is a comment about tablespace of our table -> /*!50100 TABLESPACE …

With MySQL 5.7 there will be many improvements and changes. The most valuable ones are:

1. “General Tablespace” feature -> From now you can place tables to separate tablespaces. Prior, on our hands there were only “System TableSpace” and “InnoDB file per table” options.

Check for further reading: General Tablespaces Create statement: Create TableSpace

2. Resizing InnoDB Buffer Pool online -> for changing innodb_buffer_pool_size variable restart is required prior to MySQL 5.7, from now we can change buffer pool size without restart dynamically. Read Documentation -> …