Just Someone (just...@gmail.com)

Cool!

That explains it fully. So i guess there will be a better performance
to the pre-generated views at the price of more views.

Thanks!

I have a database with multiple schemas all with the same structure
(but of course different data...).

I want to create a view that will be created in a shared schema, and
when executed will be executed against the current schema. Whenever I
try it, it seems the view is linked to a specific schema used when
creating it, and doesn't reevaluates based on the current schema.

Here is the pseudo structure/code:

schema1:
=======
create table t1 ...

schema2:
=======
create table t1 ...

shared_schema:
============
create table t3 ...

create the view:
===========
set search_path to shared_schema, schema1;
create view view1 as select * from t1;

try the view:
========
set search_path to shared_schema, schema1;
select * from view1;
set search_path to shared_schema, schema2;
select * from view1;

Results:
======
In the above, both select * from view1; will return the same data,
though the search path changed.

Is there a way to make the view use the current search_path?

I am using a similar solution, and I tested it with a test containing
20K+ different schemas. Postgres didn't show slowness at all even
after the 20K (over 2 million total tables) were created. So I have
feeling it can grow even more.

Guy.

If you rather keep SELinux on, you can still set the SELinux context
on the directory where you want the tablespaces to one postgres will
like.

To find what is the permissions you need, you can use ls -Z. It will
list the SELinux context. Check /var/lib/pgsql/data (or wherever
postgres data is pointing to), and then set this same permission on
the target dir using chcon.

For example, on my FC4 system all subdirectories on the data directory have:
root:object_r:postgresql_db_t or user_u:object_r:postgresql_db_t

So if you want to chage /path/to/foo/which/is/not/under/pgdata, run
(as root or sudo):

chcon root:object_r:postgresql_db_t /path/to/foo/which/is/not/under/pgdata

This way postgres can access it, and you get the SELinux security.

Bye,

Guy.

http://www.famundo.com
http://devblog.famundo.com
- Hide quoted text -

The schema can change, but I rather not.

The use case is a web app where you can tag items with tags
(many-2-many). There are multiple items you can tag: contacts,
schedules, lists, etc... And then you can search and categorize by
tags. The standard for this if you look aroung the web is to retrieve
the tagged records with any of the tags you select. Effectively an OR
query.

What I'm trying to do is search for items matching multiple tags at
the same time - and AND query. So that I can bring up all contacts
that are tagged with friends and movie-lovers.

Hope that clears it up a bit...

Guy.

I'm trying to generate a query that will handle tags matching in a database.

The simplified structure is

create table contacts (
  id  serial primary key,
  name varchar
);

create table books (
  id  serial primary key,
  name varchar
);

create table tags (
  id serial primary key,
  name varchar
);

create table taggings (
  tag_id int,
  tagged_id int,
  tagged_type int -- points to the table this tag is tagging
);

What I want to now achieve is to find all items that are tagged with
the same set of tags. So it's an AND matching on a list of tags I
have.

I have two types of matching. One is within the same object type
(where both tagged objects are the same, say two books with the same
set of tags) and one that will find ANY object that's tagged with the
same tag (like book and contact)

Current query (for the same object type) I am using is the following,
for a list of 4 tags called summer, winter, spring and fall.

SELECT *
FROM contacts WHERE 4 = ( SELECT COUNT(*)

    FROM tags, taggings
WHERE tags.id = taggings.tag_id

          AND lower(tags.name) IN ( 'summer' , 'winter', 'spring', 'fall' )

          AND taggings.tagged_type = 1
          AND taggings.tagged_id = contacts.id);


The query to match all the objects tagged with a given set of tags is:

SELECT DISTINCT taggings.tagged_id, taggings.tagged_type
FROM taggings WHERE 4 = ( SELECT COUNT(*)
    FROM tags, taggings as taggings2 WHERE tags.id = taggings2.tag_id
      AND lower(tags.name) IN ( 'summer' , 'winter', 'spring', 'fall' )
      AND taggings.tagged_type = taggings2.tagged_type
      AND taggings.tagged_id = taggings2.tagged_id );

The idea in both is to see that I find the number of tags needed.

I've attached a script that will create the tables, insert some data
and run the queries to make it easy to try it.

Is there a way to simplify this query and make it more efficient?

Thanks!

Guy.

I have a process for PITR backups running nicely. I'm pretty amazed by
the smoothness of it all!

Now I'm looking at the retrieval part, and I have something I'm
looking for clarification on.

The documentation say that a recovery.conf file is needed for the
restore. My tests indicate that I can just restore the backup, copy
over the latest WAL files and launch postgres. Is that ok? Can I just
use this way?

