Re: [GENERAL] Update takes longer than expected
Andy Chambers achamb...@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.consult...@comcast.net p: 305.321.1144 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update takes longer than expected
On 10 January 2012 15:46, Jerry Sievers gsiever...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update takes longer than expected
In response to Andy Chambers achamb...@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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update takes longer than expected
Andy Chambers achamb...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update takes longer than expected
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 pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update takes longer than expected
On Mon, Jan 9, 2012 at 4:30 PM, Tom Lane t...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
