pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
R: R: repeated out of shared memory error - not related to max_locks_per_transaction 2+ messages / 1 participants [nested] [flat]
* R: R: repeated out of shared memory error - not related to max_locks_per_transaction @ 2018-07-20 12:47 ` Alfonso Moscato <alfonso.moscato@merqurio.it> 1 sibling, 0 replies; 2+ messages in thread From: Alfonso Moscato @ 2018-07-20 12:47 UTC (permalink / raw) To: 'MichaelDBA' <MichaelDBA@sqlexec.com>; +Cc: 'Campbell, Lance' <lance@illinois.edu>; pgsql-admin@lists.postgresql.org I took the setting from pgtune: https://pgtune.leopard.in.ua/#/ Anyway, I will try to further reduce it. It is high because we have a very complex database, queries with a lot of tables, and million of records. Furthermore, I see a lot of free memory. Da: MichaelDBA <MichaelDBA@sqlexec.com> Inviato: venerdì 20 luglio 2018 14:40 A: Alfonso Moscato <alfonso.moscato@merqurio.it> Cc: 'Campbell, Lance' <lance@illinois.edu>; pgsql-admin@lists.postgresql.org Oggetto: Re: R: repeated out of shared memory error - not related to max_locks_per_transaction Try reducing it further just to make sure. Alfonso Moscato <mailto:alfonso.moscato@merqurio.it> Friday, July 20, 2018 8:39 AM I thought it could have be. Work_mem was 131mb and I reduced it to 60mb. Errors keep starting after one day and half. Regards alfonso Da: MichaelDBA <mailto:MichaelDBA@sqlexec.com> <MichaelDBA@sqlexec.com> Inviato: venerdì 20 luglio 2018 14:36 A: Campbell, Lance <mailto:lance@illinois.edu> <lance@illinois.edu> Cc: Alfonso Moscato <mailto:alfonso.moscato@merqurio.it> <alfonso.moscato@merqurio.it>; pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org> Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction Perhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem. Regards, Michael Vitale MichaelDBA <mailto:MichaelDBA@sqlexec.com> Friday, July 20, 2018 8:35 AM Perhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem. Regards, Michael Vitale Campbell, Lance <mailto:lance@illinois.edu> Friday, July 20, 2018 8:32 AM I would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of shared buffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQL to run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory. So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQL planning. Double check the documentation. Lance Sent from my iPad ^ permalink raw reply [nested|flat] 2+ messages in thread
* R: R: repeated out of shared memory error - not related to max_locks_per_transaction @ 2018-07-20 13:10 ` Alfonso Moscato <alfonso.moscato@merqurio.it> 1 sibling, 0 replies; 2+ messages in thread From: Alfonso Moscato @ 2018-07-20 13:10 UTC (permalink / raw) To: 066ce286@free.fr; +Cc: pgsql-admin@lists.postgresql.org autovacuum_max_workers = 3 max_prepared_transactions = 0 block_size = 8192 wal_block_size = 8192 -----Messaggio originale----- Da: 066ce286@free.fr <066ce286@free.fr> Inviato: venerdì 20 luglio 2018 15:07 A: Alfonso Moscato <alfonso.moscato@merqurio.it> Cc: pgsql-admin@lists.postgresql.org Oggetto: Re: R: repeated out of shared memory error - not related to max_locks_per_transaction What are your values for : autovacuum_max_workers max_prepared_transactions block_size wal_block_size ? ----- Mail original ----- De: "Alfonso Moscato" <alfonso.moscato@merqurio.it> À: "MichaelDBA" <MichaelDBA@sqlexec.com>, "Lance' 'Campbell" <lance@illinois.edu> Cc: pgsql-admin@lists.postgresql.org Envoyé: Vendredi 20 Juillet 2018 14:39:01 Objet: R: repeated out of shared memory error - not related to max_locks_per_transaction I thought it could have be. Work_mem was 131mb and I reduced it to 60mb. Errors keep starting after one day and half. Regards alfonso Da: MichaelDBA <MichaelDBA@sqlexec.com> Inviato: venerdì 20 luglio 2018 14:36 A: Campbell, Lance <lance@illinois.edu> Cc: Alfonso Moscato <alfonso.moscato@merqurio.it>; pgsql-admin@lists.postgresql.org Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction Perhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem. Regards, Michael Vitale Campbell, Lance Friday, July 20, 2018 8:32 AM I would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of shared buffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQL to run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory. So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQL planning. Double check the documentation. Lance Sent from my iPad ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-07-20 13:10 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-07-20 12:47 ` Alfonso Moscato <alfonso.moscato@merqurio.it> 2018-07-20 13:10 ` Alfonso Moscato <alfonso.moscato@merqurio.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox