Re: [PERFORM] Why so slow?
On Sat, Apr 29, 2006 at 11:18:10AM +0800, K C Lau wrote: I apologize for simplistic ideas again. I presume that the equivalent tuple header information is not maintained for index entries. What if they are, probably only for the most commonly used index types to allow recycling where possible? Alternatively, you could just run vacuum... Mike Stone ---(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] Easy question
SELECT geom, group, production_facs FROM south_america
WHERE UPPER(municipio) IN ('ACRE', 'ADJUNTAS', 'AGUADA');
<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi List
> I have maybe an easy question but i do not find an answer, i have this
> SQL query:
>
> SELECT geom,group,production_facs FROM south_america
> WHERE municipio = ''
> OR municipio = 'ACRE'
> OR municipio = 'ADJUNTAS'
> OR municipio = 'AGUADA'
>
> The performance of this query is quite worse as longer it gets, its
> possible that this query gets over 20 to 30 OR comparisons, but then
> the performance is really worse, is it possible to speed it up?
> Thanks
> Clemens
>
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[PERFORM] Slow restoration question
Hello list, what is the quickest way of dumping a DB and restoring it? I have done a "pg_dump -D database | split --line-bytes 1546m part" Restoration as "cat part* | psql database 2> errors 1>/dev/null" all dumpfiles total about 17Gb. It has been running for 50ish hrs and up to about the fourth file (5-6 ish Gb) and this is on a raid 5 server. A while back I did something similar for a table with where I put all the insert statements in one begin/end/commit block, this slowed down the restoration process. Will the same problem [slow restoration] occur if there is no BEGIN and END block? I assume the reason for slow inserts in this instance is that it allows for rollback, if this is the case can I turn this off? Thanks in advance Eric Lam ---(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] Easy question
Thanks, But the performance is the same just the formating is more simple. Greets, Bert ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Hardware: HP StorageWorks MSA 1500
I'd be interested in those numbers once you get them, especially for ext3. We just picked up an HP MSA1500cs with the MSA50 sled, and I am curious as to how best to configure it for Postgres. My server is the HP DL585 (quad, dual-core Opteron, 16GB RAM) with 4 HD bays run by a HP SmartArray 5i controller. I have 15 10K 300GB drives and 1 15K 150GB drive (don't ask how that happened). The database is going to be very datawarehouse-ish (bulk loads, lots of queries) and has the potential to grow very large (1+ TB). Plus, with that much data, actual backups won't be easy, so I'll be relying on RAID+watchfullness to keep me safe, at least through the prototype stages. How would/do you guys set up your MSA1x00 with 1 drive sled? RAID10 vs RAID5 across 10+ disks? Here's what I was thinking (ext3 across everything): Direct attached: 2x300GB RAID10 - OS + ETL staging area 2x300GB RAID10 - log + indexes MSA1500: 10x300GB RAID10 + 1x300GB hot spare - tablespace I'm not quite sure what to do with the 15K/150GB drive, since it is a singleton. I'm also planning on giving all the 256MB MSA1500 cache to reads, although I might change it for the batch loads to see if it speeds things up. Also, unfortunately, the MSA1500 only has a single SCSI bus, which could significantly impact throughput, but we got a discount, so hopefully we can get another bus module in the near future and pop it in. Any comments are appreciated, -Mike ---(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] Performance Issues on Opteron Dual Core
Hello, We are currently developing a web application and have the webserver and PostgreSQL with our dev db running on a machine with these specs: Win 2003 standard AMD Athlon XP 3000 / 2.1 GHZ 2 Gig ram 120 gig SATA HD PostgreSQL 8.1.0 Default pgsql configuration + shared buffers = 30,000 The performance of postgresql and our web application is good on that machine, but we decided to build a dedicated database server for our production database that scales better and that we can also use for internal applications (CRM and so on). To make a long story short, we built a machine with these specs: Windows 2003 Standard AMD Opteron 165 Dual Core / running at 2 GHZ 2 gig ram 2 x 150 Gig SATA II HDs in RAID 1 mode (mirror) PostgreSQL 8.1.3 Default pgsql configuration + shared buffers = 30,000 Perfomance tests in windows show that the new box outperforms our dev machine quite a bit in CPU, HD and memory performance. I did some EXPLAIN ANALYZE tests on queries and the results were very good, 3 to 4 times faster than our dev db. However one thing is really throwing me off. When I open a table with 320,000 rows / 16 fields in the pgadmin tool (v 1.4.0) it takes about 6 seconds on the dev server to display the result (all rows). During these 6 seconds the CPU usage jumps to 90%-100%. When I open the same table on the new, faster, better production box, it takes 28 seconds!?! During these 28 seconds the CPU usage jumps to 30% for 1 second, and goes back to 0% for the remaining time while it is running the query. What is going wrong here? It is my understanding that postgresql supports multi-core / cpu environments out of the box, but to me it appears that it isn't utilizing any of the 2 cpu's available. I doubt that my server is that fast that it can perform this operation in idle mode. I played around with the shared buffers and tried out versions 8.1.3, 8.1.2, 8.1.0 with the same result. Has anyone experienced this kind of behaviour before? How representative is the query performance in pgadmin? I appreciate your ideas, comments and help. Thanks, Greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Worsening performance with 7.4 on flash-based system
Well, since I got no response at all to this message, I can only assume that I've asked the question in an insufficient way, or else that no one has anything to offer on our problem. This was my first post to the list, so if there's a better way I should be asking this, or different data I should provide, hopefully someone will let me know... Thanks, Greg "Greg Stumph" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > We are experiencing gradually worsening performance in PostgreSQL 7.4.7, > on a system with the following specs: > Linux OS (Fedora Core 1, 2.4 kernal) > Flash file system (2 Gig, about 80% full) > 256 Meg RAM > 566 MHz Celeron CPU > > We use Orbit 2.9.8 to access PostGres. The database contains 62 tables. > > When the system is running with a fresh copy of the database, performance > is fine. At its worst, we are seeing fairly simple SELECT queries taking > up to 1 second to execute. When these queries are run in a loop, the loop > can take up to 30 seconds to execute, instead of the 2 seconds or so that > we would expect. > > VACUUM FULL ANALYZE helps, but doesn't seem to eliminate the problem. > > The following table show average execution time in "bad" performance mode > in the first column, execution time after VACUUM ANALYZE in the second > column, and % improvement (or degradation?) in the third. The fourth > column show the query that was executed. > > 741.831|582.038|-21.5| ^IDECLARE table_cursor > 170.065|73.032|-57.1| FETCH ALL in table_cursor > 41.953|45.513|8.5| CLOSE table_cursor > 61.504|47.374|-23.0| SELECT last_value FROM pm_id_seq > 39.651|46.454|17.2| select id from la_looprunner > 1202.170|265.316|-77.9| select id from rt_tran > 700.669|660.746|-5.7| ^IDECLARE my_tran_load_cursor > 1192.182|47.258|-96.0| FETCH ALL in my_tran_load_cursor > 181.934|89.752|-50.7| CLOSE my_tran_load_cursor > 487.285|873.474|79.3| ^IDECLARE my_get_router_cursor > 51.543|69.950|35.7| FETCH ALL in my_get_router_cursor > 48.312|74.061|53.3| CLOSE my_get_router_cursor > 814.051|1016.219|24.8| SELECT $1 = 'INSERT' > 57.452|78.863|37.3| select id from op_sched > 48.010|117.409|144.6| select short_name, long_name from la_loopapp > 54.425|58.352|7.2| select id from cd_range > 45.289|52.330|15.5| SELECT last_value FROM rt_tran_id_seq > 39.658|82.949|109.2| SELECT last_value FROM op_sched_id_seq > 42.158|68.189|61.7| select card_id,router_id from rt_valid > > > Has anyone else seen gradual performance degradation like this? Would > upgrading to Postgres 8 help? Any other thoughts on directions for > troubleshooting this? > > Thanks... > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Slow restoration question
Eric Lam <[EMAIL PROTECTED]> writes: > what is the quickest way of dumping a DB and restoring it? I have done a >"pg_dump -D database | split --line-bytes 1546m part" Don't use "-D" if you want fast restore ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Slow restoration question
Tom Lane <[EMAIL PROTECTED]> schrieb: > Eric Lam <[EMAIL PROTECTED]> writes: > > what is the quickest way of dumping a DB and restoring it? I have done a > > >"pg_dump -D database | split --line-bytes 1546m part" > > Don't use "-D" if you want fast restore ... hehe, yes ;-) http://people.planetpostgresql.org/devrim/index.php?/archives/44-d-of-pg_dump.html Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Worsening performance with 7.4 on flash-based system
Usually when simple queries take a long time to run, it's the system tables (pg_*) that have become bloated and need vacuuming. But that's just random guess on my part w/o my detailed info. Greg Stumph wrote: Well, since I got no response at all to this message, I can only assume that I've asked the question in an insufficient way, or else that no one has anything to offer on our problem. This was my first post to the list, so if there's a better way I should be asking this, or different data I should provide, hopefully someone will let me know... Thanks, Greg "Greg Stumph" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] We are experiencing gradually worsening performance in PostgreSQL 7.4.7, on a system with the following specs: Linux OS (Fedora Core 1, 2.4 kernal) Flash file system (2 Gig, about 80% full) 256 Meg RAM 566 MHz Celeron CPU We use Orbit 2.9.8 to access PostGres. The database contains 62 tables. When the system is running with a fresh copy of the database, performance is fine. At its worst, we are seeing fairly simple SELECT queries taking up to 1 second to execute. When these queries are run in a loop, the loop can take up to 30 seconds to execute, instead of the 2 seconds or so that we would expect. VACUUM FULL ANALYZE helps, but doesn't seem to eliminate the problem. The following table show average execution time in "bad" performance mode in the first column, execution time after VACUUM ANALYZE in the second column, and % improvement (or degradation?) in the third. The fourth column show the query that was executed. 741.831|582.038|-21.5| ^IDECLARE table_cursor 170.065|73.032|-57.1| FETCH ALL in table_cursor 41.953|45.513|8.5| CLOSE table_cursor 61.504|47.374|-23.0| SELECT last_value FROM pm_id_seq 39.651|46.454|17.2| select id from la_looprunner 1202.170|265.316|-77.9| select id from rt_tran 700.669|660.746|-5.7| ^IDECLARE my_tran_load_cursor 1192.182|47.258|-96.0| FETCH ALL in my_tran_load_cursor 181.934|89.752|-50.7| CLOSE my_tran_load_cursor 487.285|873.474|79.3| ^IDECLARE my_get_router_cursor 51.543|69.950|35.7| FETCH ALL in my_get_router_cursor 48.312|74.061|53.3| CLOSE my_get_router_cursor 814.051|1016.219|24.8| SELECT $1 = 'INSERT' 57.452|78.863|37.3| select id from op_sched 48.010|117.409|144.6| select short_name, long_name from la_loopapp 54.425|58.352|7.2| select id from cd_range 45.289|52.330|15.5| SELECT last_value FROM rt_tran_id_seq 39.658|82.949|109.2| SELECT last_value FROM op_sched_id_seq 42.158|68.189|61.7| select card_id,router_id from rt_valid Has anyone else seen gradual performance degradation like this? Would upgrading to Postgres 8 help? Any other thoughts on directions for troubleshooting this? Thanks... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Easy question
You have a functional index on UPPER(municipo), right? How large is the table?On 26 Apr 2006 18:26:07 -0700, Bert < [EMAIL PROTECTED]> wrote:Thanks,But the performance is the same just the formating is more simple. Greets,Bert---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Worsening performance with 7.4 on flash-based system
On 4/29/06, Greg Stumph <[EMAIL PROTECTED]> wrote: Well, since I got no response at all to this message, I can only assume that I've asked the question in an insufficient way, or else that no one has anything to offer on our problem. This was my first post to the list, so if there's a better way I should be asking this, or different data I should provide, hopefully someone will let me know... Thanks, Greg "Greg Stumph" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > We are experiencing gradually worsening performance in PostgreSQL 7.4.7, > on a system with the following specs: > Linux OS (Fedora Core 1, 2.4 kernal) > Flash file system (2 Gig, about 80% full) > 256 Meg RAM > 566 MHz Celeron CPU > > We use Orbit 2.9.8 to access PostGres. The database contains 62 tables. > > When the system is running with a fresh copy of the database, performance > is fine. At its worst, we are seeing fairly simple SELECT queries taking > up to 1 second to execute. When these queries are run in a loop, the loop > can take up to 30 seconds to execute, instead of the 2 seconds or so that > we would expect. If you're inserting/updating/deleting a table or tables heavily, then you'll need to vacuum it a lot more often than a reasonably static table. Are you running contrib/autovacuum at all? PG 8.0 and above have autovacuum built in but 7.4.x needs to run the contrib version. PS - the latest 7.4 version is .12 - see http://www.postgresql.org/docs/7.4/interactive/release.html for what has changed (won't be much in performance terms but may fix data-loss bugs). -- Postgresql & php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
