Thread: 15,000 tables - next step
Hi,
thanks for your comments so far - I appreciate it. I'd like to narrow
down my problem a bit:
As I said in the other thread, I estimate that only 20% of the 15,000
tables are accessed regularly. So I don't think that vacuuming or the
number of file handles is a problem. Have a look at this:
content2=# select relpages, relname from pg_class order by relpages desc
limit 20;
relpages | relname
----------+---------------------------------
11867 | pg_attribute
10893 | pg_attribute_relid_attnam_index
3719 | pg_class_relname_nsp_index
3310 | wsobjects_types
3103 | pg_class
2933 | wsobjects_types_fields
2903 | wsod_133143
2719 | pg_attribute_relid_attnum_index
2712 | wsod_109727
2666 | pg_toast_98845
2601 | pg_toast_9139566
1876 | wsod_32168
1837 | pg_toast_138780
1678 | pg_toast_101427
1409 | wsobjects_types_fields_idx
1088 | wso_log
943 | pg_depend
797 | pg_depend_depender_index
737 | wsod_3100
716 | wp_hp_zen
I don't think that postgres was designed for a situation like this,
where a system table that should be fairly small (pg_attribute) is this
large.
On 12/2/2005 6:01 PM, Michael Riess wrote: > Hi, > > thanks for your comments so far - I appreciate it. I'd like to narrow > down my problem a bit: > > As I said in the other thread, I estimate that only 20% of the 15,000 > tables are accessed regularly. So I don't think that vacuuming or the > number of file handles is a problem. Have a look at this: What makes you think that? Have you at least tried to adjust your shared buffers, freespace map settings and background writer options to values that match your DB? How does increasing the kernel file desctriptor limit (try the current limit times 5 or 10) affect your performance? Jan > > content2=# select relpages, relname from pg_class order by relpages desc > limit 20; > relpages | relname > ----------+--------------------------------- > 11867 | pg_attribute > 10893 | pg_attribute_relid_attnam_index > 3719 | pg_class_relname_nsp_index > 3310 | wsobjects_types > 3103 | pg_class > 2933 | wsobjects_types_fields > 2903 | wsod_133143 > 2719 | pg_attribute_relid_attnum_index > 2712 | wsod_109727 > 2666 | pg_toast_98845 > 2601 | pg_toast_9139566 > 1876 | wsod_32168 > 1837 | pg_toast_138780 > 1678 | pg_toast_101427 > 1409 | wsobjects_types_fields_idx > 1088 | wso_log > 943 | pg_depend > 797 | pg_depend_depender_index > 737 | wsod_3100 > 716 | wp_hp_zen > > I don't think that postgres was designed for a situation like this, > where a system table that should be fairly small (pg_attribute) is this > large. > > ---------------------------(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 -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck schrieb: > On 12/2/2005 6:01 PM, Michael Riess wrote: > >> Hi, >> >> thanks for your comments so far - I appreciate it. I'd like to narrow >> down my problem a bit: >> >> As I said in the other thread, I estimate that only 20% of the 15,000 >> tables are accessed regularly. So I don't think that vacuuming or the >> number of file handles is a problem. Have a look at this: > > What makes you think that? Have you at least tried to adjust your shared > buffers, freespace map settings and background writer options to values > that match your DB? How does increasing the kernel file desctriptor > limit (try the current limit times 5 or 10) affect your performance? > > Of course I tried to tune these settings. You should take into account that the majority of the tables are rarely ever modified, therefore I don't think that I need a gigantic freespace map. And the background writer never complained. Shared memory ... I currently use 1500 buffers for 50 connections, and performance really suffered when I used 3000 buffers. The problem is that it is a 1GB machine, and Apache + Tomcat need about 400MB. But thanks for your suggestions! I guess that I'll have to find a way to reduce the number of tables. Unfortunately my application needs them, so I'll have to find a way to delete rarely used tables and create them on the fly when they're accessed again. But this will really make my application much more complex and error-prone, and I had hoped that the database system could take care of that. I still think that a database system's performance should not suffer from the mere presence of unused tables. Mike
Michael Riess wrote: > Shared memory ... I currently use 1500 buffers for 50 connections, and > performance really suffered when I used 3000 buffers. The problem is > that it is a 1GB machine, and Apache + Tomcat need about 400MB. Well, I'd think that's were your problem is. Not only you have a (relatively speaking) small server -- you also share it with other very-memory-hungry services! That's not a situation I'd like to be in. Try putting Apache and Tomcat elsewhere, and leave the bulk of the 1GB to Postgres. With 1500 shared buffers you are not really going anywhere -- you should have ten times that at the very least. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera schrieb: > Michael Riess wrote: > >> Shared memory ... I currently use 1500 buffers for 50 connections, and >> performance really suffered when I used 3000 buffers. The problem is >> that it is a 1GB machine, and Apache + Tomcat need about 400MB. > > Well, I'd think that's were your problem is. Not only you have a > (relatively speaking) small server -- you also share it with other > very-memory-hungry services! That's not a situation I'd like to be in. > Try putting Apache and Tomcat elsewhere, and leave the bulk of the 1GB > to Postgres. No can do. I can try to switch to a 2GB machine, but I will not use several machines. Not for a 5GB database. ;-) > With 1500 shared buffers you are not really going > anywhere -- you should have ten times that at the very least. > Like I said - I tried to double the buffers and the performance did not improve in the least. And I also tried this on a 2GB machine, and swapping was not a problem. If I used 10x more buffers, I would in essence remove the OS buffers.
On 12/3/05, Michael Riess <mlriess@gmx.de> wrote: > Alvaro Herrera schrieb: > > Michael Riess wrote: > > > >> Shared memory ... I currently use 1500 buffers for 50 connections, and > >> performance really suffered when I used 3000 buffers. The problem is > >> that it is a 1GB machine, and Apache + Tomcat need about 400MB. > > > > Well, I'd think that's were your problem is. Not only you have a > > (relatively speaking) small server -- you also share it with other > > very-memory-hungry services! That's not a situation I'd like to be in. > > Try putting Apache and Tomcat elsewhere, and leave the bulk of the 1GB > > to Postgres. > > No can do. I can try to switch to a 2GB machine, but I will not use > several machines. Not for a 5GB database. ;-) > No for a 5GB database but because of the other services you have running > > With 1500 shared buffers you are not really going > > anywhere -- you should have ten times that at the very least. > > > > Like I said - I tried to double the buffers and the performance did not > improve in the least. And I also tried this on a 2GB machine, and > swapping was not a problem. If I used 10x more buffers, I would in > essence remove the OS buffers. > How many disks do you have? (i wonder if you say 1) - in most cases is good idea to have the WAL file in another disk... What type of disks (ide, scsi, etc)? How many processors? What other services (or applications) do you have in that machine? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)
On 12/3/2005 11:41 AM, Michael Riess wrote: > Alvaro Herrera schrieb: >> Michael Riess wrote: >> >>> Shared memory ... I currently use 1500 buffers for 50 connections, and >>> performance really suffered when I used 3000 buffers. The problem is >>> that it is a 1GB machine, and Apache + Tomcat need about 400MB. >> >> Well, I'd think that's were your problem is. Not only you have a >> (relatively speaking) small server -- you also share it with other >> very-memory-hungry services! That's not a situation I'd like to be in. >> Try putting Apache and Tomcat elsewhere, and leave the bulk of the 1GB >> to Postgres. > > No can do. I can try to switch to a 2GB machine, but I will not use > several machines. Not for a 5GB database. ;-) What version of PostgreSQL are we talking about? If it is anything older than 8.0, you should upgrade at least to that. With 8.0 or better try 20000 shared buffers or more. It is well possible that going from 1500 to 3000 buffers made things worse. Your buffer cache can't even hold the system catalog in shared memory. If those 50 backends serve all those 500 apps at the same time, they suffer from constant catalog cache misses and don't find the entries in the shared buffers either. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Michael Riess wrote: >> Well, I'd think that's were your problem is. Not only you have a >> (relatively speaking) small server -- you also share it with other >> very-memory-hungry services! That's not a situation I'd like to be in. >> Try putting Apache and Tomcat elsewhere, and leave the bulk of the 1GB >> to Postgres. > > > No can do. I can try to switch to a 2GB machine, but I will not use > several machines. Not for a 5GB database. ;-) > >> With 1500 shared buffers you are not really going >> anywhere -- you should have ten times that at the very least. >> > > Like I said - I tried to double the buffers and the performance did not > improve in the least. And I also tried this on a 2GB machine, and > swapping was not a problem. If I used 10x more buffers, I would in > essence remove the OS buffers. Increasing buffers do improve performance -- if you have enough memory. You just don't have enough memory to play with. My servers run w/ 10K buffers (128MB on 64-bit FC4) and it definitely runs better w/ it at 10K versus 1500. With that many tables, your system catalogs are probably huge. To keep your system catalog from continually cycling in-out of buffers/OS cache/disk, you need a lot more memory. Ordinarily, I'd say the 500MB you have available for Postgres to cache 5GB is a workable ratio. My servers all have similar ratios of ~1:10 and they perform pretty good -- *except* when the system catalogs bloated due to lack of vacuuming on system tables. My app regularly creates & drops thousands of temporary tables leaving a lot of dead rows in the system catalogs. (Nearly the same situation as you -- instead of 15K live tables, I had 200 live tables and tens of thousands of dead table records.) Even with almost 8GB of RAM dedicated to postgres, performance on every single query -- not matter how small the table was -- took forever because the query planner had to spend a significant period of time scanning through my huge system catalogs to build the execution plan. While my situtation was fixable by scheduling a nightly vacuum/analyze on the system catalogs to get rid of the bazillion dead table/index info, you have no choice but to get more memory so you can stuff your entire system catalog into buffers/os cache. Personally, w/ 1GB of ECC RAM at ~$85, it's a no brainer. Get as much memory as your server can support.
William Yu schrieb: > Michael Riess wrote: >>> Well, I'd think that's were your problem is. Not only you have a >>> (relatively speaking) small server -- you also share it with other >>> very-memory-hungry services! That's not a situation I'd like to be in. >>> Try putting Apache and Tomcat elsewhere, and leave the bulk of the 1GB >>> to Postgres. >> >> >> No can do. I can try to switch to a 2GB machine, but I will not use several machines. Not for a 5GB database. ;-) >> >>> With 1500 shared buffers you are not really going >>> anywhere -- you should have ten times that at the very least. >>> >> >> Like I said - I tried to double the buffers and the performance did not improve in the least. And I also tried this on a 2GB machine, and swapping was not a problem. If I used 10x more buffers, I would in essence remove the OS buffers. > > Increasing buffers do improve performance -- if you have enough memory. You just don't have enough memory to play with. My servers run w/ 10K buffers (128MB on 64-bit FC4) and it definitely runs better w/ it at 10K versus 1500. > > With that many tables, your system catalogs are probably huge. content2=# select sum(relpages) from pg_class where relname like 'pg_%'; sum ------- 64088 (1 row) :-) > While my situtation was fixable by scheduling a nightly vacuum/analyze on the system catalogs to get rid of the bazillion dead table/index info, you have no choice but to get more memory so you can stuff your entire system catalog into buffers/os cache. Personally, w/ 1GB of ECC RAM at ~$85, it's a no brainer. Get as much memory as your server can support. The problem is that we use pre-built hardware which isn't configurable. We can only switch to a bigger server with 2GB, but that's tops. I will do the following: - switch to 10k buffers on a 1GB machine, 20k buffers on a 2GB machine - try to optimize my connection polls to remember which apps (groups of 30 tables) were accessed, so that there is a better chance of using caches - "swap out" tables which are rarely used: export the content, drop the table, and re-create it on the fly upon access. Thanks for your comments!
On 12/4/2005 4:33 AM, Michael Riess wrote:
> I will do the following:
>
> - switch to 10k buffers on a 1GB machine, 20k buffers on a 2GB machine
> - try to optimize my connection polls to remember which apps (groups of
> 30 tables) were accessed, so that there is a better chance of using caches
> - "swap out" tables which are rarely used: export the content, drop the
> table, and re-create it on the fly upon access.
I hacked pgbench a little and did some tests (finally had to figure out
for myself if there is much of an impact with hundreds or thousands of
tables).
The changes done to pgbench:
- Use the [-s n] value allways, instead of determining the
scaling from the DB.
- Lower the number of accounts per scaling factor to 10,000.
- Add another scaling type. Option [-a n] splits up the test
into n schemas, each containing [-s n] branches.
The tests were performed on a 667 MHz P3, 640MB Ram with a single IDE
disk. All tests were IO bound. In all tests the number of clients was 5
default transaction and 50 readonly (option -S). The FreeBSD kernel of
the system is configured to handle up to 50,000 open files, fully cache
directories in virtual memory and to lock all shared memory into
physical ram.
The different scalings used were
init -a1 -s3000
run -a1 -s300
and
init -a3000 -s1
run -a300 -s1
The latter creates a database of 12,000 tables with 1,200 of them
actually in use during the test. Both databases are about 4 GB in size.
The performance loss for going from -s3000 to -a3000 is about 10-15%.
The performance gain for going from 1,000 shared_buffers to 48,000 is
roughly 70% (-a3000 test case) and 100% (-s3000 test case).
Conclusion: The right shared memory configuration easily outperforms the
loss from increase in number of tables, given that the kernel is
configured to be up to the task of dealing with thousands of files
accessed by that number of backends too.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #