Re: [PERFORM] How to improve db performance with $7K?

2005-03-29 Thread PFC

With hardware tuning, I am sure we can do better than 35Mb per sec. Also
WTF ?
My Laptop does 19 MB/s (reading <10 KB files, reiser4) !
A recent desktop 7200rpm IDE drive
# hdparm -t /dev/hdc1
/dev/hdc1:
 Timing buffered disk reads:  148 MB in  3.02 seconds =  49.01 MB/sec
# ll "DragonBall 001.avi"
-r--r--r--1 peufeu   users218M mar  9 20:07 DragonBall 001.avi
# time cat "DragonBall 001.avi" >/dev/null
real0m4.162s
user0m0.020s
sys 0m0.510s
(the file was not in the cache)
=> about 52 MB/s (reiser3.6)
So, you have a problem with your hardware...
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] Dynamic query perormance

2005-03-30 Thread PFC

  which is evaluated and replanned on each entry to the
  FOR loop. This allows the programmer to choose the speed
	On each entry is not the same as on each iteration. It would means "every  
time the loop is started"...

Regards,
    PFC
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] fine tuning for logging server

2005-03-31 Thread PFC

The reason: if the power cord is yanked, the OS _must_ boot back up in
good condition. If the DB is corrupted, whatever, nuke it then re-
initialize it. But the OS must survive act-of-god events.
Well, in that case :
- Use reiserfs3 for your disks
- Use MySQL with MyISAM tables
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq


Re: [PERFORM] Very long time to execute and Update, suggestions?

2005-03-31 Thread PFC

   So I need to either find an Index that will work with regexes or  
re-write my code to update each subdirectory separately and use simpler  
UPDATE statement for each.
	Why don't you use a LTREE type to model your directory tree ? It's been  
designed specifically for this purpose and has indexed regular expression  
search.

http://www.sai.msu.su/~megera/postgres/gist/ltree/
http://www.sai.msu.su/~megera/postgres/gist/
---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PERFORM] Query Optimizer Failure / Possible Bug

2005-04-03 Thread PFC
	Noticed this problem,too.
	You can always make the calculation you want done once inside a set  
returning function so it'll behave like a table, but that's ugly.

On Mon, 28 Mar 2005 16:14:44 +0200, Hannes Dorbath  
<[EMAIL PROTECTED]> wrote:

hm, a few days and not a single reply :|
any more information needed? test data? simplified test case? anything?
thanks
Hannes Dorbath wrote:
The query and the corresponding EXPLAIN is at
 http://hannes.imos.net/query.txt
 I'd like to use the column q.replaced_serials for multiple calculations
in the SELECT clause, but every time it is referenced there in some way
the whole query in the FROM clause returning q is executed again.
 This doesn't make sense to me at all and eats performance.
 If this wasn't clear enough, for every
 q.replaced_serials  AS some_column
 in the SELECT clause there is new block of
 ---
->  Aggregate  (cost=884.23..884.23 rows=1 width=0)
  ->  Nested Loop  (cost=0.00..884.23 rows=1 width=0)
->  Index Scan using ix_rma_ticket_serials_replace on
rma_ticket_serials rts  (cost=0.00..122.35
rows=190 width=4)
  Index Cond: ("replace" = false)
->  Index Scan using pk_serials on serials s
  (cost=0.00..3.51 rows=1 width=4)
  Index Cond: (s.serial_id = "outer".serial_id)
  Filter: ((article_no = $0) AND (delivery_id = $1))
---
 in the EXPLAIN result.
 For those who wonder why I do this FROM (SELECT...). I was searching  
for
a way to use the result of an subselect for multiple calculations in the
SELECT clause and return that calculation results as individual columns.
 I tested a bit further and found out that PG behaves the same in case q
is a view. This makes me wonder how efficient the optimizer can work
with views - or even worse - nested views.
 Tested and reproduced on PG 7.4.1 linux and 8.0.0 win32.
  Thanks in advance,
Hannes Dorbath


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq


Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread PFC

This is done using COPY syntax, not INSERT syntax. So I suppose "yes"
I do.  The file that is being used for COPY is kept on a ramdisk.
	COPY or psql \copy ?
	If you wanna be sure you commit after each COPY, launch a psql in a shell  
and check if the inserted rows are visible (watching SELECT count(*) grow  
will do)

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq


Re: [PERFORM] Follow-Up: How to improve db performance with $7K?

2005-04-06 Thread PFC

and we get about 65MB/sec sustained when writing to an ext3 filesystem
(actually, when wgetting a file off the gigabit LAN :-) ). I haven't
	Well, unless you have PCI 64 bits, the "standard" PCI does 133 MB/s which  
is then split exactly in two times 66.5 MB/s for 1) reading from the PCI  
network card and 2) writing to the PCI harddisk controller. No wonder you  
get this figure, you're able to saturate your PCI bus, but it does not  
tell you a thing on the performance of your disk or network card... Note  
that the server which serves the file is limited in the same way unless  
the file is in cache (RAM) or it's PCI64. So...


tested
reading, though.
/* Steinar */

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq


Re: [PERFORM] Functionscan estimates

2005-04-09 Thread PFC

But with all due respect to Joe, I think the reason that stuff got
trimmed is that it didn't work very well.  In most cases it's
*hard* to write an estimator for a SRF.  Let's see you produce
one for dblink() for instance ...
	Good one...
	Well in some cases it'll be impossible, but suppose I have a function  
get_id_for_something() which just grabs an ID using dblink, then I know it  
returns one row, and pg would be interested in that information too !

---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PERFORM] Functionscan estimates

2005-04-09 Thread PFC

My solution would be a lot simpler, since we could simply populate
pg_proc.proestrows with "1000" by default if not changed by the DBA.  In  
an
even better world, we could tie it to a table, saying that, for example,
proestrows = my_table*0.02.
	What if the estimated row is a function of a parameter ?
	Say a function takes as a parameter :
	- a number to use in a LIMIT
	- it's a function to generate a certain number of values from a  
predetermined set (like, array -> set returning function)

In all those cases it's no use to have just a fixed number.
Id suggest two solutions :
- The ideal solution which is impossible to do :
The function tells the planner about its stats, looking at its 
parameters
	- A solution that would be possible to do
	 pg_proc.proestrows is... the name of another function, defined by the  
user, which takes the exact same parameters as the set returning function  
we're talking about, and which returns estimates.

For instance, in pseudo-sql :
CREATE FUNCTION int_array_srf( INTEGER[] ) RETURNS SETOF INTEGER LANGUAGE  
plpgsql AS $$
BEGIN
	FOR _i IN 1..icount($1)
		RETURN NEXT $1[_i];
	END
END	In the two cases above, this would give :

CREATE FUNCTION array_srf_estimator( INTEGER[] ) RETURNS INTEGER LANGUAGE  
plpgsql AS $$
BEGIN
	RETURN icount( $1 );
END;

ALTER FUNCTION array_srf SET ESTIMATOR array_srf_estimator;
	Another interesting case would be the famous "Top 5 by category" case  
where we use a SRF to emulate an index skip scan. Say we have a table  
Categories and a table Users, each User having columns "categories" and  
"score" and we want the N users with best score in each category :

CREATE FUNCTION top_n_by_category( INTEGER ) RETURN SETOF users%ROWTYPE  
LANGUAGE plpgsql AS $$
DECLARE
	_cat_id	INTEGER;
	_n ALIAS FOR $1;
	_user	users%ROWTYPE;
BEGIN
	FOR _cat_id IN SELECT category_id FROM categories DO
		FOR _user IN SELECT * FROM users WHERE category_id = _cat_id ORDER BY  
score DESC LIMIT _n DO
			RETURN NEXT _user;
		END
	END
END
	
CREATE FUNCTION top_n_by_category_estimator( INTEGER ) RETURN INTEGER  
LANGUAGE plpgsql AS $$
BEGIN
	RETURN $1 * (the estimated number of rows for the categories table taken  
from the table statistics);
END;

ALTER FUNCTION top_n_by_category SET ESTIMATOR top_n_by_category_estimator;
Got it ?
	The array_srf case would be extremely useful as this type of function is  
generally used to join against other tables, and having estimates is  
useful for that.
	The top_n case would be useless if we're just returning the rows from the  
function directly, but would be useful if we'll join them to other tables.

This sounds pretty simple, powerful, and versatile.
	Additionally, in some cases (most notably the array case) it's easy to  
estimate the statistics on the returned values because they're all in the  
array already, so the mechanism could be extended to have a way of  
returning a pseudo pg_stats for a Set Returning function.

	For instance, say you have a SRF which returns N random rows from a  
table. It could have an estimator which would return a rowcount of N, and  
a statistics estimator which would return the sats rows for the source  
table, appropriately modified.

This sounds harder to do.
WHat do you think ?








---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PERFORM] Foreign key slows down copy/insert

2005-04-14 Thread PFC

I have a table A with an int column ID that references table B column
ID. Table B has about 150k rows, and has an index on B.ID. When trying
to copy 1 million rows into A, I get the following \timings:
	You're using 7.4.5. It's possible that you have a type mismatch in your  
foreign keys which prevents use of the index on B.
	First of all, be really sure it's THAT foreign key, ie. do your COPY with  
only ONE foreign key at a time if you have several, and see which one is  
the killer.

Then, supposing it's the column in A which REFERENCE's B(id) :
SELECT id FROM A LIMIT 1;
(check type)
SELECT id FROM B LIMIT 1;
(check type)
EXPLAIN ANALYZE the following :
SELECT * FROM B WHERE id = (SELECT id FROM A LIMIT 1);
It should use the index. Does it ?
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PERFORM] How to improve db performance with $7K?

2005-04-14 Thread PFC

The real question is whether you choose the single 15kRPM drive or  
additional
drives at 10kRPM... Additional spindles would give a much bigger
	And the bonus question.
	Expensive fast drives as a RAID for everything, or for the same price  
many more slower drives (even SATA) so you can put the transaction log,  
tables, indexes all on separate physical drives ? Like put one very  
frequently used table on its own disk ?
	For the same amount of money which one would be more interesting ?

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match


Re: [PERFORM] How to improve db performance with $7K?

2005-04-15 Thread PFC

My argument is that a sufficiently smart kernel scheduler *should*
yield performance results that are reasonably close to what you can
get with that feature.  Perhaps not quite as good, but reasonably
close.  It shouldn't be an orders-of-magnitude type difference.
	And a controller card (or drive) has a lot less RAM to use as a cache /  
queue for reordering stuff than the OS has, potentially the OS can us most  
of the available RAM, which can be gigabytes on a big server, whereas in  
the drive there are at most a few tens of megabytes...

	However all this is a bit looking at the problem through the wrong end.  
The OS should provide a multi-read call for the applications to pass a  
list of blocks they'll need, then reorder them and read them the fastest  
possible way, clustering them with similar requests from other threads.

	Right now when a thread/process issues a read() it will block until the  
block is delivered to this thread. The OS does not know if this thread  
will then need the next block (which can be had very cheaply if you know  
ahead of time you'll need it) or not. Thus it must make guesses, read  
ahead (sometimes), etc...

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly


Re: [PERFORM] How to improve db performance with $7K?

2005-04-15 Thread PFC

platter compared to the rotational speed, which would agree with the
fact that you can read 70MB/sec, but it takes up to 13ms to seek.
	Actually :
	- the head has to be moved
	this time depends on the distance, for instance moving from a cylinder to  
the next is very fast (it needs to, to get good throughput)
	- then you have to wait for the disk to spin until the information you  
want comes in front of the head... statistically you have to wait a half  
rotation. And this does not depend on the distance between the cylinders,  
it depends on the position of the data in the cylinder.
	The more RPMs you have, the less you wait, which is why faster RPMs  
drives have faster seek (they must also have faster actuators to move the  
head)...

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] Foreign key slows down copy/insert

2005-04-15 Thread PFC

Index Scan using ix_B on B (cost=0.04..3.06 rows=1 width=329) (actual  
time=93.824..93.826 rows=1 loops=1)
Index Cond: (id = $0)
InitPlan
  ->  Limit  (cost=0.00..0.04 rows=1 width=4) (actual  
time=15.128..15.129 rows=1 loops=1)
->  Seq Scan on A  (cost=0.00..47569.70 rows=1135570  
width=4) (actual time=15.121..15.121 rows=1 loops=1)
  Total runtime: 94.109 ms
94 ms for an index scan ?
this look really slow...
	was the index in the RAM cache ? does it fit ? is it faster the second  
time ? If it's still that slow, something somewhere is severely screwed.

	B has 150K rows you say, so everything about B should fit in RAM, and you  
should get 0.2 ms for an index scan, not 90 ms !
	Try this :

	Locate the files on disk which are involved in table B (table + indexes)  
looking at the system catalogs
	Look at the size of the files. Is the index severely bloated ? REINDEX ?  
