[PERFORM] performance of PostgreSQL on 64 bit MAC OS X G5!
Hi, We have been running PostgreSQL 7.3.4 on 64 bit MAC OS X G5 dual processors with 8GB of RAM for a while. Lately, we realized that consistently only about 4GB of RAM is used even when CPUs have maxed out for postgtres processes and pageouts starts to happen. Here is a portion of the output from TOP: MemRegions: num = 3761, resident = 41.5M + 7.61M private, 376M shared PhysMem: 322M wired, 1.83G active, 1.41G inactive, 3.56G used, 4.44G free VM: 14.0G + 69.9M 277034(0) pageins, 1461(0) pageouts Is it because PostgreSQL 7.3.4 can't take advantage of the 64 bit hardware or is it something else? Thanks a lot! Qing ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] CPU maximized out!
Hi, there, I am running PostgreSQL 7.3.4 on MAC OS X G5 with dual processors and 8GB memory. The shared buffer was set as 512MB. The database has been running great until about 10 days ago when our developers decided to add some indexes to some tables to speed up certain uploading ops. Now the CPU usage reaches 100% constantly when there are a few users accessing their information by SELECT tables in databases. If I REINEX all the indexes, the database performance improves a bit but before long, it goes back to bad again. My suspicion is that since now a few indexes are added, every ops are run by PostgreSQL with the indexes being used when calculating cost. This leads to the downgrade of performance. What do you think of this? What is the possible solution? Thanks! Qing The following is the output from TOP command: Processes: 92 total, 4 running, 88 sleeping... 180 threads 13:09:18 Load Avg: 2.81, 2.73, 2.50 CPU usage: 95.2% user, 4.8% sys, 0.0% idle SharedLibs: num = 116, resident = 11.5M code, 1.66M data, 4.08M LinkEdit MemRegions: num = 12132, resident = 148M + 2.82M private, 403M shared PhysMem: 435M wired, 5.04G active, 2.22G inactive, 7.69G used, 316M free VM: 32.7G + 81.5M 5281127(13) pageins, 8544145(0) pageouts PID COMMAND %CPU TIME #TH #PRTS #MREGS RPRVT RSHRD RSIZE VSIZE 27314 postgres92.2% 2:14.75 1 949 12.8M+ 396M 75.0M+ 849M 26099 postgres91.1% 19:28.04 1 967 15.9M+ 396M 298M+ 850M 24754 top 2.8% 4:48.33 12926 272K 404K 648K 27.1M 0 kernel_tas 1.9% 2:12:05 40 2 8476 67.1M 0K 281M 1.03G 294 hwmond 0.5% 2:26:34 87557 240K 544K 1.09M 31.0M 347 lookupd 0.3% 1:52:28 23573 3.05M 648K 3.14M 33.6M 89 configd 0.1% 53:05.16 3 126 151 304K 644K 832K 29.2M 26774 servermgrd 0.1% 0:02.93 11040 344K- 1.17M+ 1.86M 28.2M 170 coreservic 0.1% 0:09.04 14093 152K 532K 2.64M 28.5M 223 DirectoryS 0.1% 19:42.47 884 135 880K+ 1.44M 4.60M+ 37.1M+ 125 dynamic_pa 0.0% 0:26.79 1121716K 292K28K 17.7M 87 kextd0.0% 0:01.23 21721 0K 292K36K 28.2M 122 update 0.0% 14:27.71 1 91516K 300K44K 17.6M 1 init 0.0% 0:00.03 1121628K 320K76K 17.6M 2 mach_init0.0% 3:36.18 2951876K 320K 148K 18.2M 81 syslogd 0.0% 0:19.96 1101796K 320K 148K 17.7M ---(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] Article about PostgreSQL and RAID in Brazil
Hi, there, I am running PostgreSQL 7.3.4 on MAC OS X G5 with dual processors and 8GB memory. The shared buffer was set as 512MB. The database has been running great until about 10 days ago when our developers decided to add some indexes to some tables to speed up certain uploading ops. Now the CPU usage reaches 100% constantly when there are a few users accessing their information by SELECT tables in databases. If I REINEX all the indexes, the database performance improves a bit but before long, it goes back to bad again. My suspicion is that since now a few indexes are added, every ops are run by PostgreSQL with the indexes being used when calculating cost. This leads to the downgrade of performance. What do you think of this? What is the possible solution? Thanks! Qing The following is the output from TOP command: Processes: 92 total, 4 running, 88 sleeping... 180 threads 13:09:18 Load Avg: 2.81, 2.73, 2.50 CPU usage: 95.2% user, 4.8% sys, 0.0% idle SharedLibs: num = 116, resident = 11.5M code, 1.66M data, 4.08M LinkEdit MemRegions: num = 12132, resident = 148M + 2.82M private, 403M shared PhysMem: 435M wired, 5.04G active, 2.22G inactive, 7.69G used, 316M free VM: 32.7G + 81.5M 5281127(13) pageins, 8544145(0) pageouts PID COMMAND %CPU TIME #TH #PRTS #MREGS RPRVT RSHRD RSIZE VSIZE 27314 postgres92.2% 2:14.75 1 949 12.8M+ 396M 75.0M+ 849M 26099 postgres91.1% 19:28.04 1 967 15.9M+ 396M 298M+ 850M 24754 top 2.8% 4:48.33 12926 272K 404K 648K 27.1M 0 kernel_tas 1.9% 2:12:05 40 2 8476 67.1M 0K 281M 1.03G 294 hwmond 0.5% 2:26:34 87557 240K 544K 1.09M 31.0M 347 lookupd 0.3% 1:52:28 23573 3.05M 648K 3.14M 33.6M 89 configd 0.1% 53:05.16 3 126 151 304K 644K 832K 29.2M 26774 servermgrd 0.1% 0:02.93 11040 344K- 1.17M+ 1.86M 28.2M 170 coreservic 0.1% 0:09.04 14093 152K 532K 2.64M 28.5M 223 DirectoryS 0.1% 19:42.47 884 135 880K+ 1.44M 4.60M+ 37.1M+ 125 dynamic_pa 0.0% 0:26.79 1121716K 292K28K 17.7M 87 kextd0.0% 0:01.23 21721 0K 292K36K 28.2M 122 update 0.0% 14:27.71 1 91516K 300K44K 17.6M 1 init 0.0% 0:00.03 1121628K 320K76K 17.6M 2 mach_init0.0% 3:36.18 2951876K 320K 148K 18.2M 81 syslogd 0.0% 0:19.96 1101796K 320K 148K 17.7M ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] indexes make other queries slow!
Josh: Sorry for the reply to the existing subject! The newly added indexes have made all other queries much slower except the uploading ops. As a result, all the CPU's are running crazy but not much is getting finished and our Application Server waits for certain time and then times out. Customers thought the system hung. My guess is that all the queries that involves the columns that are being indexed need to be rewritten to use the newly created indexes to avoid the performance issues. The reason is that REINDEX does not help either. Does it make sense? Thanks! Qing On Sep 16, 2004, at 2:05 PM, Josh Berkus wrote: Qing, Please don't start a new question by replying to someone else's e-mail. It confuses people and makes it unlikely for you to get help. My suspicion is that since now a few indexes are added, every ops are run by PostgreSQL with the indexes being used when calculating cost. This leads to the downgrade of performance. That seems rather unlikely to me.Unless you've *really* complex queries and some unusual settings, you can't swamp the CPU through query planning. On the other hand, your mention of REINDEX indicates that the table is being updated very frequently. If that's the case, then the solution is probably for you to cut back on the number of indexes. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Shared buffers, Sort memory, Effective Cache Size
Hello, I have recently configured my PG7.3 on a G5 (8GB RAM) with shmmax set to 512MB and shared_buffer=5, sort_mem=4096 and effective cache size = 1. It seems working great so far but I am wondering if I should make effctive cache size larger myself. Tnaks! Qing On Apr 21, 2004, at 9:29 AM, Frédéric Robinet wrote: Hello, I have a bi-PIII server with 2Gb of RAM with Debian and a PostgreSQL 7.4 running on. What are the bests settings for shared buffers, sort memory and effective cache size? My main database have a small/mid range size: some tables may have 1 or 2 millions of records. Thanks Frédéric Robinet [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] configure shmmax on MAC OS X
Tom: I used sysctl -A to see the kernel state, I got: kern.sysv.shmmax: -1 It looks the value is too big! Thanks! Qing On Apr 13, 2004, at 12:55 PM, Tom Lane wrote: Qing Zhao [EMAIL PROTECTED] writes: My suspision is that the change i made in /etc/rc does not take effect.Is there a way to check it? sysctl has an option to show the values currently in effect. I believe that /etc/rc is the correct place to set shmmax on OSX 10.3 or later ... but we have seen prior reports of people having trouble getting the setting to take. There may be some other constraint involved. sysctl -w kern.sysv.shmmax=4294967296 // byte Hmm, does sysctl work for values that exceed the range of int? There's no particularly good reason to try to set shmmax as high as you are trying anyhow; you really don't need more than a couple hundred meg in Postgres shared memory. It's better to leave the kernel to manage the bulk of your RAM. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] column size too large, is this a bug?
Thanks a lot! We were migrating to Postgres from Oracle and every now and then, we ran into something that we do not understand completely and it is a learning process for us. Your responses have made it much clear for us. BTW, do you think that it's better for us just to rewrite everything so we don't need to use the patch at all? Why do others still use it? Thanks! Qing On Mar 25, 2004, at 6:04 PM, Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: Oh, good eye ... it's that infamous CONNECT BY patch again, without doubt. Hey, who does this patch? What's wrong wiith it? I'm just venting my annoyance at people expecting us to support hacked-up versions, especially without telling us they're hacked-up. This is the third or fourth trouble report I can recall that was eventually traced to that patch (after considerable effort). Anyway, my guess for the immediate problem is incorrect installation of the patch, viz not doing a complete make clean and rebuild after patching. The patch changes the Query struct which is referenced in many more files than are actually modified by the patch, and so if you didn't build with --enable-depend then a simple make will leave you with a patchwork of files that have different ideas about the field offsets in Query. I'm a bit surprised it doesn't just dump core... (That's not directly the fault of the patch, though, except to the extent that it can be blamed for coming without adequate installation instructions. What is directly the fault of the patch is that it doesn't force an initdb by changing catversion. The prior trouble reports had to do with views not working because their stored rules were incompatible with the patched backend. We should not have had to deal with that, and neither should those users.) Theory B, of course, is that this is an actual bug in the patch and not just incorrect installation. I'm not interested enough to investigate though. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] column size too large, is this a bug?
I have a query which get's data from a single table.
When I try to get data from for an RFQ which has around 5000 rows, it is breaking off at 18th row.
If i reduce some columns , then it returns all the rows and not so slow.
I have tried with different sets of column and there is no pattern based on columns.
But one thing is sure one size of the rows grows more than some bytes, the records do not get returned. Now the following query returns me all 5001 rows to me pretty fast
select
_level_ as l,
nextval('seq_pk_bom_detail') as bom_detail,
prior nextval('seq_pk_bom_detail') as parent_subassembly,
parent_part_number,
customer_part_number,
/* mfr_name,
mfr_part,
description,*/
commodity,
needs_date,
target_price,
comments,
case qty_per
when null then 0.1
when 0 then 0.1
else qty_per
end,
qty_multiplier1,
qty_multiplier2,
qty_multiplier3,
qty_multiplier4,
qty_multiplier5
from bom_detail_work_clean
where (0=0)
and bom_header=20252
and file_number = 1
start with customer_part_number = 'Top Assembly 1'
connect by parent_part_number = prior customer_part_number;
But if I uncomment the description then it returns me only 18 rows.
select
_level_ as l,
nextval('seq_pk_bom_detail') as bom_detail,
prior nextval('seq_pk_bom_detail') as parent_subassembly,
parent_part_number,
customer_part_number,
/* mfr_name,
mfr_part,*/
description,
commodity,
needs_date,
target_price,
comments,
case qty_per
when null then 0.1
when 0 then 0.1
else qty_per
end,
qty_multiplier1,
qty_multiplier2,
qty_multiplier3,
qty_multiplier4,
qty_multiplier5
from bom_detail_work_clean
where (0=0)
and bom_header=20252
and file_number = 1
start with customer_part_number = 'Top Assembly 1'
connect by parent_part_number = prior customer_part_number;
Now these 18 rows are level 2 records in heirarchical query. I have a feeling the server has some memory paging mechanism
and if it can not handle beyond certain byets, it just returns what it has.
During your investigation of optimization of postgreSQL did you come across any setting that might help us ?
Thanks!
Qing
PS: I just reload the file while reducing the content in the description column.
The file got uploaded. So looks like the problem is size of the record being inserted.
Re: [PERFORM] column size too large, is this a bug?
Tom, Thanks for your help! It's not through one client. I am using JDBC. But the same things happen when I use client like psql. Qing On Mar 25, 2004, at 10:20 AM, Tom Lane wrote: Qing Zhao [EMAIL PROTECTED] writes: I have a query which get's data from a single table. When I try to get data from for an RFQ which has around 5000 rows, it is breaking off at 18th row. If i reduce some columns , then it returns all the rows and not so slow. What client-side software are you using? This is surely a limitation on the client side, because there is no such problem in the server. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] column size too large, is this a bug?
It is 7.3.4 on MAC OS X (darwin). The patch we applied is hier-Pg7.3-0.5, whichx-tad-bigger allows
to perform hierarchical queries on PgSQL using Oracle's syntax.
Thanks!
Qing
/x-tad-bigger
On Mar 25, 2004, at 2:57 PM, Stephan Szabo wrote:
On Thu, 25 Mar 2004, Qing Zhao wrote:
select
_level_ as l,
nextval('seq_pk_bom_detail') as bom_detail,
prior nextval('seq_pk_bom_detail') as parent_subassembly,
parent_part_number,
customer_part_number,
/* mfr_name,
mfr_part,
description,*/
commodity,
needs_date,
target_price,
comments,
case qty_per
when null then 0.1
when 0 then 0.1
else qty_per
end,
qty_multiplier1,
qty_multiplier2,
qty_multiplier3,
qty_multiplier4,
qty_multiplier5
from bom_detail_work_clean
where (0=0)
and bom_header=20252
and file_number = 1
start with customer_part_number = 'Top Assembly 1'
connect by parent_part_number = prior customer_part_number;
What version are you running, and did you apply any patches (for example
one to support the start with/connect by syntax used above?)
Re: [PERFORM] Increasing number of PG connections.
I am new here. I have a question related to this in some way. Our web site needs to upload a large volume of data into Postgres at a time. The performance deterioates as number of rows becomes larger. When it reaches 2500 rows, it never come back to GUI. Since the tests were run through GUI, my suspision is that it might be caused by the way the application server talking to Postgres server, the connections, etc.. What might be the factors involved here? Does anyone know? Thanks a lot! Qing On Feb 2, 2004, at 11:14 AM, Kevin Barnard wrote: I am running a Dual Xeon hyperthreaded server with 4GB RAM RAID-5. The only thing running on the server is Postgres running under Fedora. I have a 700 connection limit. The DB is setup as a backend for a very high volume website. Most of the queries are simple, such as logging accesses, user login verification etc. There are a few bigger things suchas reporting etc but for the most part each transaction lasts less then a second. The connections are not persistant (I'm using pg_connect in PHP) The system was at 2 GB with a 400 connection limit. We ran into problems because we hit the limit of connections during high volume. 1. Does 400 connections sound consistant with the 2GB of RAM? Does 700 sound good with 4 GB. I've read a little on optimizing postgres. Is there anything else I can do maybe OS wise to increase how many connections I get before I start swapping? 2. Are there any clustering technologies that will work with postgres? Specifically I'm looking at increasing the number of connections. The bottom line is since the website launched (middle of January) we have increased the number of http connections, and increased bandwidth allowances by over 10 times. The site continues to grow and we are looking at our options. Some of the ideas have been possible DB replication. Write to master and read from multiple slaves. Other ideas including increasing hardware. This is the biggest site I have ever worked with. Almost everything else fits in a T1 with a single DB server handling multiple sites. Does anybody with experence in this realm have any suggestions? Thank you in advance for whatever help you can provide. -- Kevin Barnard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
