Tag: announcements

find.best.tablespace.split.pl

Changes:

  1. get connection settings from command line:
    ./find.best.tablespace.split.pl “dbi:Pg:dbname=depesz;host=127.0.0.1;port=12345" depesz
  2. calculate only for public schema (can be easily changed)
  3. distribute indexes as well – always put indexes on another tablespace than table
  4. add comment in generated file about total filesizes in tablespaces *after* migration – so it will be clearer on what to move

svn is located here.

pgsql-tools/analyze.pgsql.logs.pl

Changes:

  1. doesn't use temp files – does everything in-memory. makes the whole process *way* faster
  2. allow setting header information using postgresql log_line_prefix syntax
  3. removes dependency on readonly perl module – it is not really common module, and the functionality in this program is very limited
  4. allows setting database to track as command line option, or track sqls from all databases

SVN repo at: http://svn.depesz.com/svn/pgsql-tools/trunk

finding optimum tables placement in 2-tablespace situation

just recently we got another array for out main production database. this means – we will be able to add new tablespace, thus making everything go faster.

in theory – it's nice. but which tables to move to the other?

the basic assumption is simple – index on table should not be on the same tablespace as the table itself. that's easy. but – should we really put all tables on one tablespace, and all indexes on another?

we decided that the important things that should be “boosted" are seeks and writes. sequential reads are (in our situation) more or less irrelevant.

read on to check how we split the load.

Continue reading finding optimum tables placement in 2-tablespace situation