There’s been an issue on Natural Load Testing (our fantastic web application load testing tool) for a while now, we’ve been referring to it as the Double Stacked Graph. Normally the graphs should look like this, kindly sharing the load testing results with the end user, hopefully being both easy to start with and powerful as usage grows.

Unfortunately, what we’ve been seeing occasionally is the dreaded Double Stack: Here you can see that there is two stacked red bars on top of each other. They represent the exact same space in time, but the data is being added to the graph twice for some reason.

Initially I expected that this was an issue with me handling the updates to the graph with ajax poorly. We had an issue with Where’s it Up for a while where results were duplicated. This was the result of asking the server for “new” results every second by passing the server the list of results already obtained. If a request took more than a second to complete, the following request (initiated while the first was outstanding) would be initiated with the same list of previously obtained results, so the server would obligingly return the same “new” results to both requests.

It turned out however that this wasn’t the problem at all, having learned from the Where’s it Up issue I’d coded those ajax requests more carefully. It turns out that the issue had to do with how I was communicating with MySQL. Each time the graph updates it asks for results with a timestamp newer than the newest data it has. The exact query is something like this:

$query = "SELECT `ts` as `timestamp`, `active_requests`, `requests_initiated`, `median_response`, `running_workers`, `bytes_transferred` FROM `test_results_stats` WHERE `job_id` = '$job_id' AND `ts` > '$minTime' LIMIT 2";

The issue here turns out to be the fact that I'm quoting $minTime. Take a look at these results: mysql> CREATE TABLE `test_decimal` ( -> `job_id` varchar(32) NOT NULL, -> `ts` decimal(13,3) NOT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO `test_decimal` VALUES -> ('9_0209_511673437c4dd', 1360425827.491), -> ('9_0209_511673437c4dd', 1360425828.491), -> ('9_0209_511673437c4dd', 1360425829.491), -> ('9_0209_511673437c4dd', 1360425830.492); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * from `test_decimal` WHERE `ts` > '1360425827.491' LIMIT 2; +----------------------+----------------+ | job_id | ts | +----------------------+----------------+ | 9_0209_511673437c4dd | 1360425827.491 | | 9_0209_511673437c4dd | 1360425828.491 | +----------------------+----------------+ 2 rows in set (0.00 sec) mysql> SELECT * from `test_decimal` WHERE `ts` > 1360425827.491 LIMIT 2; +----------------------+----------------+ | job_id | ts | +----------------------+----------------+ | 9_0209_511673437c4dd | 1360425828.491 | | 9_0209_511673437c4dd | 1360425829.491 | +----------------------+----------------+ 2 rows in set (0.00 sec)

While using the greater than operator, I’m obtaining results that are equal if the term I’m comparing against is quoted. Without the quotes it performs as expected. This confused me greatly, I’ve been told countless times that I can quote my variables in MySQL to no ill effect, in fact I probably should if I’m not using paramaterized queries for increased security.

The crux of the issue will come down to MySQL’s Type Conversion rules. It must be converting the two values to float to handle the quoted comparison, though I may be at a loss to describe why they’re converted differently. By not quoting the decimal value I allow the comparison to occur between decimal values, and obtain the desired result.

Update

A few people have commented (either here or elsewhere) about MySQL versions. I just re-confirmed the test with the newest MySQL Debian would give me: mysql> SELECT * from `test_decimal` WHERE `ts` > '1360425827.491' LIMIT 2; +----------------------+----------------+ | job_id | ts | +----------------------+----------------+ | 9_0209_511673437c4dd | 1360425827.491 | | 9_0209_511673437c4dd | 1360425828.491 | +----------------------+----------------+ 2 rows in set (0.00 sec) mysql> SHOW VARIABLES LIKE "%version%"; +-------------------------+-----------------------+ | Variable_name | Value | +-------------------------+-----------------------+ | protocol_version | 10 | | version | 5.1.66-0+squeeze1-log | | version_comment | (Debian) | | version_compile_machine | i486 | | version_compile_os | debian-linux-gnu | +-------------------------+-----------------------+ 5 rows in set (0.00 sec)

Or our Percona server (issue resolved): mysql> SELECT * from `test_decimal` WHERE `ts` > '1360425827.491' LIMIT 2; +----------------------+----------------+ | job_id | ts | +----------------------+----------------+ | 9_0209_511673437c4dd | 1360425828.491 | | 9_0209_511673437c4dd | 1360425829.491 | +----------------------+----------------+ 2 rows in set (0.00 sec) mysql> SHOW VARIABLES LIKE "%version%"; +-------------------------+------------------------------------+ | Variable_name | Value | +-------------------------+------------------------------------+ | innodb_version | 1.1.8-rel29.4 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.5.29-29.4-log | | version_comment | Percona Server (GPL), Release 29.4 | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------------+ 7 rows in set (0.00 sec)


Comments »

No Trackbacks
Comments work now.
#1 Paul Reinheimer (Homepage) on 2013-02-11 15:12 (Reply)

This is probably version-dependent because I wasn't able to reproduce your case on MySQL 5.5.28-1 running on Debian/sid. In my testing both resultsets are the same.
#2 Bruce Weirdan on 2013-02-12 03:25 (Reply)

Hi Bruce,

Thanks for trying it out. I ran apt-get upgrade on my local install of debian squeeze and still see the issue there. I'll try it out on our other releases and see what I come up with.
#3 Paul Reinheimer (Homepage) on 2013-02-12 03:29 (Reply)


Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.
 

Hi, I’m Paul Reinheimer, a developer working on the web.

I wrote a book titled Professional Web APIs with PHP back in 2006, and am currently working in Biomedical Informatics for a major public health company.

I’m working on a project to help developers called WonderProxy which has proxies all over the world. Working on GeoIP development? Now you can finally test properly! We've also released Global Ping Statistics for expected ping times between cities, as well as a Load Testing Tool to measure your site's ability to handle load. Our most recent site checking tool is Where's it Up? which checks your sites availability globally, returning HTTP, DNS, and Traceroute details

My hobbies are cycling, photography, travel, and engaging Allison Moore in intelligent discourse. I frequently write about PHP and other related technologies.

I co-founded:

WonderNetwork Logo

Search