Archive for category MySQL

MySQL 5.x – How to capture the number of inserts in a MySQL database

MySQL 5.x – How to capture the number of inserts in a MySQL database

I was asked to get the number of DML on a MySQL database recently. However, the performance schema was not installed so I couldn’t query from it. I found a status variable called innodb_rows_inserted.

mysql> show status like 'Innodb_rows_inserted'; select sleep(60) ; show status like 'Innodb_rows_inserted' ;
+----------------------+-----------+
| Variable_name | Value |
+----------------------+-----------+
| Innodb_rows_inserted | 576654182 |
+----------------------+-----------+
1 row in set (0.00 sec)

+-----------+
| sleep(60) |
+-----------+
| 0 |
+-----------+
1 row in set (1 min 0.00 sec)

+----------------------+-----------+
| Variable_name | Value |
+----------------------+-----------+
| Innodb_rows_inserted | 576684821 |
+----------------------+-----------+
1 row in set (0.01 sec)

To get the value/second, simply perform the calculation below;

(576684821-576654182)/60 = ~510 inserts per second

Obviously, the number above is only for inserts. You can also modify the command above to include the following;

  • Innodb_rows_inserted
  • Innodb_rows_read
  • Innodb_rows_updated
  • Innodb_rows_deleted

Hope this helps!

Regards,
Wei Shan

Advertisements

,

Leave a comment

MySQL 5.7 – Index Condition Pushdown Optimization Bug

MySQL 5.7 – Index Condition Pushdown Optimisation Bug

Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. It uses the indexed columns to determine if they should retrieve the row instead of retrieving the entire table then filtering off the unnecessary rows.

Disable ICP if you are running a query with ORDER BY DESC against partitioned tables. It slows down the query considerably. You will definitely hit this bug if you are running zabbix server on MySQL database.

To disable ICP, use the following:

SET optimizer_switch='index_condition_pushdown=off';

Hope this helps!

Regards,
Wei Shan

References:

,

Leave a comment

Using strace in Linux to troubleshoot database performance issues

Using strace in Linux to troubleshoot database performance issues

Use Case:

Using top and iostat, we know that the database is slow due to IO constraints. What if we want to narrow down to the exact file causing the performance issues? What if we want to know what is the DB process waiting on?

Find the PID of the process

[root@hostname ~]# ps -aef | grep mysqld
mysql 16013 15513 23 Dec08 ? 1-00:17:41 /usr/sbin/mysqld --basedir=/usr --datadir=/data/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep-provider=/usr/lib64/libgalera_smm.so --log-error=/var/log/mysql/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --wsrep_start_position=a09ed4ce-79bc-11e6-9b2e-63d190df3dd7:42170895

Find out which system calls took the longest time

[root@hostname ~]# strace -cp 16013
Process 16013 attached
^CProcess 16013 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
 98.63 28.601334 147430 194 poll
 1.33 0.387070 387070 1 restart_syscall
 0.03 0.008057 22 359 futex
 0.00 0.000891 5 195 accept
 0.00 0.000369 2 195 fcntl
------ ----------- ----------- --------- --------- ----------------
100.00 28.997721 944 total

Attach strace to the PID to find out the file descriptor it is waiting for.

