Re: strange performance regression between 7.4 and 8.1

On Fri, 2 Mar 2007, Guido Neitzer wrote:

> On 02.03.2007, at 14:20, Alex Deucher wrote:
>
>> Ah OK. I see what you are saying; thank you for clarifying. Yes,
>> the SAN is configured for maximum capacity; it has large RAID 5
>> groups. As I said earlier, we never intended to run a DB on the SAN,
>> it just happened to come up, hence the configuration.
>
> So why not dumping the stuff ones, importing into a PG configured to use
> local discs (Or even ONE local disc, you might have the 16GB you gave as a
> size for the db on the local machine, right?) and testing whether the problem
> is with PG connecting to the SAN. So you have one factor less to consider
> after all your changes.
>
> Maybe it's just that something in the chain from PG to the actual HD spindles
> kills your random access performance for getting the actual rows.

I am actually starting to think that the SAN may be introducing some amount of
latency that is enough to kill your random IO which is what all of the queries
in question are doing - look up in index - fetch row from table.

If you have the time, it would be totally worth it to test with a local disk
and see how that affects the speed.

I would think that even with RAID5, a SAN with that many spindles would be
quite fast in raw throughput, but perhaps it's just seek latency that's
killing you.

When you run the bonnie tests again, take note of what the seeks/sec is
compared with the old disk. Also, you should run bonnie with the -b switch to
see if that causes significant slowdown of the writes...maybe minor synced
write activity to pg_xlog is bogging the entire system down. Is the system
spending most of its time in IO wait?

Also, another item of note might be the actual on disk DB size..I wonder if it
has changed significantly going from SQL_ASCII to UTF8.

In 8.1 you can do this:

SELECT datname,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database;

In 7.4, you'll need to install the dbsize contrib module to get the same info.

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954