Turn on the Slow Query Log (if it is not already turned on) and
monitor what goes into it. Statements that are logged there are
candidates for tuning. Note, however, that statements will only
be logged there if they take longer than long_query_time seconds
to run, so statements triggering full scans of small tables that
execute very quickly may not show up. Once you have found tuning
candidates, use the EXPLAIN statement on the queries to see which
tables should have indexes added to them.
If you are using MySQL 4.1 or later you can use the
--log-queries-not-using-indexes option to log all statements that
do a full table scan, even if they would not otherwise qualify
for the slow query log.
Note that full table scans are not necessarily bad, as long as
they are confined to very small tables, so be sure to take table
size into account as you review your queries and their EXPLAIN
plans.
This guide will get you up and running with how to identify the bottleneck queries using the excellent tool pt-query-digest. You will learn how to use and analyze the output returned by pt-query-digest. You will also learn some differences between slow query logging in various MySQL versions. Later on in the post I will also show you how to make use of the extra diagnostic data available with Percona Server.
The post Profiling your slow queries using pt-query-digest and some love from Percona Server appeared first on ovais.tariq.
I was asked for help in optimizing a MySQL query where flags are stored in a database and references should be counted based on the flag value. Sounds not complicated at first, but there are several flags that should be counted and also just once per reference. A lot of food for GROUP BY you may think. Having said this, search and group for flags in this table would be really slow due to a very poor cardinality. But let's start with the actual problem. The example is fictitious, but I did my best to find a general use case for this problem.
I was asked for help in optimizing a MySQL query where flags are stored in a database and references should be counted based on the flag value. Sounds not complicated at first, but there are several flags that should be counted and also just once per reference. A lot of food for GROUP BY you may think. Having said this, search and group for flags in this table would be really slow due to a very poor cardinality. But let's start with the actual problem. The example is fictitious, but I did my best to find a general use case for this problem.
Dealing with large data sets makes it necessary to pick out only the newest or the hottest elements and not displaying everything. In order to have older items still available, Pagination navigation's have become established. However, implementing a Pagination with MySQL is one of those problems that can be optimized poorly with MySQL and certainly other RDBM systems. However, knowing the underlying database can also help in optimizing pagination queries, because there is no real copy and paste solution.
Dealing with large data sets makes it necessary to pick out only the newest or the hottest elements and not displaying everything. In order to have older items still available, Pagination navigation's have become established. However, implementing a Pagination with MySQL is one of those problems that can be optimized poorly with MySQL and certainly other RDBM systems. However, knowing the underlying database can also help in optimizing pagination queries, because there is no real copy and paste solution.
Some things are known to be just bad. GOTOs used to be one such
thing (something I still use them, but only where appropriate,
which isn't that many places). Maybe it is just so, that some
things are useful, but not for everything, so maybe the issue is
that they are used inappropriately. Or?
The OR condition is one such things in MySQL circles! Oh, you
have an OR condition! That is going to be so slow! sort of. And
the reason an OR is "slow" is that as MySQL will use only one
index for each statement, only one "side" or the or condition can
use an index. Or sometimes even worse, MySQL will consider using
an index that is common to the two "sides" or is outside the OR
conditition, despite that fact that there are perfectly fine,
highly selective indexes on both sides of the OR condition.
If you ask me, this is not a fault with the OR condition but
rather a problem with the MySQL optimizer. Why in heavens name
can't a …
The purpose of this post is to describe what covering indexes are and how they can be used to improve the performance of queries. People mostly use indexes to filter or sort the results but not much thought is given to actually reduce the disk reads by using proper indexes. So I will show you how to reduce disk reads and hence improve the performance of queries by utilizing indexes properly.
While most people look at performance optimizations for SELECT statements, UPDATE and DELETE statements are often overlooked. These can benefit from the principles of analyzing the Query Execution Plan (QEP). You can only run an EXPLAIN on a SELECT statement, however it’s possible to rewrite an UPDATE or DELETE statement to perform like a SELECT statement.
To optimize an UPDATE, look at the WHERE clause. If you are using the PRIMARY KEY, no further analysis is necessary. If you are not, it is of benefit to rewrite your UPDATE statement as a SELECT statement and obtain a QEP as previously detailed to ensure optimal indexes are used. For example:
UPDATE t SET c1 = ‘x’, c2 = ‘y’, c3 = 100 WHERE c1 = ‘x’ AND d = CURDATE()
You can rewrite this UPDATE statement as a SELECT statement for using EXPLAIN:
EXPLAIN SELECT c1, c2, c3 FROM t WHERE c1 = ‘x’ AND d = CURDATE()
You should …
[Read more]Replication as most people know it, has mostly been SQL statement propagation from master to slave. This is known as "statement-based" replication. But there is also another kind of replication that is available, "the row-based replication" and that has quite a lot of benefits. In this post I intend on highlighting the advantages and disadvantages of both the types of replication to help you choose the best one. I also follow up with my own recommendation.