[root@hostname ~]# strace -p 16013
Process 16013 attached
restart_syscall(<... resuming interrupted call ...>) = 1
accept(34, {sa_family=AF_LOCAL, NULL}, [2]) = 128
fcntl(128, F_SETFD, FD_CLOEXEC) = 0
futex(0x1e937c4, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0x1e937c0, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
futex(0x1e93800, FUTEX_WAKE_PRIVATE, 1) = 1
poll([{fd=50, events=POLLIN}, {fd=34, events=POLLIN}], 2, 4294967295) = 1 ([{fd=34, revents=POLLIN}])
accept(34, {sa_family=AF_LOCAL, NULL}, [2]) = 128
fcntl(128, F_SETFD, FD_CLOEXEC) = 0
futex(0x1e937c4, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0x1e937c0, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
futex(0x1e93800, FUTEX_WAKE_PRIVATE, 1) = 1
poll([{fd=50, events=POLLIN}, {fd=34, events=POLLIN}], 2, 4294967295) = 1 ([{fd=34, revents=POLLIN}])
accept(34, {sa_family=AF_LOCAL, NULL}, [2]) = 128
fcntl(128, F_SETFD, FD_CLOEXEC) = 0

Find the file that the file descriptor is pointing to!

ls -altr /proc/16013
lrwx------. 1 root root 64 Dec 12 15:46 50 -> /data/mysql/zabbix/history_uint#P#p201612160000.ibd
lrwx------. 1 root root 64 Dec 12 15:46 51 -> /data/mysql/zabbix/history_uint#P#p201612170000.ibd

Now we know mysqld process has been waiting for partitioned data file history_uint#P#p201612160000.ibd!

Hope this helps.

Regards,
Wei Shan

Leave a comment

Solaris Sun Cluster – Changing Fault Monitor Password (fmuser) for MySQL

Solaris Sun Cluster – Changing Fault Monitor Password (fmuser) for MySQL

There was a requirement to change the fmuser password after the Sun Cluster for MySQL was implemented. The customer wanted to change the default password to something else. The fmuser is the default user for the Sun Cluster Fault Monitor to interact with the MySQL database.

Step 1: Check the cluster status.

# /usr/cluster/bin/clresource status
=== Cluster Resources ===

Resource Name Node Name State Status Message
------------- --------- ----- --------------
mysql-rg       node1           Online Online 
               node2           Offline Offline

mysql-lh-res node1 Offline Offline - LogicalHostname offline.
             node2 Online Online - LogicalHostname online.

mysql-stor-res node1 Offline Offline
               node2 Online Online

Step 2: Stop fault monitoring on mysql-rg resource (Note: The resource, not the resource group!)

# /usr/cluster/bin/clresource unmonitor mysql-rg
# /usr/cluster/bin/clresource status

=== Cluster Resources ===

Resource Name Node Name State Status Message
------------- --------- ----- --------------
mysql-rg node1 Online_not_monitored Online_not_monitored
 node2 Offline Offline

mysql-lh-res node1 Online Online - LogicalHostname online.
 node2 Offline Offline

mysql-stor-res node1 Online Online
 node2 Offline Offline

Step 3: Change the fmuser password in the MySQL database

/appl/bin/mysql -uroot -ppassword -S '/tmp/vnode.sock'

mysql> set password for 'fmuser'@'node1' = PASSWORD('newpassword');
Query OK, 0 rows affected (0.00 sec)

mysql> set password for 'fmuser'@'node2' = PASSWORD('newpassword');
Query OK, 0 rows affected (0.00 sec)

Step 4: Disable the mysql-rg resource and change the properties of it. We will need to change 4 properties of it. (Validate, Stop, Probe and Start)

# /usr/cluster/bin/clresource disable mysql-rg

./scrgadm -c -j mysql-rg -x Validate_command="/opt/SUNWscmys/bin/control_mysql -R mysql-rg -G mysql-rg -B /appl -D /data -U mysql -H vnode -F fmuser%newpassword -L /logs -C validate"
./scrgadm -c -j mysql-rg -x Stop_command="/opt/SUNWscmys/bin/stop_mysql -R mysql-rg -G mysql-rg -B /appl -D /data -U mysql -H vnode -F fmuser%newpassword -L /logs -C"
./scrgadm -c -j mysql-rg -x Probe_command="/opt/SUNWscmys/bin/probe_mysql -R mysql-rg -G mysql-rg -B /appl -D /data -U mysql -H vnode -F fmuser%newpassword -L /logs -C"
./scrgadm -c -j mysql-rg -x Start_command="/opt/SUNWscmys/bin/start_mysql -R mysql-rg -G mysql-rg -B /appl -D /data -U mysql -H vnode -F fmuser%newpassword -L /logs -C"

Step 5: Enable the mysql-rg resource and start back fault monitoring on the mysql resource.

#/usr/cluster/bin/clresource enable mysql-rg
#/usr/cluster/bin/clresource monitor mysql-rg

Please note that the above steps will result downtime. That’s all!

Regards,
Wei Shan

Leave a comment

MariaDB – ERROR 1033 (HY000): Incorrect information in file:

MariaDB/TokuDB – ERROR 1033 (HY000): Incorrect information in file:

While I was testing the TokuDB HotBackup plugin from Percona, I faced some error during a complete database recovery. After the recovery was completed, I tried verifying the table contents.

MariaDB [partitiontest]> select count(*) from salaries;
ERROR 1033 (HY000): Incorrect information in file: './partitiontest/salaries.frm'

Looking at the error message, I was under the impression that the tables were corrupted.

From the logs:

160127 11:10:56 [ERROR] mysql.plugin: 1 client is using or hasn't closed the table properly
160127 11:10:56 [ERROR] Can't open shared library '/usr/lib64/mysql/plugin/tokudb_backup.so' (errno: 0, cannot open shared object file: No such file or dir
ectory)
160127 11:10:56 server_audit: MariaDB Audit Plugin version 1.3.0 STARTED.
160127 11:10:56 [Note] Server socket created on IP: '::'.
160127 11:10:56 [Warning] 'user' entry 'root@centos7.local' ignored in --skip-name-resolve mode.
160127 11:10:56 [Warning] 'user' entry '@centos7.local' ignored in --skip-name-resolve mode.
160127 11:10:56 [Warning] 'proxies_priv' entry '@% root@centos7.local' ignored in --skip-name-resolve mode.
160127 11:10:56 [Note] Reading of all Master_info entries succeded
160127 11:10:56 [Note] Added new Master_info '' to hash table
160127 11:10:56 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.0.22-MariaDB-enterprise-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Enterprise Certified Binary
160127 11:11:28 [ERROR] mysqld: Incorrect information in file: './partitiontest/salaries.frm'

The error message above looks really scary. While snooping around Google, I realized that the error is very vague, I decided to look deeper. On examining the logs again, I realized that the following error:

160127 11:10:56 [ERROR] Can't open shared library '/usr/lib64/mysql/plugin/tokudb_backup.so' (errno: 0, cannot open shared object file: No such file or dir
ectory)

