Thread: Update takes longer than expected
Hi, I have an update that takes longer than expected and wondered if there's an easy way to make it go faster. It's pretty simple:- create table session ( id serial primary key, data text); update session set data = 'ipsum lorem...' where id = 5; The "ipsum lorem.." stuff is an encrypted session variable from a rails app that does tend to get quite large select avg(length(data)) from session => 31275 We're trying to migrate the app from mysql to pg and this is one of the performance bottle-necks. Unfortunately it slows down every request by about 5 seconds. MySQL (both MyISAM and InnoDB) does this almost instantaneously. Cheers, Andy
In response to Andy Chambers <achambers@mcna.net>: > > I have an update that takes longer than expected and wondered if > there's an easy way to make it go faster. > > It's pretty simple:- > > create table session ( > id serial primary key, > data text); > > update session > set data = 'ipsum lorem...' > where id = 5; > > The "ipsum lorem.." stuff is an encrypted session variable from a > rails app that does tend to get quite large > > select avg(length(data)) from session > => 31275 > > We're trying to migrate the app from mysql to pg and this is one of > the performance bottle-necks. Unfortunately it slows down every > request by about 5 seconds. MySQL (both MyISAM and InnoDB) does this > almost instantaneously. Those aren't the types of queries that normally take a long time in PostgreSQL, so my initial guess is that your DB server is very poorly tuned. What is your vacuum strategy? for example. That fact that you aren't mentioning any of these things leads me to guess that you're new enough to PostgreSQL that you need to get yourself up to speed on basid PostgreSQL config. There's a lot here, but you'll be much more comfortable with things if you familiarize yourself with this chapter: http://www.postgresql.org/docs/9.1/static/runtime-config.html On a more targeted level, doing a: EXPLAIN ANALYZE update session set data = 'ipsum lorem...' where id = 5; Will give you details on what's taking so long. If the output of that doesn't help, you can include it in an email to the list and people will provide details on what it means and advice on how to fix it. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
Andy Chambers <achambers@mcna.net> writes:
> I have an update that takes longer than expected and wondered if
> there's an easy way to make it go faster.
> It's pretty simple:-
> create table session (
> id serial primary key,
> data text);
> update session
> set data = 'ipsum lorem...'
> where id = 5;
> The "ipsum lorem.." stuff is an encrypted session variable from a
> rails app that does tend to get quite large
> select avg(length(data)) from session
> => 31275
> We're trying to migrate the app from mysql to pg and this is one of
> the performance bottle-necks. Unfortunately it slows down every
> request by about 5 seconds.
5 seconds!? I tried this example (with 31K of random data in the
literal) and got timings in the 10-to-15-millisecond range. And that's
with a debug build on an old, slow machine. There's something
drastically wrong with your setup, but there's not enough information
here to guess what.
regards, tom lane
On 01/09/12 1:03 PM, Andy Chambers wrote: > I have an update that takes longer than expected and wondered if > there's an easy way to make it go faster. > > It's pretty simple:- > > create table session ( > id serial primary key, > data text); > > update session > set data = 'ipsum lorem...' > where id = 5; > > The "ipsum lorem.." stuff is an encrypted session variable from a > rails app that does tend to get quite large > > select avg(length(data)) from session > => 31275 > > We're trying to migrate the app from mysql to pg and this is one of > the performance bottle-necks. Unfortunately it slows down every > request by about 5 seconds. MySQL (both MyISAM and InnoDB) does this > almost instantaneously. is that 5 seconds for ONE of those UPDATEs, or is your webapp making 100s of those updates for every web request? -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On Mon, Jan 9, 2012 at 4:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: Sorry. Forgot to hit "Reply to All" Aha. I think I'd gotten carried away with some of the settings in order to optimize for bulk loading. Reverting back to the default postgresql.conf gets me back to the sort of times you guys are seeing here. Thanks, Andy
Andy Chambers <achambers@mcna.net> writes: > Hi, > > I have an update that takes longer than expected and wondered if > there's an easy way to make it go faster. > > It's pretty simple:- > > create table session ( > id serial primary key, > data text); > > update session > set data = 'ipsum lorem...' > where id = 5; > > The "ipsum lorem.." stuff is an encrypted session variable from a > rails app that does tend to get quite large > > select avg(length(data)) from session > => 31275 That isn't large enough to take more than milliseconds to update on anything but the weakest hardware under normal conditions. Do you have multiple concurrent sessions updating the same row and holding transactions open for something like the delay time observed? Is that delay consistent or periodic, perhaps on some roughly predictable interval? Checkpoint induced IO flooding can lead to occasional large slowdowns on malconfigured systems and might be worth looking into if suggestion #1 disqualified. > We're trying to migrate the app from mysql to pg and this is one of > the performance bottle-necks. Unfortunately it slows down every > request by about 5 seconds. MySQL (both MyISAM and InnoDB) does this > almost instantaneously. Migrate from MySQL to Postgres? Good idea. Keep the faith :-) HTH > Cheers, > Andy > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 305.321.1144
On 10 January 2012 15:46, Jerry Sievers <gsievers19@comcast.net> wrote: >> We're trying to migrate the app from mysql to pg and this is one of >> the performance bottle-necks. Unfortunately it slows down every >> request by about 5 seconds. That's a delay that could be due to DNS problems or other network issues. It may be worth checking that you don't have any. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.