[PERFORM] MIT benchmarks pgsql multicore (up to 48)performance
Hi, for whom it may concern: http://pdos.csail.mit.edu/mosbench/ They tested with 8.3.9, i wonder what results 9.0 would give. Best regards and keep up the good work Hakan
Re: [PERFORM] in memory views
Hi, there was a similar discussion with a ramdisk: http://archives.postgresql.org/pgsql-hackers/2005-11/msg01058.php You need to populate the data on serverstart, of course. But as Timo mentionend, it's maybe not worth the trouble. Maybe their is a way to speed up the queriy itself. To analyze this, you should post the query- and table-definition and the output of explain analyze of the offending query. Best regards Hakan Kocaman Software-Development digame.de GmbH Richard-Byrd-Str. 4-8 50829 Köln Tel.: +49 (0) 221 59 68 88 31 Fax: +49 (0) 221 59 68 88 98 Email: [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Thomas Vatter Sent: Wednesday, May 10, 2006 12:43 PM To: Tino Wildenhain Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] in memory views Tino Wildenhain wrote: Thomas Vatter schrieb: Tino Wildenhain wrote: Thomas Vatter schrieb: is there a possibility for creating views or temp tables in memory to avoid disk io when user makes select operations? No need. The data will be available in OS and database caches if they are really required often. If not, tune up the caches and do a regular pre select. Regards Tino hmm, I am selecting a resultset with 1300 rows joined from 12 tables. with jdbc I am waiting 40 seconds until the first row appears. The following rows appear really fast but the 40 seconds are a problem. Well you will need the equally 40 seconds to fill your hypothetical in memory table. (even a bit more due to the creation of a datastructure). So you can do the aproaches of semi materialized views (that are in fact writing into a shadow table) or just prefetch your data at time - just at the times you would refill your memory tables if they existed. A cronjob with select/fetch should do. Regards Tino If the in memory table is created a bootup time of the dbms it is already present when user selects the data. Of course the challenge is to keep the in memory table up to date if data are changed. What do you mean with semi materialized views, I have tried select * from this_view with the same result. Also, if I repeat the query it does not run faster. regards tom -- Mit freundlichen Grüßen / Regards Vatter Network Inventory Software Sun Microsystems Principal Partner www.network-inventory.de Tel. 030-79782510 E-Mail [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Problem with LIKE-Performance
Hi,
i remember something that you need a special index with localesC.
You nned a different operator class for this index smth. like:
CREATE INDEX idx_image_title
ON image
USING btree
(title varchar_pattern_ops);
You can find the details here:
http://www.postgresql.org/docs/8.1/interactive/indexes-opclass.html
Best regards
Hakan Kocaman
Software-Development
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln
Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: [EMAIL PROTECTED]
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Tarabas (Manuel Rorarius)
Sent: Tuesday, April 18, 2006 4:35 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Problem with LIKE-Performance
Hi!
I am having trouble with like statements on one of my tables.
I already tried a vacuum and analyze but with no success.
The database is PostgreSQL Database Server 8.1.3 on i686-pc-mingw32
I get the following explain and I am troubled by the very high
startup_cost ... does anyone have any idea why that value is so
high?
{SEQSCAN
:startup_cost 1.00
:total_cost 100021432.33
:plan_rows 1
:plan_width 1311
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
:resno 1
:resname image_id
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
:resno 2
:resname customer_id
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 2
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 3
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
:resno 3
:resname theme_id
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 3
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 4
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
:resno 4
:resname gallery_id
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 4
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 5
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 5
}
:resno 5
:resname event_id
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 5
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 6
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 6
}
:resno 6
:resname width
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 6
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 7
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 7
}
:resno 7
:resname height
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 7
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 8
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 8
}
:resno 8
:resname filesize
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 8
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 9
:vartype 1114
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 9
}
:resno 9
:resname uploadtime
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 9
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 10
:vartype 1043
:vartypmod 259
:varlevelsup 0
:varnoold 1
:varoattno 10
}
:resno 10
:resname filename
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 10
:resjunk false
Re: [PERFORM] Hanging queries on dual CPU windows
Hi, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Thursday, March 09, 2006 9:11 PM To: Jan de Visser Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Hanging queries on dual CPU windows Jan de Visser [EMAIL PROTECTED] writes: Furtermore, it does not happen on Linux machines, both single CPU and dual CPU, nor on single CPU windows machines. We can only reproduce on a dual CPU windows machine, and if we take one CPU out, it does not happen. ... Which showed me that several transactions where waiting for a particular row which was locked by another transaction. This transaction had no pending locks (so no deadlock), but just does not complete and hence never relinquishes the lock. Is the stuck transaction still consuming CPU time, or just stopped? Is it possible to get a stack trace from the stuck process? I dunno if you've got anything gdb-equivalent under Windows, but that's the first thing I'd be interested in ... Debugging Tools for Windows from Microsoft http://www.microsoft.com/whdc/devtools/debugging/installx86.mspx Additinonally you need a symbol-file or you use SRV*c:\debug\symbols*http://msdl.microsoft.com/download/symbols; to load the symbol-file dynamically from the net. Best regards regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Hakan Kocaman Software-Development digame.de GmbH Richard-Byrd-Str. 4-8 50829 Köln Tel.: +49 (0) 221 59 68 88 31 Fax: +49 (0) 221 59 68 88 98 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