DROP/Recreate the index ?
	Load them into the ram cache (just cat files | wc -b several times until  
it's almost instantaneous)
	Retry your query and your COPY

	I know it's stupid... but it's a lot faster to load an index in the cache  
by plainly reading the file rather than accessing it randomly.
	(even though, with this number of rows, it should not be THAT slow !)

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly


Re: [PERFORM] BLOB's bypassing the OS Filesystem for better Image

2005-05-01 Thread PFC
	My laptop reads an entire compiled linux kernel (23000 files totalling  
250 MBytes) in about 1.5 seconds if they're in cache. It's about 15.000  
files/second. You think it's slow ? If you want to read them in random  
order, you'll probably use something else than a laptop drive, but you get  
the idea.

Filesystem is reiser4.
	If you use ext2, you'll have a problem with many files in the same  
directory because I believe it uses a linear search, hence time  
proportional to the number of files (ouch). I once tried to put a million  
1-kbyte files in a directory ; it was with reiserfs 3, and it didn't seem  
to feel anything close to molested. I believe it took some 10 minutes, but  
it was two years ago so I don't remember very well. NTFS took a day, that  
I do remember ! By curiosity I tried to stuff 1 million 1KB files in a  
directory on my laptop right now, It took a bit less than two minutes.

On Tue, 26 Apr 2005 11:34:45 +0200, [EMAIL PROTECTED] <[EMAIL PROTECTED]>  
wrote:


Which filesystems? I know ext2 used to have issues with many-thousands  
of files in one directory, but that was a directory scanning issue  
rather than file reading.
 From my Point of view i think it is better to let one Process do the  
operation to an Postgres Cluster Filestructure as
if i bypass it with a second process.

For example:
A User loads up some JPEG Images over HTTP.
a) (Filesystem)
On Filesystem it would be written in a File with a random generated  
Filename (timestamp or what ever)
(the Directory Expands and over a Million Fileobjects with will be  
archived, written, replaced, e.t.c)

b) (Database)
The JPEG Image Information will be stored into a BLOB as Part of a  
special Table, where is linked
wit the custid of the primary Usertable.

 From my Point of view is any outside Process (must be created, forked,  
Memory allocated, e.t.c)
a bad choice. I think it is generall better to Support the Postmaster in  
all Ways and do some
Hardware RAID Configurations.

My Question:
Can i speedup my Webapplication if i store my JPEG Images with small
sizes inside my PostgreSQL Database (on verry large Databasis over 1  
GByte
and above without Images at this time!)

No. Otherwise the filesystem people would build their filesystems on  
top of PostgreSQL not the other way around. Of course, if you want  
image updates to be part of a database transaction, then it might be  
worth storing them in the database.
Hmm, ORACLE is going the other Way. All File Objects can be stored into  
the Database if the DB
has the IFS Option (Database Filesystem and Fileserver insinde the  
Database).



I hope some Peoples can give me a Tip or Hint where in can
some usefull Information about it!
Look into having a separate server (process or actual hardware) to  
handle requests for static text and images. Keep the Java server for  
actually processing

Thanks
---(end of broadcast)---
TIP 6: Have you searched our list archives?
   http://archives.postgresql.org

---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PERFORM] Partitioning / Clustering

2005-05-10 Thread PFC

SELECT row1, row2 FROM table1_on_machine_a NATURAL JOIN  
table2_on_machine_b
WHERE restrict_table_1 AND restrict_table_2
AND restrict_1_based_on_2;
	I don't think that's ever going to be efficient...
	What would be efficient would be, for instance, a Join of a part of a  
table against another part of another table which both happen to be on the  
same machine, because the partitioning was done with this in mind (ie. for  
instance partitioning on client_id and keeping the information for each  
client on the same machine).

	You could build your smart pool daemon in pl/pgsql and use dblink ! At  
least you have the query parser built-in.

I wonder how Oracle does it ;)
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org


Re: [PERFORM] Partitioning / Clustering

2005-05-11 Thread PFC

However, memcached (and for us, pg_memcached) is an excellent way to  
improve
horizontal scalability by taking disposable data (like session  
information)
out of the database and putting it in protected RAM.
	So, what is the advantage of such a system versus, say, a "sticky  
sessions" system where each session is assigned to ONE application server  
(not PHP then) which keeps it in RAM as native objects instead of  
serializing and deserializing it on each request ?
	I'd say the sticky sessions should perform a lot better, and if one  
machine dies, only the sessions on this one are lost.
	But of course you can't do it with PHP as you need an app server which  
can manage sessions. Potentially the savings are huge, though.

	On Google, their distributed system spans a huge number of PCs and it has  
redundancy, ie. individual PC failure is a normal thing and is a part of  
the system, it is handled gracefully. I read a paper on this matter, it's  
pretty impressive. The google filesystem has nothing to do with databases  
though, it's more a massive data store / streaming storage.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PERFORM] BLOB's bypassing the OS Filesystem for better Image loading speed?

2005-05-12 Thread PFC

Filesystems with many Filesystem Objects can slow down the Performance
at opening and reading Data.
	On my laptop, lighttpd takes upto 15000 hits PER SECOND on static 2-3 Kb  
files (tested with apachebench 2).
	Apache is slower, of course : 3-4000 hits per second which is not that  
bad.
	Using a dynamic script with images in the database, you should account  
for query and transmission overhead, dynamic page overhead... mmm, I'd say  
using a fast application server you could maybe get 2-300 images served  
per second from the database, and that's very optimistic. And then the  
database will crawl, it will be disintegrated by the incoming flow of  
useless requests... scalability will be awful...
	Not mentioning that browsers ask the server "has this image changed since  
the last time ?" (HEAD request) and then they don't download it if it  
doesn't. The server just stat()'s the file. statting a file on any decent  
filesystem (ie. XFS Reiser JFS etc.) should take less than 10 microseconds  
if the information is in the cache. You'll have to look in the database to  
check the date... more queries !

	If you want to control download rights on files, you can still put the  
files on the filesystem (which is the right choice IMHO) and use a dynamic  
script to serve them. Even better, you could use lighttpd's authorized  
file download feature.

	The only case I see putting files in a database as interesting is if you  
want them to be part of a transaction. In that case, why not...

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


Re: [PERFORM] Partitioning / Clustering

2005-05-12 Thread PFC

machines. Which has it's own set of issues entirely. I am not entirely  
sure that memcached actually does serialize data when it's comitted into
	I think it does, ie. it's a simple mapping of [string key] => [string  
value].

memcached either, although I could be wrong, I have not looked at the  
source. Certainly if you can ensure that a client always goes back to  
the same machine you can simplify the whole thing hugely. It's generally  
not that easy though, you need a proxy server of some description  
capable of understanding the HTTP traffic and maintaining a central
	Yes...
	You could implement it by mapping servers to the hash of the user session  
id.
	Statistically, the servers would get the same numbers of sessions on each  
of them, but you have to trust statistics...
	It does eliminate the lookup table though.

idea, I would like to hear a way of implementing them cheaply (and on  
PHP) as well. I may have to give that some thought in fact. Oh yeah, and  
load balancers software often sucks in annoying (if not always  
important) ways.
	You can use lighttpd as a load balancer, I believe it has a stick  
sessions plugin (or you could code one in, it's open source after all). It  
definitely support simple round-robin load balancing, acting as a proxy to  
any number of independent servers.


matter, it's pretty impressive. The google filesystem has nothing to do  
with databases though, it's more a massive data store / streaming  
storage.
Since when did Massive Data stores have nothing to do with DBs? Isn't  
Oracle Cluster entirely based on forming an enormous scalable disk array  
to store your DB on?
	Um, well, the Google Filesystem is (like its name implies) a filesystem  
designed to store huge files in a distributed and redundant manner. Files  
are structured as a stream of records (which are themselves big in size)  
and it's designed to support appending records to these stream files  
efficiently and without worrying about locking.

	It has no querying features however, that is why I said it was not a  
database.

	I wish I could find the whitepaper, I think the URL was on this list some  
day, maybe it's on Google's site ?

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match


Re: [PERFORM] Partitioning / Clustering

2005-05-12 Thread PFC

100 hits a second = 8,640,000 hits a day. I work on a site which does  >  
100 million dynamic pages a day. In comparison Yahoo probably does  >  
100,000,000,000 (100 billion) views a day
  if I am interpreting Alexa's charts correctly. Which is about  
1,150,000 a second.

Read the help on Alexa's site... ;)
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


Re: [PERFORM] Partitioning / Clustering

2005-05-14 Thread PFC

If you make the assertion that you are transferring equal or less
session data between your session server (lets say an RDBMS) and the
app server than you are between the app server and the client, an out
of band 100Mb network for session information is plenty of bandwidth.
	So if you count on a mean page size of 6-8 kbytes gzipped, that will  
prevent you from caching the N first results of the Big Slow Search Query  
in a native object in the user session state (say, a list of integers  
indicating which rows match), so you will have to redo the Big Slow Search  
Query everytime the user clicks on Next Page instead of grabbing a set of  
cached row id's and doing a fast SELECT WHERE id IN ...
	This is the worst case ... I'd gzip() the row id's and stuff them in the  
session, that's always better than blowing up the database with the Big  
Slow Search Query everytime someone does Next Page...

This also represents OLTP style traffic, which postgresql is pretty
good at.  You should easily be able to get over 100Tps.  100 hits per
second is an awful lot of traffic, more than any website I've managed
will ever see.
	On the latest anandtech benchmarks, 100 hits per second on a blog/forum  
software is a big bi-opteron server running dotNET, at 99% load... it's a  
lot if you count only dynamic page hits.

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match


Re: [PERFORM] where+orderby+limit not (always) using appropriate index?

2005-05-18 Thread PFC

SELECT o FROM t WHERE i = 1 ORDER BY o LIMIT 1;
use :
ORDER BY i, o
	If you have a multicol index and want to order on it, you should help the  
planner by ORDERing BY all of the columns in the index...
	It bit me a few times ;)

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org


Re: [PERFORM] Need help to decide Mysql vs Postgres

2005-05-24 Thread PFC


	It's common knowledge, it seems, that MySQL without transactions will be  
a lot faster than Postgres on Inserts. And on Updates too, that is, unless  
you have more than a few concurrent concurrent connections, at which point  
the MySQL full table lock will just kill everything. And you don't have  
transactions, of course, and if something goes wrong, bye bye data, or  
funky stuff happens, like half-commited transactions if a constraint is  
violated in an INSERT SELECT, or you get 0 January  or 31 February,  
etc.
	I heard it said that MySQL with transactions (InnoDB) is slower than  
postgres. I'd believe it... and you still get 00-00- as a date for  
free.
	But from your use case postgres doesn't sound like a problem, yours  
sounds like a few big batched COPY's which are really really fast.


	And about SELECTs, this is really from an experience I had a few months  
ago, from a e-commerce site... well, to put it nicely, MySQL's planner  
don't know shit when it comes to doing anything a bit complicated. I had  
this query to show the "also purchased" products on a page, and also a few  
other queries, best buys in this category, related products, etc...,  
nothing very complicated really, at worst they were 4-table joins... and  
with 50K products MySQL planned it horrendously and it took half a second  
! Seq scans every times... I had to split the query in two, one to get the  
product id's, another one to get the products.
	I took the sql, put it in postgres with the usual changes (typenames,  
etc...) but same indexes, same data... the query took half a millisecond.  
Well... what can I say ?


	Also when you sit in front of the psql or mysql command line, it's an  
entirely different experience. One is a pleasure to work with... the other  
one is just a pain.



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] Select performance vs. mssql

2005-05-24 Thread PFC


Pretty much.  There has been discussion about allowing index-only access  
to

"frozen" tables, i.e. archive partitions.  But it all sort of hinges on
someone implementing it and testing 


	Would be interesting as a parameter to set at index creation (ie. if you  
know this table will have a lot of reads and few writes)... like create an  
index on columns X,Y keeping data on columns X,Y and Z...

But in this case do you still need the table ?
	Or even create a table type where the table and the index are one, like  
an auto-clustered table...

I don't know if it would be used that often, though ;)


---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PERFORM] OID vs overall system performances on high load

2005-05-28 Thread PFC




The OID column is an extra few bytes on each row.  If you don't have any
use for it (and let's face it: most of us don't), then create your
tables "without OID".



	Also there are some useful hacks using the oid which don't work if it  
wraps around, thus preventing it from wrapping around by not using on  
every table could be useful in some cases...


---(end of broadcast)---
TIP 6: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PERFORM] Query plan for very large number of joins

2005-06-02 Thread PFC



I am using PostgreSQL (7.4) with a schema that was generated  
automatically (using hibernate).
The schema consists of about 650 relations. One particular query (also  
generated

automatically) consists of left joining approximately 350 tables. At this


Just out of curiosity, what application is this ?
	And what are the reasons for so many tables ...and especially such a  
