[PERFORM] Databases vs Schemas
I am seeking advice on what the best setup for the following would be. My company provides a hosted web calendaring solution for school districts. For each school district we have a separate database. Each database has 57 tables. There are a total of 649 fields in those tables. Here is a table of the different kinds of field and how many there are: time without time zone bytea date smallint boolean integer timestamp without time zone numeric text 9 4 8 1 79 195 36 8 309 Over the next couple of months we will be creating an instance of our solution for each public school district in the US which is around 18,000. That means currently we would be creating 18,000 databases (all on one server right now - which is running 8.4). I am assuming this is probably not the best way of doing things. I have read up on schemas and it looks like a good change to make would be to create 1 database with 18,000 schemas. Would that be a good idea? What sort of issues should I be aware of (administrative, management, performance, etc...)? Is that too many schemas to put into 1 database? What are the limits on the number of databases and schemas you can create? Should I try to re-engineer things so that all 18,000 instances only use 1 database and 1 schema? Let me know if you need any more info. Any advice and information would be greatly appreciated. Regards, Scott Otis CIO / Lead Developer Intand www.intand.com
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
Robert Haas robertmh...@gmail.com:
Do you by any chance have a bazillion databases in this cluster? Can
you do these?
select sum(1) from pg_database;
1555
select pg_relation_size('pg_database');
221184
select sum(pg_column_size(d.*)) from pg_database;
That gave me:
ERROR: missing FROM-clause entry for table d
LINE 1: select sum(pg_column_size(d.*)) from pg_database;
So I did this:
select sum(pg_column_size(d.*)) from pg_database as d;
and got:
192910
Also did this:
select sum(pg_database_size(datname)) from pg_database;
and got:
13329800428 (12.4GB)
Scott
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
Claus Guttesen kome...@gmail.com: Would love to get some advice on how to change my conf settings / setup to get better I/O performance. Server Specs: 2x Intel Xeon Quad Core (@2 Ghz - Clovertown,L5335) 4GB RAM 4x Seagate 73GB SAS HDD 10k RPM - in RAID ( stripped and mirrored ) FreeBSD 6.4 Apache 2.2 PostgreSQL 8.3.6 PHP 5.2.9 ~1500 databases w/ ~60 tables each max_connections = 600 shared_buffers = 1GB On a dual-core HP DL380 with 16 GB ram I have set shared_buffers at 512 MB for 900 max_connections. Far the largest table have approx. 120 mill. records. You could try to lower shared_buffers. max_fsm_pages = 280 max_fsm_relations = 16 What does the last couple of lines from a 'vacuum analyze verbose' say? I have max_fsm_pages = 400 and max_fsm_relations = 1500. You can also try to lower random_page_cost to a lower value like 1.2 but I doubt this will help in your case. last couple lines from 'vacuumdb -a -v -z': INFO: free space map contains 114754 pages in 42148 relations DETAIL: A total of 734736 page slots are in use (including overhead). 734736 page slots are required to track all free space. Current limits are: 280 page slots, 16 relations, using 26810 kB. Scott
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
So is there anything I can do in 8.3 to help this? I have tried setting ' track_activities', 'track_counts' and 'autovacuum' to 'off' (which has reduced CPU and I/O a bit) - but the stats collector process is still using up a good deal of CPU and I/O - is there any way to turn stats collecting completely off? Scott Otis CIO / Lead Developer Intand www.intand.com -Original Message- From: Magnus Hagander [mailto:mag...@hagander.net] Sent: Friday, September 04, 2009 1:19 AM To: Scott Otis Cc: Robert Haas; Ivan Voras; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3 On Fri, Sep 4, 2009 at 08:54, Scott Otisscott.o...@intand.com wrote: Robert Haas robertmh...@gmail.com: Do you by any chance have a bazillion databases in this cluster? Can you do these? select sum(1) from pg_database; 1555 Note that there are two features in 8.4 specifically designed to deal with the situation where you have lots of databases and/or lots of tables (depending on how many tables you have in each database, this would definitely qualify). They both deal with the pgstats temp file too large generating i/o issue. First, it will only write the file when it's actually necessary - 8.3 and earlier will always write it. Second, you will have the ability to move the location of the file to a different filesystem - specifically intended so that you can move it off to a ramdrive. Could be worth investigating an upgrade for this issue alone. The fact that you don't have to struggle with tuning the FSM in 8.4 is another thing that makes life a *lot* easier in this kind of installations. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- 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] Seeking performance advice and explanation for high I/O on 8.3
Sorry about not responding to the whole list earlier - this is my first time posting to a mailing list. Would providing more information about the size and complexities of the databases help? I measure I/O stats with iostat - here is the command I use: iostat -d -x mfid0 -t 290 2 I tried looking at the man page for iostat but couldn't find anywhere how to determine what the stats are for sequential vs random - any help there? When using 'top -m io' the postgres stats collector process is constantly at 99% - 100%. When using 'top' the WCPU for the postgres stats collector and the autovacuum process are constantly at 20% - 21%. Is that normal? It seems to me that the stats collector is doing all the I/O (which would mean the stats collector is doing 46.1 megabytes /sec). Also, the I/O stats don't change hardly at all (except at night during backups which makes sense). They don't go up or down with user activity on the server - which makes me wonder a little bit. I have a feeling that if I just turned off Apache that the I/O stats wouldn't change. Which leads me to believe that the I/O is not query related - its stats collecting and autovacuuming related. Is that expected? It seems to me that the stats collector shouldn't be using that much I/O and CPU (and the autovacuum shouldn't be using that much CPU) - therefore something in my configuration must be messed up or could be changed somehow. But maybe I'm wrong - please let me know. I don't think my setup is necessarily slow. I just want to make it as efficient as possible and wanted to get some feedback to see if am setting things up right. I am also looking out into the future and seeing how much load I can put on this server before getting another one. If I can reduce the I/O and CPU that the stats collector and autovacuum are using without losing any functionality then I can put more load on the server. Again thanks for all the help. Scott Otis CIO / Lead Developer Intand www.intand.com -- 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] Seeking performance advice and explanation for high I/O on 8.3
Simply do iostat mfid0 1 and post 10 lines of its output. tty mfid0 cpu tin tout KB/t tps MB/s us ni sy in id 0 152 108.54 335 35.51 43 0 30 1 27 0 525 85.73 759 63.55 14 0 12 0 74 0 86 67.72 520 34.39 13 0 12 0 75 0 86 86.89 746 63.26 12 0 12 0 76 0 86 70.09 594 40.65 13 0 11 0 76 0 86 78.50 756 57.99 13 0 10 0 77 0 351 81.46 774 61.61 12 0 11 0 77 0 86 63.87 621 38.72 9 0 8 0 83 0 86 80.87 821 64.86 8 0 8 0 83 0 86 58.78 637 36.55 11 0 11 0 77 Scott -- 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] Seeking performance advice and explanation for high I/O on 8.3
Can you post to the list all the uncommented lines from your postgresql.conf file and attach the results of select * from pg_stat_all_tables as an attachment? I attached a CSV of select * from pg_stat_all_tables from one of our more heavily used databases. Note: I turned off stats collection and autvacuuming a couple days ago to see what it would do and then restarted postgres - I turned those back on this morning to that is why there aren't more autovacuumed and autoanalyzed tables. Sorry if this is a little verbose - I didn't want to leave anything out. Uncommented lines from Postgresql.conf: listen_addresses = '*' max_connections = 600 ssl = on password_encryption = on shared_buffers = 1GB work_mem = 5MB maintenance_work_mem = 256MB max_fsm_pages = 280 max_fsm_relations = 16 synchronous_commit = off checkpoint_segments = 6 checkpoint_warning = 30s effective_cache_size = 1GB log_destination = 'stderr' logging_collector = on log_directory = '/var/log/pgsql' log_filename = '%m%d%y_%H%M%S-pgsql.log' log_rotation_age = 1d log_rotation_size = 10MB log_min_messages = warning log_error_verbosity = default log_min_error_statement = warning silent_mode = on log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d ' log_temp_files = 0 track_activities = on track_counts = on update_process_title = off log_parser_stats = off log_planner_stats = off log_executor_stats = off log_statement_stats = off autovacuum = on datestyle = 'iso, mdy' lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' default_text_search_config = 'pg_catalog.english' pg_stat_all_tables2.csv Description: pg_stat_all_tables2.csv -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Seeking performance advice and explanation for high I/O on 8.3
Would love to get some advice on how to change my conf settings / setup to get better I/O performance. Server Specs: 2x Intel Xeon Quad Core (@2 Ghz - Clovertown,L5335) 4GB RAM 4x Seagate 73GB SAS HDD 10k RPM - in RAID ( stripped and mirrored ) FreeBSD 6.4 Apache 2.2 PostgreSQL 8.3.6 PHP 5.2.9 ~1500 databases w/ ~60 tables each Total I/O (these number are pretty constant throughout the day): Reads: ~ 100 / sec for about 2.6 Mb/sec Writes: ~ 400 /sec for about 46.1Mb/sec Conf settings: listen_addresses = '*' max_connections = 600 ssl = on password_encryption = on shared_buffers = 1GB work_mem = 5MB maintenance_work_mem = 256MB max_fsm_pages = 280 max_fsm_relations = 16 synchronous_commit = off checkpoint_segments = 6 checkpoint_warning = 30s effective_cache_size = 1GB pg_stat_bgwriter: checkpoints_timed: 16660 checkpoints_req: 1309 buffers_checkpoint: 656346 buffers_clean: 120922 maxwritten_clean: 1 buffers_backend: 167623 buffers_alloc: 472802349 This server also handles web traffic and PHP script processing. Most of the SQL happening is selects - very little inserts, updates and deletes comparatively. I have noticed that most/all of the I/O activity is coming from the stats collector and autovacuum processes. Would turning off the stats collector and autovacuum be helpeful / recommended? Could I change my checkpoint_* or bgwriter_* conf values to help? Let me know if you need more information / stats. Any help would be much appreciated. Thanks, Scott Otis CIO / Lead Developer Intand www.intand.com
