Avoid locks when storing counters in MySQL

If your application has a need for storing counters you can use this package which contains the scripts for a table and some stored procedures to handle managing the counters.

A common problem with storing counters in a table is that every time your application update your counter, a row lock needs to be set on the row the table. This will be a problem when your application needs to do this a lot because your clients will be waiting for the locks to be released on the table and your application will come to a halt. When you are counting page hits or clicks on links on a busy website, you will have a serious chance this will be the bottleneck of your application.

This will happen if your table look something like this:

1
2
3
4
5
6
counters
--------------------------
counter_name  VARCHAR(50)
counter_value INT
...
-------------------------

For each counter there is one row only and your application is sending more requests to update this single row then your database can handle and you will have a big traffic jam causing problems for the rest of your application. One way to solve this is adding more rows for the same counter. Then you will have more rows to update the values and less chance of a row lock. This will not work on a MyISAM table because this engine will add a table lock so only one row in the table can be updated at the same time.

By adding more rows for the same counter you will need some functionality for managing the counter. In this package you will find all functionality for managing the counters:
- Creating a counter with xx rows.
- Increment a counter by picking a random row for the counter and update it by one.
- Resetting the counter with another value and/or start date.
- Deleting the counter.
- Getting the value of the counter.
- Exception handling for not-existing counter names, creating duplicate counters etc.

How to set this up?
The script ’setup.sql’ will execute two other scripts:
create_tables.sql
This will create a table called: ‘counters’
The script will fail if there is already a table in your schema called ‘counters’

The script ‘create_procedures.sql’ will create 4 stored procedures and one function:
All procedures and the function are fully documented.
Stored procedures: counter_setup, counter_increment, counter_reset, counter_delete
Function: counter_get
The script will drop existing procedures and function if they already exist in your schema so make sure they do not exist yet.

How to use it?
If the table and procedures are created, you can insert some test data:

mysql> call counter_setup('test1','test1',NULL,NULL);
mysql> call counter_setup('test2','test2',10,1000);
mysql> call counter_setup('test3','test3',1,NULL);
mysql> select * from counters;
+--------------+------------+-------------+---------------------+---------+
| counter_name | counter_id | description | start_date          | counter |
+--------------+------------+-------------+---------------------+---------+
| test1        |          1 | test1       | 2012-05-02 07:44:49 |       0 |
| test1        |          2 | test1       | 2012-05-02 07:44:49 |       0 |
| test1        |          3 | test1       | 2012-05-02 07:44:49 |       0 |
| test1        |          4 | test1       | 2012-05-02 07:44:49 |       0 |
| test1        |          5 | test1       | 2012-05-02 07:44:49 |       0 |
| test2        |          1 | test2       | 2012-05-02 07:45:07 |    1000 |
| test2        |          2 | test2       | 2012-05-02 07:45:08 |       0 |
| test2        |          3 | test2       | 2012-05-02 07:45:08 |       0 |
| test2        |          4 | test2       | 2012-05-02 07:45:08 |       0 |
| test2        |          5 | test2       | 2012-05-02 07:45:08 |       0 |
| test2        |          6 | test2       | 2012-05-02 07:45:08 |       0 |
| test2        |          7 | test2       | 2012-05-02 07:45:08 |       0 |
| test2        |          8 | test2       | 2012-05-02 07:45:08 |       0 |
| test2        |          9 | test2       | 2012-05-02 07:45:08 |       0 |
| test2        |         10 | test2       | 2012-05-02 07:45:08 |       0 |
| test3        |          1 | test3       | 2012-05-02 07:45:31 |       0 |
+--------------+------------+-------------+---------------------+---------+
16 rows in set (0.00 sec)

You will see that ‘test1′ is created with 5 rows and the initial value of the counter is 0. This is the default.
Counter ‘test2′ has 10 rows and has an initial value of 1000.
Counter ‘test3′ has only one row and the initial value of the counter is 0

Now increase a counter a couple of times:

mysql> call counter_increment('test2');
mysql> call counter_increment('test2');
mysql> call counter_increment('test2');
mysql> call counter_increment('test2');
mysql> call counter_increment('test2');

