Re: [BUGS] BUG #6186: out of memory while analyze
Hello, /proc/sys/vm/swappiness = 60 /proc/sys/vm/overcommit_memory = 0 /proc/sys/vm/overcommit_ratio = 50 total used free sharedbuffers cached Mem: 4038 3146892 0 30 3067 -/+ buffers/cache: 49 3989 Swap: 8581 0 8581 2011/9/1 Valentine Gogichashvili : >> >> ssl = false >> shared_buffers = 900MB # min 128kB >> work_mem = 100MB # min 64kB >> maintenance_work_mem = 524MB # min 1MB >> fsync = off # turns forced synchronization on >> or off >> synchronous_commit = off # immediate fsync at commit >> full_page_writes = off # recover from partial page writes >> effective_cache_size = 1GB >> default_statistics_target = 8000 # range 1-1 >> >> >> Also tried change maintenance_work_mem to 2G and same result. >> Imposible to set over 2GB (FATAL: 3145728 is outside the valid range >> for parameter "maintenance_work_mem" (1024 .. 2097151)) >> > > Actually, it also can be, that your commit limit is reached. Can you also > look in the /proc/meminfo -> CommitLimit and Commit_AS. These values should > not be too close to each other. > What are the values > for /proc/sys/vm/swappiness, /proc/sys/vm/overcommit_memory > and /proc/sys/vm/overcommit_ratio? What is your swap size. They all > influence CommitLimit and the amount of memory, you can get. > -- Valentin > > -- Lampa -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6186: out of memory while analyze
> > > ssl = false > shared_buffers = 900MB # min 128kB > work_mem = 100MB# min 64kB > maintenance_work_mem = 524MB# min 1MB > fsync = off # turns forced synchronization on > or off > synchronous_commit = off# immediate fsync at commit > full_page_writes = off # recover from partial page writes > effective_cache_size = 1GB > default_statistics_target = 8000# range 1-1 > > > Also tried change maintenance_work_mem to 2G and same result. > Imposible to set over 2GB (FATAL: 3145728 is outside the valid range > for parameter "maintenance_work_mem" (1024 .. 2097151)) > > Actually, it also can be, that your commit limit is reached. Can you also look in the /proc/meminfo -> CommitLimit and Commit_AS. These values should not be too close to each other. What are the values for /proc/sys/vm/swappiness, /proc/sys/vm/overcommit_memory and /proc/sys/vm/overcommit_ratio? What is your swap size. They all influence CommitLimit and the amount of memory, you can get. -- Valentin
Re: [BUGS] BUG #6186: out of memory while analyze
Hi Lampa, On Thu, Sep 1, 2011 at 6:26 AM, Lampa wrote: > With default value 100 have bad perfomance :-( Don't set the default value higher: specify a higher value only where needed. See ALTER TABLE ALTER [ COLUMN ] column SET STATISTICS integer ( http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html ). -- Guillaume -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6186: out of memory while analyze
Hello, ok or write that there is limit for 32bit system. With default value 100 have bad perfomance :-( But thank you for yours time. 2011/8/31 Tom Lane : > Lampa writes: >> default_statistics_target = 8000 # range 1-1 > >> Problem seems to be in default_statistics_target values from approx >> 6300. Up to 6300 analyze works but on another machine (64bit) works >> 1 without problems on same but larger table (more data) > > Well, there's a reason why the default value of that parameter is 100 > and not 1 ;-). The larger it is, the more memory space it's going > to take to do an ANALYZE. I don't see any bug here, you're just trying > to do more than you can do in a 32-bit machine. > > I guess we could improve the documentation of default_statistics_target > to point out that it costs not only more time but more memory in > ANALYZE. > > regards, tom lane > -- Lampa -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6186: out of memory while analyze
Lampa writes: > default_statistics_target = 8000# range 1-1 > Problem seems to be in default_statistics_target values from approx > 6300. Up to 6300 analyze works but on another machine (64bit) works > 1 without problems on same but larger table (more data) Well, there's a reason why the default value of that parameter is 100 and not 1 ;-). The larger it is, the more memory space it's going to take to do an ANALYZE. I don't see any bug here, you're just trying to do more than you can do in a 32-bit machine. I guess we could improve the documentation of default_statistics_target to point out that it costs not only more time but more memory in ANALYZE. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6186: out of memory while analyze
On 08/30/11 10:25 PM, Lampa wrote: Also tried change maintenance_work_mem to 2G and same result. Imposible to set over 2GB (FATAL: 3145728 is outside the valid range for parameter "maintenance_work_mem" (1024 .. 2097151)) is this a 32bit postgres ? -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6186: out of memory while analyze
Hello, table design http://pastebin.com/RW6vLAVP And configuration: ssl = false shared_buffers = 900MB # min 128kB work_mem = 100MB# min 64kB maintenance_work_mem = 524MB# min 1MB fsync = off # turns forced synchronization on or off synchronous_commit = off# immediate fsync at commit full_page_writes = off # recover from partial page writes effective_cache_size = 1GB default_statistics_target = 8000# range 1-1 Also tried change maintenance_work_mem to 2G and same result. Imposible to set over 2GB (FATAL: 3145728 is outside the valid range for parameter "maintenance_work_mem" (1024 .. 2097151)) Problem seems to be in default_statistics_target values from approx 6300. Up to 6300 analyze works but on another machine (64bit) works 1 without problems on same but larger table (more data) 2011/8/30 Tom Lane : > "Lampa" writes: >> Trying analyze table (ANALYZE TABLE table or VACUUM ANALYZE table) with >> 3051316 rows (displayed size 2521MB in \dt+) > > What have you got maintenance_work_mem set to? shared_buffers might > also be interesting. > > regards, tom lane > -- Lampa -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6186: out of memory while analyze
"Lampa" writes: > Trying analyze table (ANALYZE TABLE table or VACUUM ANALYZE table) with > 3051316 rows (displayed size 2521MB in \dt+) What have you got maintenance_work_mem set to? shared_buffers might also be interesting. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6186: out of memory while analyze
On 30.08.2011 14:20, Lampa wrote: Trying analyze table (ANALYZE TABLE table or VACUUM ANALYZE table) with 3051316 rows (displayed size 2521MB in \dt+) Log: ... Analyze: 2236767256 total in 279 blocks; 18080 free (423 chunks); 2236749176 used > ... Hmm, that looks like a memory like in ANALYZE. What does the table look like? "\d table" would be helpful. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6186: out of memory while analyze
The following bug has been logged online: Bug reference: 6186 Logged by: Lampa Email address: lamp...@gmail.com PostgreSQL version: 9.0.4/8.4.8 Operating system: Linux Debian testing 32bit Description:out of memory while analyze Details: Trying analyze table (ANALYZE TABLE table or VACUUM ANALYZE table) with 3051316 rows (displayed size 2521MB in \dt+) Log: TopMemoryContext: 42800 total in 5 blocks; 5304 free (7 chunks); 37496 used Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used TopTransactionContext: 8192 total in 1 blocks; 7696 free (0 chunks); 496 used MessageContext: 8192 total in 1 blocks; 5688 free (1 chunks); 2504 used Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks); 4344 used smgr relation table: 8192 total in 1 blocks; 2816 free (0 chunks); 5376 used TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0 chunks); 16 used Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used PortalHeapMemory: 1024 total in 1 blocks; 1008 free (13 chunks); 16 used Analyze: 2236767256 total in 279 blocks; 18080 free (423 chunks); 2236749176 used Vacuum: 8192 total in 1 blocks; 7872 free (0 chunks); 320 used Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used CacheMemoryContext: 667696 total in 20 blocks; 135264 free (1 chunks); 532432 used i_table_zamid: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_zamestnanci_id: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_zacatek_delka: 1024 total in 1 blocks; 280 free (0 chunks); 744 used i_table_zacatek: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_tymy_instance_id: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_tymy_id: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_strid: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_stavukonu_id: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_stavcas: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_stav: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_splneno_cas: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_skupinyzamesntnacu_id: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_sablonyukonu_id: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_prostredky_id: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_priorita: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_pacienti_id: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_odbid: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_objednano_id: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_nadrazeny_ukon: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_maxid: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_kpid_spl: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_kpid_obj: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_klientskekarty_id: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_delka: 1024 total in 1 blocks; 344 free (0 chunks); 680 used i_table_adresareukonu_id: 1024 total in 1 blocks; 344 free (0 chunks); 680 used table_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used pg_user_mapping_user_server_index: 1024 total in 1 blocks; 280 free (0 chunks); 744 used pg_user_mapping_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used pg_type_typname_nsp_index: 1024 total in 1 blocks; 280 free (0 chunks); 744 used pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free (0 chunks); 744 used pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0 chunks); 744 used pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0 chunks); 744 used pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0 chunks); 744 used pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0 chunks); 832 used pg_statistic_relid_att_index: 1024 total in 1 blocks; 280 free (0 chunks); 744 used pg_class_relname_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used pg_proc_oid_index: 1024 total in 1 bloc
