Re: extremly low memory usage - Mailing list pgsql-performance

Jeremiah Jahn wrote:
> On Fri, 2005-08-19 at 12:18 -0500, John A Meinel wrote:
>
>>Jeremiah Jahn wrote:
>>


...

>>
>>Well, in general, 3ms for a single lookup seems really long. Maybe your
>>index is bloated by not vacuuming often enough. Do you tend to get a lot
>>of updates to litigant_details?
>
> I have vacuumed this already. I get lots of updates, but this data is
> mostly unchanging.
>
>
>>There are a couple possibilities at this point. First, you can REINDEX
>>the appropriate index, and see if that helps. However, if this is a test
>>box, it sounds like you just did a dump and reload, which wouldn't have
>>bloat in an index.
>
>
> I loaded it using slony

I don't know that slony versus pg_dump/pg_restore really matters. The
big thing is that Updates wouldn't be trashing your index.
But if you are saying that you cluster once/wk your index can't be that
messed up anyway. (Unless CLUSTER messes up the non-clustered indexes,
but that would make cluster much less useful, so I would have guessed
this was not the case)

>
>
>>Another possibility. Is this the column that you usually use when
>>pulling information out of litigant_details? If so, you can CLUSTER
>>litigant_details on the appropriate index. This will help things be
>>close together that should be, which decreases the index lookup costs.
>
> clustering on this right now. Most of the other things are already
> clustered. name and case_data

Just as a reality check, they are clustered on the columns in question,
right? (I don't know if this column is a primary key or not, but any
index can be used for clustering).

>
>
>>However, if this is not the common column, then you probably will slow
>>down whatever other accesses you may have on this table.
>>
>>After CLUSTER, the current data will stay clustered, but new data will
>>not, so you have to continually CLUSTER, the same way that you might
>>VACUUM. *However*, IIRC CLUSTER grabs an Exclusive lock, so it is as
>>expensive as a VACUUM FULL. Be aware of this, but it might vastly
>>improve your performance, so it would be worth it.
>
> I generally re-cluster once a week.
>
>>>
>>>>So there is no need for preloading your indexes on the identity table.
>>>>It is definitely not the bottleneck.
>>>>
>>>>So a few design bits, which may help your database.
>>>>Why is "actor_id" a text field instead of a number?
>>>
>>>This is simply due to the nature of the data.
>>>
>>
>>I'm just wondering if changing into a number, and using a number->name
>>lookup would be faster for you. It may not be. In general, I prefer to
>>use numbers for references. I may be over paranoid, but I know that some
>>locales are bad with string -> string comparisons. And since the data in
>>your database is stored as UNICODE, I'm not sure if it has to do any
>>translating or not. Again, something to consider, it may not make any
>>difference.
>
> I don't believe so. I initialze the DB as 'lang=C'. I used to have the
> problem where things were being inited as en_US. this would prevent any
> text based index from working. This doesn't seem to be the case here, so
> I'm not worried about it.
>

Sorry, I think I was confusing you with someone else who posted SHOW ALL.

>
>
>>

...

> it's cached alright. I'm getting a read rate of about 150MB/sec. I would
> have thought is would be faster with my raid setup. I think I'm going to
> scrap the whole thing and get rid of LVM. I'll just do a straight ext3
> system. Maybe that will help. Still trying to get suggestions for a
> stripe size.
>

I don't think 150MB/s is out of the realm for a 14 drive array.
How fast is
time dd if=/dev/zero of=testfile bs=8192 count=1000000
(That should create a 8GB file, which is too big to cache everything)
And then how fast is:
time dd if=testfile of=/dev/null bs=8192 count=1000000

That should give you a semi-decent way of measuring how fast the RAID
system is, since it should be too big to cache in ram.

>
>>I can point you to REINDEX and CLUSTER, but if it is caching in ram, I
>>honestly can't say why the per loop would be that much slower.
>>Are both systems running the same postgres version? It sounds like it is
>>different (since you say something about switching to 8.0).
>
> These had little or no effect.
> The production machine is running 7.4 while the devel machine is running
> 8.0
>

Well, my concern is that maybe some portion of the 8.0 code actually
slowed things down for you. You could try reverting to 7.4 on the devel
box, though I think playing with upgrading to 8.1 might be more worthwhile.

...

>
> this is a cached version.
>

I assume that you mean this is the second run of the query. I can't
compare it too much, since this is "smith" rather than "jones". But this
one is 17s rather than the other one being 46s.