query ?

Not criticizing, but curious.

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly


Re: [PERFORM] strategies for optimizing read on rather large tables

2005-06-04 Thread PFC




select advert_id from acr_cache where category_id = ? and region_id = ?
order by XXX {asc|desc} limit 20;

where XXX is one of 5 possible fields,
timestamp,
timestamp,
text,
text,
numeric


Create 5 indexes on ( category_id, region_id, a field )
where "a field" is one of your 5 fields.

Then write your query as :

select advert_id from acr_cache where category_id = ? and region_id = ?
order by category_id, region_id, XXX limit 20;

select advert_id from acr_cache where category_id = ? and region_id = ?
order by category_id desc, region_id desc, XXX desc limit 20;

This should put your query down to a millisecond. It will use the index  
for the lookup, the sort and the limit, and hence only retrieve 20 rows  
for the table. Downside is you  have 5 indexes, but that's not so bad.


If your categories and regions form a tree, you should definitely use a  
ltree datatype, which enables indexed operators like "is contained in"  
which would probably allow you to reduce the size of your cache table a  
lot.






we created index on acr_cache (category_id, region_id)
and it works rather well.
usually.
if a given "crossing" (category + region) has small amount of ads (less
then 1) - the query is good enough (up to 300 miliseconds).
but when we enter the crossings which result in 5 ads - the query
takes up to 10 seconds.
which is almost "forever".

we thought about creating indices like this:
index on acr_cache (effective_date);
where effective_dateis on of the timestamp fields.
it worked well for the crossings with lots of ads, but when we asked for
small crossing (like 1000 ads) it took > 120 seconds!
it appears that postgresql was favorizing this new advert instead of
using much better index on category_id and region_id.

actually - i'm not sure what to do next.
i am even thinkinh about createing special indices (partial) for big
crossings, but that's just weird. plus the fact that already the
acr_cache vacuum time exceeds 3 hours!.


any suggestions?
hardware is dual xeon 3 ghz, 4G ram, hardware scsi raid put into raid 1.
settings in postgresql.conf:
listen_addresses = '*'
port = 5800
max_connections = 300
superuser_reserved_connections = 50
shared_buffers = 131072
work_mem = 4096
maintenance_work_mem = 65536
fsync = false
commit_delay = 100
commit_siblings = 5
checkpoint_segments = 10
effective_cache_size = 1
random_page_cost = 1.1
log_destination = 'stderr'
redirect_stderr = true
log_directory = '/home/pgdba/logs'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = false
log_rotation_age = 1440
log_rotation_size = 502400
log_min_duration_statement = -1
log_connections = true
log_duration = true
log_line_prefix = '[%t] [%p] <[EMAIL PROTECTED]> '
log_statement = 'all'
stats_start_collector = true
stats_command_string = true
stats_block_level = true
stats_row_level = true
stats_reset_on_server_start = true
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'

actual max numer of connection is 120 plus some administrative  
connections (psql sessions).

postgresql version 8.0.2 on linux debian sarge.

best regards,

depesz





---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PERFORM] strategies for optimizing read on rather large tables

2005-06-04 Thread PFC



select advert_id from acr_cache where category_id = ? and region_id = ?
order by category_id, region_id, XXX limit 20;


	don't forget to mention all the index columns in the order by, or the  
planner won't use it.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PERFORM] SELECT DISTINCT Performance Issue

2005-06-06 Thread PFC



Previously, we have also tried to use LIMIT 1 instead of DISTINCT, but  
the performance was no better:
select PlayerID,AtDate from Player where PlayerID='0' order by  
PlayerID desc, AtDate desc LIMIT 1


	The DISTINCT query will pull out all the rows and keep only one, so the  
one with LIMIT should be faster. Can you post explain analyze of the LIMIT  
query ?


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [PERFORM] Need help to decide Mysql vs Postgres

2005-06-06 Thread PFC




	If you want something more "embedded" in your application, you could  
consider :


http://firebird.sourceforge.net/
http://hsqldb.sourceforge.net/
http://sqlite.org/

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match


Re: [PERFORM] Performance nightmare with dspam (urgent) (resolved)

2005-06-06 Thread PFC




PostgreSQL and say to use MySQL if you want reasonable performance.


	If you want MySQL performance and reliability with postgres, simply run  
it with fsync deactivated ;)
	I'd suggest a controller with battery backed up cache to get rid of the 1  
commit = 1 seek boundary.



Makes it
real fun to be a DSpam+PostgreSQL user when limits are reached, since
everyone denies responsibility.  Fortunately, PostgreSQL people are  
pretty

helpful even if they think the client software sucks. :)

Cheers,




---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [PERFORM] Need help to decide Mysql vs Postgres

2005-06-06 Thread PFC



postgres -> returns results in 2.8 seconds


	What kind of plan does it do ? seq scan on the big tables and hash join  
on the small tables ?


mysql -> takes around 16 seconds  (This is with myisam ... with   
innodb it takes 220 seconds)


I'm not surprised at all.
	Try the same Join query but with a indexed where + order by / limit on  
the big table and you should get even worse for MySQL.
	I found 3 tables in a join was the maximum the MySQL planner was able to  
cope with before blowing up just like you experienced.



I am all for postgres at this point, however just want to know why I am
getting opposite results !!! Both DBs are on the same machine


Why do you say "opposite results" ?

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match


Re: [PERFORM] Need help to decide Mysql vs Postgres

2005-06-06 Thread PFC




Please pardon my ignorance, but from whatever I had heard, mysql was
supposedly always faster than postgres  Thats why I was so surprised  
!!


	I heard a lot of this too, so much it seems common wisdom that postgres  
is slow... well maybe some old version was, but it's getting better at  
every release, and the 8.0 really delivers... I get the feeling that the  
PG team is really working and delivering improvements every few months,  
compare this to MySQL 5 which has been in beta for as long as I can  
remember.
	Also, yes, definitely mysql is faster when doing simple selects like  
SELECT * FROM table WHERE id=constant, or on updates with few users, but  
once you start digging... it can get a thousand times slower on some joins  
just because the optimizer is dumb... and then suddenly 0.2 ms for MySQL  
versus 0.3 ms for postgres on a simple query doesn't seem that attractive  
when it's 2 ms on postgres versus 2 seconds on mysql for a not so  
complicated one like pulling the first N rows from a join ordered by...
	PG is considered slower than mysql also because many people don't use  
persistent connections, and connecting postgres is a lot slower than  
connecting MySQL... But well, persistent connections are easy to use and  
mandatory for performance on any database anyway so I don't understand why  
the fuss.




I will definately post the "analyze query" thing by end of today ...

Thanks for all your helps !!
Amit


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if  
your

  joining column's datatypes do not match





---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly


Re: [PERFORM] Need help to decide Mysql vs Postgres

2005-06-07 Thread PFC


My tests included using aqua studios connection to both databases and  
.asp

page using odbc connections.


Performance also depends a lot on the driver.
	For instance, the PHP driver for MySQL is very very fast. It is also very  
dumb, as it returns everything as a string and doesn't know about quoting.
	For Python it's the reverse : the MySQL driver is slow and dumb, and the  
postgres driver (psycopg 2) is super fast, handles all quoting, and knows  
about type conversions, it will automatically convert a Python List into a  
postgres Array and do the right thing with quoting, and it works both ways  
(ie you select a TEXT[] you get a list of strings all parsed for you). It  
knows about all the postgres types (yes even numeric <=> python Decimal)  
and you can even add your own types. That's really cool, plus the  
developer is a friendly guy.


-- in psql :
test=> CREATE TABLE typetests ( id SERIAL PRIMARY KEY, iarray INTEGER[]  
NULL, narray NUMERIC[] NULL, tarray TEXT[] NULL,vnum NUMERIC NULL, vint  
INTEGER NULL, vtext TEXT NULL) WITHOUT OIDS;
NOTICE:  CREATE TABLE will create implicit sequence "typetests_id_seq" for  
serial column "typetests.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
"typetests_pkey" for table "typetests"

CREATE TABLE

--- in Python :
data = {
'myiarray' : [1,5,8,6],
'mytarray' : ['hello','world'],
'mynarray' : [Decimal("1.23"),Decimal("6.58")],
'mynum': Decimal("66.66"),
'myint': 555,
'mytext'   :u "This is an Unicode String Портал по изучению иностранных"
}
cursor.execute( """INSERT INTO typetests  
(iarray,narray,tarray,vnum,vint,vtext)
	VALUES  
(%(myiarray)s,%(mynarray)s,%(mytarray)s,%(mynum)s,%(myint)s,%(mytext)s)""",  
data );


-- in psql :
test=> SELECT * FROM typetests;
 id |  iarray   |   narray|tarray | vnum  | vint |   vtext
+---+-+---+---+--+---
 4 | {1,5,8,6} | {1.23,6.58} | {hello,world} | 66.66 |  555 | This is an  
Unicode String Портал по изучению иностранных

(1 ligne)

--- in Python :

cursor.execute( "SELECT * FROM typetests" )
for row in cursor.fetchall():
for elem in row:
print type(elem), elem

--- output :

 4
 [1, 5, 8, 6]
 [Decimal("1.23"), Decimal("6.58")]
 ['hello', 'world']
 66.66
 555
 This is an Unicode String Портал по изучению иностранных

--- in Python :

cursor = db.cursor(cursor_factory = psycopg.extras.DictCursor)
cursor.execute( "SELECT * FROM typetests" )
for row in cursor.fetchall():
for key, value in row.items():
print key, ":",  type(value), value

--- output :

iarray :  [1, 5, 8, 6]
tarray :  ['hello', 'world']
vtext :  This is an Unicode String Портал по изучению  
иностранных

id :  4
vnum :  66.66
vint :  555
narray :  [Decimal("1.23"), Decimal("6.58")]

--- Timings :

Time to execute SELECT * FROM typetests and fetch the results, including  
type conversions :


Plain query : 0.279 ms / request
Prepared query : 0.252 ms / request

(not that bad ! Pentium-M 1600 MHz laptop with local postgres).

Just doing SELECT id FROM typetests gives 0.1 ms for executing query and  
fetching the result.



Who said Postgres was slow on small queries ?












---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match


Re: [PERFORM] How does the transaction buffer work?

2005-06-16 Thread PFC



transaction, delete everything and then just dump new data in (copy
perhaps). The old data would be usable to other transactions until I
commit my insert. This would be the fastest way, but how much memory
would this use? Will this cause performance issues on a heavily loaded
server with too little memory even to begin with :)


Well.

	If you DELETE everything in your table and then COPY in new rows, it will  
be fast, old rows will still be visible until the COMMIT. I hope you  
haven't anything referencing this table with ON DELETE CASCADE on it, or  
else you might delete more stuff than you think.


Also you have to consider locking.

	You could TRUNCATE the table instead of deleting, but then anyone trying  
to SELECT from it will block until the updater transaction is finished.


If you DELETE you could also vacuum afterwards.

	You could also COPY your rows to a temporary table and use a Joined  
Update to update your table in place. This might well be the more elegant  
solution, and the only one if the updated table has foreign key references  
pointing to it.



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PERFORM] Multiple disks: RAID 5 or PG Cluster

2005-06-18 Thread PFC



I do not know what clustering would do for you.  But striping will  
provide a
high level of assurance that each of your hard drives will process  
equivalent

amounts of IO operations.


	I don't know what I'm talking about, but wouldn't mirorring be faster  
than striping for random reads like you often get on a database ? (ie. the  
reads can be dispatched to any disk) ? (or course, not for writes, but if  
you won't use fsync, random writes should be reduced no ?)




---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [PERFORM] Do Views execute underlying query everytime ??

2005-06-21 Thread PFC




However, I donot know if the query that creates the view is executed
everytime I select something from the view. Because if that is the case,
then I think my queries will again be slow. But if that is the way views
work, then what would be the point in creating them ..


	Views are more for when you have a query which keeps coming a zillion  
time in your application like :


SELECT p.*, pd.* FROM products p, products_names pd WHERE p.id=pd.id AND  
pd.language=...


You create a view like :

CREATE VIEW products_with_name AS SELECT p.*, pd.* FROM products p,  
products_names pd WHERE p.id=pd.id


And then you :

SELECT * FROM products_with_name WHERE id=... AND language=...

	It saves a lot of headache and typing over and over again the same thing,  
and you can tell your ORM library to use them, too.


	But for your application, they're useless, You should create a  
"materialized view"... which is just a table and update it from a CRON job.
	You can still use a view to fill your table, and as a way to hold your  
query, so the cron job doesn't have to issue real queries, just filling  
tables from views :


CREATE VIEW cached_stuff_view AS ...

And once in while :

BEGIN;
DROP TABLE cached_stuff;
CREATE TABLE cached_stuff AS SELECT * FROM cached_stuff_view;
CREATE INDEX ... ON cached_stuff( ... )
COMMIT;
ANALYZE cached_stuff;

Or :
BEGIN;
TRUNCATE cached_stuff;
INSERT INTO cached_stuff SELECT * FROM cached_stuff_view;
COMMIT;
ANALYZE cached_stuff;

If you update your entire table it's faster to just junk it or truncate it  
then recreate it, but maybe you'd prefer TRUNCATE which saves you from  
having to re-create of indexes... but it'll be faster if you drop the  
indexes and re-create them afterwards anyway instead of them being updated  
for each row inserted. So I'd say DROP TABLE.













---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PERFORM] Do Views execute underlying query everytime ??

