[ADMIN] How do I know my table is bloated?
Hi, everyone. I saw some people talking about the reindex command and I read in the docs the one reason to use reindex is when a table is bloated. But how do I know when a table is bloated?
Re: [ADMIN] How do I know my table is bloated?
On Thu, May 30, 2013 at 3:55 PM, Igor Neyman iney...@perceptron.com wrote: From: Rodrigo Barboza [mailto:rodrigombu...@gmail.com] Sent: Thursday, May 30, 2013 2:50 PM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] How do I know my table is bloated? Well, maybe I am. But I am worried because I know that there are some tables that do lots of updates and delete. As this concept is new for me, I am trying to be prepared to detect a situation like this. -- It all depends on pattern of your inserts/updates/deletes. If your index accumulates lots of almost (but not completely) empty pages with just few entries left, than - yes, REINDEX is your friend. b.t.w., this concept is not unique to Postgres, it's just a nature of B-tree indexes. Igor I see... I guess the only way to know is if I suspect that size of my db is growing unexpected. But it is good to know that a b-tree concept concept. Because I came from a very old mysql installation (that is way postgres is new for me) and I am trying to keep my postgres the most stable possible.
Re: [ADMIN] How do I know my table is bloated?
On Fri, May 31, 2013 at 2:06 AM, Sergey Konoplev gray...@gmail.com wrote: On Thu, May 30, 2013 at 6:05 AM, Rodrigo Barboza rodrigombu...@gmail.com wrote: I saw some people talking about the reindex command and I read in the docs the one reason to use reindex is when a table is bloated. But how do I know when a table is bloated? Take a look at the pgcompactor tool https://code.google.com/p/pgtoolkit/. This will find out what tables and indexes in your cluster are bloated and softly (without heavy locks) remove the bloat. Before using it setup the pgstattuple extension in all the databases you age going to check for bloat. Some usage examples are below. To get bloat statistics for all the cluster do: pgcompactor --all --reindex --verbose info --dry-run To remove bloat from all the cluster: pgcompactor --all --reindex --verbose info For a specific table use: pgcompactor --table tablename --reindex --verbose info --dry-run You can also specify --dbname, --schema, --exclude-dbname/schema/table, etc. For all the options: pgcompactor --man -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray...@gmail.com Thanks, guys!
[ADMIN] Question about maintenance_work_mem and shared_buffer
Hi, everyone. I have a doubt. I have a 32-bit postrgesql running with 2.5gb of shared_buffer. And I have maintenance_work_mem = 1gb and autovacuum_max_workers = 3. How maintenance_work_mem is related to shared_buffer? If the 3 workers uses 1gb, will the database crash? Or their memory usage are separated from each other?
Re: [ADMIN] Question about maintenance_work_mem and shared_buffer
On Tue, May 21, 2013 at 1:03 PM, Amit Langote amitlangot...@gmail.comwrote: On Wed, May 22, 2013 at 12:07 AM, Rodrigo Barboza rodrigombu...@gmail.com wrote: Hi, everyone. I have a doubt. I have a 32-bit postrgesql running with 2.5gb of shared_buffer. And I have maintenance_work_mem = 1gb and autovacuum_max_workers = 3. How maintenance_work_mem is related to shared_buffer? They are independent settings. If the 3 workers uses 1gb, will the database crash? Documentation does warn about not setting it (maintenance_work_mem) too high: Note that when autovacuum runs, up to autovacuum_max_workers times this memory may be allocated, so be careful not to set the default value too high. http://www.postgresql.org/docs/9.3/static/runtime-config-resource.html -- Amit Langote So what would it be a reasonable value? Because I saw some people suggesting to set it to total_mem / 8. In my case I am running a 32 bit postgres with 64gb of total memory. My kernel is 64 bits (I know that is not ideal, but that's what I have now) to recognize and use the to total memory.
[ADMIN] Delete xlog files
Hi, guys. I started accdentally a recovery with archive mode on and stand by server off. It created a lot of xlog files and filled my whole disk. If I decided to delete xlog files by hand, how do I know which one to delete?
Re: [ADMIN] pg_stat_tmp file
On Mon, May 6, 2013 at 7:32 AM, Magnus Hagander mag...@hagander.net wrote: On Fri, May 3, 2013 at 5:09 PM, Rodrigo Barboza rodrigombu...@gmail.com wrote: Hi, guys. Postgres doc says it is better to place the pgstat.stat file in ram disk. And it says that at shutdown, it is copied to global. What happens if postgres do not shutdown the usual way? If machine loses power, for example... PostgreSQL always discards the statistics file on an unclean shutdown, such as a powerloss, regardless of where it's stored. Therefor, it's not a problem that it's stored on a ramdrive. This is because it cannot be guaranteed to be consistent in that case. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ Ok, thanks!!
[ADMIN] pg_stat_tmp file
Hi, guys. Postgres doc says it is better to place the pgstat.stat file in ram disk. And it says that at shutdown, it is copied to global. What happens if postgres do not shutdown the usual way? If machine loses power, for example...
[ADMIN] postmaster is restarting
Hi, guys. I am using the script that comes with postgres installation to start and stop my database. When I stop the database, it restarts after some minutes. I have looked through every path and I don't find who starts it again. I am using Linux. Is there some kind of signaling to for it to restart? Does anybody have any tips where to find it?
Re: [ADMIN] postmaster is restarting
On Mon, Apr 29, 2013 at 7:48 PM, Johnny Tan johnnyd...@gmail.com wrote: Chef or Puppet? On Mon, Apr 29, 2013 at 5:48 PM, Lou Picciano loupicci...@comcast.netwrote: You probably have a service with overarching control over PostgreSQL. Gonna depend on how you installed PostgreSQL, which distro you've installed, etc... - Original Message - From: Rodrigo Barboza rodrigombu...@gmail.com To: pgsql-admin@postgresql.org Sent: Mon, 29 Apr 2013 21:35:08 - (UTC) Subject: [ADMIN] postmaster is restarting Hi, guys. I am using the script that comes with postgres installation to start and stop my database. When I stop the database, it restarts after some minutes. I have looked through every path and I don't find who starts it again. I am using Linux. Is there some kind of signaling to for it to restart? Does anybody have any tips where to find it? The only thing my script does is call postmaster. This script comes with postgres source code. I installed postgres passing the prefix for the installation in configure and I am using LFS.
[ADMIN] Size of pgstat.stat
Hello, guys. I was getting a lot of pg_stat timeout messages from postgres and I saw people na dpostgres doc suggesting to set this file to ram disk to reduce IO. But how large can this file be? I'm worried that it gets so large that it will get all the memory. Does anybody know?
Re: [ADMIN] Database encoding and collation
On Sat, Apr 20, 2013 at 1:52 PM, Craig James cja...@emolecules.com wrote:
On Fri, Apr 19, 2013 at 5:12 PM, Rodrigo Barboza
rodrigombu...@gmail.comwrote:
Hi guys.
I created a database with default encoding (SQL_ASCII) and default
collate (C).
I created a table test like this:
create table test (a varchar (10));
Then i executed insert into teste (a) values ('áéçã','Æ','ß');
After that:
select * from test;
a
-
áéçã
Æ
ß
Why did it stora correctly if those values are not ASCII?
Characters are interpreted and displayed by your terminal, not the
Postgres system. I suspect that you have language settings on whatever
windowing system you use. Postgres merely interprets the characters you
send as a series of 8-bit bytes. It's up to your display system to
interpret them. If you change your display terminal's language, I expect
you'll see something different.
The language settings of Postgres don't change what is stored, only how it
is interpreted (such as sorting).
Craig
I see.
When you say Postgres merely interprets the characters you send as a
series of 8-bit bytes, you meant for SQL_ASCII or every encoding?
Isn't sorting defined by collation?
Could I dump my database, create a new one with LATIN1 and restores?
[ADMIN] Database encoding and collation
Hi guys.
I created a database with default encoding (SQL_ASCII) and default collate
(C).
I created a table test like this:
create table test (a varchar (10));
Then i executed insert into teste (a) values ('áéçã','Æ','ß');
After that:
select * from test;
a
-
áéçã
Æ
ß
Why did it stora correctly if those values are not ASCII?
