Re: [GENERAL] High cpu usage after many inserts
On Tue, Feb 24, 2009 at 12:40 AM, Jordan Tomkinson jor...@moodle.com wrote: On Tue, Feb 24, 2009 at 11:42 AM, Greg Smith gsm...@gregsmith.com wrote: Right, the useful thing to do in this case is to take a look at how big all the relations (tables, indexes) involved are at each of the steps in the process. The script at http://wiki.postgresql.org/wiki/Disk_Usage will show you that. That will give some feedback on whether the vacuum/reindex methodology is really doing what you expect, and it will also let you compare the size of the table/index with how much RAM is in the system. taken before the ~7000 rows were entered. relation | size ---+ public.mdl_log | 595 MB public.mdl_forum_posts | 375 MB public.mdl_log_coumodact_ix | 197 MB public.mdl_user | 191 MB public.mdl_cache_text | 162 MB public.mdl_log_usecou_ix | 137 MB public.mdl_log_act_ix | 119 MB public.mdl_log_cmi_ix | 97 MB public.mdl_log_tim_ix | 97 MB public.mdl_log_id_pk | 97 MB public.mdl_question_states | 48 MB public.mdl_stats_user_daily | 48 MB public.mdl_hotpot_responses | 47 MB public.mdl_register_downloads | 45 MB public.mdl_message_read | 37 MB public.mdl_course_display | 37 MB public.mdl_stats_user_weekly | 31 MB public.mdl_mnet_log | 27 MB public.mdl_user_ema_ix | 26 MB public.mdl_regidown_url_ix | 23 MB What's more interesting is how quickly they grow during your test. I'm betting that as public.mdl_log and public.mdl_forum_posts grow, you get a dataset larger than memory. There are two levels of caching that pgsql uses, the highest and closest to pgsql is the shared_buffer cache, and the next is the kernel level file system cache.While it's still way faster to hit the kernel level of file cache than to hit the actual hard drives, the pg shared_buffers is the fastest. You may be in a situation where giving a bit more memory to pg will help, but with a 4G dataset and 8G of ram you're cutting it close. You need a few gig for sorts and processes and such like that. Going to 16Gig you could set shared_buffers at somewhere in the 4 to 8Gig range and it might work out. If you're looking at scaling to large amounts of data, you can't plan on it all fitting into memory, and you have to start planning for faster Disk I/O. This means more disks, fast RAID controllers with optional battery backed cache (not really optional) and / or kernel level RAID, for read mostly stuff it's quite fast. As expensive as 16 or 24 or 32 fast hard drives are, they're cheaper than servers with a half terabyte of ram or whatever you'd need for a big dataset. First things first I'd try increasing shared_buffers to the just over 4G range. I'd check after each run with vacuum verbose (NOT FULL) to see how bloated my db was getting. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Large object loading stalls
Tom Lane wrote: Michael Akinde michael.aki...@met.no writes: Anyway - the situation now is that just the loading process is hanging on the server, with an IDLE in transaction. But it is definitely the loading program that is hanging, not the Postgres server. What the stack traces seem to show is that both the client and the server are waiting for each other to send some data. Which means somebody's bollixed the protocol. In the past we've seen this type of thing caused by multithreaded client programs in which more than one thread tried to use the same PGconn object without adequate interlocking. libpq itself does not provide any threading guards --- if you want more than one thread accessing a PGconn then it's up to you to use a mutex or something to serialize them. Is it possible this case applies here? My apologies for the delayed response. Our application is single-threaded, so it seems unlikely that we are running into a problem with that. The only thing I can think of right now, is that we are running a Postgres 8.3 on Debian Etch (so a backported debian package), whereas the libraries linked into our application are older library version (libpq4 and libpqxx 2.6.8). I'll try to upgrade the OS to a version with native support for 8.3 and up to date (or at least more up to date) versions of pq, pqxx and check whether the tests still break down. Regards, Michael A. begin:vcard fn:Michael Akinde n:Akinde;Michael org:Meteorologisk Institutt, Norge;IT adr;quoted-printable:;;Gaustadall=C3=A9en 30D;Oslo;;0313;Norge email;internet:michael.aki...@met.no tel;work:22963379 tel;cell:45885379 x-mozilla-html:FALSE url:http://www.met.no version:2.1 end:vcard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] javascript and postgres
thanks for replies. Craig Ringer wrote: This is a really, really, REALLY bad idea. I agree. John R Pierce wrote: if you mean client side Javascript running on the end users web browser, no, it should NOT be allowed to connect to a database server directly. Web pages have username and password with basic, digest or ldap authorization. So if I createuser with same user and password, and if there is md5 or something to encode password, I wonder javascript connects to postgres securely. As John said, I meant client side Javascript. regards -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] restore single table
Kevin Duffy wrote: I need guidance on how move some changes that I have made to my production database. On my development database I made changes to a table called DEPT. I added a column, added a couple of records and did some general data cleanup What I did not do was change any of the keys on existing records. The primary key of DEPT is a foreign key in several other tables. Here is my question: Can I do a table restore on to the production database and expect these changes to be moved over? Will the restore handle, via some magic, suspend the foreign key constraints and allow the new table structure to be created and then populated with new data. I have not changed the keys of existing records so that existing relationships should be restored. A few thoughts: Maybe you do not need to delete and recreate the table. An ALTER TABLE statement can, for example, add a column to an existing table. That way you could leave the foreign key constraints in place while you do the update. If you cannot avoid dropping and recreating the table, you could proceed like this: drop all foreign key constraints to your table, recreate it and add the constraints again. You should write an SQL script that does the necessary changes and test it beforehand. Lock out all database users while you perform substantial changes to the database. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Oracle Functions to PostgreSQL
Hi all, Is there any preferably open source tool to convert Oracle Functions to PostgreSQL Functions. Thanks, Abdul Rehman.
Re: [GENERAL] Oracle Functions to PostgreSQL
In response to Abdul Rahman : Hi all, Is there any preferably open source tool to convert Oracle Functions to PostgreSQL Functions. Maybe orafce, see http://pgfoundry.org/projects/orafce/ Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle Functions to PostgreSQL
Abdul Rahman wrote: Hi all, Is there any preferably open source tool to convert Oracle Functions to PostgreSQL Functions. No, not that I know of. I assume you mean user-created functions. There is orafce for adding Oracle functions to Postgres. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Rotating WAL files
Hello, I've just read about WAL and tried to set these 2 commands for my test database (which is doing nothing 99% of time): archive_command = 'cp -v %p /var/lib/pgsql/data/archive/%f' archive_timeout = 300 # force a logfile segment switch after this # many seconds; 0 is off And after few days it is already crowded in the archive dir: # ll /var/lib/pgsql/data/archive/|wc -l 1098 # du -hs /var/lib/pgsql/data/archive/ 18G /var/lib/pgsql/data/archive/ Is there some archive_ command for rotating WAL files available (can't find it in the docs) or is it my responsibility to get rid of the old files (and how do I identify them then?) Or should I maybe just set archive_timeout to 0? (the doc isn't clear enough for me what happens then) My target is to have backups for the any point in the last 4 weeks. Thank you Alex PS: I'm using NetApp filers with snapshots and: # rpm -qa|grep postg postgresql-libs-8.2.12-1PGDG.rhel5 compat-postgresql-libs-4-1PGDG.rhel5 postgresql-8.2.12-1PGDG.rhel5 postgresql-server-8.2.12-1PGDG.rhel5 # cat /etc/*release CentOS release 5.2 (Final) -- compatible to RHEL 5.2 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rotating WAL files
Alexander Farber wrote:
Hello,
I've just read about WAL and tried to set these 2 commands
for my test database (which is doing nothing 99% of time):
archive_command = 'cp -v %p /var/lib/pgsql/data/archive/%f'
archive_timeout = 300 # force a logfile segment switch after this
# many seconds; 0 is off
And after few days it is already crowded in the archive dir:
# ll /var/lib/pgsql/data/archive/|wc -l
1098
# du -hs /var/lib/pgsql/data/archive/
18G /var/lib/pgsql/data/archive/
Is there some archive_ command for rotating WAL files
available (can't find it in the docs) or is it my responsibility
to get rid of the old files (and how do I identify them then?)
Or should I maybe just set archive_timeout to 0?
(the doc isn't clear enough for me what happens then)
You need a setup/script which does the following:
1) In recovery.conf, use recovery_target_time to restore up to a certain
timestamp
2) In your archive dir, run the following to restore WALs up to 24 hours
ago: find . -maxdepth 1 -type f -daystart -mtime +1 -exec mv {}
/somewhere/else/where/your/recover/script/can/find/them/ \; -- change
the mtime arg to suit your needs.
3) Once recovery is complete, remove files using a variation of the
above find command -- something along the lines of: find /mypath -type f
-daystart -mtime +2 -exec rm -vf {} \; -- use +2 here so that you don't
remove everything in case you need a few files from the previous 24
hours later on.
You need to be really careful when running the remove command... if you
lose even one WAL file which postgres may need later on to continue the
restore, you're boned.
-salman
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Large object loading stalls
Michael Akinde michael.aki...@met.no writes: Tom Lane wrote: In the past we've seen this type of thing caused by multithreaded client programs in which more than one thread tried to use the same PGconn object without adequate interlocking. Our application is single-threaded, so it seems unlikely that we are running into a problem with that. Well, maybe you've found an actual bug then; but without a test case that other people can poke at, it's hard to investigate. The only thing I can think of right now, is that we are running a Postgres 8.3 on Debian Etch (so a backported debian package), whereas the libraries linked into our application are older library version (libpq4 and libpqxx 2.6.8). Older libpq versions should work fine with an 8.3 server --- if they don't, that's a bug in itself. I do not know the status of libpqxx though; you might want to check whether there are known bugs in that version. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High cpu usage after many inserts
* Greg Smith gsm...@gregsmith.com [090201 00:00]: Shouldn't someone have ranted about RAID-5 by this point in the thread? What? Sorry, I wasn't paying attention... You mean someone's actually still using RAID-5? ;-) -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
[GENERAL] Indexing a Bit String column
Hi all, I am planning to use the Bit String data type for a large number of binary strings, e.g. CREATE TABLE myTable (myBitStringCol BIT(3)); I will need to perform (bitwise AND) operations using SELECT on this column, e.g. SELECT * FROM myTable WHERE myBitStringCol B'101' = myBitStringCol; To optimise this type of SELECT statement, I guess I’ll have to build an index on the Bit String column, e.g. CREATE INDEX myBitStringCol_idx ON myTable (myBitStringCol); Is it all I need to do? Will PgSQL know how to index properly a Bit String column? Should I build the index using a special method, e.g. CREATE INDEX myBitStringCol_idx ON myTable USING gist(myBitStringCol); Since we’re already talking of a Bit String column, the USING gist() statement looks a bit redundant to me. Basically, I though I would ask if I need to do anything special when indexing a BIT column. Thanks for your comments. George. _ Twice the fun—Share photos while you chat with Windows Live Messenger. Learn more. http://www.microsoft.com/uk/windows/windowslive/products/messenger.aspx
Re: [GENERAL] Oracle Functions to PostgreSQL
you're going to have alot of work as ANNOTATIONS and DATATYPES are different
e.g.
--- Postgres function which calcs geo_distance
CREATE OR REPLACE FUNCTION geo_distance (point, point)
RETURNS float8
LANGUAGE 'C' IMMUTABLE STRICT AS '$libdir/earthdistance';
--Oracle has no clue what points or float8 might be so you'll have to convert
to
--known Oracle Datatypes or packaged objects
NUMBER,VARCHAR2,CHAR,DATE,BLOB,CLOB
IMMUTABLE can be accomplished if you birth the functionality to a Java Class
and add @Immutable
CREATE OR REPLACE PACKAGE Function_Container AS
FUNCTION JavaFunction
(AOracleDatatype IN VARCHAR2)
RETURN VARCHAR2
IS
LANGUAGE 'JAVA'
NAME 'package.ImmutableProjectToAccomplishAOracleFunction' (char[]) return
char[]';
/
Here is the Java file
package package;
@Immutable
public class ImmutableProjectToAccomplishAOracleFunction { }
--STRICT is only available in Oracle11
Martin
__
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business
of Sender. This transmission is of a confidential nature and Sender does not
endorse distribution to any party other than intended recipient. Sender does
not necessarily endorse content contained within this transmission.
Date: Tue, 24 Feb 2009 04:46:30 -0800
From: abr_...@yahoo.com
Subject: [GENERAL] Oracle Functions to PostgreSQL
To: pgsql-general@postgresql.org
Hi all,
Is there any preferably open source tool to convert Oracle Functions to
PostgreSQL Functions.
Thanks,
Abdul Rehman.
_
Windows Live™ Hotmail®:…more than just e-mail.
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_explore_022009
Re: [GENERAL] Indexing a Bit String column
George Oakman oakm...@hotmail.com writes: Is it all I need to do? Will PgSQL know how to index properly a Bit String column? Should I build the index using a special method, e.g. CREATE INDEX myBitStringCol_idx ON myTable USING gist(myBitStringCol); No, the default will be to build a btree index which won't help these types of queries at all. You would want a GIST index if there was a built-in GIST opclass for these kinds of queries, but sadly there isn't. You could add one fairly easily but it would require C code. I think it would be a valuable addition to Postgres if you do write one. Note that something like WHERE myBitStringCol B'101' might be selecting too much of your table to make an index useful anyways. If each bit is set in half the table then you're talking about selecting 3/4 of the table in which case a full table scan would be more efficient than any index. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexing a Bit String column
Gregory Stark st...@enterprisedb.com writes:
Note that something like WHERE myBitStringCol B'101' might be selecting
too much of your table to make an index useful anyways. If each bit is set in
half the table then you're talking about selecting 3/4 of the table in which
case a full table scan would be more efficient than any index.
If the expectation is that the bitstring is mostly zeroes, I wonder
whether the OP wouldn't be better off using an integer-array
representation, ie instead of '0101' store '{6,8}'. Then he could
use the existing GIST or GIN support for integer array operations.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexing a Bit String column
Hi, Thanks for the info. I new it would have been too easy! :) Sorry, I made a mistake earlier, my queries will actually be more like SELECT * FROM myTable WHERE myBitStringCol B'101' = B'101'; This doesn't make much difference for the indexing problem, but it may help address the very good point you raised regarding the predicted number of results, and therefore the justification of needing an index at all. In practice, my BitStrings will be 1024 bits long - both A and B in WHERE A B = B will be 1024 bits long. Assuming that bits are independents and randomly distributed in the database, am I right in thinking that a typical search is expected to return N*0.5^n, where N is the total number of rows in the table and n is the number of bits set to 1 in B? If this calculation is correct, even if 1% of the bits are set to 1 in B, then this would give N*0.5^10 results, i.e. roughly 0.1% of the database. So it looks like I'll need the index in the end! I am actually new to PgSQL - I would be very grateful if you could point me to resources/tutorials to help me build an index extension in C? Many thanks for your help. George. To: oakm...@hotmail.com CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] Indexing a Bit String column From: st...@enterprisedb.com Date: Tue, 24 Feb 2009 15:35:58 + George Oakman oakm...@hotmail.com writes: Is it all I need to do? Will PgSQL know how to index properly a Bit String column? Should I build the index using a special method, e.g. CREATE INDEX myBitStringCol_idx ON myTable USING gist(myBitStringCol); No, the default will be to build a btree index which won't help these types of queries at all. You would want a GIST index if there was a built-in GIST opclass for these kinds of queries, but sadly there isn't. You could add one fairly easily but it would require C code. I think it would be a valuable addition to Postgres if you do write one. Note that something like WHERE myBitStringCol B'101' might be selecting too much of your table to make an index useful anyways. If each bit is set in half the table then you're talking about selecting 3/4 of the table in which case a full table scan would be more efficient than any index. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ Windows Live Messenger just got better .Video display pics, contact updates more. http://www.download.live.com/messenger
[GENERAL] Warm standby failover mechanism
Hi all, We're looking at setting up a warm-standby server using log shipping and aren't too sure about how we should trigger failover. Is there a commonly-used approach which is reliable enough to recommend? Looking at the documentation, there doesn't seem to be any recommendation. I preferrably don't want to use a witness server. Also, what would you say is the best way to tell the failed primary server that it is no longer the primary server? Thanks Thom
Re: [GENERAL] javascript and postgres
野村 wrote: Web pages have username and password with basic, digest or ldap authorization. So if I createuser with same user and password, and if there is md5 or something to encode password, I wonder javascript connects to postgres securely. for that to work, irregardless of security aspects, the postgres client libraries would have to be installed on each web browser system, in a form that javascript could invoke. However, I've not heard of any javascript - postgres bindings suitable for use in a webbrowser context... Javascript in a webbrowser is running in a sort of sandbox and isn't supposed to be allowed to make its own network connections, or call system libraries directly, allowing this would be a gross security flaw (for instance, a hostile web page could take over a users computer). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Warm standby failover mechanism
On Tue, 2009-02-24 at 16:55 +, Thom Brown wrote: We're looking at setting up a warm-standby server using log shipping and aren't too sure about how we should trigger failover. Is there a commonly-used approach which is reliable enough to recommend? Looking at the documentation, there doesn't seem to be any recommendation. I preferrably don't want to use a witness server. Also, what would you say is the best way to tell the failed primary server that it is no longer the primary server? http://www.postgresql.org/docs/8.3/static/pgstandby.html -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query question
Hi, I am new to Postgres and am trying to write a query like the one below (without any luck) SELECT col_speed, col_time, (col_speed / col_time) AS distance FROM speed_ratings HAVING distance ? ORDER BY distance In other words, I want to filter on a calculated column. But I get an error that column distance is not defined column distance does not exist at character 272 Interestingly if I remove the filter (HAVING distance ?), the query works. So I can sort on distance but not filter. I have tried substituting the HAVING clause with a WHERE clause as well with no luck. I have also added a GROUP BY clause with the HAVING as well with no luck. Any ideas? Thanks Sid
Re: [GENERAL] Query question
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Sharma, Sid Sent: Tuesday, February 24, 2009 12:47 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Query question Hi, I am new to Postgres and am trying to write a query like the one below (without any luck) SELECT col_speed, col_time, (col_speed / col_time) AS distance FROM speed_ratings HAVING distance ? ORDER BY distance In other words, I want to filter on a calculated column. But I get an error that column distance is not defined column distance does not exist at character 272 Interestingly if I remove the filter (HAVING distance ?), the query works. So I can sort on distance but not filter. I have tried substituting the HAVING clause with a WHERE clause as well with no luck. I have also added a GROUP BY clause with the HAVING as well with no luck. Any ideas? Thanks Sid You were on the right track, unfortunately the rules are not very Consistent regarding when aliases can or cannot be used. In this case, WHERE and HAVING cannot use an alias, but ORDER BY and most others require it. Also, HAVING is applied to aggregate functions (like min/max/average) Try your query in this form: SELECT col_speed, col_time, (col_speed / col_time) AS distance FROM speed_ratings WHERE (col_speed / col_time) ? ORDER BY dd If you want to use GROUP BY / HAVING, you need to use another field to group the results by, as well as the aggregate function. for example, if you had a type_id field and wanted the maximum distance travelled per type: SELECT type_id, MAX(col_speed / col_time) AS max_distance FROM speed_ratings GROUP BY type_id HAVING MAX(col_speed / col_time) ? ORDER BY dd Finally, if you really want distance, I assume you mean speed * time, not speed/time. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] speaking of 8.4...
Hi, On Tue, Feb 24, 2009 at 5:16 AM, John R Pierce pie...@hogranch.com wrote: is it looking like the simple replication will make it into 8.4? You mean the built-in synchronous replication feature? If so, no. It was decided that synch-rep will be postponed to 8.5. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Function parameter
Hi ! I'm trying to modify an input parameter of a function, but I receive following error : ERROR: $17 is declared CONSTANT CONTEXT: compile of PL/pgSQL function update_jobreg near line 26 Is there a way to modify an input parameter or I have to declare a local variable and assign that input parameter to it ? Many thanks in advance, Nico Callewaert
Re: [GENERAL] Poor select count(*) performance
On Mon, Feb 23, 2009 at 11:21:16PM -0800, Mike Ivanov wrote: On Mon, Feb 23, 2009 at 6:54 PM, Sam Mason s...@samason.me.uk wrote: Depending on where these are on disk and how fast your disks are this could take up to 30 seconds. This does not sound very inspiring :-) It was calculated with a pessimistic seek time of 10ms * 3000 seeks. Real worst case would be even worse as you'd have to factor in potential misses of the index as well but that's unlikely. In practice, a table is likely to be stored close together on the disk and hence assuming average seek time is not accurate. If it's having to go off and read the index then you may loose this spacial clustering and performance will suffer. Would throwing more hardware (memory, faster CPU) at the server improve the situation? You're IO bound not CPU bound; faster disks would help or if your dataset's small enough more memory. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High cpu usage after many inserts
On Wed, Feb 25, 2009 at 12:05 AM, Aidan Van Dyk ai...@highrise.ca wrote: * Greg Smith gsm...@gregsmith.com [090201 00:00]: Shouldn't someone have ranted about RAID-5 by this point in the thread? What? Sorry, I wasn't paying attention... You mean someone's actually still using RAID-5? ;-) What exactly is wrong with RAID5 and what should we have gone with?
Re: [GENERAL] High cpu usage after many inserts
On Wed, 2009-02-25 at 09:21 +0900, Jordan Tomkinson wrote: On Wed, Feb 25, 2009 at 12:05 AM, Aidan Van Dyk ai...@highrise.ca wrote: * Greg Smith gsm...@gregsmith.com [090201 00:00]: Shouldn't someone have ranted about RAID-5 by this point in the thread? What? Sorry, I wasn't paying attention... You mean someone's actually still using RAID-5? ;-) What exactly is wrong with RAID5 and what should we have gone with? RAID5 outside of RAID 0 is the worst possible RAID level to run with a database. (of the commonly used raid level's that is). It is very, very slow on random writes which is what databases do. Switch to RAID 10. Sincerely, Joshua D. Drkae -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High cpu usage after many inserts
--- On Wed, 2/25/09, Jordan Tomkinson jor...@moodle.com wrote: ... What exactly is wrong with RAID5 and what should we have gone with? RAID10 is often used. As others have pointed out, it is very slow for random writes. It also has issues that expose your data to total loss, see for instance http://www.miracleas.com/BAARF/RAID5_versus_RAID10.txt. HTH, Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High cpu usage after many inserts
On Wed, Feb 25, 2009 at 9:23 AM, Joshua D. Drake j...@commandprompt.comwrote: RAID5 outside of RAID 0 is the worst possible RAID level to run with a database. (of the commonly used raid level's that is). It is very, very slow on random writes which is what databases do. Switch to RAID 10. surely being (real) hardware raid with 15k rpm disks this wouldn't be a huge issue unless a large amount of data was being written ?
Re: [GENERAL] High cpu usage after many inserts
On Tue, Feb 24, 2009 at 5:21 PM, Jordan Tomkinson jor...@moodle.com wrote: On Wed, Feb 25, 2009 at 12:05 AM, Aidan Van Dyk ai...@highrise.ca wrote: * Greg Smith gsm...@gregsmith.com [090201 00:00]: Shouldn't someone have ranted about RAID-5 by this point in the thread? What? Sorry, I wasn't paying attention... You mean someone's actually still using RAID-5? ;-) What exactly is wrong with RAID5 and what should we have gone with? RAID 5 is only suitable for situations where you need maximum storage for minimum cost and the database is mostly / all read all the time. Like large reporting databases. It's slow on writes, and it has a low tolerance for dead drives (2 and it's all gone) HOWEVER. RAID-10, which is theoretically MUCH better, is only better if it's implemented right, and lot of cheap RAID controllers don't do any better running RAID-10. Many of these can be put into JBOD mode where you do RAID-10 in the kernel, or you can do RAID-1 on the card (x sets) And RAID-0 in the kernel. RAID-10 is almost always the right choice when you're buying good controllers and fast drives and you want maximum performance. If you REALLY need a lot of storage, and you have to use something like RAID 5 at least look at RAID 6. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function parameter
On Feb 24, 2009, at 5:10 PM, Nico Callewaert wrote: I'm trying to modify an input parameter of a function, but I receive following error : ERROR: $17 is declared CONSTANT CONTEXT: compile of PL/pgSQL function update_jobreg near line 26 Is there a way to modify an input parameter or I have to declare a local variable and assign that input parameter to it ? Declaring a local variable is the best way to do it. You can modify a parameter if you declare it as INOUT, but you generally only want to do that if you want to return something from the function. Note that you can declare and assign the value in a single line in the DECLARE section of the function, e.g. text_var text := text_param; John DeSoi, Ph.D. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High cpu usage after many inserts
On Wed, 2009-02-25 at 09:44 +0900, Jordan Tomkinson wrote: On Wed, Feb 25, 2009 at 9:23 AM, Joshua D. Drake j...@commandprompt.com wrote: RAID5 outside of RAID 0 is the worst possible RAID level to run with a database. (of the commonly used raid level's that is). It is very, very slow on random writes which is what databases do. Switch to RAID 10. surely being (real) hardware raid with 15k rpm disks this wouldn't be a huge issue unless a large amount of data was being written ? Tests done by Mark Wong on a 3 disk 15k scsi versus 4 disk raid 10 scsi show that RAID 10 is on average 30% faster. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High cpu usage after many inserts
On Tue, Feb 24, 2009 at 4:40 PM, Jordan Tomkinson jor...@moodle.com wrote: taken before the ~7000 rows were entered. relation| size ---+ public.mdl_log| 595 MB public.mdl_forum_posts| 375 MB public.mdl_log_coumodact_ix | 197 MB public.mdl_user | 191 MB public.mdl_cache_text | 162 MB public.mdl_log_usecou_ix | 137 MB public.mdl_log_act_ix | 119 MB public.mdl_log_cmi_ix | 97 MB public.mdl_log_tim_ix | 97 MB public.mdl_log_id_pk | 97 MB public.mdl_question_states| 48 MB public.mdl_stats_user_daily | 48 MB public.mdl_hotpot_responses | 47 MB public.mdl_register_downloads | 45 MB public.mdl_message_read | 37 MB public.mdl_course_display | 37 MB public.mdl_stats_user_weekly | 31 MB public.mdl_mnet_log | 27 MB public.mdl_user_ema_ix| 26 MB public.mdl_regidown_url_ix| 23 MB (20 rows) Taken after 9000 rows entered, by this stage performance is terrible. relation| size ---+ public.mdl_log| 597 MB public.mdl_forum_posts| 389 MB public.mdl_log_coumodact_ix | 198 MB public.mdl_user | 193 MB public.mdl_cache_text | 162 MB public.mdl_log_usecou_ix | 137 MB public.mdl_log_act_ix | 119 MB public.mdl_log_cmi_ix | 98 MB public.mdl_log_tim_ix | 97 MB public.mdl_log_id_pk | 97 MB public.mdl_question_states| 48 MB public.mdl_stats_user_daily | 48 MB public.mdl_hotpot_responses | 47 MB public.mdl_register_downloads | 45 MB public.mdl_message_read | 37 MB public.mdl_course_display | 37 MB public.mdl_stats_user_weekly | 31 MB public.mdl_mnet_log | 27 MB public.mdl_user_ema_ix| 26 MB public.mdl_regidown_url_ix| 23 MB (20 rows) as you can see, the tables arent growing by much (only a few mb) so is this really to do with buffer/cache size? I set shared_buffers to 3072 (from 2048) and it hasnt made much improvement, requests are still taking longer and longer to execute.
Re: [GENERAL] High cpu usage after many inserts
Joshua D. Drake wrote: On Wed, 2009-02-25 at 09:21 +0900, Jordan Tomkinson wrote: On Wed, Feb 25, 2009 at 12:05 AM, Aidan Van Dyk ai...@highrise.ca wrote: * Greg Smith gsm...@gregsmith.com [090201 00:00]: Shouldn't someone have ranted about RAID-5 by this point in the thread? You mean someone's actually still using RAID-5? ;-) What exactly is wrong with RAID5 and what should we have gone with? On top of the stuff Joshua wrote, there's also the RAID 5 Write Hole. Quoting Wikipedia: In the event of a system failure while there are active writes, the parity of a stripe may become inconsistent with the data. If this is not detected and repaired before a disk or block fails, data loss may ensue as incorrect parity will be used to reconstruct the missing block in that stripe. This potential vulnerability is sometimes known as the write hole. Battery-backed cache and similar techniques are commonly used to reduce the window of opportunity for this to occur. And in more detail from http://blogs.sun.com/bonwick/entry/raid_z RAID-5 write hole... What's worse, it will do so silently -- it has no idea that it's giving you corrupt data. I sometimes wonder if postgres should refuse to start up on RAID-5 in the same way it does on VFAT or running root. :-) RAID5 outside of RAID 0 is the worst possible RAID level to run with a database. (of the commonly used raid level's that is). It is very, very slow on random writes which is what databases do. Switch to RAID 10. Sincerely, Joshua D. Drkae -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function parameter
- Original Message - From: John DeSoi de...@pgedit.com To: Nico Callewaert callewaert.n...@telenet.be Cc: pgsql-general@postgresql.org Sent: Wednesday, February 25, 2009 1:52 AM Subject: Re: [GENERAL] Function parameter On Feb 24, 2009, at 5:10 PM, Nico Callewaert wrote: I'm trying to modify an input parameter of a function, but I receive following error : ERROR: $17 is declared CONSTANT CONTEXT: compile of PL/pgSQL function update_jobreg near line 26 Is there a way to modify an input parameter or I have to declare a local variable and assign that input parameter to it ? Declaring a local variable is the best way to do it. You can modify a parameter if you declare it as INOUT, but you generally only want to do that if you want to return something from the function. Note that you can declare and assign the value in a single line in the DECLARE section of the function, e.g. text_var text := text_param; Hi ! Thank you for the explanation. I was not warae of the fact that you could declare and assign a variable in 1 line. Thanks, best regards, Nico -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
