[PERFORM] filesystems benchmark
Hi I'm trying to compare different filesystems for postgres using pgbench. The problem I've seen is that my IO wait is very very low. Is there a way I can get pgbench to do more to push that up a little? -- Adrian Moisey Acting Systems Designer | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: adr...@careerjunction.co.za Phone: +27 21 818 8621 | Mobile: +27 82 858 7830 | Fax: +27 21 818 8855 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] connections slowing everything down?
Hi # ps -ef | grep idle | wc -l 87 # ps -ef | grep SELECT | wc -l 5 I have 2 web servers which connect to PGPool which connects to our postgres db. I have noticed that idle connections seem to take up CPU and RAM (according to top). Could this in any way cause things to slow down? -- Adrian Moisey Systems Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 -- 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] connections slowing everything down?
Hi # ps -ef | grep idle | wc -l 87 [...] I have 2 web servers which connect to PGPool which connects to our postgres db. I have noticed that idle connections seem to take up CPU and RAM (according to top). Could this in any way cause things to slow down? Dependant on how much memory you have in your system, yes. You can fix the constant use of memory by idle connections by adjusting the child_life_time setting in your pgpool.conf file. The default if 5 minutes which a bit long. Try dropping that down to 20 or 30 seconds. We have 32GBs. If I get it to close the connections faster, will that actually help? Is there a way i can figure it out? -- Adrian Moisey Systems Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 -- 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] db size
Hi Running VACUUM VERBOSE will give you a detailed view of space usage of each individual table. I did that. Not too sure what I'm looking for, can someone tell me what this means: INFO: blahxxx: scanned 27 of 27 pages, containing 1272 live rows and 0 dead rows; 1272 rows in sample, 1272 estimated total rows INFO: free space map contains 4667977 pages in 1199 relations DETAIL: A total of 4505344 page slots are in use (including overhead). 4505344 page slots are required to track all free space. Current limits are: 15537488 page slots, 1200 relations, using 91172 kB. -- Adrian Moisey Systems Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 -- 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] db size
Hi You are tracking ~ 4.6 million pages and have space to track ~ 15.5 million, so that's fine. You are right up against your limit of relations (tables, indexes etc) being tracked though - 1200. You'll probably want to increase max_fsm_relations - see manual for details (server configuration / free space map). That is helpful, thanks. I did a grep on the output to find out more about the max_fsm_relations: INFO: free space map contains 2333562 pages in 832 relations INFO: free space map contains 3012404 pages in 544 relations INFO: free space map contains 3012303 pages in 654 relations INFO: free space map contains 3012345 pages in 669 relations INFO: free space map contains 3012394 pages in 678 relations INFO: free space map contains 3017248 pages in 717 relations INFO: free space map contains 2860737 pages in 824 relations INFO: free space map contains 4667977 pages in 1199 relations INFO: free space map contains 3140238 pages in 181 relations INFO: free space map contains 3140322 pages in 182 relations INFO: free space map contains 3140387 pages in 183 relations INFO: free space map contains 3142781 pages in 184 relations It doesn't go up close to 1200 often... should I still up that value? -- Adrian Moisey Systems Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 -- 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] db size
Hi INFO: blahxxx: scanned 27 of 27 pages, containing 1272 live rows and 0 dead rows; 1272 rows in sample, 1272 estimated total rows This is a small table that takes up 27 pages and it scanned all of them. You have 1272 rows in it and none of them are dead (i.e. deleted/updated but still taking up space). I had a look through a few other tables...: INFO: table1: scanned 22988 of 22988 pages, containing 2713446 live rows and 895662 dead rows; 45000 rows in sample, 2713446 estimate d total rows INFO: table2: scanned 24600 of 24600 pages, containing 270585 live rows and 65524 dead rows; 45000 rows in sample, 270585 estimated total rows Is that dead rows an issue? Should I try clean it out? Will it improve performance ? -- Adrian Moisey Systems Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 -- 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] db size
Hi Now, is the bloat in the tables (which tables ?) or in the indexes (which indexes ?), or in the toast tables perhaps, or in the system catalogs or all of the above ? Or perhaps there is a long-forgotten process that got zombified while holding a huge temp table ? (not very likely, but who knows). Use pg_relation_size() and its friends to get an idea of the size of stuff. Can anybody give me some advice on the above? I'm not sure where to start looking or how to start looking -- Adrian Moisey Systems Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] db size
Hi We currently have a 16CPU 32GB box running postgres 8.2. When I do a pg_dump with the following parameters /usr/bin/pg_dump -E UTF8 -F c -b I get a file of 14GB in size. But the database is 110GB in size on the disk. Why the big difference in size? Does this have anything to do with performance? -- Adrian Moisey Systems Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 -- 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] db size
Hi If you suspect your tables or indexes are bloated, restore your dump to a test box. Use fsync=off during restore, you don't care about integrity on the test box. This will avoid slowing down your production database. Then look at the size of the restored database. If it is much smaller than your production database, then you have bloat. I have done that, and I get the following: the live one is 113G the restored one is 78G How should I get rid of the bloat? VACUUM FULL? -- Adrian Moisey Systems Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 -- 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] db size
Hi the live one is 113G the restored one is 78G Good news for you is that you know that you can do something ;) :) Will this help with performance ? Now, is the bloat in the tables (which tables ?) or in the indexes (which indexes ?), or in the toast tables perhaps, or in the system catalogs or all of the above ? Or perhaps there is a long-forgotten process that got zombified while holding a huge temp table ? (not very likely, but who knows). Use pg_relation_size() and its friends to get an idea of the size of stuff. I'll look into that, thanks -- Adrian Moisey Systems Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] analyze
Hi How long is a piece of string? While we're at it, how often do I vacuum analyze? Seriously though, how do I try measure this? -- Adrian Moisey System Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] scheduler
Hi Which scheduler is recommended for a box that is dedicated to running postgres? I've asked google and found no answers. Is it the OS itself? Yes, in linux. I've found that cfq or deadline is best, but I haven't seen anyone try a benchmark -- Adrian Moisey System Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] strange pauses
Hi Perhaps, if you want to avoid I/O caused by temp tables (but it's not at checkpoint time, so perhaps this has nothing to do with your problem), you could try raising temp_buffers. How can I find out if temp_buffers is being exceeded ? -- Adrian Moisey System Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] scheduler
Hi Which scheduler is recommended for a box that is dedicated to running postgres? I've asked google and found no answers. -- Adrian Moisey System Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] strange pauses
Hi If I tail the logs, I see a steady stream of functions that took longer then 5 seconds. But every now and then I see nothing for 3 minutes and after that a whole bunch (about 20 - 30) queries all finishing at the same second. Search this list for references to checkpoints. If you run vmstat/iostat for a bit you should see bursts of disk activity at those times. Counter-intuitively you probably want *more* checkpoints (with less data to write out each time), but see the list archives for discussion. Also check the manuals for some background info. We use a lot of checkpoints in our database, we don't care about these much. We think this is causing the checkpoint to write a lot of data at checkpoint time. Can we stop this behavior ? -- Adrian Moisey System Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] strange pauses
Hi If I tail the logs, I see a steady stream of functions that took longer then 5 seconds. But every now and then I see nothing for 3 minutes and after that a whole bunch (about 20 - 30) queries all finishing at the same second. Search this list for references to checkpoints. If you run vmstat/iostat for a bit you should see bursts of disk activity at those times. Counter-intuitively you probably want *more* checkpoints (with less data to write out each time), but see the list archives for discussion. Also check the manuals for some background info. We use a lot of checkpoints in our database, we don't care about these We use a lot of temporary tables in our database, sorry. much. We think this is causing the checkpoint to write a lot of data at checkpoint time. Can we stop this behavior ? -- Adrian Moisey System Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] strange pauses
Hi I've noticed a strange pattern in our logs. We only log anything that takes longer then 5 seconds. If I tail the logs, I see a steady stream of functions that took longer then 5 seconds. But every now and then I see nothing for 3 minutes and after that a whole bunch (about 20 - 30) queries all finishing at the same second. During this 3 minutes of nothing being logged, the db is still active and I can access our website Looks to me like something is locking and causing requests to be delayed. Once this lock unlocks, everything goes through. Sorry for the bad description, can anybody help? Where do I start looking? -- Adrian Moisey System Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] strange pauses
Hi Search this list for references to checkpoints. If you run vmstat/iostat for a bit you should see bursts of disk activity at those times. The most straightforward way to prove or disprove that the slow queries line up with checkpoints is to set to checkpoint_warning to a high value (3600 should work), which should log every checkpoint, and then see if they show up at the same time in the logs. You guys were spot on. During these pauses the IO goes up high. I've got the following set: checkpoint_timeout = 5min checkpoint_warning = 3600s log_min_messages = info But I see nothing in the logs about checkpoints -- Adrian Moisey System Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 ---(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] Linux/PostgreSQL scalability issue - problem with 8 cores
Hi I can think of three things that might be producing this: we've found it: TRUNCATE I haven't been following this thread. Can someone please explain to me why TRUNCATE causes these spikes? -- Adrian Moisey System Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] big database performance
Hi Also, we're running the db on ext3 with noatime. Should I look at changing or getting rid of journaling ? No (unless you like really long fsck times). data=writeback is safe with PostgreSQL, though. I tested that on a dev box, and I didn't notice a difference when using pgbench -- Adrian Moisey System Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 ---(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] big database performance
Hi I do large databases in Pg, like 300GB/day of new data. Need a lot more data on what you're having issues with. That is big! What sort of information do you need from me ? Is your problem with performance database reads? writes? (insert/copy?) How many indicies do you have? I think the problem is related to load. Everything is slow because there are way too many connections. So everything is making everything else slow. Not much detail, is it? We have 345 indicies on the db. -- Adrian Moisey System Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PERFORM] big database performance
Hi We recently converted to postgres (from mssql) and we're having performance issues. Not all the issues are related to postgres, but we're trying to sort everything out. The server is running ubuntu Gutsy with the database stored on a IBM SAN. It is a Dell box with dual quad core 2.00GHz Xeons and 8GB RAM. The database is about 71GB in size. I've looked at the postgres config files and we've tweaked as much as our knowledge allows. Can someone shed some light on the settings I should use ? Thanks in advance -- Adrian Moisey System Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 ---(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] big database performance
Hi We recently converted to postgres (from mssql) and we're having performance issues. Not all the issues are related to postgres, but we're trying to sort everything out. The server is running ubuntu Gutsy with the database stored on a IBM SAN. It is a Dell box with dual quad core 2.00GHz Xeons and 8GB RAM. The database is about 71GB in size. I've looked at the postgres config files and we've tweaked as much as our knowledge allows. Can someone shed some light on the settings I should use ? Umpf that isn't quite enough info :) but assuming you are running 8.2.x: Sorry :/ Yes, we are running 8.2.x Start with 1GB shared_buffers (you may be able to go hire), 4MB work_mem, wal_sync_method = open_sync, checkpoint_segments = 30, default_statistics_target = 150, effective_cache_size = 6GB . Our shared_buffers is 1GB. work_mem is 32MB I changed wal_sync_method to open_sync (which helped a ton!) Can someone please explain effective_cache_size. what cache does it want to know about? Linux cache? Also, we're running the db on ext3 with noatime. Should I look at changing or getting rid of journaling ? 64bit Gutsy? Yes How is the SAN connected? fibre What does mpstat 5 (3 iterations) say? Even better what does sar -A say over a 24 hour period? I'll get these for you -- Adrian Moisey System Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 ---(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