And that includes having 8k rows instead of having 5k rows.

Have you tried other values with disabled nested loops? Because this
query (at least in cached form) seems to be *way* faster than with
nested loops.
I know that you somehow managed to get 200s in your testing, but it
might just be that whatever needed to be loaded is now loaded, and you
would get better performance.
If this is true, it means you might need to tweak some settings, and
make sure your statistics are decent, so that postgres can actually pick
the optimal plan.

>
>>copa=> explain analyze select
full_name,identity_id,identity.case_id,court.id,date_of_birth,assigned_case_role,litigant_details.impound_litigant_data
>>copa-> from identity
>>copa-> join litigant_details on identity.actor_id = litigant_details.actor_id
>>copa-> join case_data on litigant_details.case_id = case_data.case_id and litigant_details.court_ori =
case_data.court_ori
>>copa-> join court on identity.court_ori = court.id
>>copa-> where identity.court_ori = 'IL081025J' and full_name like 'SMITH%' order by  full_name;
>>                                                                                     QUERY PLAN

>>-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> Sort  (cost=100502560.72..100502583.47 rows=9099 width=86) (actual time=17843.876..17849.401 rows=8094 loops=1)
>>   Sort Key: identity.full_name
>>   ->  Merge Join  (cost=100311378.72..100501962.40 rows=9099 width=86) (actual time=15195.816..17817.847 rows=8094
loops=1)
>>         Merge Cond: ((("outer".court_ori)::text = "inner"."?column10?") AND (("outer".case_id)::text =
"inner"."?column11?"))
>>         ->  Index Scan using case_speed on case_data  (cost=0.00..170424.73 rows=3999943 width=26) (actual
time=0.015..4540.525rows=3018284 loops=1) 
>>         ->  Sort  (cost=100311378.72..100311400.82 rows=8839 width=112) (actual time=9594.985..9601.174 rows=8094
loops=1)
>>               Sort Key: (litigant_details.court_ori)::text, (litigant_details.case_id)::text
>>               ->  Nested Loop  (cost=100002491.43..100310799.34 rows=8839 width=112) (actual time=6892.755..9555.828
rows=8094loops=1) 
>>                     ->  Seq Scan on court  (cost=0.00..3.29 rows=1 width=12) (actual time=0.085..0.096 rows=1
loops=1)
>>                           Filter: ('IL081025J'::text = (id)::text)

What I don't really understand is the next part. It seems to be doing an
index scan on 3.7M rows, and getting very decent performance (5s), and
then merging against a table which returns only 8k rows.
Why is it having to look through all of those rows?
I may be missing something, but this says it is able to do 600 index
lookups / millisecond. Which seems superfast. (Compared to your earlier
4ms / lookup)

Something fishy is going on here.


>>                     ->  Merge Join  (cost=2491.43..310707.66 rows=8839 width=113) (actual time=6892.656..9519.680
rows=8094loops=1) 
>>                           Merge Cond: (("outer".actor_id)::text = "inner"."?column7?")
>>                           ->  Index Scan using lit_actor_speed on litigant_details  (cost=0.00..295722.00
rows=4956820width=81) (actual time=0.027..5613.814 rows=3736703 loops=1) 
>>                           ->  Sort  (cost=2491.43..2513.71 rows=8913 width=82) (actual time=116.071..122.272
rows=8100loops=1) 
>>                                 Sort Key: (identity.actor_id)::text
>>                                 ->  Index Scan using name_speed on identity  (cost=0.00..1906.66 rows=8913 width=82)
(actualtime=0.133..81.104 rows=8100 loops=1) 
>>                                       Index Cond: (((full_name)::text >= 'SMITH'::character varying) AND
((full_name)::text< 'SMITI'::character varying)) 
>>                                       Filter: (((court_ori)::text = 'IL081025J'::text) AND ((full_name)::text ~~
'SMITH%'::text))
>> Total runtime: 17859.917 ms
>
>
>>But really, you have worse index speed, and that needs to be figured out.
>>
>>John
>>=:->

I'm assuming your data is private (since it looks like legal stuff).
Unless maybe that makes it part of the public record.
Anyway, I'm not able to, but sometimes someone like Tom can profile
stuff to see what is going on.

I might just be messing up my ability to read the explain output. But
somehow things don't seem to be lining up with the cost of a single
index lookup.
On my crappy Celeron 450 box, an index lookup is 0.06ms once things are
cached in ram.

John
=:->