[GENERAL] How to speed up commits?
Hi, I was doing a few tests to optimize my perl web app, and this is what I got. without database: 140 hits/sec with a rollback/begin and a select: 90 hits/sec with a rollback/begin, select and an update (but no commit): 70 hits/sec with a rollback/begin, select + update + commit: 13 hits/sec Any idea how to speed things up? Turning off sync would be dangerous right? The minimum my web app does is: 1) rollback/begin 2) select session information 3) update session information (new session time out) 4) commit update I written a simple script which does the same thing and the figures are the same. Any idea how to speed things up without switching to another database engine or session control method? Or do I have to live with 13 hits/sec max? )-; Does MySQL turn off sync? I don't think it does, but it seems to be able to do updates (and thus syncs) a lot faster. I know postgresql has got transactions and all that, but from the "time" statistics, the CPU isn't really being pushed, so if it's not sync what's it waiting for? (benchmark does 100 sets of the four steps). time ./benchmark 0.26user 0.02system 0:07.65elapsed 3%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (295major+221minor)pagefaults 0swaps Whereas if I remove the commit: time ./benchmark 0.21user 0.02system 0:01.57elapsed 14%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (294major+223minor)pagefaults 0swaps Any suggestions welcome! Cheerio, Link.
Re: [GENERAL] How to speed up commits?
At 02:51 PM 3/04/00 +0800, Lincoln Yeoh wrote: Hi, I was doing a few tests to optimize my perl web app, and this is what I got. A few questions, if you don't do the commit, then you loose your data, so there isn't much point in doing that is there ... How much RAM does you machine have? Where is the blocking happening, what does vmstat tell you (is it blocking on disk, ram or what?) What sort of disk drives are you using? How many postmasters are you running, what is the maximum you have set (I think the default is 32 or somthing) Does you code pool database connections at all or does it reconnect for each statement (select, update, etc) -- Mr Grumpy is now a virtual personality ... http://www.cyber4.org/members/grumpy/camera/index.html
Re: [GENERAL] How to speed up commits?
Lincoln Yeoh wrote: Hi, I was doing a few tests to optimize my perl web app, and this is what I got. without database: 140 hits/sec with a rollback/begin and a select: 90 hits/sec with a rollback/begin, select and an update (but no commit): 70 hits/sec with a rollback/begin, select + update + commit: 13 hits/sec Any idea how to speed things up? Turning off sync would be dangerous right? The minimum my web app does is: 1) rollback/begin 2) select session information 3) update session information (new session time out) 4) commit update I written a simple script which does the same thing and the figures are the same. Any idea how to speed things up without switching to another database engine or session control method? Or do I have to live with 13 hits/sec max? )-; Does MySQL turn off sync? I don't think it does, but it seems to be able to do updates (and thus syncs) a lot faster. I know postgresql has got transactions and all that, but from the "time" statistics, the CPU isn't really being pushed, so if it's not sync what's it waiting for? A statement in the mySQL documentation's change log for 3.22.9 leads me to believe that mySQL does not flush dirty kernel buffers to disk with a call to fsync() on each insert/update/delete: "You can now start mysqld on Win32 with the --flush option. This will flush all tables to disk after each update. This makes things much safer on NT/Win98 but also MUCH slower." And in the change log for 3.22.18: "Added option -O flush-time=# to mysqld. This is mostly useful on Win32 and tells how often MySQL should close all unused tables and flush all updated tables to disk." These statements imply that unlike PostgreSQL, which defaults to fsync() ON, mySQL defaults to fsync() OFF. By the way, we have been running a production PostgreSQL server on 6.5beta for over a year with fsync() off (-o -F) without problems. If your server doesn't suffer from kernel crashes, and is backed by a UPS, there's no reason in running PostgreSQL with fsync() on. It seems pretty clear that the mySQL folks didn't even consider a flushing option until the port to Win32, where the "kernel" was far from reliable... Hope that helps, Mike Mascari (benchmark does 100 sets of the four steps). time ./benchmark 0.26user 0.02system 0:07.65elapsed 3%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (295major+221minor)pagefaults 0swaps Whereas if I remove the commit: time ./benchmark 0.21user 0.02system 0:01.57elapsed 14%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (294major+223minor)pagefaults 0swaps Any suggestions welcome! Cheerio, Link.
Re: [GENERAL] How to speed up commits?
At 04:23 AM 03-04-2000 -0400, Mike Mascari wrote: Does MySQL turn off sync? I don't think it does, but it seems to be able to do updates (and thus syncs) a lot faster. I know postgresql has got transactions and all that, but from the "time" statistics, the CPU isn't really being pushed, so if it's not sync what's it waiting for? A statement in the mySQL documentation's change log for 3.22.9 leads me to believe that mySQL does not flush dirty kernel buffers to disk with a call to fsync() on each insert/update/delete: Yah, seems like it now. I must have been fooled by the statement that it does a write() after every SQL statement. I created a shell script with 100 syncs, and it took 6.25 seconds to run. So I've got egg on my face now :*), and the bottleneck is sync not postgresql. By the way, we have been running a production PostgreSQL server on 6.5beta for over a year with fsync() off (-o -F) without problems. If your server doesn't suffer from kernel crashes, and is backed by a UPS, there's no reason in running PostgreSQL with fsync() on. It seems pretty clear that the mySQL folks didn't even consider a flushing option until the port to Win32, where the "kernel" was far from reliable... Got UPS, linux 2.2.14 but I don't dare go fsyncless because another bunch is using the postgresql engine for another app. I'd like to run another postgres backend but the docs are sparse on running multiple independent postmasters/postgres. Should be possible- just start it on a different port, but the likely trouble areas will be the start/stop scripts - whether they can cope with killing just the relevant postgres stuff (instead of everything ;) ). Actually a good balance would be to have a separate database engine just for session handling, as syncs won't be important on this, it means more memory used, but that's not too difficult to fix nowadays grin. Any idea how much faster will it be without the sync? e.g. how many (begin, select, update, commit) per second? I'm guessing that with fsync off it's going to be just slightly slower than the no commit version e.g. 50-70 per second, woohoo. In that case if MySQL really doesn't do syncs on each SQL write, then things are about even and that's another feather in the Postgresql developers' caps. I seem to need to do vacuum analyze quite often to just maintain performance, should I do it with cron or have something that does it during low load times (which could mean never if I'm unlucky, or a death spiral as things go slower and slower ;) ). Cheerio, Link.
Re: [GENERAL] sql92
On Mon, 3 Apr 2000, Jeff MacDonald wrote: SQL-92 - is this an ansi standard ? Yes. Also ISO and IEC. -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
[GENERAL] cursor in plpgsql
Sorry for my bad english! Can I use "DECLARE CURSOR" in plpgsql function? I have : create table ipacc(ip_from inet, sourse_ports int2, ip_to inet, dist_port, bytes int4, packets int4, when_time datetime) I want to build report (I'm planing step): 1. CURSOR select distinct ip_from from ipacc; 2. FETCH from cursor into variable; 3. select sum(bytes) from ipacc where ip_from = variable; 4. Do while FETCH return row; I do: CREATE FUNCTION report() RETURNS text AS ' BEGIN WORK; DECLARE host_cursor CURSOR FOR SELECT DISTINCT ip_from FROM ipacc; host record; host_sum int4; BEGIN WHILE (FETCH FROM host_cursor INTO :host) LOOP; SELECT sum(bytes) from ipacc where ip_from=host; END LOOP; END; psql report - CREATE I do: select report(); psql report - NOTICE: plpgsql: ERROR during compile of report near line 3 ERROR: parse error at or near "CURSOR" Where I do mistake? -- - Grigoriy G. Vovk
[GENERAL] 6.5.3: Two 'INHERITS' problems.
Hi, folks. I'm having some trouble with inheritance in 6.5.3; first off, PRIMARY KEY attributes don't seem to be inherited. For example, CREATE TABLE test (i integer primary key); CREATE TABLE test2 (j integer) INHERITS(test); then INSERT INTO test VALUES (1); INSERT INTO test VALUES (1); correctly returns an error, while INSERT INTO test VALUES (1); INSERT INTO test2 VALUES (1,1); does not. Is this proper behavior?? If so, how can I guarantee that 'i' will be a primary (unique) key for the entire inheritance hierarchy? Secondly, create table superparent ( super_member_1INTEGER ); create table subchild ( child_member_2 INTEGER ) INHERITS (superparent); insert into subchild VALUES (1,1); select * from subchild; -- should return (1, 1) select * from superparent; -- should return null select * from superparent*; -- should return (1) test inheritance: -- add a member specifically to superparent: alter table superparent add member_2 INTEGER; -- add a member to superparent + children alter table superparent* add super_member_3 INTEGER; -- SELECT * : get good behavior from subchild, good behavior from -- superparent, and bad (random??) behavior from superparent*: select * from subchild; select * from superparent; select * from superparent*; -- lesson: either (a) don't add members specifically to superparent, or (b) have some other check on the query results! Selecting everything from superparent* returns a nonsense value for 'member_2', which was added only to superparent and not to the full hierarchy. Again, is this proper behavior?? How can one guard against it? Upgrading to PG 7 is not yet an option - I'm using pgACS, which doesn't yet working with 7 fully AFAIK. If upgrading is the only solution, I'll put up with it 'til I can use 7 ;). Thanks, --titus P.S. Pointers to appropriate documentation would be appreciated; I've read everything I can find... -- Titus Brown, [EMAIL PROTECTED]
RE: [GENERAL] Embedded SQL -- ecpg
Title: RE: [GENERAL] Embedded SQL -- ecpg You send them through the ecpg pre-compiler first, which replaces all the EXEC SQL statements with C, and then compile. When you compile, make sure that you link to the correct libraries (RTFM). MikeA -Original Message- From: Nilesh A. Phadke [mailto:[EMAIL PROTECTED]] Sent: 01 April 2000 21:49 To: [EMAIL PROTECTED] Subject: [GENERAL] Embedded SQL -- ecpg -- From: Nilesh A. Phadke[SMTP:[EMAIL PROTECTED]] Sent: Saturday, April 01, 2000 10:49:09 PM To: [EMAIL PROTECTED] Subject: [GENERAL] Embedded SQL -- ecpg Auto forwarded by a Rule hello , I am new to this embedded sql stuff. How do you compile the C program that has this EXEC SQL statements.?? Thanks in advance for the help, Nilesh.
[GENERAL] Re: How do I change the default editor in psql
On Fri, 31 Mar 2000 15:20:29 GMT, "anonymous" [EMAIL PROTECTED] wrote: The default editor when i invoke "\e" in "psql" it is "vi". How can I replace the editor with something else? Thanks, Rafael. Maybe setting system variable EDITOR should help. Try: $ export EDITOR=pico BTW, I'd rather learn vi ;-) -- Rafal Slubowski\\ [EMAIL PROTECTED] /O `. www.slubek.w.pl* (_.-. )\ *|* rs //--// X
[GENERAL] extern transaction logging
Hi all, I am a Postgres novice,so please excuse if my question looks rather dumb. I need to log all commited transactions externally on a backup server as soon as they are commited. What would be the best way to do it efficiently? Originally, in the first design of my 3-tier-system, I planned to send all SQL statements from my clients to a transaction server, parse and process them, and then pass them on to the Postgres server. The erformance costs are obvious. Several other options came to my mind after browsing through the Postgres documentation: - triggers / stored procedures - rules - ? That way, my application would communicate directly with the Postgres server, and the Postgres server would just start a thread sending all the data off to my transaction logging server after each insert/update/delete event - therefore no delay for the end user. Now, I can write the "transaction server" in C, but I have the feeling it would be much less work (and more efficient in performance terms) using triggers or rules or a different mechanism already built in in Postgres. Any suggestions for a PostgreSQL greenhorn? Horst
[GENERAL] word search
Hello, I would like to proceed to word search in a table. Use of "[...] attribute~*'string';" in the condition were fine. However, presence of operators within the string give me an error. For example : [...] attribute~*'C++'; To unspecialise the operators could be a way of solving this. Do you know to do that, if it is possible ? Thanks for you help, Sincerely, CP __ Get Your Private, Free Email at http://www.hotmail.com
Re: [GENERAL] doing backups
Now if you make pgsql user and postgresql analgous to root and unix, the postgres user shouldn't need a passwd. My root users always have passwords. *shrug* But you're right, automated backup on password protected databases is next to impossible right now, especially when using pg_dump. If those users want to enable you to do backups for them perhaps they should give you (and only you) some sort of ident controlled access. I missed the first half of this, but, if I have understood correctly, I don't think this is the case: you can do automated backups by doing echo -e 'username\npassword\n' | pg_dump -u databasename Yours, Moray
Re: [GENERAL] doing backups
that's true but that still assumes you know the pasword. also someone mentioned that you can just su to any account, well that's true but it still doesn't negate teh fact that the user has a passwd on thier database. as i said, i think the postgres user should have acess to all databases no matter what. jeff On Mon, 3 Apr 2000, Moray McConnachie wrote: Now if you make pgsql user and postgresql analgous to root and unix, the postgres user shouldn't need a passwd. My root users always have passwords. *shrug* But you're right, automated backup on password protected databases is next to impossible right now, especially when using pg_dump. If those users want to enable you to do backups for them perhaps they should give you (and only you) some sort of ident controlled access. I missed the first half of this, but, if I have understood correctly, I don't think this is the case: you can do automated backups by doing echo -e 'username\npassword\n' | pg_dump -u databasename Yours, Moray Jeff MacDonald [EMAIL PROTECTED]
Re: [GENERAL] Re: ODBC Interface questions
- Original Message - From: Michael Black [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, April 02, 2000 9:17 PM Subject: [GENERAL] Re: ODBC Interface questions I am using the Insight ODBC driver version 6.40.0009 on a Win98 box and Access 97. I set it up as a System DNS. Originally, I linked all the tables with the Read Only option checked. I have since changed this to un-checked. However, the tables were still RO. So I dropped all the links and re-linked them. I am now able to add or update records in the database. This is true for all versions of Access, and indeed if you change the port or the host of your pgsql database, you will similarly need to drop and relink all tables. If you turn on in Access the option to view all system tables, and explore a bit, you will see that the specific ODBC string generated from the DSN is stored with the table definition. I believe, though I haven't tried it, that it's possible to rewrite all these tabledefs just to refer only to the DSN, and omit any specific references. Going to be slower, though Yours, Moray
[GENERAL] Re: [ADMIN] PG_VERSION Errors???
"Glerum, Joe - KyEM" wrote: Database system in directory /usr/local/pgsql/data is not compatable with this version of Postgres, or we are unable to read the PG_VERSION file. Explanation from the ValidatePgVersion: Version number in file '/usr/local/pgsql/data/PG_VERSION' should be 6.5, not 6.4. When I change the 6.4 to 6.5 and restart the postmaster I can't do so with errors to change it back to 6.4. Did you do a dump/initdb/restore cycle? For a major version upgrade (such s 6.4.2 to 6.5.3), you must first dump your old database using pg_dumpall, clear out the database directory, initdb with the new version, then restore using 'psql -e template1 dumped-data-file'. By the error message you gave, I'm assuming you are using the standard, non-RPM distribution. The RPM distribution since 6.5.1 or so has included a set of tools to make the migration easier. Upon an upgrade of the RPM's, a copy of the old executables is saved; also, a postgresql-dump utility is provided to use the old executables to make a dump. Then, the new version can be started up. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
[GENERAL] Cannot create init file pg_internal.ini - postmaster won't start
Hello, When I do (as postgres): bash$ psql mydb I get: Connection to database 'mydb' failed. FATAL 1: cannot create init file pg_internal.init I'm not out of disk space. What's wrong? Where is it trying to create these files? Is it a permissions issue? Is it a libraries problem? Mine are in: /usr/local/pgsql/lib but they're also linked from: /url/lib/pgsql cause that seemed to fix alot of things. I'm using 7.0beta2 and starting postmaster *was* working with the version previously. Thanks, Phil
[GENERAL] PQsendQuery() -- query is too long. Maximum length is 8191
I see that the code has this coded in using a constant. Anyone know if I can up the length of this safely? We have queries that WAY exceed this limit! It's amazing wha the PHPLIB and session variables can do to make PostgreSQL's life misery! Brad
[GENERAL] The nl RPMs.
What are the "nl" RPMs and when should I use them? -=michael=- * * Michael S. Kelly * 4800 SW Griffith Dr., Ste. 202 * Beaverton, OR 97005 USA * voice: (503)644-6106 x122 fax: (503)643-8425 * [EMAIL PROTECTED] * http://www.axian.com/ * *Axian: Software Consulting and Training *
[GENERAL] unsigned int4
I looked in the FAQs, lists, docs, etc but couldn't find an answer to my question. Is there a way to specify an unsigned int, such that it would create an ERROR condition if a transaction were to attempt to cause a particular value to go negative. This would be very useful to me. I would like to be able to use the automatic rollback feature of WORK blocks if a debit is generated that would subtract more than it's positive balance. I feel this would be helpful due to the concurrent nature of our bank application. Thanks for any help you can give me. Scott Jackson ([EMAIL PROTECTED])
Re: [GENERAL] SELECT speed with LIKE
I had the same problem with 6.5.3. It turns out that there is a "known" (at least to the developers; I haven't seen it documented anywhere) problem in 6.5: if your postgresql was compiled with Locale support on, index searches of the form LIKE 'foo%' go very, very slow (much slower than deleting the index and forcing a sequential search). The solution is to recompile postgresql with Locale off. Note that I tried to use the RPM that claims to be compiled this way, but it didn't help; I had to recompile myself from the source RPM. Once I did the search on 340,000 rows went from 20 seconds to 0.1 seconds. 7.0 supposedly fixes this, but I haven't tried it.
Re: [GENERAL] unsigned int4
I looked in the FAQs, lists, docs, etc but couldn't find an answer to my question. Is there a way to specify an unsigned int, such that it would create an ERROR condition if a transaction were to attempt to cause a particular value to go negative. This would be very useful to me. I would like to be able to use the automatic rollback feature of WORK blocks if a debit is generated that would subtract more than it's positive balance. I feel this would be helpful due to the concurrent nature of our bank application. Oid should be unsigned int. Not sure it is completely implemented, but it should be. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[GENERAL] Fetching record of the day
To the PostGresQL mastermind: Working with postgresql and SQL in general is a blast. However, I need advice on how best to form the following query. I'm sure there are a dozen different ways to do it. Please give me your suggestion: I want to publish a "Link of the Day" on my website, with title, description, and URL. I have a field named startdate with type of datetime, but not all records have this field filled in. In those cases, it contains a null. I would like to retireve the record (there should be only one) whose field "startdate" contains today's date. So I only want to test for date, not datetime and I also want to ignore the null records. Of course, if it works better, I could store the intended startdate in text format or even an integer format, if that works better. So, in general, here's what I want to do: select [stuff] from [table] where date(startdate) = date(today) Several approaches I have tried have all died because of the nulls stored in the datetime-type field startdate. Please suggest a workable solution that you have tested. The best one (that works) gets their website listed on my "link of the day". What a deal! Thanks, Gary ** * Gary B. Hoffman, Computing Services Manager e-mail: [EMAIL PROTECTED] * * Graduate School of International Relations and Pacific Studies (IR/PS) * * University of California, San Diego (UCSD) voice: (858) 534-1989 * * 9500 Gilman Dr., La Jolla, CA 92093-0519 USA fax: (858) 534-3939 * **