Get the new value for counter ‘test2′:

mysql> SELECT counter_get('test2') AS total;
+-------+
| total |
+-------+
|  1005 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM counters WHERE counter_name = 'test2';
+--------------+------------+-------------+---------------------+---------+
| counter_name | counter_id | description | start_date          | counter |
+--------------+------------+-------------+---------------------+---------+
| test2        |          1 | test2       | 2012-05-02 07:55:55 |    1000 |
| test2        |          2 | test2       | 2012-05-02 07:55:55 |       2 |
| test2        |          3 | test2       | 2012-05-02 07:55:55 |       0 |
| test2        |          4 | test2       | 2012-05-02 07:55:55 |       0 |
| test2        |          5 | test2       | 2012-05-02 07:55:55 |       1 |
| test2        |          6 | test2       | 2012-05-02 07:55:55 |       0 |
| test2        |          7 | test2       | 2012-05-02 07:55:55 |       0 |
| test2        |          8 | test2       | 2012-05-02 07:55:55 |       1 |
| test2        |          9 | test2       | 2012-05-02 07:55:55 |       0 |
| test2        |         10 | test2       | 2012-05-02 07:55:55 |       1 |
+--------------+------------+-------------+---------------------+---------+
10 rows in set (0.00 sec)

The rows are randomly updated

Reset the counter back to 1000 and update the start date to the current date:

mysql> call counter_reset('test2',1000,TRUE);
Query OK, 10 rows affected (0.00 sec)
mysql> SELECT * FROM counters WHERE counter_name = 'test2';
+--------------+------------+-------------+---------------------+---------+
| counter_name | counter_id | description | start_date          | counter |
+--------------+------------+-------------+---------------------+---------+
| test2        |          1 | test2       | 2012-05-02 08:02:53 |    1000 |
| test2        |          2 | test2       | 2012-05-02 08:02:53 |       0 |
| test2        |          3 | test2       | 2012-05-02 08:02:53 |       0 |
| test2        |          4 | test2       | 2012-05-02 08:02:53 |       0 |
| test2        |          5 | test2       | 2012-05-02 08:02:53 |       0 |
| test2        |          6 | test2       | 2012-05-02 08:02:53 |       0 |
| test2        |          7 | test2       | 2012-05-02 08:02:53 |       0 |
| test2        |          8 | test2       | 2012-05-02 08:02:53 |       0 |
| test2        |          9 | test2       | 2012-05-02 08:02:53 |       0 |
| test2        |         10 | test2       | 2012-05-02 08:02:53 |       0 |
+--------------+------------+-------------+---------------------+---------+
10 rows in set (0.00 sec)

Now delete the counter:
mysql> call counter_drop('test2');

How does it perform?
Let’s setup some counters.
This script (you can find it in the .zip file) will truncate the counters table, add 1500 counters with a random number of rows per counter (max 50). It will also analyze the table and show you a summary of the counters table. This script can take a little time since I did not used a bulk insert, sorry for that…

mysql> source test_insertdata.sql;
+---------------+-----------------------+
| Rows inserted | Avg. rows per counter |
+---------------+-----------------------+
|         38263 |                    25 |
+---------------+-----------------------+
1 row in set (0.01 sec)

Now update the counters table with 250 concurrent connections and increment 4 counters per user. We will do this a with 100 iterations with mysqlslap.
So this will make 100.000 updates to 4 counters only.

shell: mysqlslap --host=localhost --user=ronald -p --create-schema=schemas --concurrency=250 --iterations=100 --query="call counter_increment('countername100');call counter_increment('countername500');call counter_increment('countername1000');call counter_increment('countername1499')"
Enter password:
Benchmark
Average number of seconds to run all queries: 0.346 seconds
Minimum number of seconds to run all queries: 0.266 seconds
Maximum number of seconds to run all queries: 0.977 seconds
Number of clients running queries: 250
Average number of queries per client: 1

Let’s check if we indeed inserted 100.000 counts:

