mysql-proxy-discuss team mailing list archive

[Adrienne Porter Felt <apf@xxxxxxxxxxxx>]

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>')

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?

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.)

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



On Fri, Sep 4, 2009 at 12:02 PM, Giuseppe Maxia <g.maxia@xxxxxxxxx> wrote:

> Hi,
> really puzzling indeed.
> * Can you share the query that was faster under Proxy?
> * Did you check if the results were the same in both cases?
> * Could it be that the query through Proxy was cached?
>
> Cheers
>
> Giuseppe
>
> On Fri, Sep 4, 2009 at 8:50 PM, Adrienne Porter Felt<apf@xxxxxxxxxxxx>
> wrote:
> > Hi everyone,
> > I've come across a puzzling discovery while trying to test a baseline.
> >  Here's our setup: we make a connection from a python script to a local
> > MySQL database through MySQL Proxy.  We're using MySQL proxy with the
> funnel
> > modification (but I'm not sure that matters).  We are sure our queries
> are
> > actually going through the proxy to the database (they aren't being
> > reflected back by a Lua script).
> > Bizarrely enough, some of our queries are running FASTER with MySQL Proxy
> in
> > the middle than if we were just directly connecting to the database.
>  This
> > doesn't seem to make sense -- the system with the proxy is doing strictly
> > more work.
> > Does anyone know why this is happening?  Have you seen this before?
>  We've
> > gone over our measurement system repeatedly and can't find bugs (but of
> > course there may still be bugs).  One thought is that MySQL Proxy is
> loading
> > some library it shares with MySQL into memory and thereby pre-fetching
> the
> > library for MySQL...but I don't know if that makes sense since the MySQL
> > server is always running in the background anyway.
> > We'd appreciate thoughts.
> > Thanks!
> > Adrienne
> > _______________________________________________
> > Mailing list: https://launchpad.net/~mysql-proxy-discuss
> > Post to     : mysql-proxy-discuss@xxxxxxxxxxxxxxxxxxx
> > Unsubscribe : https://launchpad.net/~mysql-proxy-discuss
> > More help   : https://help.launchpad.net/ListHelp
> >
> >
>
>
>
> --
> The Data Charmer
> http://datacharmer.org/
>

Attachment: chart1-alltypes.png
Description: PNG image

Attachment: resultSetSize.png
Description: PNG image