I could create the recovery file and have it copy the files, but as
the second machine I am restoring into can always have the DB
reloaded, I wonder if it isn't easier just to copy the files. I
actually rsync my backup directory and my WAL archive directories,
into the second machine.

Bye,

Guy.

Hi Tom,

> If you sort first by mtime and second by file name you should find the
> right one in all cases, ie, take the latest mtime among the
> properly-named files, breaking ties by taking the higher filename.
>
> It'd probably be better if we had a function to report this, but
> you can get along without one.

For now I'm using ls with grep:
LAST_WAL=3D$(/bin/ls -t1p $WAL_DIR | /bin/grep -v / | /bin/grep -v
backup | /usr/bin/head -1)

But a pg function for that would be great.

Regards,

Guy Naor.

What is the best way to find the latest partial WAL file?

Based on my tests, using the mtime isn't 100% accurate, as if a
pg_start_backup/pg_stop_backup() operation is run, the .backup file
created might be newer than the last WAL file. It also seems that the
WAL file related to the backup is being updated according to the
mtime. Using a file name is also not 100% accurate, as it seems that
postgres will recycle the archived files, giving them "future" names
before they are used, leaving the directory with files that are older
by name, but not yet with relevant data.

Is there a way to discover what is the real current WAL file? I've
read that one of the todo's for 8.2 is to handle partial files
archiving, but I'm looking for something I can use now.

Regards,

Guy.

I implemented wal archiving and it seems to be working. The segments
are being copied by the shell script, and in the pg_log file I see
this line:

LOG:  archived transaction log file "000000010000001D00000096"

But the file is still int he pg_xlog directory. In the documentation I
read that it might either delete or recycle the segment files. As I
still see it in the directory, I want to make absolutely sure my
archiving is working correctly.

So my question is: Is what I'm seeing meaning the WAL archiving is
working? Or should I expect the file to be deleted?

Bye,

Guy.

After exchanging a few emails regarding pgbench in the list, I was
thinking it'll be cool to have a place to post pgbench resulats from
all over the place. Just so people can get an idea of what others are
getting.

If more people think it's a good idea, I'll be glad to host it as part
of my blog. I'll create a section for that and will update it
everytime results are submitted. If that picks up steam I'll create a
small database and app to make it more automatic.

Let me know if there's interest and I'll take care of it.

Bye,

Guy.

Hi Jim,

Actually, most table are VERY small. On each schema I would expect 4 -
5 tables to go over 1000 records. The rest will be much smaller, with
most at under 100. And aside from 2-3 tables, the activity will be
pretty low (few records a day at the most extreme).

Can I use this to optimize the fsm part in a different way?

Bye,

Guy.

Hi Jim,

On 3/24/06, Jim Nasby <jnasby@pervasive.com> wrote:
> You want max_fsm_relations to be greater than select count(*) from pg_cla=
ss where
> relkind in ('i','t') *across all databases*. And you want max_fsm_pages t=
o be bigger than
> that. That's the only way you can be assured that you'll be tracking free=
space info for
> every table.

So I need something like 2,000,000 in max_fsm_relations. So
max_fsm_pages now need to be 16 times that? That's how I read the
postgresql.conf comment.

> Taking a look at the last few lines of a vacuumdb -av would also be insig=
htful, but I'm
> pretty certain that will only show what you need right *now*, not what yo=
u might need in
> the future.

Thanks!

Bye,

Guy

Hi Jim,

> Just make sure you increase max_fsm_relations, and that max_fsm_pages is
> at least > max_fsm_relations, because each relation must get at least
> one page.

I increased it to 40000 relations, should I go even higher?

Hi,

I am creating a hosted solution that I want to base on separation by
schemas. So that each hosted family we will have, will have a schema
assigned to it (and a user). On login I will set the search path, and
so each family will see it's tables. This is all tested and works
fine.

But I would like to know if there's a practical limit to the number of
schemas and tables I can have. Please note that I'm using table spaces
to make sure the directories are manageable.

I tested it so far with 13000 schemas and users, with 26 tables in
each schema (a total of more that 330000 tables). It works perfectly,
but I would like to know if someone has experience with this number of
tables/schemas, and if there's a limit I should be careful of.

Bye,

Guy.

Hi Magnus,

> It might seem that I'm selling ext3 or something :) but it's the linux
> filesystem I know best.
> If you want ext3 to perform with large directories, there is an mkfs
> option that enables directory hashing that you can try: -O dir_index.

Not at all (sell ext3 ;-) ). It's great to get this kind of info! I
rather use ext3 as it's VERY stable., and the default in Fedora
anyway. So thanks for the tip!