2005-06-21 Thread PFC


From what you say I understand that you have a huge table like this :

( name, value, id )

And you want to make statistics on (value) according to (name,id).

***

First of all a "materialized view" doen't exist in postgres, it's just a  
word to name "a table automatically updated by triggers".

An example would be like this :

table orders (order_id, ...)
table ordered_products (order_id, product_id, quantity, ...)

If you want to optimize the slow request :
"SELECT product_id, sum(quantity) as total_quantity_ordered
FROM ordered_products GROUP BY product_id"

You would create a cache table like this :
table ordered_products_cache (product_id, quantity)

And add triggers ON UPDATE/INSERT/DELETE on table ordered_products to  
update ordered_products_cache accordingly.


Of course in this case everytime someone touches ordered_products, an  
update is issued to ordered_products_cache.


***

In your case I don't think that is the solution, because you do big  
updates. With triggers this would mean issuing one update of your  
materialized view per row in your big update. This could be slow.


In this case you might want to update the cache table in one request  
rather than doing an awful lot of updates.


So you have two solutions :

1- Junk it all and rebuild it from scratch (this can be faster than it  
seems)
2- Put the rows to be added in a temporary table, update the cache table  
considering the difference between this temporary table and your big  
table, then insert the rows in the big table.


This is the fastest solution but it requires a bit more coding (not THAT  
much though).


***

As for the structure of your cache table, you want :


Screen 1 -
Stat1  Stat2Stat3
Value  ValueValue
Value  ValueValue



Screen 2 -
Stat3  Stat1Stat5
Value  ValueValue
Value  ValueValue

You have several lines, so what is that ? is it grouped by date ? I'll  
presume it is.


So your screens basically show a subset of :

SELECT date, name, sum(value) FROM table GROUP BY name, date

This is what you should put in your summary table.
Then index it on (date,name) and build your screens with :

SELECT * FROM summary WHERE (date BETWEEN .. AND ..) AND name IN (Stat3,   
Stat1,  Stat5)


That should be pretty easy ; you get a list of (name,date,value) that you  
just have to format accordingly on your screen.

























---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [PERFORM] Querying 19million records very slowly

2005-06-21 Thread PFC


use CURRENT_TIME which is a constant instead of now() which is not  
considered constant... (I think)


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [PERFORM] Too slow querying a table of 15 million records

2005-06-28 Thread PFC




database=> explain select date_trunc('hour', time),count(*) as total from
test where p1=53 and time > now() - interval '24 hours' group by
date_trunc order by date_trunc ;


	1. Use CURRENT_TIMESTAMP (which is considered a constant by the planner)  
instead of now()
	2. Create a multicolumn index on (p1,time) or (time,p1) whichever works  
better


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [PERFORM] How to speed up delete

2005-07-07 Thread PFC
On Thu, 07 Jul 2005 13:16:30 +0200, Bendik Rognlien Johansen  
<[EMAIL PROTECTED]> wrote:



Hello,
I was wondering if there is any way to speed up deletes on this table  
(see details below)?
I am running few of these deletes (could become many more) inside a  
transaction and each one takes allmost a second to complete.

Is it because of the foreign key constraint, or is it something else?

Thanks!


	Check your references : on delete, pg needs to find which rows to  
cascade-delete, or set null, or restrict, in the tables which reference  
this one. Also if this table references another I think it will lookup it  
too. Do you have indexes for all this ?


---(end of broadcast)---
TIP 6: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PERFORM]

2005-07-07 Thread PFC



So, it seems that for my application (database in memory, 14 millions
of very small requests), Centrino (aka Pentium M) has a build-in
hardware to boost Postgres performance :-)
Any experience to confirm this fact ?


	On my Centrino, Python flies. This might be due to the very large  
processor cache. Probably it is the same for perl. With two megabytes of  
cache, sorting things that fit into the cache should be a lot faster too.  
Maybe this explains it.


Check this out :

http://www.anandtech.com/linux/showdoc.aspx?i=2308&p=5

http://www.anandtech.com/cpuchipsets/showdoc.aspx?i=2129&p=11

Bonus for Opteron lovers :

"The Dual Opteron 252's lead by 19% over the Quad Xeon 3.6 GHz 667MHz FSB"

http://www.anandtech.com/cpuchipsets/showdoc.aspx?i=2397&p=12

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PERFORM] Need suggestion high-level suggestion on how to solve a performance problem

2005-07-07 Thread PFC


Hello,
	I once upon a time worked in a company doing backup software and I  
remember these problems, we had exactly the same !
	The file tree was all into memory and everytime the user clicked on  
something it haaad to update everything. Being C++ it was very fast, but  
to backup a million files you needed a gig of RAM, which is... a problem  
let's say, when you think my linux laptop has about 400k files on it.
	So we rewrote the project entirely with the purpose of doing the million  
files thingy with the clunky Pentium 90 with 64 megabytes of RAM, and it  
worked.

What I did was this :
- use Berkeley DB
	Berkeley DB isn't a database like postgres, it's just a tree, but it's  
cool for managing trees. It's quite fast, uses key compression, etc.

It has however a few drawbacks :
	- files tend to fragment a lot over time and it can't reindex or vacuum  
like postgres. You have to dump and reload.
	- the price of the licence to be able to embed it in your product and  
sell it is expensive, and if you want crash-proof, it's insanely expensive.
	- Even though it's a tree it has no idea what a parent is so you have to  
mess with that manually. We used a clever path encoding to keep all the  
paths inside the same directory close in the tree ; and separated database  
for dirs and files because we wanted the dirs to be in the cache, whereas  
we almost never touched the files.


And...
	You can't make it if you update every node everytime the user clicks on  
something. You have to update 1 node.

In your tree you have nodes.
	Give each node a state being one of these three : include, exclude,  
inherit
	When you fetch a node you also fetch all of its parents, and you  
propagate the state to know the state of the final node.

If a node is in state 'inherit' it is like its parent, etc.
	So you have faster updates but slower selects. However, there is a bonus  
: if you check a directory as "include" and one of its subdirectory as  
"exclude", and the user adds files all over the place, the files added in  
the "included" directory will be automatically backed up and the ones in  
the 'ignored' directory will be automatically ignored, you have nothing to  
change.
	And it is not that slow because, if you think about it, suppose you have  
/var/www/mysite/blah with 20.000 files in it, in order to inherit the  
state of the parents on them you only have to fetch /var once, www once,  
etc.
	So if you propagate your inherited properties when doing a tree traversal  
it comes at no cost.


IMHO it's the only solution.

	It can be done quite easily also, using ltree types and a little stored  
procedures, you can even make a view which gives the state of each  
element, computed by inheritance.


	Here's the secret : the user will select 100.000 files by clicking on a  
directory near root, but the user will NEVER look at 100.000 files. So you  
can make looking at files 10x slower if you can make including/excluding  
directories 100.000 times faster.


	Now you'll ask me, but how do I calculate the total size of the backup  
without looking at all the files ? when I click on a directory I don't  
know what files are in it and which will inherit and which will not.


	It's simple : you precompute it when you scan the disk for changed files.  
This is the only time you should do a complete tree exploration.


	On each directory we put a matrix [M]x[N], M and N being one of the three  
above state, containing the amount of stuff in the directory which would  
be in state M if the directory was in state N. This is very easy to  
compute when you scan for new files. Then when a directory changes state,  
you have to sum a few cells of that matrix to know how much more that adds  
to the backup. And you only look up 1 record.


Is that helpful ?
















---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [PERFORM] Need suggestion high-level suggestion on how to solve a performance problem

2005-07-07 Thread PFC



   This is the kicker right there; my program is released under the GPL  
so it's fee-free. I can't eat anything costly like that. As it is there  
is hundreds and hundreds of hours in this program that I am already  
hoping to recoup one day through support contracts. Adding commercial  
software I am afraid is not an option.


If you open-source GPL then Berkeley is free for you.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] slow joining very large table to smaller ones

2005-07-15 Thread PFC



Ok, I tried this one.  My ssh keeps getting cut off by a router
somewhere between me and the server due to inactivity timeouts, so
all I know is that both the select and explain analyze are taking
over an hour to run.  Here's the explain select for that one, since
that's the best I can get.


one word : screen !
one of the most useful little command line utilities...

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread PFC


	What happens if, say at iteration 6000 (a bit after the mess starts), you  
pause it for a few minutes and resume. Will it restart with a plateau like  
at the beginning of the test ? or not ?
	What if, during this pause, you disconnect and reconnect, or restart the  
postmaster, or vacuum, or analyze ?




On 7/18/05, Tom Lane <[EMAIL PROTECTED]> wrote:

> The table has 15 columns, 5 indexes (character, inet and timestamp).
> No foreign keys. The only other thing running on the machine was the
> application actually DOING the benchmarking, written in Python
> (psycopg), but it was, according to top, using less than 1% of the
> CPU.  It was just talking through a pipe to a psql prompt to do the
> COPY.

Sounds pretty plain-vanilla all right.

Are you in a position to try the same benchmark against CVS tip?
(The nightly snapshot tarball would be plenty close enough.)  I'm
just wondering if the old bgwriter behavior of locking down the
bufmgr while it examined the ARC/2Q data structures is causing this...


Tom,

It looks like the CVS HEAD is definately "better," but not by a huge
amount.  The only difference is I wasn't run autovacuum in the
background (default settings), but I don't think this explains it.
Here's a graph of the differences and density of behavior:

http://blog.amber.org/diagrams/pgsql_copy_803_cvs.png

I can provide the raw data.  Each COPY was 500 rows.  Note that fsync
is turned off here.  Maybe it'd be more stable with it turned on?

Chris




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread PFC




total.  If the insertion pattern is sufficiently random that the entire
index ranges are "hot" then you might not have enough RAM.


	Try doing the test dropping some of your indexes and see if it moves the  
number of iterations after which it becomes slow.


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread PFC




I think PFC's question was not directed towards modeling your
application, but about helping us understand what is going wrong
(so we can fix  it).


	Exactly, I was wondering if this delay would allow things to get flushed,  
for instance, which would give information about the problem (if giving it  
a few minutes of rest resumed normal operation, it would mean that some  
buffer somewhere is getting filled faster than it can be flushed).


	So, go ahead with a few minutes even if it's unrealistic, that is not the  
point, you have to tweak it in various possible manners to understand the  
causes.


	And instead of a pause, why not just set the duration of your test to  
6000 iterations and run it two times without dropping the test table ?


	I'm going into wild guesses, but first you should want to know if the  
problem is because the table is big, or if it's something else. So you run  
the complete test, stopping a bit after it starts to make a mess, then  
instead of dumping the table and restarting the test anew, you leave it as  
it is, do something, then run a new test, but on this table which already  
has data.


'something' could be one of those :
	disconnect, reconnect (well you'll have to do that if you run the test  
twice anyway)

just wait
restart postgres
unmount and remount the volume with the logs/data on it
reboot the machine
analyze
vacuum
vacuum analyze
cluster
vacuum full
reindex
	defrag your files on disk (stopping postgres and copying the database  
from your disk to anotherone and back will do)

or even dump'n'reload the whole database

	I think useful information can be extracted that way. If one of these  
fixes your problem it'l give hints.


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] Looking for tips

2005-07-19 Thread PFC



What programming language are these scripts written in ?

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PERFORM] Impact of checkpoint_segments under continual load conditions

2005-07-19 Thread PFC



It is a subprocess of a Python process, driven using a pexpect
interchange. I send the COPY command, then wait for the '=#' to come
back.


did you try sending the COPY as a normal query through psycopg ?

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [PERFORM] Looking for tips

2005-07-19 Thread PFC


	I can't say wether MySQL is faster for very small queries (like  
SELECT'ing one row based on an indexed field).

That's why I was asking you about the language...

I assume you're using a persistent connection.
	For simple queries like this, PG 8.x seemed to be a lot faster than PG  
7.x. Have you tried 8 ?
	I was asking you which language, because for such really small queries  
you have to take into account the library overhead. For instance, in PHP a  
simple query can be 10 times slower in Postgres than in MySQL and I  
believe it is because php's MySQL driver has seen a lot of optimization  
whereas the postgres driver has not. Interestingly, the situation is  
reversed with Python : its best postgres driver (psycopg 2) is a lot  
faster than the MySQL adapter, and faster than both php adapters (a lot  
faster).


