HOWTO : Performance tuning for PostgreSQL on Ubuntu/Debian

Step 1 :

Edit postgresql.conf.

sudo nano /etc/postgresql/8.3/main/postgresql.conf

Step 2 :

The performance tuning setting is as the following :

(1) shared_buffers

Recommended : 0.25 * Available Memory

(2) work_mem

Recommended : Available Memory / max_connections
(If your queries tend to be more complicated, then divide that by 2. If you typically run very close to max_connections connections, then consider dividing by 2 again. If that gives you a number that isn’t at least 16MB, buy more memory.)

(3) maintenance_work_mem

Recommended : Available Memory / 8

(4) wal_buffers

Recommended : 8MB

(5) checkpoint_segments

Recommended : 16 to 128

(6) effective_cache_size

Recommended : Available Memory * 0.75

(7) cpu_tuple_cost

Recommended : 0.0030

(8) cpu_index_tuple_cost

Recommended : 0.0010

(9) cpu_operator_cost

Recommended : 0.0005

(10) fsync

Recommended : off

Warning : If “fsync” is set to “off”, you may encounter data loss when the power failure unless you have a battery backup unit at your hardware RAID card.

(11) max_connection

Recommended : 140% (100 clients average means 140 max connections)

(12) checkpoint_timeout

Recommended : 1h

Step 3 :

Restart PostgreSQL server.

sudo /etc/init.d/postgresql-8.3 restart

Step 4 :

If it produces error message and cannot restart, change the setting for “kernel.shmmax” on sysctl.conf as suggested.

Edit the sysctl.conf as suggested.

sudo nano /etc/sysctl.conf

Reference #1 :

The following is the my setting of a 8GB RAM server which is running PostgreSQL.

/etc/postgresql/8.3/main/postgresql.conf


max_connections = 140
shared_buffers = 2GB
temp_buffers = 8MB
work_mem = 16MB
maintenance_work_mem = 1GB
wal_buffers = 8MB
checkpoint_segments = 128
effective_cache_size = 6GB
cpu_tuple_cost = 0.0030
cpu_index_tuple_cost = 0.0010
cpu_operator_cost = 0.0005
fsync = off
checkpoint_timeout = 1h


Reference #2 :

The following is my setting of sysctl.conf on the same server.

/etc/sysctl.conf

kernel.sem = 250 32000 100 128
kernel.shmall = 2097152
kernel.shmmax = 2209914880
kernel.shmmni = 4096
fs.file-max = 262140
vm.vfs_cache_pressure = 50
vm.min_free_kbytes = 65536

net.core.rmem_default = 33554432
net.core.rmem_max = 33554432
net.core.wmem_default = 33554432
net.core.wmem_max = 33554432
net.ipv4.tcp_rmem = 10240 87380 33554432
net.ipv4.tcp_wmem = 10240 87380 33554432
net.ipv4.tcp_no_metrics_save = 1
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_timestamps = 1
net.ipv4.tcp_sack = 1
net.core.netdev_max_backlog = 5000
net.ipv4.tcp_mem = 786432 1048576 26777216
net.ipv4.ip_local_port_range = 1024 65535
net.ipv4.tcp_max_tw_buckets = 360000


Step 5 :

Add the following parameters to the kernel tag of Grub.

reservation,nodiratime,noatime

Step 6 :

sudo mount -a

If no error message produced, issue the following command to make it work.

sudo mount -o remount /

My Dream Home Network 2009

The following is the setting of “My Dream Home Network 2009″. Virtualization technology can reduce the number of servers you owned. It saves room, electricity and money as well as manpower to manage. Therefore, it has one more term – Green Computing.

Configuration of Server #A
Intel Q9550 Quad-Core CPU
8GB DDR2 RAM
2 x 160GB Hard drive on hardware RAID 1

Server #A
It is running Untangle as a router and Unified Threat Management System (UTM)

***********

Configuration of Server #0
2 x Intel Xeon E5420 Quad-Core CPU
16GB ECC DDR2 RAM
6 x 1TB Hard drive on hardware RAID 5EE

Server #0
Virtualization Server (that runs the following 4 servers)
Proxmox on Debian 5.01 Lenny

Server #1
Almost perfect and secure Ubuntu 9.04 LAMP Server

Server #2
WebDAV on Ubuntu 9.04 Server

