mysql-proxy-discuss team mailing list archive

[Kay Röpke <Kay.Roepke@xxxxxxx>]

Hi Adrienne!

On Sep 4, 2009, at 9:32 PM, Adrienne Porter Felt wrote:

> Here's an example of a query that runs faster:
> INSERT INTO jforum_posts_text ( post_id, post_text, post_subject )  
> VALUES
> (<counter>, '<random>', '<random>')

No selects are involved in this run, correct?

> We run it 100x and the numbers are consistently faster.  That table  
> has
> about 10 records in it and we clear it back to the original 10 each  
> time
> before we re-run the INSERT.  We know the INSERT is actually working  
> since
> we've debugged and checked the database to see that the INSERT  
> actions are
> reflected in the table.  We have --query_cache_size=0 on the MySQL  
> server,
> but I'm not sure that matters?

It does not matter for a pure insert workload, correct. For selects,  
assuming
you do not insert/update/delete something the table in between the  
selects, it
also should not matter, even though you'd be measuring the performance  
of the
query cache. Turning it off is best for this type of test.
I guess disk i/o can be ignored, since all of this will fit into  
pretty much
all caches/pools in memory anyway.

> Attached is a chart (chart1-alltypes.png)
> showing the run times for different queries (each run 100x each, with
> an error bar for a 95% confidence interval).  UPDATE and SELECT
> statements run as expected -- a little bit slower.
> Then INSERT statements are faster!  Consistently, too -- we're run  
> several
> trials of 100 runs, and one trial of 1000 runs.  But here's the  
> extra-weird
> thing.....if we increase the result set size for a SELECT statement by
> increasing the size of the table, eventually (around 7000) they are  
> faster,
> too.  (resultSetSize.png shows that data.)

Are the queries run sequentially or in parallel?

> Does MySQL Proxy do any kind of
> caching or optimization on queries that I'm not aware of?


There is no caching or optimization going on, if you did not write  
them yourself
in a Lua script.
The only thing recent versions of proxy do is to not buffer up the  
packets
comprising the result set (although this still is a slowdown versus  
the direct
path to mysqld, of course). This is purely a win against older (pre  
0.7 versions)
of proxy.

Since you mentioned you use the funnel plugin, there's no chance of  
Lua being in
the middle, since it doesn't support scripts at the moment.
The funnel plugin does accept more queries than proxy's backend can  
handle though.
It could very well explain the speedup you see.
I would be _very_ interested in seeing the same benchmark done with  
swapping out
the funnel plugin with the proxy plugin. I suspect it will be slower.

As an aside, we have seen speedups happen even without funnel:
There are rare cases in certain workload mixes, database content,  
buffer pool sizes
and a bit of mysqld thread scheduling chance, where the slight  
slowdown caused by
proxy improves the concurrency in mysqld, thereby leading to a slight  
throughput
increase. But this happens only in rare cases, and only with queries  
sent by
many clients.

As for the improvements with larger result sets:
This can be explained by the nature of event-based network i/o and  
also by using
funnel (with a connection pool > 1).
Because proxy by default doesn't not buffer the entire result set, the  
overhead
is minimized (basically copying each packet once into userland and  
then sending it
out again). If some connection is in the pool for funnel to send the  
next query,
incoming resultset packets can be processed each time some connection  
needs to wait
on some i/o operation, this is basically cooperative multitasking with  
a single thread.
At the same time funnel will accept more connections, even though  
there's no available
backend to send it to. I'm guessing fortunate timing and a decent  
number of parallel
connections benefit the current proxy/funnel design, leading to higher  
than expected
throughput.

All of this feels very unnatural, but yes, it can happen.
Of course, most of my hypothesis shatters to pieces if you are  
actually sending the
queries sequentially…
Where do you measure the response times? I'm assuming in the client  
app, correct?

BTW, is this MySQL Proxy 0.7.x, or a custom built 0.8? 0.8 would be  
multithreaded
already screwing my entire explanation even more ;)

cheers,
-k
--
Kay Roepke
Software Engineer, MySQL Enterprise Tools

Sun Microsystems GmbH    Sonnenallee 1, DE-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer:    Thomas Schroeder,  Wolfang Engels,  Wolf Frenkel
Vorsitz d. Aufs.rat.: Martin Haering                    HRB MUC 161028