The same query can get (this is from the back of my head):

PHP+Postgres3-5 ms
Python+MySQL1ms
PHP+MySQL   0.5 ms
Python+Postgres 0.15 ms

And yes, I had queries executing in 150 microseconds or so, this includes  
time to convert the results to native python objects ! This was on a loop  
of 1 times the same query. But psycopg2 is fast. The overhead for  
parsing a simple query and fetching just a row is really small.


This is on my Centrino 1.6G laptop.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [PERFORM] Looking for tips

2005-07-19 Thread PFC



PS: Where can I find benchmarks comparing PHP vs Perl vs Python in
terms of speed of executing prepared statements?


I'm afraid you'll have to do these yourself !

	And, I don't think the Python drivers support real prepared statements  
(the speed of psycopy is really good though).
	I don't think PHP either ; they don't even provide a database interface  
to speak of (ie you have to build the query string by hand including  
quoting).



---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PERFORM] Looking for tips

2005-07-19 Thread PFC


	You could have a program pre-parse your log and put it in a format  
understandable by COPY, then load it in a temporary table and write a part  
of your application simply as a plpgsql function, reading from this table  
and doing queries (or a plperl function)...



So... (bear with me here.. trying to make sense of this)..
With triggers there's a way I can do the parsing I need to on a log
file and react to completed events in non-sequential order (you can
ignore that part.. it's just how we piece together different related
events) and then have perl/DBD::Pg invoke a copy command (which, from
what I can tell, has to operate on a file...) and the copy command can
feed the ID I need back to perl so I can work with it...
If that doesn't hurt my brain, then I'm at least kinda confused...
Anyway. Heading home now. I'll think about this more tonight/tomorrow.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq





---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [PERFORM] Planner doesn't look at LIMIT?

2005-07-22 Thread PFC


Which row do you want ? Do you want 'a row' at random ?
I presume you want the N latest rows ?
	In that case you should use an ORDER BY on an indexed field, the serial  
primary key will do nicely (ORDER BY id DESC) ; it's indexed so it will  
use the index and it will fly.



Any ideas how to make it work (other than rewriting the query to use
subselects, use explicit id-rows, disabling hashjoin completely)?
Or is this a bug?

   Regards,
  Dawid

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match





---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PERFORM] Cheap RAM disk?

2005-07-26 Thread PFC



I'm a little leary as it is definitely a version 1.0 product (it is
still using an FPGA as the controller, so they were obviously pushing to
get the card into production).


	Not necessarily. FPGA's have become a sensible choice now. My RME studio  
soundcard uses a big FPGA.


	The performance in the test doesn't look that good, though, but don't  
forget it was run under windows. For instance they get 77s to copy the  
Firefox source tree on their Athlon 64/raptor ; my Duron / 7200rpm ide  
drive does it in 30 seconds, but not with windows of course.


	However it doesnt' use ECC so... That's a pity, because they could have  
implemented ECC in "software" inside the chip, and have the benefits of  
error correction with normal, cheap RAM.


Well; wait and see...

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [Bizgres-general] Re: [PERFORM] faster INSERT with possible

2005-07-27 Thread PFC




I had in mind the extra tables that an application sometimes needs to
operate faster. Denormalisations, pre-joined tables, pre-calculated
results, aggregated data. These are not temporary tables, just part of
the application - multi-user tables that stay across shutdown/restart.


	You could also add caching search results for easy pagination without  
redoing always entirely on each page the Big Slow Search Query that every  
website has...


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PERFORM] Performance problems testing with Spamassassin 3.1.0

2005-07-29 Thread PFC




Also, this test goes a bit faster with sync turned off, if mysql isn't
using sync that would be why it's so much faster.  Anyone know what the
default for mysql is?


	For InnoDB I think it's like Postgres (only slower) ; for MyISAM it's no  
fsync, no transactions, no crash tolerance of any kind, and it's not a  
default value (in the sense that you could tweak it) it's just the way  
it's coded.


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PERFORM] Performance problems testing with Spamassassin 3.1.0

2005-08-01 Thread PFC




select put_tokens2(1, '{"\\246\\323\\061\\332\\277"}', 1, 1, 1);


	Try adding more backslashes until it works (seems that you need  or  
something).

Don't DBI convert the language types to postgres quoted forms on its 
own ?


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PERFORM] Performance problems testing with Spamassassin 3.1.0

2005-08-04 Thread PFC



What I really want to do is have the token array available as a record
so that I can query against it, but not have it take up the resources of
a real table.  If I could copy from an array into a record then I can
even get rid of the loop.  Anyone have any thoughts on how to do this?


	You could make a set-returning-function (about 3 lines) which RETURNs  
NEXT every element in the array ; then you can use this SRF just like a  
table and SELECT from it, join it with your other tables, etc.


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [PERFORM] partial index regarded more expensive

2005-08-10 Thread PFC


why not simply create an index on (game_end, state) ?

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PERFORM] Speedier count(*)

2005-08-11 Thread PFC




You could lock the count table to prevent the problem
where 2 competing transactions do an insert, read the
start value and add 1 to it and then write the result
- which is n+1 rather then n+2 - so you are off by one.
Think of the same when one transaction inserts 100
and the other 120. Then you could even be off by 100.


Niet.

	If your trigger does UPDATE counts_cache SET cached_count =  
cached_count+N WHERE ...

Then all locking is taken care of by Postgres.
Of course if you use 2 queries then you have locking issues.

	However the UPDATE counts_cache has a problem, ie. it locks this row FOR  
UPDATE for the whole transaction, and all transactions which want to  
update the same row must wait to see if the update commits or rollbacks,  
so if you have one count cache row for the whole table you get MySQL style  
scalability...


	To preserve scalability you could, instead of UPDATE, INSERT the delta of  
rows inserted/deleted in a table (which has no concurrencies issues) and  
compute the current count with the sum() of the deltas, then with a cron,  
consolidate the deltas and update the count_cache table so that the deltas  
table stays very small.


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [PERFORM] sustained update load of 1-2k/sec

2005-08-19 Thread PFC



While I agree that hundreds of threads seems like overkill, I think the
above advice might be going too far in the other direction.  The problem
with single-threaded operation is that any delay affects the whole
system --- eg, if you're blocked waiting for disk I/O, the CPU doesn't


You use UDP which is a connectionless protocol... then why use threads ?

I'd advise this :

	Use asynchronous network code (one thread) to do your network stuff. This  
will lower the CPU used by this code immensely.

Every minute, dump a file contianing everything to insert into the 
table.
	Use another thread to COPY it into the DB, in a temporary table if you  
wish, and then INSERT INTO ... SELECT.

This should be well adapted to your requirements.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PERFORM] Caching by Postgres

2005-08-23 Thread PFC


Josh Berkus has already mentioned this as conventional wisdom as written  
by Oracle. This may also be legacy wisdom. Oracle/Sybase/etc has been  
around for a long time; it was probably a clear performance win way back  
when. Nowadays with how far open-source OS's have advanced, I'd take it  
with a grain of salt and do my own performance analysis. I suspect the  
big vendors wouldn't change their stance even if they knew it was no  
longer true due to the support hassles.


Reinvent a filesystem... that would be suicidal.

	Now, Hans Reiser has expressed interest on the ReiserFS list in tweaking  
his Reiser4 especially for Postgres. In his own words, he wants a "Killer  
app for reiser4". Reiser4 will offser transactional semantics via a  
special reiser4 syscall, so it might be possible, with a minimum of  
changes to postgres (ie maybe just another sync mode besides fsync,  
fdatasync et al) to use this. Other interesting details were exposed on  
the reiser list, too (ie. a transactional filesystems can give ACID  
guarantees to postgres without the need for fsync()).


Very interesting.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PERFORM] Read/Write block sizes

2005-08-24 Thread PFC



of effort reinventing the wheel ... but our time will be repaid much
more if we work at levels that the OS cannot have knowledge of, such as
join planning and data statistics.


Considering a global budget of man-hours which is the best ?

1- Spend it on reimplementing half of VFS in postgres, half of Windows in  
postgres, half of FreeBSD in postgres, half of Solaris in Postgres, only  
to discover you gain a meagre speed increase and a million and a half bugs,


2- Spending 5% of that time lowering the impedance between the OS and  
Postgres, and another 5% annoying Kernel people and helping them tweaking  
stuff for database use, and the rest on useful features that give useful  
speedups, like bitmap indexes, skip scans, and other features that enhance  
power and usability ?


If you're Oracle and have almost unlimited resources, maybe. But even  
Microsoft opted for option 2 : they implemented ReadFileGather and  
WriteFileScatter to lower the syscall overhead and that's it.


And point 2 will benefit to many other apps, wether 1 would benefit only  
postgres, and then only in certain cases.


I do believe there is something ineresting to uncover with reiser4 though  
(it definitely fits point 2).


I'm happy that the pg team chose point 2 and that new versions keep coming  
with new features at an unbelievable rate these times. Do you guys sleep ?


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [PERFORM] Caching by Postgres

2005-08-24 Thread PFC


Really?  Cool, I'd like to see that.   Could you follow up with Hans?   
Or give

me his e-mail?


You can subscribe to the Reiser mailinglist on namesys.com or :
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] Caching by Postgres

2005-08-24 Thread PFC



At least on Sparc processors, v8 and newer, any double precision math  
(including longs) is performed with a single instruction, just like for  
a 32 bit datum.  Loads and stores of 8 byte datums are also handled via  
a single instruction.   The urban myth that 64bit math is  
different/better on a 64 bit processor is just that;  yes, some lower  
end processors would emulate/trap those instructions but that an  
implementation detail, not architecture.I believe that this is all  
true for other RISC processors as well.


The 64bit API on UltraSparcs does bring along some extra FP registers  
IIRC.


It's very different on x86.
	64-bit x86 like the Opteron has more registers, which are very scarce on  
the base x86 (8 I think). This alone is very important. There are other  
factors as well.


Solaris, at least, provided support for far more than 4GB of physical  
memory on 32 bit kernels.  A newer 64 bit kernel might be more  
efficient, but that's just because the time was taken to support large  
page sizes and more efficient data structures.  It's nothing intrinsic  
to a 32 vs 64 bit kernel.


	Well, on a large working set, a processor which can directly address more  
than 4GB of memory will be a lot faster than one which can't, and has to  
play with the MMU and paging units !


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PERFORM] Advise about how to delete entries

2005-09-02 Thread PFC



"DELETE FROM statistics_sasme WHERE statistic_id = 9832;"


	As Michael said, why use a NUMERIC when a bigint is faster and better for  
your use case, as you only need an integer and not a fixed precision  
decimal ?


	Also if you use postgres < 8, the index will not be used if you search on  
a type different from the column type. So, if your key is a bigint, you  
should do WHERE  statistic_id = 9832::bigint.


	For mass deletes like this, you should use one of the following, which  
will be faster :


DELETE FROM ... WHERE ID IN (list of values)
	Don't put the 3 values in the same query, but rather do 300 queries  
with 100 values in each.


	COPY FROM a file with all the ID's to delete, into a temporary table, and  
do a joined delete to your main table (thus, only one query).


EXPLAIN DELETE is your friend.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] Performance considerations for very heavy INSERT traffic

2005-09-12 Thread PFC




I know I haven't provided a whole lot of application-level detail here,


You did !

What about :

- using COPY instead of INSERT ?
(should be easy to do from the aggregators)

- using Bizgres ?   
(which was designed for your use case)

- splitting the xlog and the data on distinct physical drives or arrays

- benchmarking something else than ext3
(xfs ? reiser3 ?)

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


[PERFORM] Advice on RAID card

2005-09-24 Thread PFC


Hello fellow Postgresql'ers.

	I've been stumbled on this RAID card which looks nice. It is a PCI-X SATA  
Raid card with 6 channels, and does RAID  0,1,5,10,50.

It is a HP card with an Adaptec chip on it, and 64 MB cache.

HP Part # : 372953-B21
Adaptec Part # : AAR-2610SA/64MB/HP

There' even a picture :
http://megbytes.free.fr/Sata/DSC05970.JPG

	I know it isn't as good as a full SCSI system. I just want to know if  
some of you have had experiences with these, and if this cards belong to  
the "slower than no RAID" camp, like some DELL card we often see mentioned  
here, or to the "decent performance for the price" camp. It is to run on a  
Linux.


Thanks in advance for your time and information.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PERFORM] Advice on RAID card

2005-09-24 Thread PFC



It looks like a rebranded low end Adaptec 64MB PCI-X <-> SATA RAID card.
Looks like the 64MB buffer is not upgradable.
Looks like it's SATA, not SATA II


	Yeah, that's exactly what it is. I can get one for 150 Euro, the Areca is  
