Re: [PERFORM] DB Design
The complete answer is probably it depends, but this does not help much...:-) I would try out the simple approach first (i.e one 50 million row table), but read up about : i) partial indexes and maybe ii) clustering iii) think about presorting the data before loading to place likely to be accessed rows close together in the table (if possible). iv) get to know the analyze, explain, explain analyze commands Best wishes Mark Michael Ryan S. Puncia wrote: Hi Guys, My question is .. which is better design 1. Single Table with 50 million records or 2. Multiple Table using inheritance to the parents table I will use this only for query purpose .. Thanks .. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Hardware Platform
Duane wrote: P.S. I've only just begun using PostgreSQL after having used (and still using) DB2 on a mainframe for the past 14 years. My experience with Unix/Linux is limited to some community college classes I've taken but we do have a couple of experienced Linux sysadmins on our team. I tell you this because my ignorance will probably show more than once in my inquiries. Duane, If you've been actively using and developing in DB2, presumably under MVS or whatever big blue is calling it these days, for 14 years, then you will bring a wealth of big system expertise to Pg. Please stay involved and make suggestions where you thing Pg could be improved. Marty ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] PostgreSQL performance in simple queries
Joseph Shraibman [EMAIL PROTECTED] writes: Neil Conway wrote: PostgreSQL ( 7.5) won't consider using an indexscan when the predicate involves an integer literal and the column datatype is int2 or int8. Is this fixed for 7.5? It isn't checked off on the TODO list at http://developer.postgresql.org/todo.php It is. I don't know why Bruce hasn't checked it off. Some other stuff that needs work in TODO: : Bracketed items [] have more detailed. More detailed what? Grammar please. : * Remove unreferenced table files and temp tables during database vacuum : or postmaster startup (Bruce) I'm not sure this is still needed given that we now log file deletion in WAL. : * Allow pg_dump to dump sequences using NO_MAXVALUE and NO_MINVALUE Seems to be done. : * Prevent whole-row references from leaking memory, e.g. SELECT COUNT(tab.*) Done. : * Make LENGTH() of CHAR() not count trailing spaces Done. : * Allow SELECT * FROM tab WHERE int2col = 4 to use int2col index, int8, : float4, numeric/decimal too Done, per above. : * Allow more ISOLATION LEVELS to be accepted, but issue a warning for them Presently we accept all four with no warning ... : * Add GUC setting to make created tables default to WITHOUT OIDS Seems to be done, other than the argument about how pg_dump should work. : * Allow fastpast to pass values in portable format This was done in 7.4. : * Move psql backslash database information into the backend, use nmumonic : commands? [psql] Spelling problem... : * JDBC With JDBC out of the core, I'm not sure why we still have a JDBC section in the core TODO. : * Have pg_dump -c clear the database using dependency information I think this works now. Not really tested, but in principle it should work. : * Cache last known per-tuple offsets to speed long tuple access This sounds exactly like attcacheoff, which has been there since Berkeley. Either remove this or fix the description to give some idea what's really meant. : * Automatically place fixed-width, NOT NULL columns first in a table This is not ever going to happen, given that we've rejected the idea of having separate logical and physical column positions. : * Change representation of whole-tuple parameters to functions Done. (However, you might want to add something about supporting composite types as table columns, which isn't done.) : * Allow the regression tests to start postmaster with -i so the tests : can be run on systems that don't support unix-domain sockets Done long ago. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Interpreting vmstat
Well, Since I haven't seen any other responds, I'll offer a bit of advice and let others correct me. :) Your shared buffers may be too big (?). It is much larger than the guide on varlena.com recommends. All I can suggest is trying some experiments with halving/doubling the numbers to see which way performance goes. Also, if you are counting on cache to improve performance, then the db has to be loaded into cache the first time. So, are subsequent re-queries faster? Thom Dyson Director of Information Services Sybex, Inc. [EMAIL PROTECTED] wrote on 05/18/2004 11:12:14 AM: Hello, (note best viewed in fixed-width font) I'm still trying to find where my performance bottle neck is... I have 4G ram, PG 7.3.4 shared_buffers = 75000 effective_cache_size = 75000 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] PostgreSQL performance in simple queries
Tom Lane wrote: : * JDBC With JDBC out of the core, I'm not sure why we still have a JDBC section in the core TODO. Speaking of which why is the jdbc site so hard to find? For that matter the new foundry can only be found through the news article on the front page. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Wierd context-switching issue on Xeon
Guys, Oh, you wanted a fix? That seems harder :-(. AFAICS we need a redesign that causes less load on the BufMgrLock. FWIW, we've been pursuing two routes of quick patch fixes. 1) Dave Cramer and I have been testing setting varying rates of spin_delay in an effort to find a sweet spot that the individual system seems to like. This has been somewhat delayed by my illness. 2) The OSDL folks have been trying various patches to use Linux 2.6 Futexes in place of semops (if I have that right) which, if successful, would produce a linux-specific fix. However, they haven't yet come up wiith a version of the patch which is stable. I'm really curious, BTW, about how all of Jan's changes to buffer usage in 7.5 affect this issue. Has anyone tested it on a recent snapshot? -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] Wierd context-switching issue on Xeon
Josh Berkus [EMAIL PROTECTED] writes: I'm really curious, BTW, about how all of Jan's changes to buffer usage in 7.5 affect this issue. Has anyone tested it on a recent snapshot? Won't help. (1) Theoretical argument: the problem case is select-only and touches few enough buffers that it need never visit the kernel. The buffer management algorithm is thus irrelevant since there are never any decisions for it to make. If anything CVS tip will have a worse problem because its more complicated management algorithm needs to spend longer holding the BufMgrLock. (2) Experimental argument: I believe that I did check the self-contained test case we eventually developed against CVS tip on one of Red Hat's SMP machines, and indeed it was unhappy. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
