The MySQL UC 2009 is over and I got bored in the evening before I had to leave Santa Clara and decided to play again with query caching in mysqlnd. I have always said that mysqlnd is ready for it, the infrastructure is there and that we need just few changes to make it work. Well, I decided to bite the bullet and do it. Spent about 4h in the evening and on the next day 2h while waiting on the San Jose (SJC) airport and continued on the flight to Denver, where I am having vacation. On the plane I was almost ready and today I made the final changes to the preliminary patch, that seems to work :)

I did a small benchmark, a microbenchmark.

And here are the results.

andrey@winnie:~/dev/vanilla/php5_3$ time ./php cache.php 0
Query=[SELECT 2] loops=1000 mask=0 runtime=0.17404

real    0m0.196s
user    0m0.052s
sys     0m0.020s
andrey@winnie:~/dev/vanilla/php5_3$ time ./php cache.php 1
Query=[SELECT 2] loops=1000 mask=4 runtime=0.03741

real    0m0.084s
user    0m0.048s
sys     0m0.016s

First run is without caching, the second one is caching. 1000 loops of the query with a cold cache. Which means that the query is executed once when caching and this runtime is included.

Here comes more. I looped the execution of the script using bash for loop and also increased the inner loop to 10000 cycles.

andrey@winnie:~/dev/vanilla/php5_3$ for i in `seq 1 10`; do ./php cache.php 0; done
Query=[SELECT 2] loops=10000 mask=0 runtime=1.48373
Query=[SELECT 2] loops=10000 mask=0 runtime=1.42853
Query=[SELECT 2] loops=10000 mask=0 runtime=1.44667
Query=[SELECT 2] loops=10000 mask=0 runtime=1.40388
Query=[SELECT 2] loops=10000 mask=0 runtime=1.40763
Query=[SELECT 2] loops=10000 mask=0 runtime=1.43488
Query=[SELECT 2] loops=10000 mask=0 runtime=1.42788
Query=[SELECT 2] loops=10000 mask=0 runtime=1.46109
Query=[SELECT 2] loops=10000 mask=0 runtime=1.45751
Query=[SELECT 2] loops=10000 mask=0 runtime=1.44877
andrey@winnie:~/dev/vanilla/php5_3$ for i in `seq 1 10`; do ./php cache.php 1; done
Query=[SELECT 2] loops=10000 mask=4 runtime=0.21036
Query=[SELECT 2] loops=10000 mask=4 runtime=0.20506
Query=[SELECT 2] loops=10000 mask=4 runtime=0.18329
Query=[SELECT 2] loops=10000 mask=4 runtime=0.18252
Query=[SELECT 2] loops=10000 mask=4 runtime=0.19021
Query=[SELECT 2] loops=10000 mask=4 runtime=0.23113
Query=[SELECT 2] loops=10000 mask=4 runtime=0.18138
Query=[SELECT 2] loops=10000 mask=4 runtime=0.18135
Query=[SELECT 2] loops=10000 mask=4 runtime=0.18880
Query=[SELECT 2] loops=10000 mask=4 runtime=0.18388

Conclusion: As seen before, with this simple query SELECT 2, which should not cost the server too much run time and is probably the fastest query to execute, we get 6x boost!

Just to show you what I mean I modified the script to run a query that includes a simple table, (int, varchar(4)), which has 6 rows and we select them all

andrey@winnie:~/dev/vanilla/php5_3$ for i in `seq 1 10`; do ./php cache.php 0; done
Query=[SELECT * FROM test.t1] loops=10000 mask=0 runtime=1.04996
Query=[SELECT * FROM test.t1] loops=10000 mask=0 runtime=0.96465
Query=[SELECT * FROM test.t1] loops=10000 mask=0 runtime=1.00110
Query=[SELECT * FROM test.t1] loops=10000 mask=0 runtime=1.00420
Query=[SELECT * FROM test.t1] loops=10000 mask=0 runtime=0.99781
Query=[SELECT * FROM test.t1] loops=10000 mask=0 runtime=0.96678
Query=[SELECT * FROM test.t1] loops=10000 mask=0 runtime=0.96933
Query=[SELECT * FROM test.t1] loops=10000 mask=0 runtime=1.00753
Query=[SELECT * FROM test.t1] loops=10000 mask=0 runtime=1.14486
Query=[SELECT * FROM test.t1] loops=10000 mask=0 runtime=1.00231
andrey@winnie:~/dev/vanilla/php5_3$ for i in `seq 1 10`; do ./php cache.php 1; done
Query=[SELECT * FROM test.t1] loops=10000 mask=4 runtime=0.09978
Query=[SELECT * FROM test.t1] loops=10000 mask=4 runtime=0.10393
Query=[SELECT * FROM test.t1] loops=10000 mask=4 runtime=0.10655
Query=[SELECT * FROM test.t1] loops=10000 mask=4 runtime=0.09721
Query=[SELECT * FROM test.t1] loops=10000 mask=4 runtime=0.09816
Query=[SELECT * FROM test.t1] loops=10000 mask=4 runtime=0.09570
Query=[SELECT * FROM test.t1] loops=10000 mask=4 runtime=0.09747
Query=[SELECT * FROM test.t1] loops=10000 mask=4 runtime=0.09574
Query=[SELECT * FROM test.t1] loops=10000 mask=4 runtime=0.09960
Query=[SELECT * FROM test.t1] loops=10000 mask=4 runtime=0.09960

Conclusion: With non-simple queries the boost jumps to 10x!

Comments are disabled for this post.