Techniques to Optimize MySQL: Indexes, Slow Queries, Configuration

Posted on November 16, 2017 by Mario

Many web developers see MySQL as the world’s most popular relational database. However, at some points, you will find many parts haven’t been optimized. However, instead of investigating it further, many people prefer to leave it at default values. As a solution, we have to combine the previous tips with new method that came out since, as presented bellows:

Configuration Optimization

One of the most important ways that every user of MySQL should do is to upgrade the configuration.5.7 which has better defaults than its previous version. If you use a Linux-based host, your configuration will be like /etc/mysql/my.cnf. Furthermore, your installation might load a secondary configuration file into that configuration file, as a result if the my.cnf file doesn’t contain much content, the other file /etc/mysql/mysql.conf.d/mysqld.cnf might have.

Editing Configuration

It is important to feel comfortable when you are using the command line, before learning on how to edit configuration. For example, you can copy the file out into the main filesystem by copying it into the shared folder with cp /etc/mysql/my.cnf /home/vagrant/Code if you’re editing locally on a Vagrant box. Then, use a regular text editor to edit it and copy it back into place when done or else you can use a simple text editor, for instance vim by executing sudo vim /etc/mysql/my.cnf.

Manual Tweaks

To create this config file under the [mysqld] section, you should make the following manual tweaks out of the box.

innodb_buffer_pool_size = 1G # (adjust value here, 50%-70% of total RAM)

innodb_log_file_size = 256M

innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0

innodb_flush_method = O_DIRECT

innodb_buffer_pool_size

The buffer pool is used to store caching data and indexes in memory. It can keep frequently accessed data in memory. Therefore, you can add this part of your app(s) the most RAM up to 70% of all RAM when you’re running a dedicated or virtual server where there is often a bottleneck in the DB.

Even though, you can find clear information about the log file size here, but the important point is about how much data to store in a log before removing it. A log in this case indicates checkpoint time because with MySQL, even though writes happen in the background, it still affects foreground performance. In fact, having big log files mean better performance because you create new and smaller checkpoints. However, it takes longer recovery time when there is a crash.
innodb_flush_log_at_trx_commit will explain what happens with the log file. Select 1 to get the safest setting since the log is flushed to disk after every transaction. Select 0 or 2 to get less ACID, but more performant. There is no big difference in this case to outweigh the stability benefits of the setting of 1.
innodb_flush_method to avoid double buffering, it will be set to Unless the I/O system is in very low performance, you should always perform this command.

Variable Inspector

Here are the steps to install the variable inspector on Ubuntu:

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.debsudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.debsudo apt-get updatesudo apt-get install percona-toolkit

You can also apply the instructions for other systems.

Then, run the toolkit with:

pt-variable-advisor h=localhost,u=homestead,p=secret

The output should not show these:

# WARN delay_key_write: MyISAM index blocks are never flushed until necessary. # NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB. # NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it. # NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later. # WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.

You don’t have to fix these as none of them are critical. Binary logging for replication and snapshot purposes is the only one we could add.

max_binlog_size = 1Glog_bin = /var/log/mysql/mysql-bin.logserver-id=master-01binlog-format = ‘ROW’

The max_binlog_size setting will determine how large binary logs will be. These logs will log your transactions and queries and make checkpoints. A log may be bigger than max if a transaction is bigger than max. Otherwise, MySQL will keep them at that limit.
With log_bin option, you can turn on the binary logging altogether. However, without it you can’t do snapshotting or replication. Note that this can be very strenuous on the disk space. Bear in mind that this can weigh the disk space. To activate binary logging, you will need a server ID, this will inform the logs which server they came from.

With its sane defaults, the new MySQL makes things nearly production ready. Every app is certainly different and has additional custom tweaks applicable.

MySQL Tuner

The main purpose of Tuner is to monitor a database in longer intervals and suggest changes based on what it’s seen in the logs.

You can simply download it to install it:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.plchmod +x mysqltuner.pl

You also will be asked for admin username and password for the database when running it with ./mysqltuner.pl as well as running output information from the quick scan. You can see the example below.