at least 600. This is for a budget server so while it would be nice to  
have all the high-tech stuff, it's not the point. My question was raher,  
is it one of the crap RAID5 cards which are actually SLOWER than plain IDE  
disks, or is it decent, even though low-end (and cheap), and worth it  
compared to software RAID5 ?



Assuming you are not building 1U boxes, get one of the full height
cards and order it with the maximum size buffer you can afford.
The cards take 1 SODIMM, so that will be a max of 1GB or 2GB
depending on whether 2GB SODIMMs are available to you yet.


	It's for a budget dev server which should have RAID5 for reliability, but  
not necessarily stellar performance (and price). I asked about this card  
because I can get one at a good price.


Thanks for taking the time to answer.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] Advice on RAID card

2005-09-25 Thread PFC



The common explanation is that CPUs are so fast now that it doesn't make  
a difference.
 From my experience software raid works very, very well. However I have  
never put

software raid on anything that is very heavily loaded.


Even for RAID5 ? it uses a bit more CPU for the parity calculations.
	An advantage of software raid, is that if the RAID card dies, you have to  
buy the same one ; whether I think that you can transfer a bunch of  
software RAID5 disks to another machine if the machine they're in dies...


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [PERFORM] Advice on RAID card

2005-09-25 Thread PFC


There is a huge advantage to software raid on all kinds of levels. If  
you have the CPU then I suggest
it. However you will never get the performance out of software raid on  
the high level (think 1 gig of cache)

that you would on a software raid setup.

It is a bit of a tradeoff but for most installations software raid is  
more than adequate.


	Which makes me think that I will use Software Raid 5 and convert the  
price of the card into RAM.

This should be nice for a budget server.
	Gonna investigate now if Linux software RAID5 is rugged enough. Can  
always buy the a card later if not.


Thanks all for the advice, you were really helpful.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [PERFORM] Comparative performance

2005-09-29 Thread PFC


It appears that PostgreSQL is two to three times slower than MySQL.  For  
example, some pages that have some 30,000 characters (when saved as  
HTML) take 1 to 1 1/2 seconds with MySQL but 3 to 4 seconds with  
PostgreSQL.  I had read that the former was generally faster than the  
latter, particularly for simple web applications but I was hoping that  
Postgres' performance would not be that noticeably slower.


	From my experience, the postgres libraries in PHP are a piece of crap,  
and add a lot of overhead even from small queries.
	For instance, a simple query like "SELECT * FROM table WHERE  
primary_key_id=1234" can take the following time, on my laptop, with data  
in the filesystem cache of course :


EXPLAIN ANALYZE <0.1 ms
python + psycopg 2  0.1 ms (damn fast)
php + mysql 0.3 ms
php + postgres  1-2 ms (damn slow)

	So, if your pages are designed in The PHP Way (ie. a large number of  
small queries), I might suggest using a language with a decent postgres  
interface (python, among others), or rewriting your bunches of small  
queries as Stored Procedures or Joins, which will provide large speedups.  
Doing >50 queries on a page is always a bad idea, but it's tolerable in  
php-mysql, not in php-postgres.


	If it's only one large query, there is a problem, as postgres is usually  
a lot smarter about query optimization.


	If you use the usual mysql techniques (like, storing a page counter in a  
row in a table, or storing sessions in a table) beware, these are no-nos  
for postgres, these things should NOT be done with a database anyway, try  
memcached for instance.


---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PERFORM] Comparative performance

2005-09-29 Thread PFC



I just tried using pg_pconnect() and I didn't notice any significant  
improvement.  What bothers me most is that with Postgres I tend to see  
jerky behavior on almost every page:  the upper 1/2 or 2/3 of the page  
is displayed first and you can see a blank bottom (or you can see a  
half-filled completion bar).  With MySQL each page is generally  
displayed in one swoop.


	Persistent connections are useful when your page is fast and the  
connection time is an important part of your page time. It is mandatory if  
you want to serve more than 20-50 hits/s without causing unnecessary load  
on the database. This is not your case, which is why you don't notice any  
improvement...


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [HACKERS] [PERFORM] A Better External Sort?

2005-09-29 Thread PFC


Just to add a little anarchy in your nice debate...

Who really needs all the results of a sort on your terabyte table ?

	I guess not many people do a SELECT from such a table and want all the  
results. So, this leaves :

- Really wanting all the results, to fetch using a cursor,
- CLUSTER type things, where you really want everything in order,
	- Aggregates (Sort->GroupAggregate), which might really need to sort the  
whole table.
	- Complex queries where the whole dataset needs to be examined, in order  
to return a few values

- Joins (again, the whole table is probably not going to be selected)
- And the ones I forgot.

However,

Most likely you only want to SELECT N rows, in some ordering :
- the first N (ORDER BY x LIMIT N)
- last N (ORDER BY x DESC LIMIT N)
- WHERE x>value ORDER BY x LIMIT N
- WHERE x	Or, you are doing a Merge JOIN against some other table ; in that case,  
yes, you might need the whole sorted terabyte table, but most likely there  
are WHERE clauses in the query that restrict the set, and thus, maybe we  
can get some conditions or limit values on the column to sort.


	Also the new, optimized hash join, which is more memory efficient, might  
cover this case.


	Point is, sometimes, you only need part of the results of your sort. And  
the bigger the sort, the most likely it becomes that you only want part of  
the results. So, while we're in the fun hand-waving, new algorithm trying  
mode, why not consider this right from the start ? (I know I'm totally in  
hand-waving mode right now, so slap me if needed).


I'd say your new, fancy sort algorithm needs a few more input values :

- Range of values that must appear in the final result of the sort :
		none, minimum, maximum, both, or even a set of values from the other  
side of the join, hashed, or sorted.

- LIMIT information (first N, last N, none)
	- Enhanced Limit information (first/last N values of the second column to  
sort, for each value of the first column) (the infamous "top10 by  
category" query)

- etc.

	With this, the amount of data that needs to be kept in memory is  
dramatically reduced, from the whole table (even using your compressed  
keys, that's big) to something more manageable which will be closer to the  
size of the final result set which will be returned to the client, and  
avoid a lot of effort.


	So, this would not be useful in all cases, but when it applies, it would  
be really useful.


Regards !


















---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PERFORM] Comparative performance

2005-09-29 Thread PFC



  Total runtime: 16.000 ms


	Even though this query isn't that optimized, it's still only 16  
milliseconds.

Why does it take this long for PHP to get the results ?

	Can you try pg_query'ing this exact same query, FROM PHP, and timing it  
with getmicrotime() ?


	You can even do an EXPLAIN ANALYZE from pg_query and display the results  
in your webpage, to check how long the query takes on the server.


You can also try it on a Linux box.

This smells like a TCP communication problem.

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] [PERFORM] A Better External Sort?

2005-09-30 Thread PFC



Bulk loading speed is irrelevant here - that is dominated by parsing,  
which

we have covered copiously (har har) previously and have sped up by 500%,
which still makes Postgres < 1/2 the loading speed of MySQL.


Let's ask MySQL 4.0


LOAD DATA INFILE blah

0 errors, 666 warnings

SHOW WARNINGS;

not implemented. upgrade to 4.1

duh

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [PERFORM] Comparative performance

2005-10-04 Thread PFC


It's more understandable if the table names are in front of the column  
names :


SELECT relationship.topic_id1, relationship.topic_id2, topic.topic_name,  
topic.categ_id, topic.list_name, topic.title,
topic.url, topic.page_type, relationship.rel_type, entry_type.inverse_id,  
relationship.description AS rel_descrip,

relationship.created, relationship.updated
FROM relationship, topic, entry_type
WHERE
	((relationship.topic_id1 = topic.topic_id AND relationship.topic_id2 =  
1252)
	OR (relationship.topic_id2 =  topic.topic_id and relationship.topic_id1 =  
1252))

AND relationship.rel_type = entry_type.type_id
AND entry_type.class_id = 2
ORDER BY rel_type, list_name;

I see a few problems in your schema.
- topic_id1 and topic_id2 play the same role, there is no constraint to  
determine which is which, hence it is possible to define the same relation  
twice.

- as you search on two columns with OR, you need UNION to use indexes.
- lack of indexes
- I don't understand why the planner doesn't pick up hash joins...
- if you use a version before 8, type mismatch will prevent use of the  
indexes.


I'd suggest rewriting the query like this :
SELECT topic.*, foo.* FROM
topic,
(SELECT topic_id2 as fetch_id, topic_id1, topic_id2, rel_type, description  
as rel_descrip, created, updated

FROM relationship
WHERE
rel_type IN (SELECT type_id FROM entry_type WHERE class_id = 2)
AND topic_id1 = 1252
UNION
SELECT topic_id1 as fetch_id, topic_id1, topic_id2, rel_type, description  
as rel_descrip, created, updated

FROM relationship
WHERE
rel_type IN (SELECT type_id FROM entry_type WHERE class_id = 2)
AND topic_id2 = 1252)
AS foo
WHERE topic.topic_id = foo.fetch_id


CREATE INDEX'es ON
entry_type( class_id )

relationship( topic_id1, rel_type, topic_id2 )	which becomes your new  
PRIMARY KEY

relationship( topic_id2, rel_type, topic_id1 )

Of course, this doesn't explain how MySQL manages to execute the query  
in about 9 msec.  The only minor differences in the schema are:   
entry_type.title and rel_title are char(32) in MySQL,  
entry_type.class_id is a tinyint, and topic.categ_id, page_type and  
dark_ind are also tinyints. MySQL also doesn't have the REFERENCES.


Can you post the result from MySQL EXPLAIN ?

You might be interested in the following code. Just replace mysql_ by pg_,  
it's quite useful.


$global_queries_log = array();

function _getmicrotime()	{	list($u,$s) = explode(' ',microtime());	return  
$u+$s;	}


/*  Formats query, with given arguments, escaping all strings as needed.
db_quote_query( 'UPDATE junk SET a=%s WHERE b=%s', array( 1,"po'po" ) )
=>   UPDATE junk SET a='1 WHERE b='po\'po'
*/
function db_quote_query( $sql, $params=false )
{
// if no params, send query raw
if( !$params )
return $sql;

// quote params
foreach( $params as $key => $val )
{
if( is_array( $val ))
$val = implode( ',', $val );
$params[$key] = "'".mysql_real_escape_string( $val )."'";
}
return vsprintf( $sql, $params );
}

/*  Formats query, with given arguments, escaping all strings as needed.
Runs query, logging its execution time.
Returns the query, or dies with error.
*/
function db_query( $sql, $params=false )
{
// it's already a query
if( is_resource( $sql ))
return $sql;

$sql = db_quote_query( $sql, $params );

$t = _getmicrotime();
$r = mysql_query( $sql );
if( !$r )
{
		echo "Erreur MySQL  
:".mysql_error()."Requ􏻪te  
:".$sql."Traceback :";

foreach( debug_backtrace() as $t ) xdump( $t );
echo "";
die();
}
global $global_queries_log;
$global_queries_log[] = array( _getmicrotime()-$t, $sql );
return $r;
}

At the end of your page, display the contents of $global_queries_log.




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PERFORM] browsing table with 2 million records

2005-10-26 Thread PFC



I am running Postgre 7.4 on FreeBSD. The main table have 2 million record
(we would like to do at least 10 mil or more). It is mainly a FIFO  
structure

with maybe 200,000 new records coming in each day that displace the older
records.


I'm so sorry, but I have to rant XDDD

	People who present a list of 100 items, paginated with 10 items per page  
so that it fits on half a 800x600 screen should be shot.

I can scroll with my mousewheel and use text search in my browser...

	People who present a paginated view with 100.000 pages where you have to  
apply bisection search by hand to find records starting with "F" are on  
page 38651 should be forced to use a keyboard with just 1 key and type in  
morse code.


	Problem of pagination is that the page number is meaningless and rather  
useless to the user. It is also meaningless to the database, which means  
you have to use slow kludges like count() and limit/offset. And as people  
insert stuff in the table while you browse, when you hit next page you  
will see on top, half of what was on the previous page, because it was  
pushed down by new records. Or you might miss records.


	So, rather than using a meaningless "record offset" as a page number, you  
can use something meaningful, like a date, first letter of a name, region,  
etc.


	Of course, MySQL, always eager to encourage sucky-sucky practices,  
provides a neat CALC_FOUND_ROWS hack, which, while not being super SQL  
standard compliant, allows you to retrieve the number of rows the query  
would have returned if you wouldn't have used limit, so you can compute  
the number of pages and grab one page with only one query.


	So people use paginators instead of intelligent solutions, like  
xmlhttp+javascript enabled autocompletion in forms, etc. And you have to  
scroll to page 38651 to find letter "F".


So if you need to paginate on your site :

CHEAT 

Who needs a paginated view with 100.000 pages ?

- Select min(date) and max(date) from your table
	- Present a nifty date selector to choose the records from any day, hour,  
minute, second

