Category Archives: Databases

Everything around databases. How to setup, tune, backup and restore.

Install pljava on OpenSuSE

Install SUN/Oracle JDK and PostgreSQL via zypper or Yast.

Download pljava here http://pgfoundry.org/frs/?group_id=1000038&release_id=1024

Create a directory for example /usr/src/pljava and extract pljava there.

create /etc/ld.so.conf.d/postgres.conf with this two lines in it if your using i386 cpu architecture.

/usr/lib/jvm/java/lib
/usr/lib/jvm/jre/lib/i386/server

now you have to run:

ldconfig

edit /var/lib/pgsql/data/postgresql.conf and add this two lines

custom_variable_classes = 'pljava'
pljava.classpath = '/usr/lib/postgresql/pljava.jar'

copy /usr/src/pljava/pljava.jar and /usr/src/pljava/pljava.so to /usr/lib/postgresql/

cp /usr/src/pljava/pljava.jar /usr/lib/postgresql/
cp /usr/src/pljava/pljava.so /usr/lib/postgresql/

restart postgres service:

rcpostgres restart

apply the install.sql:

su postgres -c "/usr/bin/psql -d template1 -f /usr/src/pljava/install.sql"

Install PostgreSQL 8.4 on CentOS

This tutorial explains a basic configuration using PostgreSQL and CentOS. For detailed information about configuration parameter please read the PostgreSQL documentation which is very good organized.

First thing you should do is updating your system to the last patch level. Be sure all the other installed software on this system works with the newest patches and then simply update your CentOS with:

yum update

Now reboot and install PostgreSQL. I prefer the 8.4 version on CentOS as long as there is no newer stable one which is delivered via the official repository.

yum install postgresql84-server

Before the first start the database needs to be initialized with the following command:

service postgresql initdb

The output looks like this:

postgresql initdb output

Start the service:

service postgresql start

and add it to your runlevel:

chkconfig –level 35 postgresql on

Now PostgreSQL is installed and running. Per default PostgreSQL data directory is located in /var/lib/pgsql/data. With no changes PostgreSQL service is only listening to localhost and is bound to 127.0.0.1. This is not practical if you plan to run database and application on different hosts via network connection.

To allow for example connections from your private network 192.168.1.0/24 add the following line in pg_hba.conf

host all all 192.168.1.0/24 trust

and change or add the following line in /var/lib/pgsql/data/postgresql.conf

listen_addresses = ‘*’

The database needs to be restarted after changing this files.

service postgresql restart

Be sure you only allow access to your database for hosts which really need it.

 

http://yum.pgsqlrpms.org/reporpms/repoview/pgdg-centos.html

MySQL basic tuning

Most webapplications are using a database to store there information nowadays. Often MySQL is used to do this task if it’s an cost sensitive organisation or person. MySQL is easy to install and manage in small environments but if you have many transactions and higher load on your database you should know how to speed up the database in a safe way.

Some time ago i did some research on this in the web and found some nice information, sites and tools.

Before we start we should think about the machine providing the database. Time is money and if you try to optimize a database on an 10 year old Pentium3 Server with 512MB Ram you should think about buying a better hardware and not spending hours of your time in such a task. Check your system logs to be sure everything with your hard disks is o.k if the performance was better in the past.

If that hardware is o.k make a backup.

First thing you should do if you want to optimize your database is to monitor how the database works now. We need some numbers to compare before and after optimization. Matthew Montgomer developed a nice script to analyze MySQL databases. You can find the script here.

Now review the output of the script and start optimizing you database.

After you are done restart your MySQL database and rerun the script to be sure your changes are o.k and the database is running.

Some more useful information are available here:

http://www.mysqlperformanceblog.com/