?

Log in

No account? Create an account

Query Cost, Warm vs Cold, Prepared Statements

« previous entry | next entry »
Jan. 3rd, 2008 | 02:14 am

I've been getting pinged about query prioritization because of a comment I made elsewhere so I might as well explain the idea :)

If you have a given number of processors, lets say 32, you may want to reserve a set number across a spectrum of performance. For instance let us take a fuzzy definition of:
  • Slow
  • Fast
  • Unknown

    You could then map half your processors to Fast queries, and then split your Slow and Unknown queries across your other processors. That way you could always know that you have enough CPU to handle Fast queries (or you could give hints that certain queries matching a pattern should always be fast).

    With MySQL you never have costs which are exact before the query is optimized, namely because the optimizer recomputes the execution path for each query. This means you do not have stale data being used for execution, but it means also that the query is always being calculated.

    If you can tie the query execution cost calculation to a specific prepared statement you could keep a running average of cost (possibly even the deviation, which would be nice for knowing which queries are well behaved).

    I am sure proxy could do this by tracking response times, which is great for scale out, but with faster machines there is no reason not to figure out the scale up solution as well.

    With the libevent work we now have pools of threads, so the above work is conceivable.

    On a slightly different note, I was asked about storage engines doing this. The answer is that yes a storage engine could do some of this as well, but thread pooling works better in the layer above the engines, so you will get more bang for the buck by working on the upper end of the server. I've run into this problem several times with the Archive engine. I can push data at this point faster then the server can really process it (aka, it is the bottleneck). The upper end of the server will take more TLC to really make use of multiple CPUs. Libevent was a good start, but once we hit 32+ way machines we are going to need to work on our strategies.

    32+ way machines are going to make everyone rethink their strategies :)
  • Link | Leave a comment | Share

    Comments {5}

    qu1j0t3

    Sun

    from: qu1j0t3
    date: Jan. 3rd, 2008 07:57 pm (UTC)
    Link

    once we hit 32+ way machines

    Like those you can already buy cheaply from Sun?

    Reply | Thread

    Brian "Krow" Aker

    Re: Sun

    from: krow
    date: Jan. 3rd, 2008 08:03 pm (UTC)
    Link

    Yes :)

    I have not worked with a T2000 yet. I was really unimpressed with the T1000 hardware. I am really hoping the T2000 turns out to be a large improvement.

    32way machines will have to come down in price before they become very common. Right now the tip is at 16way, and I don't know if we will see 32way happen in mass this year.

    Reply | Parent | Thread

    (no subject)

    from: jamesd
    date: Jan. 3rd, 2008 08:03 pm (UTC)
    Link

    Thanks for following up. :) I don't care about the layer, just the result. :) Well, in the server beats in a proxy IMO, but that's about it.

    You're spot on about changing strategies for lots of cores. One of those times when devs have to run just to keep up with the change rate. As for shared resources that can be needed for significant time, with just one mutex for the whole resource (cache pool, say)... dead concept. Just hasn't been finally buried yet.

    Reply | Thread

    Brian "Krow" Aker

    (no subject)

    from: krow
    date: Jan. 3rd, 2008 08:18 pm (UTC)
    Link

    BTW with the IPv6 patch, the global lock around DNS lookup goes away.

    There still exists a global lock on the DNS Cache lookup. We really need a better internal hash table algo for mysys. A lot of locks exist now because of its design.

    Reply | Parent | Thread

    (no subject)

    from: jamesd
    date: Jan. 3rd, 2008 08:25 pm (UTC)
    Link

    Progress anyway. Good to read.

    Reply | Parent | Thread