On Thu, Jun 12, 2014 at 12:08 AM, Matt Silverlock
<matt@eatsleeprepeat.net> wrote:
>
> Pages that SELECT multiple rows with OFFSET and LIMIT conditions struggle to top 1.3k req/s
Is that tested at the OFFSET and LIMIT of 0 and 15, as shown in the
explain plan?
> The query in question is: http://explain.depesz.com/s/7g8 and the table schema is as below:
The reported runtime of 0.078 ms should be able to sustain nearly 10
times the reported rate of 1.3k/s, so the bottleneck would seem to be
elsewhere.
Perhaps the bottleneck is formatting the result set in postgres to be
sent over the wire, then sending it over the wire, then parsing it in
the Go connection library to hand back to the Go user code, and then
the Go user code doing something meaningful with it.
What happens if you get rid of the offset and the order by, and just
use limit? I bet it doesn't change the speed much (because that is
not where the bottleneck is).
You seem to be selecting an awful lot of wide columns. Do you really
need to see all of them?
>
> Some pgbench results from this machine as well:
>
> $ pgbench -c 128 -C -j 4 -T 15 -M extended -S
This is just benchmarking how fast you can make and break connections
to the database.
Because your app is using an embedded connection pooler, this
benchmark isn't very relevant to your situation.
>
> Ultimately I'm not expecting a miracle—database ops are nearly always the slowest part
> of a web server outside the latency to the client itself—but I'd expect something a little
> closer (even 10% of 33k would be a lot better). And of course, this is somewhat "academic"
> because I don't expect to see four million hits an hour—but I'd also like to catch problems
> for future reference.
I think you have succeeded in doing that. If you want to get
substantially faster than the current speed in the future, you will
need a web-app-side results cache for this type of query.
I can't imagine the results of such a query change more than 1300
times/s, nor that anyone would notice or care if the observed results
which were stale by two or three seconds.
That type of cache is a PITA, and I've never needed one because I also
don't expect to get 4 millions hits an hour. But if this is what your
future looks like, you'd be best off to embrace it sooner rather than
later.
Cheers,
Jeff