Re: [PERFORM] index on two tables or Howto speedup max/aggregate-function
Hi, CREATE INDEX mail_id_sent_idx ON mail(id,sent) should do the trick? Of course you can only replace one of the two scans by an index scan since there are no other conditions... Jacques. At 09:59 13/10/2009, Michael Schwipps wrote: Hi, I want to select the last contact of person via mail. My sample database is build with the following shell-commands | createdb -U postgres test2 | psql -U postgres test2 mail_db.sql | mailtest.sh | psql -U postgres I call to get the answer | SELECT address, max(sent) from mail inner join | tomail on (mail.id=tomail.mail) group by address; The result is ok, but it's to slow. The query plan, see below, tells that there two seq scans. Howto transforms them into index scans? postgres ignores simple indexes on column sent. An Index on two tables is not possible (if I understand the manual correctly). Any other idea howto speed up? Ciao Michael === test2=# explain analyze SELECT address, max(sent) from mail inner join tomail on (mail.id=tomail.mail) group by address; QUERY PLAN --- HashAggregate (cost=36337.00..36964.32 rows=50186 width=20) (actual time=3562.136..3610.238 rows=5 loops=1) - Hash Join (cost=14191.00..33837.00 rows=50 width=20) (actual time=1043.537..2856.933 rows=50 loops=1) Hash Cond: (tomail.mail = mail.id) - Seq Scan on tomail (cost=0.00..8396.00 rows=50 width=20) (actual time=0.014..230.264 rows=50 loops=1) - Hash (cost=7941.00..7941.00 rows=50 width=8) (actual time=1042.996..1042.996 rows=50 loops=1) - Seq Scan on mail (cost=0.00..7941.00 rows=50 width=8) (actual time=0.018..362.101 rows=50 loops=1) Total runtime: 3629.449 ms (7 rows) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Insert performance vs Table size
Hi, At 11:50 28/06/2005, Praveen Raja wrote: I assume you took size to mean the row size? Nope, the size of the table. What I really meant was does the number of rows a table has affect the performance of new inserts into the table (just INSERTs) all other things remaining constant. Sorry for the confusion. As I said previously, in most cases it does. One of the few cases where it doesn't would be an append-only table, no holes, no indexes, no foreign keys... I know that having indexes on the table adds an overhead but again does this overhead increase (for an INSERT operation) with the number of rows the table contains? It depends on what you are indexing. If the index key is something that grows monotonically (e.g. a unique ID or a timestamp), then the size of the table (and hence of the indexes) should have a very limited influence on the INSERTs. If the index key is anything else (and that must definitely be the case if you have 7 or 8 indexes!), then that means updates will happen all over the indexes, which means a lot of read and write activity, and once the total size of your indexes exceeds what can be cached in RAM, performance will decrease quite a bit. Of course if your keys are concentrated in a few limited areas of the key ranges it might help. My instinct says no to both. If I'm wrong can someone explain why the number of rows in a table affects INSERT performance? As described above, maintaining indexes when you hit anywhere in said indexes is very costly. The larger the table, the larger the indexes, the higher the number of levels in the trees, etc. As long as it fits in RAM, it shouldn't be a problem. Once you exceed that threshold, you start getting a lot of random I/O, and that's expensive. Again, it depends a lot on your exact schema, the nature of the data, the spread of the different values, etc, but I would believe it's more often the case than not. Jacques. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Insert performance vs Table size
Hi, At 13:24 27/06/2005, Praveen Raja wrote: I'm wondering if and how the size of a table affects speed of inserts into it? What if the table has indexes, does that alter the answer? Many parameters will affect the result: - whether there are any indexes (including the primary key, unique constraints...) to update or not - whether there are any foreign keys from or to that table - the size of the rows - whether the table (or at least the bits being updated) fit in RAM or not - whether the table has holes (due to former updates/deletes and vacuum) and how they are placed - and probably a bunch of other things... Obviously, if you have an append-only (no updates, no deletes) table with no indexes and no foreign keys, the size of the table should not matter much. As soon as one of those conditions is not met table size will have an impact, probably small as long as whatever is needed can be held in RAM, a lot bigger once it's not the case. Hope that helps, Jacques. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Insert performance vs Table size
Hi, At 13:50 27/06/2005, Praveen Raja wrote: Just to clear things up a bit, the scenario that I'm interested in is a table with a large number of indexes on it (maybe 7-8). If you're after performance you'll want to carefully consider which indexes are really useful and/or redesign your schema so that you can have less indexes on that table. 7 or 8 indexes is quite a lot, and that really has a cost. In this scenario other than the overhead of having to maintain the indexes (which I'm guessing is the same regardless of the size of the table) Definitely not: indexes grow with the size of the table. Depending on what columns you index (and their types), the indexes may be a fraction of the size of the table, or they may be very close in size (in extreme cases they may even be larger). With 7 or 8 indexes, that can be quite a large volume of data to manipulate, especially if the values of the columns inserted can span the whole range of the index (rather than being solely id- or time-based, for instance, in which case index updates are concentrated in a small area of each of the indexes), as this means you'll need to have a majority of the indexes in RAM if you want to maintain decent performance. does the size of the table play a role in determining insert performance (and I mean only insert performance)? In this case, it's really the indexes that'll cause you trouble, though heavily fragmented tables (due to lots of deletes or updates) will also incur a penalty just for the data part of the inserts. Also, don't forget the usual hints if you are going to do lots of inserts: - batch them in large transactions, don't do them one at a time - better yet, use COPY rather than INSERT - in some situations, you might be better of dropping the indexes, doing large batch inserts, then re-creating the indexes. YMMV depending on the existing/new ratio, whether you need to maintain indexed access to the tables, etc. - pay attention to foreign keys Jacques. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] autovacuum suggestions for 500,000,000+ row
Hi, At 16:44 20/06/2005, Alex Stapleton wrote: We never delete anything (well not often, and not much) from the tables, so I am not so worried about the VACUUM status DELETEs are not the only reason you might need to VACUUM. UPDATEs are important as well, if not more. Tables that are constantly updated (statistics, session data, queues...) really need to be VACUUMed a lot. but I am wary of XID wraparound nuking us at some point if we don't sort vacuuming out so we VACUUM at least once every year ;) That would give you a maximum average of 31 transactions/sec... Don't know if that's high or low for you. However not running ANALYZE for such huge periods of time is probably impacting the statistics accuracy somewhat, and I have seen some unusually slow queries at times. Anyway, does anyone think we might benefit from a more aggressive autovacuum configuration? ANALYZE is not a very expensive operation, however VACUUM can definitely be a big strain and take a long time on big tables, depending on your setup. I've found that partitioning tables (at the application level) can be quite helpful if you manage to keep each partition to a reasonable size (under or close to available memory), especially if the partitioning scheme is somehow time-related. YMMV. Jacques. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] investigating slow queries through
Hi, At 19:55 20/06/2005, Dan Harris wrote: Also, I'm sure some people will respond with turn on query logging.. I've explored that option and the formatting of the log file and the fact that EVERY query is logged is not what I'm after for this project. You can log just those queries that take a little bit too much time. See log_min_duration_statement in postgresql.conf. Set it really high, and you'll only get those queries you're after. Jacques. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Multiple disks: RAID 5 or PG Cluster
Hi, At 18:00 18/06/2005, PFC wrote: I don't know what I'm talking about, but wouldn't mirorring be faster than striping for random reads like you often get on a database ? (ie. the reads can be dispatched to any disk) ? (or course, not for writes, but if you won't use fsync, random writes should be reduced no ?) Roughly, for random reads, the performance (in terms of operations/s) compared to a single disk setup, with N being the number of drives, is: RAID 0 (striping): - read = N - write = N - capacity = N - redundancy = 0 RAID 1 (mirroring, N=2): - read = N - write = 1 - capacity = 1 - redundancy = 1 RAID 5 (striping + parity, N=3) - read = N-1 - write = 1/2 - capacity = N-1 - redundancy = 1 RAID 10 (mirroring + striping, N=2n, N=4) - read = N - write = N/2 - capacity = N/2 - redundancy N/2 So depending on your app, i.e. your read/write ratio, how much data can be cached, whether the data is important or not, how much data you have, etc, one or the other option might be better. Jacques. ---(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] Updates on large tables are extremely slow
Hi, At 19:22 13/06/2005, Yves Vindevogel wrote: It can't be indexes on other tables, right ? It could be foreign keys from that table referencing other tables or foreign keys from other tables referencing that table, especially if you don't have the matching indexes... Jacques. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Updates on large tables are extremely slow
Hi, At 19:40 12/06/2005, Yves Vindevogel wrote: Hi, I'm trying to update a table that has about 600.000 records. The update query is very simple :update mytable set pagesdesc = - pages ; (I use pagesdesc to avoid problems with sort that have one field in ascending order and one in descending order. That was a problem I had a week ago) An index on (-pages) would probably do exactly what you want without having to add another column. The query takes about half an hour to an hour to execute. Depending on the total size of the table and associated indexes and on your exact setup (especially your hardare), this could be quite normal: the exuctor goes through all rows in the table, and for each, creates a copy with the additional column, updates indexes, and logs to WAL. You might want to look into moving your WAL files (pg_xlog) to a separate disk, increase WAL and checkpoint buffers, add more RAM, add more disks... But as I said, you might not even need to do that, just use an index on an expression... Jacques. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Index ot being used
Hi, At 18:10 10/06/2005, [EMAIL PROTECTED] wrote: tle-bu= EXPLAIN ANALYZE SELECT file_type, file_parent_dir, file_name FROM file_info_7; What could the index be used for? Unless you have some WHERE or (in some cases) ORDER BY clause, there's absolutely no need for an index, since you are just asking for all rows from the table... Jacques. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] How to improve db performance with $7K?
Hi, At 18:56 18/04/2005, Alex Turner wrote: All drives are required to fill every request in all RAID levels No, this is definitely wrong. In many cases, most drives don't actually have the data requested, how could they handle the request? When reading one random sector, only *one* drive out of N is ever used to service any given request, be it RAID 0, 1, 0+1, 1+0 or 5. When writing: - in RAID 0, 1 drive - in RAID 1, RAID 0+1 or 1+0, 2 drives - in RAID 5, you need to read on all drives and write on 2. Otherwise, what would be the point of RAID 0, 0+1 or 1+0? Jacques. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] How to improve db performance with $7K?
Hi, At 16:59 18/04/2005, Greg Stark wrote: William Yu [EMAIL PROTECTED] writes: Using the above prices for a fixed budget for RAID-10, you could get: SATA 7200 -- 680MB per $1000 SATA 10K -- 200MB per $1000 SCSI 10K -- 125MB per $1000 What a lot of these analyses miss is that cheaper == faster because cheaper means you can buy more spindles for the same price. I'm assuming you picked equal sized drives to compare so that 200MB/$1000 for SATA is almost twice as many spindles as the 125MB/$1000. That means it would have almost double the bandwidth. And the 7200 RPM case would have more than 5x the bandwidth. While 10k RPM drives have lower seek times, and SCSI drives have a natural seek time advantage, under load a RAID array with fewer spindles will start hitting contention sooner which results into higher latency. If the controller works well the larger SATA arrays above should be able to maintain their mediocre latency much better under load than the SCSI array with fewer drives would maintain its low latency response time despite its drives' lower average seek time. I would definitely agree. More factors in favor of more cheap drives: - cheaper drives (7200 rpm) have larger disks (3.7 diameter against 2.6 or 3.3). That means the outer tracks hold more data, and the same amount of data is held on a smaller area, which means less tracks, which means reduced seek times. You can roughly count the real average seek time as (average seek time over full disk * size of dataset / capacity of disk). And you actually need to physicall seek less often too. - more disks means less data per disk, which means the data is further concentrated on outer tracks, which means even lower seek times Also, what counts is indeed not so much the time it takes to do one single random seek, but the number of random seeks you can do per second. Hence, more disks means more seeks per second (if requests are evenly distributed among all disks, which a good stripe size should achieve). Not taking into account TCQ/NCQ or write cache optimizations, the important parameter (random seeks per second) can be approximated as: N * 1000 / (lat + seek * ds / (N * cap)) Where: N is the number of disks lat is the average rotational latency in milliseconds (500/(rpm/60)) seek is the average seek over the full disk in milliseconds ds is the dataset size cap is the capacity of each disk Using this formula and a variety of disks, counting only the disks themselves (no enclosures, controllers, rack space, power, maintenance...), trying to maximize the number of seeks/second for a fixed budget (1000 euros) with a dataset size of 100 GB makes SATA drives clear winners: you can get more than 4000 seeks/second (with 21 x 80GB disks) where SCSI cannot even make it to the 1400 seek/second point (with 8 x 36 GB disks). Results can vary quite a lot based on the dataset size, which illustrates the importance of staying on the edges of the disks. I'll try to make the analysis more complete by counting some of the overhead (obviously 21 drives has a lot of other implications!), but I believe SATA drives still win in theory. It would be interesting to actually compare this to real-world (or nearly-real-world) benchmarks to measure the effectiveness of features like TCQ/NCQ etc. Jacques. ---(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] How to improve db performance with $7K?
Hi, At 20:16 18/04/2005, Alex Turner wrote: So my assertion that adding more drives doesn't help is pretty wrong... particularly with OLTP because it's always dealing with blocks that are smaller that the stripe size. When doing random seeks (which is what a database needs most of the time), the number of disks helps improve the number of seeks per second (which is the bottleneck in this case). When doing sequential reads, the number of disks helps improve total throughput (which is the bottleneck in that case). In short: in always helps :-) Jacques. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] How to improve db performance with $7K?
Hi, At 20:21 18/04/2005, Alex Turner wrote: So I wonder if one could take this stripe size thing further and say that a larger stripe size is more likely to result in requests getting served parallized across disks which would lead to increased performance? Actually, it would be pretty much the opposite. The smaller the stripe size, the more evenly distributed data is, and the more disks can be used to serve requests. If your stripe size is too large, many random accesses within one single file (whose size is smaller than the stripe size/number of disks) may all end up on the same disk, rather than being split across multiple disks (the extreme case being stripe size = total size of all disks, which means concatenation). If all accesses had the same cost (i.e. no seek time, only transfer time), the ideal would be to have a stripe size equal to the number of disks. But below a certain size, you're going to use multiple disks to serve one single request which would not have taken much more time from a single disk (reading even a large number of consecutive blocks within one cylinder does not take much more time than reading a single block), so you would add unnecessary seeks on a disk that could have served another request in the meantime. You should definitely not go below the filesystem block size or the database block size. There is a interesting discussion of the optimal stripe size in the vinum manpage on FreeBSD: http://www.freebsd.org/cgi/man.cgi?query=vinumapropos=0sektion=0manpath=FreeBSD+5.3-RELEASE+and+Portsformat=html (look for Performance considerations, towards the end -- note however that some of the calculations are not entirely correct). Basically it says the optimal stripe size is somewhere between 256KB and 4MB, preferably an odd number, and that some hardware RAID controllers don't like big stripe sizes. YMMV, as always. Jacques. ---(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
[PERFORM] Use of data within indexes
Hi, Just wondering... Is Postgresql able to use data present within indexes without looking up the table data? To be more explicit, let's say I have table with two fields a and b. If I have an index on (a,b) and I do a request like SELECT b FROM table WHERE a=x, will Postgresql use only the index, or will it need to also read the table page for that (those) row(s)? There might be a reason why this is not possible (I don't know if the indexes have all necessary transaction ID information?) but otherwise this could possibly provide an interesting performance gain for some operations, in particular with some types of joins. Or maybe it already does it. Any hint welcome! Thanks, Jacques. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Sluggish server performance
Hi, At 20:20 28/03/2005, Patrick Hatcher wrote: I'm experiencing extreme load issues on my machine anytime I have more than 40 users connected to the database. The majority of the users appear to be in an idle state according TOP, but if more than3 or more queries are ran the system slows to a crawl. The queries don't appear to the root cause because they run fine when the load drops. I also doing routine vacuuming on the tables. Is there some place I need to start looking for the issues bogging down the server? Check that your queries use optimal plans, which usually (but not always) means they should use indexes rather than sequential scans. You can check for this by using EXPLAIN query or EXPLAIN ANALYZE query. You can also check the pg_stat_* and pg_statio_* tables to get a feel of what kind of accesses are done. You also might want to find out if your system is limited by IO or by the CPU. Most probably the former. You can also check the performance tips section of the manual. Also you shared_buffers setting seems to be pretty low given your configuration. Hope that helps, Jacques. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Performance tuning
Hi all, I'm preparing a set of servers which will eventually need to handle a high volume of queries (both reads and writes, but most reads are very simple index-based queries returning a limited set of rows, when not just one), and I would like to optimize things as much as possible, so I have a few questions on the exact way PostgreSQL's MVCC works, and how transactions, updates and vacuuming interact. I hope someone will be able to point me in the right direction (feel free to give pointers if I missed the places where this is described). From what I understand (and testing confirms it), bundling many queries in one single transaction is more efficient than having each query be a separate transaction (like with autocommit on). However, I wonder about the limits of this: - are there any drawbacks to grouping hundreds or thousands of queries (inserts/updates) over several minutes in one single transaction? Other than the fact that the inserts/updates will not be visible until committed, of course. Essentially turning autocommit off, and doing a commit once in a while. - does this apply only to inserts/selects/updates or also for selects? Another way to put this is: does a transaction with only one select actually have much transaction-related work to do? Or, does a transaction with only selects actually have any impact anywhere? Does it really leave a trace anywhere? Again, I understand that selects grouped in a transaction will not see updates done after the start of the transaction (unless done by the same process). - if during a single transaction several UPDATEs affect the same row, will MVCC generate as many row versions as there are updates (like would be the case with autocommit) or will they be grouped into one single row version? Another related issue is that many of the tables are indexed on a date field, and one process does a lot of updates on recent rows (which lead to many dead tuples), but after that older rows tend to remain pretty much unchanged for quite a while. Other than splitting the tables into old and recent tables, is there any way to make vacuum more efficient? Scanning the whole table for dead tuples when only a small portion of the table actually has any does not feel like being very efficient in this situation. Other issue: every five minutes or so, I see a noticeable performance drop as PostgreSQL checkpoints. This is 7.4.3 with pretty lousy hardware, I know 8.0 with decent hardware and separate disk(s) for pg_xlog will definitely help, but I really wonder if there is any way to reduce the amount of work that needs to be done at that point (I'm a strong believer of fixing software before hardware). I have already bumped checkpoint_segments to 8, but I'm not quite sure I understand how this helps (or doesn't help) things. Logs show 3 to 6 recycled transaction log file lines at that time, that seems quite a lot of work for a load that's still pretty low. Does grouping of more queries in transactions help with this? Are there other parameters that can affect things, or is just a matter of how much inserts/updates/deletes are done, and the amount of data that was changed? Last point: some of the servers have expandable data (and will be replicated with slony-I) and will run with fsync off. I have read conflicting statements as to what exactly this does: some sources indicate that setting fsync off actually switches off WAL/checkpointing, others that it just prevents the fsync (or equivalent) system calls. Since I still see checkpointing in that case, I guess it's not exactly the former, but I would love to understand more about it. Really, I would love to be able to set some tables or databases to go as fast as you can and don't worry about transactions, MVCC or anything like that, but I'm not sure that option exists... Thanks, Jacques. ---(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