mysql>SELECT counter_get('counterName100') AS cnt100, counter_get('counterName500') AS cnt500, counter_get('counterName1000') AS cnt1000, counter_get('counterName1499') AS cnt1499;
+--------+--------+---------+---------+
| cnt100 | cnt500 | cnt1000 | cnt1499 |
+--------+--------+---------+---------+
|  25000 |  25000 |   25000 |   25000 |
+--------+--------+---------+---------+
1 row in set (0.01 sec)

This is how the rows for one counter look like:

mysql>SELECT * FROM counters WHERE counter_name = 'counterName100';
+----------------+------------+-----------------------+---------------------+---------+
| counter_name   | counter_id | description           | start_date          | counter |
+----------------+------------+-----------------------+---------------------+---------+
| counterName100 |          1 | counterDescription100 | 2012-05-09 17:31:55 |    1348 |
| counterName100 |          2 | counterDescription100 | 2012-05-09 17:31:55 |    1218 |
| counterName100 |          3 | counterDescription100 | 2012-05-09 17:31:55 |    1304 |
| counterName100 |          4 | counterDescription100 | 2012-05-09 17:31:55 |    1265 |
| counterName100 |          5 | counterDescription100 | 2012-05-09 17:31:55 |    1313 |
| counterName100 |          6 | counterDescription100 | 2012-05-09 17:31:55 |    1215 |
| counterName100 |          7 | counterDescription100 | 2012-05-09 17:31:55 |    1288 |
| counterName100 |          8 | counterDescription100 | 2012-05-09 17:31:55 |    1240 |
| counterName100 |          9 | counterDescription100 | 2012-05-09 17:31:55 |    1205 |
| counterName100 |         10 | counterDescription100 | 2012-05-09 17:31:55 |    1223 |
| counterName100 |         11 | counterDescription100 | 2012-05-09 17:31:56 |    1216 |
| counterName100 |         12 | counterDescription100 | 2012-05-09 17:31:56 |    1196 |
| counterName100 |         13 | counterDescription100 | 2012-05-09 17:31:56 |    1230 |
| counterName100 |         14 | counterDescription100 | 2012-05-09 17:31:56 |    1211 |
| counterName100 |         15 | counterDescription100 | 2012-05-09 17:31:56 |    1253 |
| counterName100 |         16 | counterDescription100 | 2012-05-09 17:31:56 |    1315 |
| counterName100 |         17 | counterDescription100 | 2012-05-09 17:31:56 |    1253 |
| counterName100 |         18 | counterDescription100 | 2012-05-09 17:31:56 |    1193 |
| counterName100 |         19 | counterDescription100 | 2012-05-09 17:31:56 |    1290 |
| counterName100 |         20 | counterDescription100 | 2012-05-09 17:31:56 |    1224 |
+----------------+------------+-----------------------+---------------------+---------+
20 rows in set (0.00 sec)

It turns out that counter100 was assigned 20 rows and the updates are done pretty evenly among the rows.

The performance is very good and we do not have any locks. However the performance can be improved.
When writing the counter_setup procedure it occurred to me that you do not always want to add the same amount of rows for all counters.
E.g. a counter for page hits on your homepage will need more rows then for example a counter for hits on your privacy policy page ;-)
In order to pick a random row for the counter we need to find out how many rows exist for the counter. If you always want to use a fixed amount of rows you can hard code it in the procedure and that will save you a lookup.

You can download all code, stored procedures, scripts for load testing and documentation in a zip file: counters_2012-05-09.zip

Related posts

Tags: , , , , ,

3 Responses to “Avoid locks when storing counters in MySQL”

  1. Asu says:

    I see what you’re doing here, very nifty solution. Thank you!

  2. Hi Clair,

    There is no magic number, you should keep the amount of row as small as possible.
    E.g.: If you have a counter with 3 rows that logs an activity of your 300 concurrent users without a problem but on peak hours you have 1200 concurrent users and you notice the users have to wait. You can increase the amount of rows to e.g. 12 or 15 and see if the problem is solved.

  3. Clair Saverin says:

    Thanks, I have downloaded the code and gave it a spin. Looking good so far.
    What is a good number of rows to set for a counter?

Leave a Reply