MySQL – When an Optimize Fails
When using the MyISAM engine, you may need to run an “optimize” to regain space and increase performance.  What do you need to know BEFORE you start the “optimize” command.

The first thing to know is the “optimize” creates a Temporary file in the database datadir, (see mr.TMD).  The problem is there is only 16GB of free space with the mr.MYD and mr.MYI are almost 40GB.  There is one more thing you need to know.  MySQL uses tmpdir at the end of the “optimize” which can be a problem if there is not enough space.

SERVER1:/db/mysql/data/stats>ls -lt kh*                                                                   -rw-rw---- 1 pm pmapp 18163175424 Mar 23 …

Scripts I Use to Isolate MySQL Performance Problems

#!/bin/sh
#./run_iostat.sh > REPORT.doc &
x=1
while [ $x -le 60 ]
do
echo "START RUN TIME"
date
/usr/bin/iostat -d 2 6
date
  x=$(( $x + 1 ))
date
echo "END RUN TIME"
sleep 60
done


#!/bin/sh
#./run_mysqlreport_sleep60.sh > REPORT.doc &
x=1
while [ $x -le 60 ]
do
echo "START TIME"
date
mysqlreport –user USER --password PASSWD --host HOST --port 3306
  x=$(( $x + 1 ))
echo "END DATE"
sleep 60
done


#!/bin/sh
#./run_iostat.sh > VMSTAT_REPORT.doc &
x=1
while [ $x -le 60 ]
do
echo "START TIME"
date
/usr/bin/vmstat
/usr/bin/vmstat -d
  x=$(( $x + 1 ))
echo "END DATE" …

=================================================MySQL Configuration Changes

 =================================================This document is intended to explain how MySQL creates Temporary Tables and how to tune the MySQL Configuration file to improve query performance for Power Users.  MySQL will create a Temporary Table when a user issues a Group By or Order By command.  
MySQL will first create the Temporary Table in Memory, if the query exceeds the value of certain parameters in the my.cnf file, MySQL will use the MyISAM Storage Engine creating *.MYI and *.MYD files in the /tmp directory.  When MySQL has to create the *.MYI and *.MTD files the query performance will be slower than creating the Temporary Tables in Memory.

I created a Test on dl4dev to illustrate how MySQL creates Temporary Tables and how changing the MySQL Configuration file will improve performance.  My plan was …

MySQL 8 features invisible indexes. But what good is an index if you can see it? br>Is It Really InvisibleYou probably know by now that indexes can really speed up data searches. And many of you have mastered the use of EXPLAIN to see if the optimizer is using an index for your query. Which means you should know that sometimes for some queries an index does not help.

mysql> CREATE TABLE t2 (
    ->   i INT NOT NULL,
    ->   j INT NOT NULL,
    ->   UNIQUE j_idx (j)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values (1,2),(3,4),(5,6),(7,8);
Query OK, 4 rows affected (0.00 sec)

mysql> explain select j from t2 where j>2;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
| id | select_type | table | …

Percona announces the release of Percona Server 5.6.35-80.0 on February 8, 2017. Download the latest version from the Percona web site or the Percona Software Repositories.

Based on MySQL 5.6.35, and including all the bug fixes in it, Percona Server 5.6.35-80.0 is the current GA release in the Percona Server 5.6 series. Percona Server is open-source and free – this is the latest release of our …

As a part database administration, DBA has to take care of sub-components of database like server logs and has to plan for maintenance activity for these components regularly.

MySQL has various types of log i.e binary log, error log, slow query log, general log for different purposes. And after certain time these logs will grow and you will start seeing issues like low disk space, a large number of logs etc.

MySQL allows you to flush logs using flush logs command, next “How to rotate and clean up old MySQL logs? ”

Linux has a utility called “logrotate” , using logrotate we can implement log rotation for MySQL server logs.

Binary logs: This one is critical if you have replication setup, By enabling  expire_logs_days mysql variable you can manage …

This blog we describe an issue with MySQL 5.7’s super_read_only feature when used alongside with GTID in chained slave instances.

Background

In MySQL 5.7.5 and onward introduced the gtid_executed table in the MySQL database to store every GTID. This allows slave instances to use the GTID feature regardless whether the binlog option is set or not. Here is an example of the rows in the gtid_executed table:

mysql> SELECT * FROM mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end | …

There are several advantages to keeping up to date with the latest major versions of MySQL. To provide a short example using MySQL 5.7:

• Let application developers benefit from new functionality, e.g. JSON in 5.7
• Use your hardware infrastructure more efficiently with parallel slaves in 5.7
• Improve monitoring or management for DevOps with more performance schema instrumentation in 5.7
• Improve security by using features like  transparent data encryption, firewall, auditing, and sha256 authentication in 5.7
• Keep within the EOL window for access to maintenance releases and support

This blog post explains general activities in an upgrade project and what to look out for prior to an upgrade between major MySQL Server releases.…

MySQL 5.7 introduced the LOGICAL_CLOCK type of multi-threaded slave (MTS).  When using this type of parallel replication (and when slave_parallel_workers is greater than zero), slaves use information from the binary logs (written by the master) to run transactions in parallel.  However, enabling parallel replication on slaves might not be enough to get a higher replication throughput (VividCortex

If you’ve been reading enough database related forums, mailing lists or blogs you probably heard complains about MySQL being unable to handle more than 1.000.000 (or select any other number) rows by some of the users. On other hand it is well known with customers like Google, Yahoo, Live Journal, Technocarati MySQL has installations with many billions of rows and delivers great performance. What could be the reason?

The reason is normally table design and understanding inner works of MySQL. If you design your data wisely considering what MySQL can do and what it can’t you will get great performance if not, you might become upset and become one of those bloggers.

Note – Any database management system is different in some respect and what works well for Oracle, MS SQL, and PostgreSQL may not work well for MySQL and other way around. Even storage engines have very important differences which can …