Re: [PERFORM] Decide between Postgresql and Mysql (help of
Marcos wrote: So, what exactly are you planning on doing? The application will be a chat for web, the chats will be stored in the server. In a determined interval of time... more or less 2 seconds, the application will be looking for new messages. I believe that it will make many accesses. The write in disc will be constant. Ok. I would favor PostgreSQL for reasons of ease of future development. However, lets look at what both RDBMS's buy you: MySQL: 1) Possibility of many components for web apps that can be used though the lack of certain features (such as complex updateable views) makes this possibly an issue. 2) Great simple read performance. PostgreSQL: 1) Possibility to integrate any other components later (including those on MySQL via DBI-Link). 2) Fabulous community support (and I am sure fabulous paid support too given the fact that many of those who contribute to the great community support also offer paid support). 3) Better parallel write performance. 4) Greater extensibility, leading to greater flexibility down the road should you want to add in new components without rewriting your front-end. For a simple chat client, you can probably put something together with some Perl/CGI scripts, Jabber, and MySQL or PostgreSQL pretty easily and without much development labor at all. Indeed I would suggest that the RDBMS is, absent other specific concerns, the least of your issues. In other words, both are probably adequate. It is impossible to provide an estimate for capacity though without knowing the app in question, expected query composition, and so forth. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] firebird X postgresql 8.1.2 windows, performance comparison
Andre Felipe Machado wrote: Postgresql uses around 30% cpu and hard disk heavily (not so as vacuum) at all executions. Firebird uses around 40% cpu and hard disk heavily at the first execution. The second execution uses around 60% cpu and **NO** disk activity. The previously cited query running at 26 miliseconds down from 10 minutes, can achieve this performance at the second run, with **NO** disk activity. At the first run it uses 1,7 seconds, down from 10 minutes. The hard disk is clearly a bottleneck. 1,7 seconds against 26 miliseconds. So, How convince postgresql to use windows disk cache or to read all indexes to ram? It seems that effective_cache_size does not tell postgresql to actually use windows disk cache. What parameter must be configured? Do you have some suggestions? Assuming these are selects and that you have already vacuumed, etc. Look at memory useage. It seems likely that you have a difference in caching behavior. PostgreSQL has its own cache, and failing that will use the OS disk cache. So there may be a number of possible issues involved including whether the data is staying in the OS cache, how much memory is being used for caching, etc. It is also likely that the Windows version of PostgreSQL may have some issues in these areas that the UNIX/Linux versions may not simply because it is more immature. You might even try a vacuum full to retrieve space. This may mean smaller tables, more likely to remain in disk cache, etc. But that would not provide any indication of scalability. Best Wishes, Chris Travers Metatron Technology Consulting begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] tel;work:509-888-0220 tel;cell:509-630-9974 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Massive performance issues
Akshay Mathur wrote: Ron, Can you give me some pointers to make the tables RAM resident. If one does so, is the program accessing the data need to change. Does pgsql take care to write the data to disk? PostgreSQL tried to intelligently cache information and then will also use the OS disk cache as a secondary cache. So a sufficiently small and frequently accessed table will be resident in RAM. The simplest way to affect this calculus is to put more RAM in the machine. There are hacks I can think of to create RAM caches of specific tables, but I don't want to take responsibility for anyone trying these and running into trouble. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Need indexes on empty tables for good performance ?
Lenard, Rohan (Rohan) wrote: I've read that indexes aren't used for COUNT(*) and I've noticed (7.3.x) with EXPLAIN that indexes never seem to be used on empty tables - is there any reason to have indexes on empty tables, or will postgresql never use them. You could add a row, vacuum analyze, delete the row, etc Then you are fine until you vacuum analyze again ;-) This is a feature designed to prevent really bad plans when you are loading tables with data. However, you are right. It can create bad plans sometimes. Any chance one can eventually come up with a way to tell the planner that an empty table is expected not to grow? Otherwise, I can see nightmares in a data warehouse environment where you have an empty parent table... Best Wishes, Chris Travers Metatron Technology Consulting begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Need indexes on empty tables for good performance ?
Lenard, Rohan (Rohan) wrote: Actually the indexes on the child table do seem to get used - I just wanted to make sure there was no penalty not having indexes on the empty parent tables. You are right - the parent is the best way to get at the unknown children ... Indexes are created in the inheritance process, iirc. However, index entries are not inherited, which means that index-based unique constraints don't properly get inherited. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance for relative large DB
tobbe wrote: Hi Chris. Thanks for the answer. Sorry that i was a bit unclear. 1) We update around 20.000 posts per night. 2) What i meant was that we suspect that the DBMS called PervasiveSQL that we are using today is much to small. That's why we're looking for alternatives. Today we base our solution much on using querry-specific tables created at night, so instead of doing querrys direct on the post table (with 4-6M rows) at daytime, we have the data pre-aligned in several much smaller tables. This is just to make the current DBMS coop with our amount of data. What I am particulary interested in is if we can expect to run all our select querrys directly from the post table with PostgreSQL. 20k transactions per day? Doesn't seem too bad. That amounts to how many transactions per second during peak times? Personally I don't think it will be a problem, but you might want to clarify what sort of load you are expecting during its peak time. 3) How well does postgres work with load balancing environments. Is it built-in? There is no load balancing built in. You would need to use Slony-I and possibly Pg-Pool for that. I don't know about Pg-Pool, but Slony-I was written in large part by member(s?) of the core development team so even if it is not built in it is not as if it is a team of outsiders who wrote it. If you need something proprietary, there are similar solutions with replication built in which are based on PostgreSQL and licensed under proprietary licenses. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Left joining against two empty tables makes a query
Gnanavel S wrote: vacuum reindex the department and project table as the planner expects there are 1060 rows but actually returning nothing. I guess I should have mentioned that I have been vacuuming and reindexing at least once a week, and I did so just before running this test. Normally I do: vacuum analyze; reindex database ; Secondly, the project table has *never* had anything in it. So where are these numbers coming from? Best Wishes, Chris Travers Metatron Technology Consulting ---(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] Left joining against two empty tables makes a query
Secondly, the project table has *never* had anything in it. So where are these numbers coming from? pg_statistics I very much doubt that. I was unable to locate any rows in pg_statistic where the pg_class.oid for either table matched any row's starelid. Tom's argument that this is behavior by design makes sense. I assumed that something like that had to be going on, otherwise there would be nowhere for the numbers to come from. I.e. if there never were any rows in the table, then if pg_statistic is showing 1060 rows, we have bigger problems than a bad query plan. I hope however that eventually tables which are truly empty can be treated intelligently sometime in the future in Left Joins. Otherwise this limits the usefulness of out of the box solutions which may have functionality that we don't use. Such solutions can then kill the database performance quite easily. Chris Travers Metatron Technology Consulting ---(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] Left joining against two empty tables makes a query SLOW
=716.67..728.40 rows=8 width=8) (actual time=130.692..130.805 rows=10 loops=1) - Nested Loop (cost=716.67..720.89 rows=1 width=8) (actual time=130.626..130.639 rows=1 loops=1) - HashAggregate (cost=716.67..716.67 rows=1 width=4) (actual time=130.484..130.487 rows=1 loops=1) - Subquery Scan IN_subquery (cost=716.66..716.67 rows=1 width=4) (actual time=130.455..130.464 rows=1 loops=1) - Aggregate (cost=716.66..716.66 rows=1 width=4) (actual time=130.445..130.448 rows=1 loops=1) - Seq Scan on ar (cost=0.00..659.55 rows=22844 width=4) (actual time=0.020..74.174 rows=22844 loops=1) Filter: (customer_id = 11373) - Index Scan using ar_id_key on ar a (cost=0.00..4.20 rows=1 width=4) (actual time=0.122..0.125 rows=1 loops=1) Index Cond: (a.id = outer.max) Filter: (customer_id = 11373) - Index Scan using acc_trans_trans_id_key on acc_trans ac (cost=0.00..7.41 rows=8 width=12) (actual time=0.051..0.097 rows=10 loops=1) Index Cond: (outer.max = ac.trans_id) Total runtime: 131.879 ms (17 rows) I am not sure if I want to remove support for the other two tables yet. However, I wanted to submit this here as a (possibly corner-) case where the plan seems to be far slower than it needs to be. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Trigger question
Exists in pg any way to define the trigger execution only if I have changes on some fields? No, but you chould check for those fields and return if no changes have been made. Depending on how intensive the trigger is, this might help. You may also want to look at statement-level triggers or conditional rules. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] about performance of postgreSQL
Hi Xin; PostgreSQL is configured to run on virutally anything out of the box. The reason for this is that, like Oracle, the database manager will not start if it cannot allocate sufficient resources. We take the approach of ensuring that it will start so you can tune it. I would recomment trying to take a close look at many of the posts on the Performance list (searching the archives) and paying attention to things such as effective_cache_size and shared_buffers. If these don't answer your questions, ask this list again. Best Wishes, Chris Travers xin fu wrote: Dear master: I have learned postgreSQL for serveral days, now i meet some problems. when I use a TPCC(Transaction Processing Performance Council) test program to test the performance of postgreSQL , postgreSQL works very slowly, it almost need 1 minute to finish a transaction, and the CPU percent is almost 100%, the test environment is : OS: redhat 9.0(ext3, default configurations) Server: postgre7.3.4(default configurations) , PIII 800M, 1G Memory Client: tpcc test program,using ODBC API, PIII 800M, 1G Memory when using SQLServer, it can work on a workload of 40 Warehouse, but postgreSQL can not work even on 1 warehouse. I think there must be some problem with my postgreSQL, can you help me? I am in china, and my english is very poor, but i hope you can give me some advice, thanks. *Do You Yahoo!?* + http://cn.rd.yahoo.com/mail_cn/tag/?http://cn.promo.yahoo.com/minisite/messenger1/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Moving postgresql.conf tunables into 2003...
Matthew Nuzum wrote: I'm highly resistant to/disappointed in this attitude and firmly believe that there are well understood algorithms that DBAs use to diagnose and solve performance problems. It's only a black art because it hasn't been documented. Performance tuning isn't voodoo, it's adjusting constraints to align with the execution of applications and we know what the applications do, therefore the database can mold to the applications' needs. I agree. We often seem to forget simple lessons in human nature. Expecting someone to spend 20 extra seconds to do something is often too much. In many cases, the only manual that a person will see is the .conf files. In my opinion, a serious RDBMS system will *always* require the admin to be doing research in order to learn how to use it effectively. We are not talking about a word processor here. That being said, I think that a good part of the problem is that admins don't know where to look for the appropriate documentation and what is needed. Expecting someone to spend 20 seconds looking for a piece of info is not too bad, but expecting them to spend hours trying to figure out what info is relavent is not going to get us anywhere. For those who have been following the discussion relating to MySQL vs PostgreSQL, I think this is relavent here. MySQL does much of its tuning at compile time, and the MySQL team very carefully controls the build process for the various binary distriutions they offer. If you want to see a real mess, try compiling MySQL from source. Talk about having to read documentation on items which *should* be handled by the configure script. OTOH, PostgreSQL is optomized using configuration files and is tunable on the fly. This is, I think, a better approach but it needs to be better documented. Maybe a Beginner's guide to database server tuning or something like that. Secondly, documenting the tuning algorythms well my allow PostgreSQL to automatically tune itself to some extent or for the development of performance tuning tools for the server. This would be a big win for the project. Unfortunately I am not knowledgable on this topic to really do this subject justice. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