Once I enabled the TokuDB plugin in /etc/my.cnf.d/tokudb.conf, the recovery works fine. I learnt to be more careful when dealing with such MariaDB errors.

Regards,
Wei Shan

Leave a comment

MariaDB/MySQL + TokuDB Partitioning Error – ERROR 1463 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

MariaDB/MySQL + TokuDB Partitioning Error – ERROR 1463 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

The above error occurs while trying to add a new partition in a MariaDB/MySQL partitioned table.

Existing table definition

MariaDB [partitiontest]> show create table salaries;
| salaries | CREATE TABLE `salaries` (
 `emp_no` int(11) NOT NULL,
 `salary` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`from_date`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1 `compression`='tokudb_zlib'
/*!50500 PARTITION BY RANGE COLUMNS(from_date)
(PARTITION p01 VALUES LESS THAN ('1985-12-31') ENGINE = TokuDB,
 PARTITION p02 VALUES LESS THAN ('1986-12-31') ENGINE = TokuDB,
 PARTITION p03 VALUES LESS THAN ('1987-12-31') ENGINE = TokuDB,
 PARTITION p04 VALUES LESS THAN ('1988-12-31') ENGINE = TokuDB,
 PARTITION p05 VALUES LESS THAN ('1989-12-31') ENGINE = TokuDB,
 PARTITION p06 VALUES LESS THAN ('1990-12-31') ENGINE = TokuDB,
 PARTITION p07 VALUES LESS THAN ('1991-12-31') ENGINE = TokuDB,
 PARTITION p08 VALUES LESS THAN ('1992-12-31') ENGINE = TokuDB,
 PARTITION p09 VALUES LESS THAN ('1993-12-31') ENGINE = TokuDB,
 PARTITION p10 VALUES LESS THAN ('1994-12-31') ENGINE = TokuDB,
 PARTITION p11 VALUES LESS THAN ('1995-12-31') ENGINE = TokuDB,
 PARTITION p12 VALUES LESS THAN ('1996-12-31') ENGINE = TokuDB,
 PARTITION p13 VALUES LESS THAN ('1997-12-31') ENGINE = TokuDB,
 PARTITION p14 VALUES LESS THAN ('1998-12-31') ENGINE = TokuDB,
 PARTITION p15 VALUES LESS THAN ('1999-12-31') ENGINE = TokuDB,
 PARTITION p16 VALUES LESS THAN ('2000-12-31') ENGINE = TokuDB,
 PARTITION p17 VALUES LESS THAN ('2001-12-31') ENGINE = TokuDB,
 PARTITION p18 VALUES LESS THAN ('2002-12-31') ENGINE = TokuDB,
 PARTITION p19 VALUES LESS THAN (MAXVALUE) ENGINE = TokuDB) */

Error occur while trying to add a new partition

alter table salaries
add partition
(partition p20 values less than ('2003-12-31')
engine=TokuDB);
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

The error is due to the following reasons:

  • You are trying to add a “lesser” value that the current largest value, which captures all
  • In this case, I was trying to add <2003-12-13 date, which is lesser than the current last partition, MAXVALUE

I will now “REORGANIZE” the table so that I can add a partition between “<2002-12-13” and “MAXVALUE”.

alter table salaries
reorganize partition p19 into
(partition p19 values less than ('2003-12-31'),
partition p20 values less than (MAXVALUE)
)

Let’s take at a look at the happier table now!

MariaDB [partitiontest]> show create table salaries;
 | salaries | CREATE TABLE `salaries` (
 `emp_no` int(11) NOT NULL,
 `salary` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`from_date`)
 ) ENGINE=TokuDB DEFAULT CHARSET=latin1 `compression`='tokudb_zlib'
 /*!50500 PARTITION BY RANGE COLUMNS(from_date)
 (PARTITION p01 VALUES LESS THAN ('1985-12-31') ENGINE = TokuDB,
 PARTITION p02 VALUES LESS THAN ('1986-12-31') ENGINE = TokuDB,
 PARTITION p03 VALUES LESS THAN ('1987-12-31') ENGINE = TokuDB,
 PARTITION p04 VALUES LESS THAN ('1988-12-31') ENGINE = TokuDB,
 PARTITION p05 VALUES LESS THAN ('1989-12-31') ENGINE = TokuDB,
 PARTITION p06 VALUES LESS THAN ('1990-12-31') ENGINE = TokuDB,
 PARTITION p07 VALUES LESS THAN ('1991-12-31') ENGINE = TokuDB,
 PARTITION p08 VALUES LESS THAN ('1992-12-31') ENGINE = TokuDB,
 PARTITION p09 VALUES LESS THAN ('1993-12-31') ENGINE = TokuDB,
 PARTITION p10 VALUES LESS THAN ('1994-12-31') ENGINE = TokuDB,
 PARTITION p11 VALUES LESS THAN ('1995-12-31') ENGINE = TokuDB,
 PARTITION p12 VALUES LESS THAN ('1996-12-31') ENGINE = TokuDB,
 PARTITION p13 VALUES LESS THAN ('1997-12-31') ENGINE = TokuDB,
 PARTITION p14 VALUES LESS THAN ('1998-12-31') ENGINE = TokuDB,
 PARTITION p15 VALUES LESS THAN ('1999-12-31') ENGINE = TokuDB,
 PARTITION p16 VALUES LESS THAN ('2000-12-31') ENGINE = TokuDB,
 PARTITION p17 VALUES LESS THAN ('2001-12-31') ENGINE = TokuDB,
 PARTITION p18 VALUES LESS THAN ('2002-12-31') ENGINE = TokuDB,
 PARTITION p19 VALUES LESS THAN ('2003-12-31') ENGINE = TokuDB,
 PARTITION p20 VALUES LESS THAN (MAXVALUE) ENGINE = TokuDB) */|

Regards,
Wei Shan

Leave a comment