Server #3
Sockso (Music Server) on Ubuntu 9.04 Server

Server #4
ntop on Ubuntu 9.04 Server

P.S. There will be one more server for bittorrent (coming soon).

**********

Router to router
Connecting wired router to wireless router

HOWTO : Sockso 1.1.8 (Music Server) on Ubuntu 9.04 Server

Sockso is a cross platform music server and requires no installation. She runs on a standalone personal computer or on a server. For running on personal computer with GUI, please refer to her official site.

The client computer requires no mp3 player to play the music but needs a Flash player.

The advantage of Sockso is that you can listen to your mp3 files at anytime and anywhere under the condition that fast internet connection is available. The disadvantage is that you should have at least IEEE 802.11g (54M) Wifi connection for smooth operation.

Sockso requires Sun Java only and it is requires no Apache or other web server to run.

Step 1 :

Sockso requires Sun Java to work. You should install the following packages.

sudo apt-get install sun-java6-bin sun-java6-fonts sun-java6-jre unzip

Step 2 :

Download the latest version of Sockso. The current version is 1.1.8 at the time of this writing.

wget http://sockso.googlecode.com/files/sockso-1.1.8.zip

unzip sockso-1.1.8.zip

sudo mkdir /usr/share/sockso

sudo cp -R /home/samiux/sockso-1.1.8/* /usr/share/sockso/*

sudo mkdir /var/sockso
sudo chmod -R 0755 /var/sockso

Step 3 :

Run the Sockso at command prompt.

sudo sh /usr/share/sockso/linux.sh --nogui --datadir /var/sockso

If you have some mp3 at /home/samiux/music and /home/mary/mp3, just runs the following command to make the music collection.

#SockSo#>coladd /home/samiux/music
#SockSo#>coladd /home/mary/mp3

If you want to list all collections, use the following command.
collist

If you want to delete one of the collections, use the following command.
coldel

Add a user to the Sockso.

#SockSo#>useradd samiux <your_password_here> samiux@gmail.com


To exit the #SockSo#;gt; command prompt.
exit

Step 4 :

Copy the init.d script to /etc/init.d/

sudo cp /usr/share/sockso/scripts/init.d/sockso /etc/init.d/sockso.pl

Create a sockso script file.

sudo nano /etc/init.d/sockso

-------- CUT HERE ---------
#!/bin/bash

perl /etc/init.d/sockso.pl $1

exit 0
-------- CUT HERE ---------

Edit the sockso.pl as the following.

sudo nano /etc/init.d/sockso.pl


use constant SOCKSO_DIR => "/usr/share/sockso/";

system( 'sh linux.sh --nogui --datadir /var/sockso > /dev/null 2>&1 &' );

Step 5 :

Now, you can start the sockso with the following command.

sudo chmod +x /etc/init.d/sockso
sudo chmod +x /etc/init.d/sockso.pl

sudo /etc/init.d/sockso start


You can also stop the sockso with the following command.

sudo /etc/init.d/sockso stop

Listen to the music with your browser.


http://192.168.0.100:4444


Step 6 :

Run the script automatically after reboot.

sudo update-rc.d sockso defaults

Remarks : Make sure you have stopped the Sockso before reboot or shutdown; otherwise, the mp3 databases would be corrupted. If so, you should delete everything inside /var/sockso and redo the Step 3.

Remarks : Don’t broadcast copyrighted musics or songs. Or, you may be in lawsuit.

That’s all. See you!

HOWTO : Logwatch on Ubuntu 9.04 Server

Logwatch reads your log files and can send you daily email about the most interesting parts.

Step 1 :

sudo apt-get update
sudo apt-get upgrade

sudo apt-get install logwatch

Step 2 :

sudo nano /usr/share/logwatch/default.conf/logwatch.conf

Change the following as shown :

Output = mail
Format = html
MailTo = samiux@gmail.com

Step 3 :

sudo nano /etc/cron.daily/00logwatch

/usr/sbin/logwatch --mailto samiux@gmail.com

Enjoy!

HOWTO : NTop on Ubuntu 9.04 Server

Install NTop to monitor all the traffic of your machines in your network.

Step 1 :

sudo apt-get install ntop rrdtool

Step 2 :

Set the admin password.

sudo ntop -A

Step 3 :

sudo ntop -d -L

You can add to the /etc/rc.local to make it execute when boot up automatically.

Step 4 :

Access ntop at
http://ip_address_ntop:3000


Enjoy!

HOWTO : WebDAV on Ubuntu 9.04 Server

WebDAV is a file manager that running on web server. You can access it like on your desktop. Easy and enjoyable.

Install Ubuntu 9.04 server as usual and select LAMP and OpenSSH when asked for choice. You can also install vsFTPd if you want to but it is optional.

Step 1 :

sudo a2enmod dav_fs
sudo a2enmod dav
sudo a2enmod dav_lock

sudo /etc/init.d/apache2 restart

Step 2 :

To create a virtual host for the WebDAV.

sudo mkdir -p /var/www/webdav
chown www-data /var/www/webdav

sudo cp /etc/apache2/sites-available/default /etc/apache2/sites-available/webdav

sudo nano /etc/apache2/sites-available/webdav

Make the a portion of the file as the following :


....
DocumentRoot /var/www/webdav
<Directory /var/www/webdav/>
   Options Indexes FollowSymLinks MultiViews
   AllowOverride None
   Order allow,deny
   allow from all
</Directory>
<Location />
   DAV On
   AuthType Basic
   AuthName "webdav"
   AuthUserFile /var/www/.passwd.dav
   Require valid-user
   DavMinTimeout 600
   <LimitExcept GET PUT HEAD OPTIONS POST>
      Require valid-user
   </LimitExcept>
</Location>
....


Step 3 :

sudo htpasswd -c /var/www/.passwd.dav samiux

chown root:www-data /var/www/.passwd.dav
chmod 640 /var/www/.passwd.dav

suod chmod -R 0777 /var/www/webdav
sudo chown www-data:www-data /var/www/webdav

sudo /etc/init.d/apache2 restart

Step 4 :

To test if WebDAV owrks or not.

sudo apt-get install cadaver

sudo cadaver http://localhost/

If you got “dav:/” prompt, enter “quit” to quit. Otherwises, fix the problem.

Step 5 (Windows only) :

Download NetDrive at
http://www.netdrive.net/
and set it accordingly. The port should be 80.

Now you can access your WebDAV server from Windows.

Step 6 (Ubuntu only) :

Go to “Place” > “Connect to Server“. Select “WebDAV (HTTP)“. Enter the IP of your WebDAV server and then press “Connect“. Submit the username and password. An icon will be displayed on your desktop. Double click it and go.

Now you can access your WebDAV server from Ubuntu.

That’s all!

UPDATED ON JUNE 30, 2009
If you open the OpenOffice files on the WebDAV by clicking, you can only open it in read only mode. However, there is method to overcome this problem. You open OpenOffice Write (for example), click the “Open file” and at the “Name of file” enter the following :

vnd.sun.star.webdav://192.168.0.100/openoffice_readonly_on_webdav.odt

Now you can edit and save it on WebDAV.

UPDATED ON JULY 3, 2009

For better performance (such as the speed that pasting files on the WebDAV), please fine tune your server as the following :

Performance tuning

UPDATED ON AUGUST 9, 2009

Make sure you have disabled the default site at Apache.

sudo a2dissite default

When you upload files by FTP, make sure you change the ownership of the files.

sudo chown -R www-data:www-data /var/www/webdav

HOWTO : Cacti on Ubuntu 9.04 server

*** THIS HOWTO IS INVALID AND PENDING FOR UPDATE ***

Cacti is a graphical network traffic analysis system based on Apache, PHP, MySQL, SNMP and RRDTool. It can monitor the machines in your network.

To install Cacti on Ubuntu 9.04 is very easy and straight forward.

Step 1 :

Install Ubuntu 9.04 server as Cacti server. Select LAMP and OpenSSH during the installation. For example, the IP of the Cacti server is 192.168.10.200.

Step 2 :

sudo apt-get install cacti

Step 3 :

At the client side (any machine or server in your network that to be monitored), it should be Ubuntu server or debian server. For other distributions, please use the related command to install snmpd accordingly.

sudo apt-get install snmpd

Step 4 :

sudo nano /etc/snmp/snmpd.conf

Add the following lines to the related sections :

com2sec notConfigUser 192.168.10.200 public
access notConfigGroup "" any noauth exact all none none

Save it and exit. Then restart the snmpd.

sudo /etc/init.d/snmpd restart

Step 5 :

At the browser, enter the address as the following :


http://192.168.10.200/cacti


User name and password are “admin“.

Accept the default settings.

Add the machine(s) that you want to monitor on the screen.

Step 6 :

Wait for the graphical charts to generate.

Enjoy!

HOWTO : Install Proxmox VE 1.3 on debian 5.01 (Lenny) AMD64

Since disk image of Proxmox VE 1.3 cannot install to my RAID 5EE or 6 hard drive space that larger than 2TB. I have a 3.6TB RAID 5EE hard storage. Therefore, I install it by packages on debian Lenny AMD64.

Installation of debian

Download the netinstall disk image of debian 5.01 (Lenny) AMD64 (should be AMD64, others are not suitable) and install to the server as is. Make sure you choose the entire disk with LVM. At the end of installation, you will be asked to select which services or servers to be installed. You just select "Base Standard" only.

Installation of Proxmox VE

Step 1 :

Log in the debian server as root and edit the following file.

nano /etc/apt/sources.list

Add the following line at the end of the file.

deb http://download.proxmox.com/debian lenny pve

Get and install the repository key.

wget -O- "http://download.proxmox.com/debian/key.asc" | apt-key add -

Update the repository and system.

apt-get update
apt-get upgrade

Step 2 :

Install Proxmox VE kernel.

apt-get install pve-kernel

Then, edit the following file when need.

nano /boot/grub/grub.cfg

Or

Delete the debian official kernel and image. Then issue the following command.

update-grub

Reboot debian server and make sure you select the PVE kernel when boot at the Grub Menu.

Step 3 :

Log in debian server as root and issue the following commands.

apt-get install proxmox-ve ntp postfix pve-manager

Connect to Proxmox VE web interface.


https://your_debian_server_ip


e.g. https://192.168.1.10

Step 4 :

Configure the vmbr0 interface at "System", "Network".

Configure bridge vmbr0

IP Address : 192.168.1.10 # your debian server's IP
Subnet Mask : 255.255.255.0
Gateway : 192.168.1.1 # your router or gateway's IP

Reboot your debian server.

Enjoy!

HOWTO : IPython for Python programming on Ubuntu

The current IPython is for Python 2.5 at this writing.

Install IPython as your Python programming environment.

sudo apt-get update
sudo apt-get install python-setuptools

sudo easy_install IPython

Once IPython is installed, you can run it as the following :

ipython

Type quit() to quit.

HOWTO : Security enhanced your Ubuntu 9.04 LAMP server with AppArmor

Step 1 :

Check if AppArmor is enabled or not. And make sure MySQL profile is enabled too.

sudo apparmor_status

Step 2 :

Create a profile of Apache2.

sudo aa-genprof apache2

sudo nano /etc/apparmor.d/usr.sbin.apache2

Add the following lines within ^DEFAULT_URI bracket.

/usr/sbin/suexec2 rix,
/usr/share/apache2/** r,
/var/log/apache2/** rwl,
/var/xoops/** r,
/var/www/xoops/** r,

Step 3 :

Put the profile in complain mode.

sudo aa-complain /etc/apparmor.d/usr.sbin.apache2
sudo /etc/init.d/apache2 restart

Step 4 :

After running the XOOPS for a while, we can update the profiles.

sudo aa-logprof

When the prompt ask for your selection, choose “A (Add)” to add a rule to the profiles. Save the file at the end of the process.

You can repeat this step when necessary.

Step 5 :

After running the XOOPS for a longer time and found no error, you can make the profile in enforce mode. Before doing so, make sure you have conducted the Step 4 once more.

sudo aa-enforce /etc/apparmor.d/usr.sbin.apache2
sudo /etc/init.d/apache2 restart

Step 6 (Optional) :

If you encounter any error, you can disable the profile.

sudo ln -s /etc/apparmor.d/usr.sbin.apache2 /etc/apparmor.d/disable/
sudo apparmor_parser -R < /etc/apparmor.d/usr.sbin.apache2
sudo rm /etc/aparmor.d/disable/usr.sbin.apache2

Reference :
(1) Ubuntu Documentation (AppArmor)
(2) Introduction to AppArmor
(3) Share your profiles
(4) AppArmor support threads