- show them, with "next day" and "previous day" buttons

	- It's more useful to the user (most likely he wants to know what  
happened on 01/05/2005 rather than view page 2857)
	- It's faster (no more limit/offset ! just "date BETWEEN a AND b",  
indexed of course)

- no more new items pushing old ones to the next page while you browse
- you can pretend to your boss it's just like a paginated list














---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [PERFORM] browsing table with 2 million records

2005-10-27 Thread PFC




I've done it...
First of all I totally agree with PFC's rant regarding absolute
positioning while browsing datasets.  Among other things, it has serious
problems if you have multiple updating your table.  Also it's kind of
silly to be doing this in a set based data paradigm.


	Recently I've been browsing some site and it had this problem : as users  
kept adding new entries as I was browsing the list page after page, when I  
hit "next page" I got on the next page half of what I already saw on the  
previous page. Of course the webmaster has set the "visited links" color  
the same as "unvisited links", so I couldn't tell, and had to use my  
brain, which was quite upsetting XDDD


	And bookmarking a page to resume browsing at some later time does not  
work either, because if I bookmark page 15, then when I come back, users  
have added 10 pages of content and what I bookmarked is now on page 25...



All very well and good, but now do it generically...


Hehe. I like ranting...
	It is not possible to do it in a generic way that works in all cases. For  
instance :


Forum topic case :
- posts are added at the bottom and not at the top
- page number is relevant and meaningful

However, in most cases, you can use a multipart key and get it right.
	Suppose that, for instance, you have a base of several million records,  
organized according to :


- date (like the original poster)
or :
- country, region, city, customer last name, first name.

	You could ask for the first three, but then you'll get 5 Smiths in  
New York and 1 Van Bliezinsky.


	Or you could precalculate, once a week, a key interval distribution that  
creates reasonable sized intervals (for instance, 100 values in each),  
maybe asking that each interval should only contain only one city. So, you  
would get :


Country Region City LastNameFirstName
USA NYC NY  Smith,  ''
USA NYC NY  Smith,  Albert
USA NYC NY  Smith,  Bernard
.
USA NYC NY  Smith,  William
...
USA NYC NY  Von Braun
...

	So you'd predetermine your "page breaks" ahead of time, and recompute  
them once in a while. You won't get identically sized pages, but if the  
statistical distribution of the data plays nice, you should get evenly  
sized pages.


	The interesting part is that you can present the user with a selector  
which presents meaningful and useful data, AND is fast to compute, AND is  
fast to use.
	In this case, it would amount to "Select country, region, city", then,  
display a list like this :

Smith, ...Albert
Smith, Albus...Bernard
...
Smith, William...
...
Von Braun...Von Schwarts
...

	So Jeannette Smith would be easy to find, being in the link "Smith,  
Jean...John" for instance.


	If the aim is to quickly locate a particular record, I like  
javascript-powered autocompletion better ; but for browsing, this  
pagination method is cool.


Regards !


























---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [PERFORM] Auto-tuning a VIEW?

2005-12-14 Thread PFC



create view v1 (code,num) as
select 'AAA',id from table1
union
select 'BBB',id from table2;


	As your rows are, by definition, distinct between each subquery, you  
should use UNION ALL instead of UNION to save postgres the trouble of  
hunting non-existing duplicates. This will save you a few sorts.



select * from v1 where code||num = 'AAA200501';


Why don't you use code='AAA' and num='200501' ?

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PERFORM] Auto-tuning a VIEW?

2005-12-14 Thread PFC
Thanks, now the SELECT from the huge VIEW runs under one third of the  
original runtime.


Nice.


select * from v1 where code||num = 'AAA200501';


	I do not know if it is at all possible, but maybe you could use a rule  
to, on select to your view, do instead a select on the two separate  
columns used in the key, with a bit of massaging on the values using  
substring()...


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


[PERFORM] Postgres Benchmark Results

2007-05-20 Thread PFC


I felt the world needed a new benchmark ;)
	So : Forum style benchmark with simulation of many users posting and  
viewing forums and topics on a PHP website.


http://home.peufeu.com/ftsbench/forum1.png

	One of those curves is "a very popular open-source database which claims  
to offer unparallelled speed".
	The other one is of course Postgres 8.2.3 which by popular belief is  
"full-featured but slow"


What is your guess ?

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PERFORM] Postgres Benchmark Results

2007-05-20 Thread PFC


I assume red is PostgreSQL and green is MySQL. That reflects my own  
benchmarks with those two.


Well, since you answered first, and right, you win XD

The little curve that dives into the ground is MySQL with InnoDB.
The Energizer bunny that keeps going is Postgres.

But I don't fully understand what the graph displays. Does it reflect  
the ability of the underlying database to support a certain amount of  
users per second given a certain database size? Or is the growth of the  
database part of the benchmark?


	Basically I have a test client which simulates a certain number of  
concurrent users browsing a forum, and posting (posting rate is  
artificially high in order to fill the tables quicker than the months it  
would take in real life).


	Since the fake users pick which topics to view and post in by browsing  
the pages, like people would do, it tends to pick the topics in the first  
few pages of the forum, those with the most recent posts. So, like in real  
life, some topics fall through the first pages, and go down to rot at the  
bottom, while others grow much more.


	So, as the database grows (X axis) ; the total number of webpages served  
per second (viewings + postings) is on the Y axis, representing the user's  
experience (fast / slow / dead server)


	The number of concurrent HTTP or Postgres connections is not plotted, it  
doesn't really matter anyway for benchmarking purposes, you need to have  
enough to keep the server busy, but not too much or you're just wasting  
RAM. For a LAN that's about 30 HTTP connections and about 8 PHP processes  
with each a database connection.
	Since I use lighttpd, I don't really care about the number of actual slow  
clients (ie. real concurrent HTTP connections). Everything is funneled  
through those 8 PHP processes, so postgres never sees huge concurrency.

About 2/3 of the CPU is used by PHP anyway, only 1/3 by Postgres ;)

Btw, did you consider that older topics are normally read much less and  
almost never get new postings? I think the size of the "active data set"  
is more dependent on the amount of active members than on the actual  
amount of data available.


Yes, see above.
	The posts table is clustered on (topic_id, post_id) and this is key to  
performance.


That can reduce the impact of the size of the database greatly, although  
we saw very nice gains in performance on our forum (over 22GB of  
messages) when replacing the databaseserver with one with twice the  
memory, cpu's and I/O.


Well, you can see on the curve when it hits IO-bound behaviour.

	I'm writing a full report, but I'm having a lot of problems with MySQL,  
I'd like to give it a fair chance, but it shows real obstination in NOT  
working.



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [PERFORM] Postgres Benchmark Results

2007-05-20 Thread PFC

On Sun, 20 May 2007 19:26:38 +0200, Tom Lane <[EMAIL PROTECTED]> wrote:


PFC <[EMAIL PROTECTED]> writes:

The little curve that dives into the ground is MySQL with InnoDB.
The Energizer bunny that keeps going is Postgres.


Just for comparison's sake it would be interesting to see a curve for
mysql/myisam.  Mysql's claim to speed is mostly based on measurements
taken with myisam tables, but I think that doesn't hold up very well
under concurrent load.

regards, tom lane



I'm doing that now. Here is what I wrote in the report :

	Using prepared statements (important), Postgres beats MyISAM on "simple  
selects" as they say, as well as complex selects, even with 1 thread.


	MyISAM caused massive data corruption : posts and topics disappear,  
storage engine errors pop off, random thrashed rows appear in the forums  
table, therefore screwing up everything, etc. In short : it doesn't work.  
But, since noone in their right mind would use MyISAM for critical data, I  
include this result anyway, as a curiosity.


	I had to write a repair SQL script to fix the corruption in order to see  
how MySQL will fare when it gets bigger than RAM...



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [PERFORM] 500 requests per second

2007-05-21 Thread PFC


I'm looking for a database+hardware solution which should be  
able to handle up to 500 requests per second.


What proportion of reads and writes in those 500 tps ?
	(If you have 450 selects and 50 inserts/update transactions, your  
hardware requirements will be different than those for the reverse  
proportion)


What is the estimated size of your data and hot working set ?

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] Postgres Benchmark Results

2007-05-21 Thread PFC


Well that matches up well with my experience, better even yet, file a  
performance bug to the commercial support and you'll get an explanation  
why your schema (or your hardware, well anything but the database  
software used) is the guilty factor.


	Yeah, I filed a bug last week since REPEATABLE READ isn't repeatable : it  
works for SELECT but INSERT INTO ... SELECT switches to READ COMMITTED and  
thus does not insert the same rows that the same SELECT would have  
returned.


but you know these IT manager journals consider mysql as the relevant  
opensource database. Guess it matches better with their expection than  
PG or say MaxDB (the artist known formerly as Sap DB).


Never tried MaxDB.

	So far, my MyISAM benchmarks show that, while on the CPU limited case,  
Postgres is faster (even on small simple selects) , when the dataset grows  
larger, MyISAM keeps going much better than Postgres. That was to be  
expected since the tables are more compact, it can read indexes without  
hitting the tables, and of course it doesn't have transaction overhead.


	However, these good results are slightly mitigated by the massive data  
corruption and complete mayhem that ensues, either from "transactions"  
aborting mid-way, that can't be rolled back obviously, leaving stuff with  
broken relations, or plain simple engine bugs which replace your data with  
crap. After about 1/2 hour of hitting the tables hard, they start to  
corrupt and you get cryptic error messages. Fortunately "REPAIR TABLE"  
provides good consolation in telling you how much corrupt data it had to  
erase from your table... really reassuring !


	I believe the following current or future Postgres features will provide  
an interesting answer to MyISAM :


- The fact that it doesn't corrupt your data, duh.
- HOT
- the new non-logged tables
	- Deferred Transactions, since adding a comment to a blog post doesn't  
need the same guarantees than submitting a paid order, it makes sense that  
the application could tell postgres which transactions we care about if  
power is lost. This will massively boost performance for websites I  
believe.

- the patch that keeps tables in approximate cluster order

	By the way, about the ALTER TABLE SET PERSISTENCE ... for non-logged  
tables, will we get an ON RECOVER trigger ?
	For instance, I have counts tables that are often updated by triggers. On  
recovery, I could simply re-create the counts from the actual data. So I  
could use the extra speed of non-crash proof tables.




Andreas


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq





---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [PERFORM] Postgres Benchmark Results

2007-05-21 Thread PFC
On Mon, 21 May 2007 23:05:22 +0200, Jim C. Nasby <[EMAIL PROTECTED]>  
wrote:



On Sun, May 20, 2007 at 04:58:45PM +0200, PFC wrote:


I felt the world needed a new benchmark ;)
So : Forum style benchmark with simulation of many users posting and
viewing forums and topics on a PHP website.

http://home.peufeu.com/ftsbench/forum1.png


Any chance of publishing your benchmark code so others can do testing?
It sounds like a useful, well-thought-out benchmark (even if it is
rather specialized).


Yes, that was the intent from the start.
	It is specialized, because forums are one of the famous server killers.  
This is mostly due to bad database design, bad PHP skills, and the  
horrendous MySQL FULLTEXT.
	I'll have to clean up the code and document it for public consumption,  
though.
	However, the Python client is too slow. It saturates at about 1000 hits/s  
on a Athlon 64 3000+, so you can forget about benchmarking anything meaner  
than a Core 2 duo.



Also, I think it's important for you to track how long it takes to
respond to requests, both average and maximum. In a web application no
one's going to care if you're doing 1000TPS if it means that every time
you click on something it takes 15 seconds to get the next page back.
With network round-trip times and what-not considered I'd say you don't
want it to take any more than 200-500ms between when a request hits a
webserver and when the last bit of data has gone back to the client.


Yeah, I will do that too.


I'm guessing that there's about 600MB of memory available for disk
caching? (Well, 600MB minus whatever shared_buffers is set to).


It's about that. The machine has 1 GB of RAM.



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


[PERFORM] Feature suggestion : FAST CLUSTER

2007-05-22 Thread PFC



	Well, CLUSTER is so slow (and it doesn't cluster the toast tables  
associated with the table to be clustered).

However, when people use CLUSTER they use it to speed up their queries.
For that the table does not need to be perfectly in-order.

So, here is a new idea for CLUSTER :

- choose a chunk size (about 50% of your RAM)
- setup disk sorts for all indexes
- seq scan the table :
- take a chunk of chunk_size
- sort it (in memory)
- write it into new table file
		- while we have the data on-hand, also send the indexed columns data  
into the corresponding disk-sorts


- finish the index disk sorts and rebuild indexes

	This does not run a complete sort on the table. It would be about as fast  
as your seq scan disk throughput. Obviously, the end result is not as good  
as a real CLUSTER since the table will be made up of several ordered  
chunks and a range lookup. Therefore, a range lookup on the clustered  
columns would need at most N seeks, versus 1 for a really clustered table.  
But it only scans the table once and writes it once, even counting index  
rebuild.


	I would think that, with this approach, if people can CLUSTER a large  
