Re: Update takes longer than expected

Andy Chambers <achambers(at)mcna(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 305.321.1144