Bye,

Guy.

I played a bit with kernnel versions as I was getting a kernel panic
on my Adaptec card. I downgraded to 2.6.11 (the original that came
with fedora core 4) and the panic went away, but more than that, the
performance on XFS went considerably higher. With the exact same
settings as before, I got now Average of 813.65tps with a standard
deviation of: 130.33.

I hope this kernel doesn't panic on me. But I'll know just tomorrow as
I'm pounding on the machine now.

Bye,

Guy.

Hi,

> Did you re-initialize the test pgbench database between runs?
> I get weird results otherwise since some integers gets overflowed in the
> test (it doesn't complete the full 10000 transactions after the first run=
).

No, I didn't. The reason is that I noticed that the first run is
always MUCH faster. My initial runs if I reinit pgbench and run again
will always hover around 900-970 tps for xfs. And I didn't need this
as a real performance test, it was a side effect of a load test I was
doing on the server. Also, pgbench isn't close to the load I'll see on
my server (web application which will be mostly read)

> Could you please tell me what stripe size you have on the raid system?
> Could you also share the mkfs and mount options on each filesystem you
> tried?

RAID stripe size of 256K.
File system creation:
xfs: mkfs -t xfs -l size=3D64m /dev/md0
jfs: mkfs -t jfs /dev/md0

Mount for xfs with -o noatime,nodiratime,logbufs=3D8
jfs: -o noatime,nodiratime

> A hint on using a raided ext3 system is to use whole block device
> instead of partitions to align the data better and use data=3Djournal wit=
h
> a big journal. This might seem counter-productive at first (it did to
> me) but I increased my throughput a lot when using this.

Thanks for the advice! Actually, the RAID 10 I have is mounted as
/var/lib/pgsql, so it's ONLY for postgres data, and the pg_xlog
directory is mounted on another disk.

> My filesystem parameters are calculated like this:
> stripe=3D256 # <- 256k raid stripe size
> bsize=3D4 # 4k blocksize
> bsizeb=3D$(( $bsize * 1024 )) # in bytes
> stride=3D$(( $stripe / $bsize ))
>
> mke2fs -b $bsizeb -j -J size=3D400 -m 1 -O sparse_super \
>   -T largefile4 -E stride=3D$stride /dev/sdb
>
> Mounted with: mount -t ext3 -o data=3Djournal,noatime /dev/sdb /mnt/test8

That's an interesting thing to try, though because of other things I
want, I prefer xfs or jfs anyway. I will have an extreme number of
schemas and files, which make high demands on the directory structure.
My tests showed me that ext3 doesn't cope with many files in
directories very well. With xfs and jfs I can create 500K files in one
directory in no time (about 250 seconds), with ext3 it start to crawl
after about 30K files.

> I'm a little surprised that I can get more pgbench performance out of my
> system since you're using 10K scsi disks. Please try the above settings
> and see if it helps you...
>
> I've not run so many tests yet, I'll do some more after the weekend...

Please share the results. It's very interesting...

Bye,

Guy.

BTW, one thing I also tested is a software RAID0 over two RAID5 SATA
arrays. Total disk count in this is 15. The read performance was
really good. The write performance (as expected) not so great. But
that was just a test to get a feeling of the speed. This RAID5 system
is only used for file storage, not database.

Jim,

I did another test with ext3 using data=3Dwriteback, and indeed it's much b=
etter:

Avg:    429.87
Stdev:  77

A bit (very tiny bit) faster than xfs and bit slower than jfs. Still,
very much improved.

Bye,

Guy.

I was doing some load testing on a server, and decided to test it with
different file systems to see how it reacts to load/speed. I tested
xfs, jfs and ext3. The machine runs FC4 with the latest 2.6.15 kernel
from Fedora.

Hardware: Dual Opteron 246, 4GB RAM, Adaptec 2230 with battery backup,
2 10K SCSI disks in RAID1 for OS and WAL (with it's own partiton on
ext3), 6 10K scsi disks in RAID10 (RAID1 in hw, RAID0 on top of that
in sw). Postgres config tweaked as per the performance guide.

Initialized the data with: pgbench -i -s 100
Test runs: pgbench -s 100 -t 10000 -c 20
I did 20 runs, removed the first 3 runs from each sample to account
for stabilization. Here are the results in tps without connection
establishing:

FS:       JFS     XFS     EXT3
Avg:     462      425       319
Stdev:  104        74       106

Intererstingly, the first 3 samples I removed had a MUCH higher tps
count. Up to 900+.

Bye,

Guy.