table in 5 minutes instead of hours, they will use it, instead of not  
using it. Therefore, even if the resulting table is not as optimal as a  
fully clustered table, it will still be much better than the non-clustered  
case.





---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] Postgres Benchmark Results

2007-05-22 Thread PFC
Note that while the average hits/s between 100 and 500 is over 600 tps  
for
Postgres there is a consistent smattering of plot points spread all the  
way

down to 200 tps, well below the 400-500 tps that MySQL is getting.


Yes, these are due to checkpointing, mostly.
	Also, note that a real forum would not insert 100 posts/s, so it would  
not feel this effect. But in order to finish the benchmark in a correct  
amount of time, we have to push on the inserts.


Some of those are undoubtedly caused by things like checkpoints and  
vacuum
runs. Hopefully the improvements that are already in the pipeline will  
reduce

them.


	I am re-running it with other tuning, notably cost-based vacuum delay and  
less frequent checkpoints, and it is a *lot* smoother.
	These take a full night to run, so I'll post more results when I have  
usefull stuff to show.

This has proven to be a very interesting trip to benchmarkland...

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PERFORM] Key/Value reference table generation: INSERT/UPDATE performance

2007-05-22 Thread PFC

On Tue, 22 May 2007 10:23:03 +0200, valgog <[EMAIL PROTECTED]> wrote:


I found several post about INSERT/UPDATE performance in this group,
but actually it was not really what I am searching an answer for...

I have a simple reference table WORD_COUNTS that contains the count of
words that appear in a word array storage in another table.


Mmm.

If I were you, I would :

	- Create a procedure that flattens all the arrays and returns all the  
words :


PROCEDURE flatten_arrays RETURNS SETOF TEXT
FOR word_array IN SELECT word_array FROM your_table LOOP
FOR i IN 1...array_upper( word_array ) LOOP
RETURN NEXT tolower( word_array[ i ] )

So, SELECT * FROM flatten_arrays() returns all the words in all the arrays.
To get the counts quickly I'd do this :

SELECT word, count(*) FROM flatten_arrays() AS word GROUP BY word

You can then populate your counts table very easily and quickly, since  
it's just a seq scan and hash aggregate. One second for 10.000 rows would  
be slow.


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [PERFORM] Tips & Tricks for validating hardware/os

2007-05-22 Thread PFC



Out of curiosity, can anyone share his tips & tricks to validate a
machine before labelling it as 'ready to use postgres - you probably
won't trash my data today' ?
I'm looking for a way to stress test components especially kernel/disk
to have confidence > 0 that I can use postgres on top of it.


	That would be running a filesystem benchmark, pulling the plug, then  
counting the dead.


http://sr5tech.com/write_back_cache_experiments.htm

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PERFORM] max_fsm_pages, shared_buffers and checkpoint_segments

2007-05-23 Thread PFC


When I bring shared_buffers or max_fsm_pages back down, the daemon  
starts and all is

normal.


	Linux has a system setting for the maximum number of shared memory that a  
process can allocate. When Postgres wants more, Linux says "No."

Look in the docs for the setting (sysctl whatsisname).
VACUUM VERBOSE will tell you if you need to put more max_fsm_pages or 
not.


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PERFORM] LIKE search and performance

2007-05-24 Thread PFC



PG could scan the index looking for matches first and only load the
actual rows if it found a match, but that could only be a possible win
if there were very few matches, because the difference in cost between a
full index scan and a sequential scan would need to be greater than the
cost of randomly fetching all of the matching data rows from the table
to look up the visibility information.


	If you need to do that kind of thing, ie. seq scanning a table checking  
only one column among a large table of many columns, then don't use an  
index. An index, being a btree, needs to be traversed in order (or else, a  
lot of locking problems come up) which means some random accesses.


	So, you could make a table, with 2 columns, updated via triggers : your  
text field, and the primary key of your main table. Scanning that would be  
faster.


Still, a better solution for searching in text is :

- tsearch2 if you need whole words
- trigrams for any substring match
- xapian for full text search with wildcards (ie. John* = Johnny)

	Speed-wise those three will beat any seq scan on a large table by a huge  
margin.


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate


Re: [PERFORM] LIKE search and performance

2007-05-25 Thread PFC



OK - any application that allows user-built queries: foo>  


Want another? Any application that has a "search by name" box - users  
can (and do) put one letter in and hit enter.


Unfortunately you don't always have control over the selectivity of  
queries issued.


-*- HOW TO MAKE A SEARCH FORM -*-

Imagine you have to code the search on IMDB.

This is what a smart developer would do

First, he uses AJAX autocompletion, so the thing is reactive.
	Then, he does not bother the user with a many-fields form. Instead of  
forcing the user to think (users HATE that), he writes smart code.
	Does Google Maps have separate fields for country, city, street, zipcode  
? No. Because Google is about as smart as it gets.


So, you parse the user query.

	If the user types, for instance, less than 3 letters (say, spi), he  
probably wants stuff that *begins* with those letters. There is no point  
in searching for the letter "a" in a million movie titles database.
	So, if the user types "spi", you display "name LIKE spi%", which is  
indexed, very fast. And since you're smart, you use AJAX. And you display  
only the most popular results (ie. most clicked on).


http://imdb.com/find?s=all&q=spi

	Since 99% of the time the user wanted "spiderman" or "spielberg", you're  
done and he's happy. Users like being happy.
	If the user just types "a", you display the first 10 things that start  
with "a", this is useless but the user will marvel at your AJAX skillz.  
Then he will probably type in a few other letters.


	Then, if the user uses his space bar and types "spi 1980" you'll  
recognize a year and display spielberg's movies in 1980.
	Converting your strings to phonetics is also a good idea since about 0.7%  
of the l33T teenagers can spell stuff especially spiElberg.


	Only the guy who wants to know who had sex with marilyn monroe on the  
17th day of the shooting of Basic Instinct will need to use the Advanced  
search.


	If you detect several words, then switch to a prefix-based fulltext  
search like Xapian which utterly rocks.
	Example : the user types "savin priv", you search for "savin*" NEAR  
"priv*" and you display "saving private ryan" before he has even finished  
typing the second word of his query. Users love that, they feel  
understood, they will click on your ads and buy your products.


	In all cases, search results should be limited to less than 100 to be  
easy on the database. The user doesn't care about a search returning more  
than 10-20 results, he will just rephrase the query, and the time taken to  
fetch those thousands of records with name LIKE '%a%' will have been  
utterly lost. Who goes to page 2 in google results ?


BOTTOM LINE : databases don't think, you do.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [PERFORM] LIKE search and performance

2007-05-25 Thread PFC
None of which address the question of what plan PG should produce for:  
SELECT * FROM bigtable WHERE foo LIKE 's%'


Ah, this one already uses the btree since the '%' is at the end.
	My point is that a search like this will yield too many results to be  
useful to the user anyway, so optimizing its performance is a kind of red  
herring.



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] Feature suggestion : FAST CLUSTER

2007-05-27 Thread PFC
On Sun, 27 May 2007 17:53:38 +0200, Jim C. Nasby <[EMAIL PROTECTED]>  
wrote:



On Tue, May 22, 2007 at 09:29:00AM +0200, PFC wrote:

This does not run a complete sort on the table. It would be about as
	fast  as your seq scan disk throughput. Obviously, the end result is  
not as
good  as a real CLUSTER since the table will be made up of several  
ordered

chunks and a range lookup. Therefore, a range lookup on the clustered
columns would need at most N seeks, versus 1 for a really clustered  
table.

But it only scans the table once and writes it once, even counting index
rebuild.


Do you have any data that indicates such an arrangement would be
substantially better than less-clustered data?


	While the little benchmark that will answer your question is running,  
I'll add a few comments :


	I have been creating a new benchmark for PostgreSQL and MySQL, that I  
will call the Forum Benchmark. It mimics the activity of a forum.
	So far, I have got interesting results about Postgres and InnoDB and will  
publish an extensive report with lots of nasty stuff in it, in, say, 2  
weeks, since I'm doing this in spare time.


	Anyway, forums like clustered tables, specifically clusteriing posts on  
(topic_id, post_id), in order to be able to display a page with one disk  
seek, instead of one seek per post.
	PostgreSQL humiliates InnoDB on CPU-bound workloads (about 2x faster  
since I run it on dual core ; InnoDB uses only one core). However, InnoDB  
can automatically cluster tables without maintenance. This means InnoDB  
will, even though it sucks and is awfully bloated, run a lot faster than  
postgres if things become IO-bound, ie. if the dataset is larger than RAM.
	Postgres needs to cluster the posts table in order to keep going. CLUSTER  
is very slow. I tried inserting into a new posts table, ordering by  
(post_id, topic_id), then renaming the new table in place of the old. It  
is faster, but still slow when handling lots of data.
	I am trying other approaches, some quite hack-ish, and will report my  
findings.


Regards




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [PERFORM] Feature suggestion : FAST CLUSTER

2007-05-27 Thread PFC




How does it know what to cluster by? Does it gather statistics about
query patterns on which it can decide an optimal clustering, or does
it merely follow a clustering previously set up by the user?


	Nothing fancy, InnoDB ALWAYS clusters on the primary key, whatever it is.  
So, if you can hack your stuff into having a primary key that clusters  
nicely, good for you. If not, well...
	So, I used (topic_id, post_id) as the PK, even though it isn't the real  
PK (this should be post_id)...


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] Postgres Benchmark Results

2007-05-27 Thread PFC

On Mon, 28 May 2007 05:53:16 +0200, Chris <[EMAIL PROTECTED]> wrote:



I am re-running it with other tuning, notably cost-based vacuum  
delay and less frequent checkpoints, and it is a *lot* smoother.
These take a full night to run, so I'll post more results when I  
have usefull stuff to show.

This has proven to be a very interesting trip to benchmarkland...


[ rather late in my reply but I had to ]

Are you tuning mysql in a similar fashion ?


	Well, the tuning knobs are different, there are no check points or  
vacuum... but yes I tried to tune MySQL too, but the hardest part was  
simply making it work without deadlocking continuously.





---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PERFORM] Feature suggestion : FAST CLUSTER

2007-05-28 Thread PFC

On Sun, 27 May 2007 19:34:30 +0200, PFC <[EMAIL PROTECTED]> wrote:

On Sun, 27 May 2007 17:53:38 +0200, Jim C. Nasby <[EMAIL PROTECTED]>  
wrote:



On Tue, May 22, 2007 at 09:29:00AM +0200, PFC wrote:

This does not run a complete sort on the table. It would be about as
	fast  as your seq scan disk throughput. Obviously, the end result is  
not as
good  as a real CLUSTER since the table will be made up of several  
ordered

chunks and a range lookup. Therefore, a range lookup on the clustered
columns would need at most N seeks, versus 1 for a really clustered  
table.
But it only scans the table once and writes it once, even counting  
index

rebuild.


Do you have any data that indicates such an arrangement would be
substantially better than less-clustered data?


	While the little benchmark that will answer your question is running,  
I'll add a few comments :


Alright, so far :

	This is a simulated forum workload, so it's mostly post insertions, some  
edits, and some topic deletes.
	It will give results applicable to forums, obviously, but also anything  
that wotks on the same schema :

- topics + posts
- blog articles + coomments
- e-commerce site where users can enter their reviews
	So, the new trend being to let the users to participate, this kind of  
workload will become more and more relevant for websites.


	So, how to cluster the posts table on (topic_id, post_id) to get all the  
posts on the same webpake in 1 seek ?


I am benchmarking the following :
- CLUSTER obviously
	- Creating a new table and INSERT .. SELECT ORDER BY topic_id, post_id,  
then reindexing etc

- not doing anything (just vacuuming all tables)
- not even vacuuming the posts table.

I al also trying the following more exotic approaches :

* chunked sort :

	Well, sorting 1GB of data when your work_mem is only 512 MB needs several  
passes, hence a lot of disk IO. The more data, the more IO.

So, instead of doing this, I will :
- grab about 250 MB of posts from the table
- sort them by (topic_id, post_id)
- insert them in a new table
- repeat
- then reindex, etc and replace old table with new.
	(reindex is very fast, since the table is nicely defragmented now, I get  
full disk speed. However I would like being able to create 2 indexes with  
ONE table scan !)

I'm trying 2 different ways to do that, with plpgsql and cursors.
	It is much faster than sorting the whole data set, because the sorts are  
only done in memory (hence the "chunks")
	So far, it seems a database clustered this way is about as fast as using  
CLUSTER, but the clustering operation is faster.

More results in about 3 days when the benchmarks finish.

* other dumb stuff

	I'll try DELETing the last 250MB of records, stuff them in a temp table,  
vacuum, and re-insert them in order.





---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


  1   2   3   4   >