Re: [PERFORM] How to improve db performance with $7K?
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
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
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?
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
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 ... ?
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?
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
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
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
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?
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?
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?
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
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
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
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
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?
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
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
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
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?
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
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
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
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
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
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
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
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
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)
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
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
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
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?
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
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 ??
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 ??
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
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
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
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]
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
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
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
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
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
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
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
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
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
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
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
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?
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?
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
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
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
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
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
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(*)
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
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
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
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
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
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
"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
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
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
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
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
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
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
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?
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
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?
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
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()."Requte
:".$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
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
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?
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?
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
