Message-ID: <007901c38f2b$0b585590$2802a8c0@webbased10>
From: "Nick Barr" <nicky@chuckie.co.uk>
To: <pgsql-performance@postgresql.org>
Subject: go for a script! / ex:  PostgreSQL vs. MySQL
Date: Fri, 10 Oct 2003 13:35:53 +0100
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----=_NextPart_000_0076_01C38F33.6CF81E90"

This is a multi-part message in MIME format.

------=_NextPart_000_0076_01C38F33.6CF81E90
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Heya Guys n Gals,

Having been following the thread on "go for a script! / ex: PostgreSQL vs.
MySQL". I thought I would throw something together in Perl. My current issue
is that I only have access to a RH Linux box and so cannot make it
cross-platform on my own :-(. Anyhow please find it attached. It runs fine
on my box, it doesnt actually write to postgresql.conf because I didnt want
to mess it up, it does however write to postgresql.conf.new for the moment.
The diffs seem to be writing correctly. There are a set of parameters at the
top which may need to get tweaked for your platform. I can also carry on
posting to this list new versions if people want. Clearly this lot is open
source, so please feel free to play with it and post patches/new features
back either to the list or my email directly. In case you cant see my email
address, it is nicky at the domain below.

 I will also post it on me website and as I develop it further new versions
will appear there

http://www.chuckie.co.uk/postgresql/pg_autoconfig.pl

Is this a useful start?


Nick

------=_NextPart_000_0076_01C38F33.6CF81E90
Content-Type: application/octet-stream;
	name="pg_autoconfig.pl"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
	filename="pg_autoconfig.pl"

#!/usr/bin/perl

# Copyright (c) 2003 Nicholas Barr
#
# This is free software. There is NO warranty; not even for MERCHANTABILITY
# or FITNESS FOR A PARTICULAR PURPOSE.

use strict;

my %settings =3D ();

$settings{"path_uname"}  =3D "/bin/uname";
$settings{"path_uptime"} =3D "/usr/bin/uptie";
$settings{"path_pgsql"}  =3D "/var/lib/pgsql";

$settings{"pgsql_pagesize_kbytes"} =3D 8;
$settings{"pgsql_pagesize_bytes"} =3D $settings{"pgsql_pagesize_kbytes"} * =
1024;

######################
# RECOMMENDED RATIOS #
######################

# ratio_shared_buffer
#
# The ratio of shared buffers compared to total memory
# Range : 0 - 1
$settings{"ratio_shared_buffer"}   =3D 0.0625;	# i.e. 1/16th of total memory

# ratio_effective_cache_size
#
# The ratio of the effective cache size comapared to
# kernel cache size. This should be done on a box that
# has been up quite a while
# Range : 0 - 1
$settings{"ratio_effective_cache_size"} =3D 0.8;

# Initialise the operating system parameters
my $os_type =3D "";	# Type of operating system (i.e. Linux/Solaris etc.)
my $os_vers =3D "";	# Version of the kernel if we actually care?

# Iniitialise the various memory parameters
my $mem_total   =3D 0;
my $mem_used    =3D 0;
my $mem_free    =3D 0;
my $mem_shared  =3D 0;
my $mem_buffers =3D 0;
my $mem_cached  =3D 0;

# Initialise the various shared memory parameters
my $shm_max =3D 0;

my $uptime =3D 0;

print "PostgreSQL Autoconfigurer Thingy\n";
print "=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D\n\n";

($os_type, $os_vers) =3D getOperatingSystem();
($mem_total, $mem_used, $mem_free, $mem_shared, $mem_buffers, $mem_cached) =
=3D getMemory();
($shm_max) =3D getShmMax();
($uptime) =3D getUptime();

my $mem_kcache          =3D int ($mem_buffers + $mem_cached);

my $pgsql_rec_shrdbuf   =3D int (($mem_total * $settings{"ratio_shared_buff=
er"}) / $settings{"pgsql_pagesize_bytes"});
my $pgsql_max_shrdbuf   =3D int ($shm_max / $settings{"pgsql_pagesize_bytes=
"});

my $pgsql_shrdbuf;

my $pgsql_max_effective =3D int ($mem_kcache / $settings{"pgsql_pagesize_by=
tes"});
my $pgsql_rec_effective =3D int ($pgsql_max_effective * $settings{"ratio_ef=
fective_cache_size"});

my $pgsql_effective;

print "O/S : " . $os_type . ", Kernel : " . $os_vers . "\n";
print "Uptime : " . formatTime($uptime) . "\n";

print "Total memory : " . formatSize($mem_total) . ", Max shared memory : "=
 . formatSize($shm_max) . "\n";

print "  RECOMMEND : shared_buffers =3D " . $pgsql_rec_shrdbuf . "\n";
print "  MAX       : shared_buffers =3D " . $pgsql_max_shrdbuf . "\n";

$pgsql_shrdbuf =3D $pgsql_rec_shrdbuf;

if ($pgsql_rec_shrdbuf > $pgsql_max_shrdbuf)
{
	print "  Recomended value exceeds maximum. Will use max for now. You will =
need to tweak your shmmax parameter if you wish to increase this.\n";
	$pgsql_shrdbuf =3D $pgsql_max_shrdbuf;
}

print "Kernel cache : " . formatSize($mem_kcache) . "\n";
print "  RECOMMEND : effective_cache_size =3D " . $pgsql_rec_effective . "\=
n";
print "  MAX       : effective_cache_size =3D " . $pgsql_max_effective . "\=
n";

$pgsql_effective =3D $pgsql_rec_effective;

print "Looking for postgresql.conf.\n";

$settings{"path_pgsql_full"} =3D $settings{"path_pgsql"} . "/data/postgresq=
l.conf";

if (-e $settings{"path_pgsql_full"})
{
	print "  Found in " . $settings{"path_pgsql_full"} . ".\n";

	my $curr_shared_buffers       =3D 0;
	my $curr_effective_cache_size =3D 0;

	open (READ,"<" . $settings{"path_pgsql_full"});
	while(<READ>)
	{
		if (/^\#*shared_buffers\s*=3D\s*([0-9]+)/)
		{
			$curr_shared_buffers =3D $1;
		}
		if (/^\#*effective_cache_size\s*=3D\s*([0-9]+)/)
		{
			$curr_effective_cache_size =3D $1;
		}
	}
	close(READ);

	print "  shared_buffers       =3D " . $curr_shared_buffers;
	if ($curr_shared_buffers !=3D $pgsql_shrdbuf)
	{
		print " *";
	}
	print "\n";
	print "  effective_cache_size =3D " . $curr_effective_cache_size;
	if ($curr_effective_cache_size !=3D $pgsql_effective)
	{
		print " *";
	}
	print "\n";

	print "Do you wish to change these settings (y or n) ? ";
	my $line =3D <STDIN>;
=09
	$line =3D trim($line);

	if (($line eq "y") || ($line eq "Y"))
	{
		open(READ, "<" . $settings{"path_pgsql_full"});
		open(WRITE, ">" . $settings{"path_pgsql_full"} . "\.new");

		while(<READ>)
		{
			if (/^\#*shared_buffers\s*=3D\s*([0-9]+)/)
			{
				print WRITE "shared_buffers =3D " . $pgsql_shrdbuf . "\n";
			}
			elsif (/^\#*effective_cache_size\s*=3D\s*([0-9]+)/)
			{
				print WRITE "effective_cache_size =3D " . $pgsql_effective . "\n";
			}
			else
			{
				print WRITE $_;
			}
		}

		close (WRITE);
		close (READ);
	}
}

sub getOperatingSystem
{
	my $os_type =3D "";
	my $os_vers =3D 0;

	open (READOS, $settings{"path_uname"} . " -s |");
	$os_type =3D trim(<READOS>);
	close(READOS);

	open (READOS, $settings{"path_uname"} . " -r |");
	$os_vers =3D trim(<READOS>);
	close(READOS);

	return ($os_type, $os_vers);
}

sub getMemory
{
	my $mem_total;
	my $mem_used;
	my $mem_free;
	my $mem_shared;
	my $mem_buffers;
	my $mem_cached;

	my @line_parts;

	if ($os_type eq "Linux")
	{
		open (READMEM, "/proc/meminfo");
		while (<READMEM>)
		{
			@line_parts =3D split(" ");

			if ($os_type eq "Linux")
			{
				if ($line_parts[0] eq "Mem:")
				{
					($mem_total, $mem_used, $mem_free, $mem_shared, $mem_buffers, $mem_cac=
hed) =3D=20
						@line_parts[1..$#line_parts];
				}
			}
		}
		close(READMEM);
	}

	return ($mem_total, $mem_used, $mem_free, $mem_shared, $mem_buffers, $mem_=
cached);
}

sub getShmMax
{
	my $shm_max =3D 0;

	if ($os_type eq "Linux")
	{
		open (READOS, "/proc/sys/kernel/shmmax");
		$shm_max =3D trim(<READOS>);
		close(READOS);
	}

	return $shm_max;
}

sub getUptime
{
	my $uptime =3D 0;

	if ($os_type eq "Linux")
	{
		open (READUP, "/proc/uptime");
		($uptime) =3D split(" ", <READUP>);
		close(READUP);
	}

	return $uptime;
}

sub formatSize
{
	my $size =3D shift;

	my $newsize =3D $size;
	my $newtext =3D "bytes";
	my $newprec =3D 0;

	my $onekay =3D 1024;

	if ( ($newsize / $onekay) >=3D 0.5)
	{
		$newprec =3D 2;

		$newsize /=3D $onekay;
		$newtext =3D "kB";
		if ( ($newsize / $onekay) >=3D 0.5)
		{
			$newsize /=3D $onekay;
			$newtext =3D "MB";
			if ( ($newsize / $onekay) >=3D 0.5)
			{
				$newsize /=3D $onekay;
				$newtext =3D "GB";
				if ( ($newsize / $onekay) >=3D 0.5)
				{
					$newsize /=3D $onekay;
					$newtext =3D "TB";
					if ( ($newsize / $onekay) >=3D 0.5)
					{
						$newsize /=3D $onekay;
						$newtext =3D "PB";
						if ( ($newsize / $onekay) >=3D 0.5)
						{
							$newsize /=3D $onekay;
							$newtext =3D "EB";
							if ( ($newsize / $onekay) >=3D 0.5)
							{
								$newsize /=3D $onekay;
								$newtext =3D "ZB";
							}
						}
					}
				}
			}
		}
	}

	return sprintf("%.".$newprec."f %s", $newsize, $newtext);
}

sub formatTime
{
	my $time =3D shift;

	my $num;
	my $str;

	$num =3D $time;
	$str =3D "seconds";

	return $num . " " . $str;
}

sub trim
{
	my $string =3D shift;

	$string =3D~ s/^\s*(.*)/$1/ge;
	$string =3D~ s/(.*)\s*$/$1/ge;

	return $string;
}


------=_NextPart_000_0076_01C38F33.6CF81E90--