coderamblings.com

Narayan, regarding tuning for database import scripts
I've created a file called /etc/my.cnf.edit that has some initial
settings changes with larger read,read_rnd,sort and join buffers (all
upped to 2M). Usually its not a good idea to have them that high, as
it increases connection time, but for a batch conversion job its
likely worth it. I've also upped the table_cache (which had under a
50% hit rate in the initial configuration..opening tables is an
expensive operation usually) and increased the key_buffer from 16M to
100M (the index size for all of the tables in the main db is hovering
at around 80M, so 100 is a safe guess to start with..we may have to
increase it from there). The tmp_table_size may need to be increased
as well, there are quite a few temp tables hitting the disk, but I
don't want to do that without actually seeing it happen.

=======
Khalid

These are the non comment lines in postgresql.conf

hba_file = '/etc/postgresql/8.2/main/pg_hba.conf'
ident_file = '/etc/postgresql/8.2/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/8.2-main.pid'
listen_addresses = 'localhost'
port = 5432
max_connections = 100
unix_socket_directory = '/var/run/postgresql'
shared_buffers = 24MB
max_fsm_pages = 153600
log_line_prefix = '%t '
stats_row_level = on
autovacuum = on
datestyle = 'iso, dmy'
lc_messages = 'en_CA.UTF-8'
lc_monetary = 'en_CA.UTF-8'
lc_numeric = 'en_CA.UTF-8'
lc_time = 'en_CA.UTF-8'

For MySQL, this is the default delivered with Ubuntu (and I imagine Debian would be close).

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
query_cache_limit = 1M
query_cache_size = 16M
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
skip-bdb

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]

[isamchk]
key_buffer = 16M

!includedir /etc/mysql/conf.d/

Note that there are no config files in the includedir.

What I normally do (not for this benchmark, but for tuning) is:

Comment out the bin-log lines (log_bin, expire_logs_days, max_bin_log_size). This takes of some load, but more importantly saves disk space on some starved VPSs.
Add skip-innodb, which saves some 100MB of RAM for the MySQL.
These are irrelevant to the benchmark at hand anyway, but is useful to know.

For larger web sites, I use a configuration similar to the one listed here:

http://2bits.com/articles/mysql-my-cnf-configuration-for-a-large-drupal-...

======

Bill Moran
Once it's installed, there's another step called "initializing
the cluster" ... which is basically creating an empty database.
Some packaging systems might do this for you. If PostgreSQL
starts, then it's done (it won't implicitly create an empty
system).

PG is designed to allow multiple version on a single machine.
I don't know how many people actually do this, but it's a godsend
when you need it. The point is that each "cluster" (as it's called)
is a directory with all the configuration and database files for
that running instance -- usually /usr/local/pgsql or something like
like that. do a "find / -name postgresql.conf" and you'll find
it.

That's the first file you want to look at. There's a lot of
esoteric tuning options, but the obvious one near the beginning
is called "listen_address" which defaults to "localhost" only.
(Although different package systems may install a custom config
file, so you should check)

> Also, add
> a "drupal" user with "drupal" password which can do... anything
> whatever it means. This process is clear on MySQL and after two
> years with occassionally working with pgsql, I could not figure
> out its grant system. I am daft, I know.

I doubt you're daft, but I'm confused as to where the problem is.
PostgreSQL's grant system is simpler than MySQL's in my
experience.

The only thing that might be complex is that PG versions greater
than 8 have unified users and groups into a single concept called
a "role". If you just think of a "user" as a role with login
permissions, you'll be OK.

If you connect with the psql command, you can issue "\du" to get
a list of users. I'm guessing by default that there's only the
default superuser on your system. To create a new one:
CREATE ROLE drupal WITH LOGIN;
Or, to create it with superuser permissions:
CREATE ROLE drupal WITH LOGIN SUPERUSER;
You can also tweak roles after creation:
ALTER ROLE drupal PASSWORD = 'somepassword';
Then create a database:
CREATE DATABASE drupal;
And tweak settings as you like:
GRANT all ON DATABASE drupal TO drupal;

PG has a two-pronged approach to security. The role system which
I just described is actually the second layer. The first layer
is called "host-based authentication". These two layers combined
basically equate to MySQL's role system.

In the same directory as the postgresql.conf file, you'll find
a pg_hba.conf file. In this are lines that control what users
from what hosts can connect to what databases. For your
purposes, you probably want lines like:

host all all 127.0.0.1/32 trust
local all all trust

The first means that any user connecting via the loopback is
allowed to connect without a password. The second means that
any user can connect to any database via the local unix socket
without a password.

You can get more specific if you want:
# Allow anyone from local network to connect to database myaccount
# as user drupal, as long as they know the password
host drupal myaccount 192.168.5.0/24 password
# Allow anyone to connect to database drupal as any user from a
# single remote machine without a password
host all drupal 172.16.56.21/32 trust

psql is your friend. Some useful commands for seeing what your
database looks like:
# Show all roles (The "u" is legacy from when they were users)
\du
# Show all databases
\l
# Show the table structure of table "users"
\d users
# Show the permissions on all objects in the database
\dp
# Show the built-in help
\?