There are a few ways to implement counters. Even though it’s not a complex feature, often I see people having problems around it. This post describes how bad implementation can impact both application and MySQL performance and how to improve it.

A customer asked me for help with performance problem they were facing. I logged into their database and found many client connections waiting for table locks. Almost all threads were stuck on one, small table called hits. What was the reason?

The problem was related to the way they developed a very simple system for counting page views they later used in some reporting. The table structure was:

mysql> SHOW CREATE TABLE hits\G
*************************** 1. row ***************************
Table: hits
Create Table: CREATE TABLE `hits` (
`cnt` int(11) NOT NULL
) ENGINE=MyISAM

mysql> SELECT * FROM hits;
+---------+
| cnt     |
+---------+
| 3823273 |
+---------+

…

MySQL 5.1 or newer can sometimes start throwing a strange message into an error log. The message states that a query was unsafe for binary logging along with some additional information. What does it mean? Is it a problem?

From time to time you might spot MySQL error log filling with the following warning:

“[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: DELETE FROM score WHERE user_id = 12345 AND created = ’2012-04-15′ LIMIT 1″

If binary logging is enabled and the log format is set to STATEMENT, MySQL generates such message when it considers that a query is ambiguous and could behave differently each time it executes against the same data set. Such situation could happen, for example, on a …

This is the second blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is aimed at the optimizer enhancement Multi Range Read (MRR). Its available in both MySQL 5.6 and MariaDB 5.5

Now let’s take a look at what this optimization actually is and what benefits it brings.

Multi Range Read

With traditional secondary index lookups, if the columns that are being fetched do not belong to the secondary index definition (and hence covering index optimization is not used), then primary key lookups have to be performed for each secondary key entry fetched. This means that secondary key lookups for column values that do not belong to the secondary index definition can result in a lot of Random I/O. The …

70x Higher JOIN Performance, NoSQL Key-Value API & Cross Data Center Sharding with Synchronous Replication 

Oracle is delighted to announce the immediate availability of the production-ready, GA release of MySQL Cluster 7.2, available for download under the GPL, and as part of the commercial MySQL Cluster Carrier Grade Edition, including management tools, product certifications and 24x7 global support.

1 Billion Queries per Minute

MySQL Cluster delivered 1 billion queries per minute (17.6m million queries per second), scaled-out across 8x commodity Intel x86 server nodes, accessed by the NoSQL C++ NDB API.

It …

Yesterday I was discussing with a fellow DBA about ways to check the status of existing and/or past RMAN jobs. Good backup scripts usually write their output to some sort of log file so, checking the output is usually a straight-forward task. However, backup jobs can be scheduled in many different ways (crontab, Grid Control, Scheduled Tasks, etc) and finding the log file may be tricky if you don’t know the environment well.
Furthermore, log files may also have already been overwritten by the next backup or simply just deleted. An alternative way of accessing that information, thus, may come handy.

Fortunately, RMAN keeps the backup metadata around for some time and it can be accessed through the database’s V$ views. Obviously, if you need this information because your database just crashed and needs to be restored, the method described here is useless.

Backup jobs’ status and metadata

A lot of metadata about …

This is a follow up to my previous post titled “MySQL analytics: information_schema polling for table engine percentages”. Here’s an updated query with more output and quicker execution time. What you get: innodb table space utilization percentage, data+index usage total and per innodb/myisam engine, innodb data/index/percentage, myisam data/index/percentages, and overall percentage values. Rather useful for profiling your table engine usage.

Sample output:
innodb_tablespace_utilization_perc: 100
total_size_gb: 26.275011910126
index_size_gb: 2.994891166687
data_size_gb: 23.280120743439
innodb_total_size_gb: 6.751220703125
innodb_data_size_gb: 5.2576751708984
innodb_index_size_gb: 1.4935455322266
myisam_total_size_gb: 19.523791207001
myisam_data_size_gb: 18.02244557254
myisam_index_size_gb: 1.5013456344604
perc_index: 11.3982
perc_data: 88.6018
…

The MySQL IN() Comparison Operator is said to be very quick if all the values are constants (the values are then evaluated and sorted first, and the search is done using a binary search). However, what if the field which the IN clause refers to, is part of the index used to execute the query? […]

MySQL seems to be happy to convert types for you. Developers are rushed to complete their project and if the function works they just move on. But what is the costs of mixing your types? Does it matter if your are running across a million rows or more? Lets find out.

Here is what the programmers see.

mysql> select 1+1;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

mysql> select "1"+"1";
+---------+
| "1"+"1" |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

Benchmark

What if we do a thousand simple loops?  How long does the looping itself take?

The BENCHMARK() function executes the expression expr repeatedly count times. It may be used to time how …

Introduction

If your WordPress comment counts got messed up, whether because of a plugin (I'm talking about you, DISQUS) or you messed with your database manually and did something wrong (yup, that's what I just did), fear not – I have a solution for you.

But first, a little background.

Comment Counts In WordPress

Here's how comment counts work in WP:

• Posts live in a table called wp_posts and each has an ID.
• Comments reside in a table called wp_comments, each referring to an ID in wp_posts.
• However, to make queries faster, the comment count is also cached in the wp_posts table, rather than getting calculated on every page load.
  If this count ever gets out of sync with the actual number of comments for some reason, WordPress, while still displaying all comments properly, will simply show the wrong count. …

A few weeks ago, we had a query optimization request from one of our customer.

The query was very simple like:

PLAIN TEXT CODE:

1. SELECT * FROM `table` WHERE (col1='A'||col1='B') ORDER BY id DESC LIMIT 20 OFFSET 0

This column in the table is looks like this:

PLAIN TEXT CODE:

1. `col1` enum('A','B','C','CD','DE','F','G','HI') default NULL

The table have 549252 rows and of course, there is an index on the col1. MySQL estimated the cardinality of that index as 87, though what was of course misleading as index cardinality in this case can't be over 9, as there is only 8(+ NULL) different possible values for this column.

PLAIN TEXT CODE:

1. …