[—] InnoDB is enabled.[—] InnoDB Thread Concurrency: 0[OK] InnoDB File per table is activated[OK] InnoDB buffer pool / data size: 1.0G/11.2M[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 256.0M * 2/1.0G should be equal 25%[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).[—] Number of InnoDB Buffer Pool Chunk : 8 for 8 Buffer Pool Instance(s)[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances[OK] InnoDB Read buffer efficiency: 96.65% (19146 hits/ 19809 total)[!!] InnoDB Write Log efficiency: 83.88% (640 hits/ 763 total)[OK] InnoDB log waits: 0.00% (0 waits / 123 writes)

Keep in mind that this tool should be run once per week since the server has been running. You can also set up a cronjob to inform you the results periodically. So, make sure after every configuration change, you will restart the mysql server:

sudo service mysql restart

Indexes

The easiest way to understand MySQL indexes is from looking at the index which is in a book. When a book has any indexes, you won’t have to go through the whole book to search for a subject. Index helps you search something faster without having to go through the whole book. Therefore, MySQL indexes will help you speeding up your select queries. However, the index also has to be created and stored which cause the update and insert queries will be slower. Besides, it will cost you a bit more disk space. In general, you won’t notice the difference with updating and inserting if you have indexed your table correctly and therefore it’s advisable to add indexes at the right locations.

If the tables only contain a few rows, it doesn’t really get any benefits from indexing. Therefore, can we discover which indexes to add and which types of indexes exist?

Unique/Primary Indexes

Primary indexes are the main indexes of data, such as a user account, that might be a user ID, or a username, even a main email. Primary indexes are unique which indexes cannot be repeated in a set of data.

For example, you may experience when a user selected a specific username, nobody else can use it. Therefore as a solution, you can add a “unique” index to the username column. Furthermore, MySQL will notify if someone else tries to insert a raw which has an existed username.

…ALTER TABLE `users` ADD UNIQUE INDEX `username` (`username`);…

You can make both single column and multiple columns For example, you may need a unique index on both of those columns to make sure only you that own that username per country.

…ALTER TABLE `users`ADD UNIQUE INDEX `usercountry` (`username`, `country`),…

Regular Indexex

One of the most easiest to lookup indexes is regular indexes. This type is very useful, especially when you need to find data by specific column or combination of columns fast, without the need of data to be unique.

…ALTER TABLE `users`ADD INDEX `usercountry` (`username`, `country`),…

Fulltext Indexes

If you are looking for full-text searches, you can use FULLTEXT indexes. Several storage engines that support FULLTEXT indexes are only the InnoDB and MyISAM. While for TEXT columns are only CHAR and VARCHAR.

You will find these indexes are very useful especially for all the text searching. Keep in mind that finding words inside of bodies is FULLTEXT’s specialty. Therefore, you can use it on posts, comments, descriptions, reviews, etc.

Descending Indexes

Descending Indexes is an alteration from version 8+. When you have enormous tables to cultivate, you will find this index will come in handy. It works by sorting in descending order but came at a small performance penalty. It surely will speed things up.

CREATE TABLE t ( c1 INT, c2 INT, INDEX idx1 (c1 ASC, c2 ASC), INDEX idx2 (c1 ASC, c2 DESC), INDEX idx3 (c1 DESC, c2 ASC), INDEX idx4 (c1 DESC, c2 DESC));

Furthermore, when dealing with logs written in the database, posts and comments which are stored last to first and similar, you can consider applying DESC to an index.

Bottlenecks

This part will explain how to detect and monitor for bottlenecks in a database.

slow_query_log = /var/log/mysql/mysql-slow.loglong_query_time = 1log-queries-not-using-indexes = 1

You can add the above command to the configuration; as a result it will monitor queries and those not using indexes. You can analyze it for index usage with the aforementioned pt-index-usage tool, once this log has some data or you can also apply the pt-query-digest tool which the results will be like these:

pt-query-digest /var/log/mysql/mysql-slow.log # 360ms user time, 20ms system time, 24.66M rss, 92.02M vsz# Current date: Thu Feb 13 22:39:29 2014# Hostname: *# Files: mysql-slow.log# Overall: 8 total, 6 unique, 1.14 QPS, 0.00x concurrency ________________# Time range: 2014-02-13 22:23:52 to 22:23:59# Attribute total min max avg 95% stddev median# ============ ======= ======= ======= ======= ======= ======= =======# Exec time 3ms 267us 406us 343us 403us 39us 348us# Lock time 827us 88us 125us 103us 119us 12us 98us# Rows sent 36 1 15 4.50 14.52 4.18 3.89# Rows examine 87 4 30 10.88 28.75 7.37 7.70# Query size 2.15k 153 296 245.11 284.79 48.90 258.32# ==== ================== ============= ===== ====== ===== ===============# Profile# Rank Query ID Response time Calls R/Call V/M Item# ==== ================== ============= ===== ====== ===== ===============# 1 0x728E539F7617C14D 0.0011 41.0% 3 0.0004 0.00 SELECT blog_article# 2 0x1290EEE0B201F3FF 0.0003 12.8% 1 0.0003 0.00 SELECT portfolio_item# 3 0x31DE4535BDBFA465 0.0003 12.6% 1 0.0003 0.00 SELECT portfolio_item# 4 0xF14E15D0F47A5742 0.0003 12.1% 1 0.0003 0.00 SELECT portfolio_category# 5 0x8F848005A09C9588 0.0003 11.8% 1 0.0003 0.00 SELECT blog_category# 6 0x55F49C753CA2ED64 0.0003 9.7% 1 0.0003 0.00 SELECT blog_article# ==== ================== ============= ===== ====== ===== ===============# Query 1: 0 QPS, 0x concurrency, ID 0x728E539F7617C14D at byte 736 ______# Scores: V/M = 0.00# Time range: all events occurred at 2014-02-13 22:23:52# Attribute pct total min max avg 95% stddev median# ============ === ======= ======= ======= ======= ======= ======= =======# Count 37 3# Exec time 40 1ms 352us 406us 375us 403us 22us 366us# Lock time 42 351us 103us 125us 117us 119us 9us 119us# Rows sent 25 9 1 4 3 3.89 1.37 3.89# Rows examine 24 21 5 8 7 7.70 1.29 7.70# Query size 47 1.02k 261 262 261.25 258.32 0 258.32# String:# Hosts localhost# Users *# Query_time distribution# 1us# 10us# 100us ################################################################# 1ms# 10ms# 100ms# 1s# 10s+# Tables# SHOW TABLE STATUS LIKE ‘blog_article’\G# SHOW CREATE TABLE `blog_article`\G# EXPLAIN /*!50100 PARTITIONS*/SELECT b0_.id AS id0, b0_.slug AS slug1, b0_.title AS title2, b0_.excerpt AS excerpt3, b0_.external_link AS external_link4, b0_.description AS description5, b0_.created AS created6, b0_.updated AS updated7 FROM blog_article b0_ ORDER BY b0_.created DESC LIMIT 10

You can also analyze these logs by hand, but you have to export the log into a more “analyzable” format which can be done like this:

mysqldumpslow /var/log/mysql/mysql-slow.log

To filter data and make sure only important things are exported, you can have additional parameters. For example: the top 10 queries sorted by average execution time.

mysqldumpslow -t 10 -s at /var/log/mysql/localhost-slow.log

Summary

The above techniques are given to make MySQL fly. So, when you have to deal with configuration optimization, indexes and bottlenecks, don’t hesitate to apply the above techniques.

MySQL 5.7 Reference Manual

Posted on April 11, 2017 by Mario

Nowadays, MySQL is the most popular database system used with PHP among so many developers. PHP combined with MySQL are cross-platform which you can develop in Windows and serve on a Unix platform. Basically, tables stores the data in a MySQL database. A table consists columns and rows which is a collection of related data. Databases are useful for storing information categorically. My SQL is an open source relational database management system (RDBMS). So, whether you are a mobile or web developer, it is never too late to learn more about MySQL 5.7. Here are the details and don’t hesitate to check them out!

MySQL has supported for full-text indexing and searching, such as follows:

A full-text index in MySQL is an index of type FULLTEXT.
Only with InnoDB or MyISAM tables, you can create Full-text indexes, and can be created only for CHAR, VARCHAR, or TEXT
As of MySQL 5.7.6, My SQL provides a built-in full-text ngram parser. It supports Chinese, Japanese, and Korean (CJK), and an installable MeCab full-text parser plugin for Japanese. Parsing differences are outlined in Section 13.9.8, “ngram Full-Text parser”. Then, Section 13.9.9, “MeCab Full-Text Parser Plugin”.
CREATE TABLE provides A FULLTEXT index definition or adds later using ALTER TABLE orCREATE INDEX.
To load large data sets faster, you can use a table that has no FULLTEXT index. Then, create the index to load data into a table that has an existing FULLTEXT index.

By using MATCH() … AGAINST syntax, you can perform full-text searching. MATCH() takes a comma-separated list that names the columns to be searched. You can use AGAINST to take a string and an optional modifier that indicates what type of search to perform. Create search string that is constant during query evaluation through string value. This rules out, for example, a table column because that can differ for each now.

There are three types of full-text searches:

A natural language search interprets search string as a phrase in natural human language. There are no special operators. The stop word list applies if the modifier gives or not give the IN NATURAL LANGUAGE MODE. Full-text searches are natural language searches.

Using the rules of a special query language, a Boolean search interprets the search string. The string contains the words to search for. It can also contain operators that specify requirements such that a word must be present or absent in matching rows. It also should be weighted higher or lower than usual. In Boolean search interprets, search index will omit certain common words. Besides, some words do not match if present in the search string. The IN BOOLEAN MODE modifier specifies a Boolean search.

A modification of a natural language search is a query expansion. To perform a natural language search, you can use search string. After adding the words to the search string, then words from the most relevant rows returned by the search. The search is done again. From the second search, the query returns the rows.

PHP Prepared Statements

Posted on July 26, 2016 by Mario

Somehow for web developers, doing a query can be more complicated if it should face a large amount of data since you may have to repeat the same query for several times. Therefore, if you would like to input a large amount of database into your query, you can try using prepared statements to optimize your query process and prevent hacker from corrupting your database through SQL injection method. Besides, in order to execute the same (or similar) SQL statement repeatedly with high efficiency, a prepared statement is the best feature for it which it basically works like this:

Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled “?”). Example: INSERT INTO MyGuests VALUES(?,?,?)
The query optimization on the SQL statement template are parsed, compiled, and performed by the database which in the end the result will be stored without the need to execute it.
Execute: In the future, the application binds the values to the parameters, and the database executes the statement. The application may execute the statement as many times as it wants with different values.

There are two main advantages that prepared statements can offer when it is compared to executing SQL statements directly:

This technique will be suitable if you wish to reduce parsing time since you only need to make the preparation on the query once.
As you need send only the parameters each time and not the whole query, bound parameters will minimize bandwidth to the server.
Because parameter values, which are transmitted later using a different protocol, need not be correctly escaped, prepared statements are very useful against SQL injections. Besides, SQL injection cannot occur if the original statements template is not derived from external input.

Prepared Statements in MySQLi

The following example uses prepared statements and bound parameters in MySQLi:

Example (MySQLi with Prepared Statements)

<?php
$servername = “localhost”;
$username = “username”;
$password = “password”;
$dbname = “myDB”;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare(“INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)”);
$stmt->bind_param(“sss”, $firstname, $lastname, $email);

// set parameters and execute
$firstname = “John”

$lastname = “Doe”;
$email = “john@example.com”;
$stmt->execute();

$firstname = “Mary”;
$lastname = “Moe”;
$email = “mary@example.com”;
$stmt->execute();

$firstname = “Julie”;
$lastname = “Dooley”;
$email = “julie@example.com”;
$stmt->execute();

echo “New records created successfully”;

$stmt->close();
$conn->close();
?>

Code lines to explain from the example above:

“INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)”

We insert a question mark (?) in our SQL where we want to substitute in an integer, string, double or blob value.

Then, have a look at the bind_param() function:

$stmt->bind_param(“sss”, $firstname, $lastname, $email);

This function binds the parameters to the SQL query and tells the database what the parameters are. The “sss” argument lists the types of data that the parameters are. The s character tells mysql that the parameter is a string.

The argument may be one of four types:

i-integer
d-double
s-string
b-BLOB

We must have one of these for each parameter. By telling mysql what type of data to expect, we minimize the risk of SQL injections.

Prepared Statements in PDO

The following example uses prepared statements and bound parameters in PDO:

Example (PDO with Prepared Statements)

<?php
$servername = “localhost”;
$username = “username”;
$password = “password”;
$dbname = “myDBPDO”;

try {
    $conn = new PDO(“mysql:host=$servername;dbname=$dbname”, $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// prepare sql and bind parameters
    $stmt = $conn->prepare(“INSERT INTO MyGuests (firstname, lastname, email)
    VALUES (:firstname, :lastname, :email)”);
    $stmt->bindParam(‘:firstname’, $firstname);
    $stmt->bindParam(‘:lastname’, $lastname);
    $stmt->bindParam(‘:email’, $email);

    // insert a row
    $firstname = “John”;
    $lastname = “Doe”;
    $email = “john@example.com”;
    $stmt->execute();

    // insert another row
    $firstname = “Mary”;
    $lastname = “Moe”;
    $email = “mary@example.com”;
    $stmt->execute();

// insert another row
    $firstname = “Julie”;
    $lastname = “Dooley”;
    $email = “julie@example.com”;
    $stmt->execute();

    echo “New records created successfully”;
    }
catch(PDOException $e)
    {
    echo “Error: ” . $e->getMessage();
    }
$conn = null;
?>

OnlineMagz

The best website magazine

Responsive Business Theme

sql

Techniques to Optimize MySQL: Indexes, Slow Queries, Configuration

MySQL 5.7 Reference Manual

PHP Prepared Statements