PostgreSQL get back storage occupied by deleted tuples or records

Posted on in Categories Howto last updated September 30, 2006

PostgreSQL is a free object-relational database server/database management system, released under a flexible BSD-style license. It has features such as Functions, Indexes, Triggers, MVCC and much more.

With vacuum you can reclaim storage occupied by deleted tuples. In normal PostgreSQL operation, tuples that are deleted or obsolete by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore, it is necessary to do VACUUM periodically, especially on frequently updated tables.

vacuumdb is a utility for cleaning a PostgreSQL database. vacuumdb will also generate internal statistics used by the PostgreSQL query optimizer.

For example type the following command:
$ vacuumdb -v -z -a
$ vacuumdb -v -z -a -U USERNAME -W


  • -v: Verbose output
  • -z: Calculate statistics for use by the optimizer
  • -a: Vacuum all databases
  • -U USERNAME: Use USERNAME name to connect as
  • -W: Prompt for password

How to tune MySQL server for performance

Posted on in Categories MySQL, Tips, Tuning last updated September 29, 2006

MySQL Performance site, published tips about tuning your MySQL server Performance.

From the article:
My favorite question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server straight after installation, assuming it was installed with default settings.

I’m surprised how many people fail to provide any reasonable answer to this question, and how many servers are where in wild which are running with default settings.

Even though you can tune quite a lot of variables in MySQL Servers only few of them are really important for most common workload. After you get these settings right other changes will most commonly offer only incremental performance improvements.

Read more at mysqlperformanceblog

How to PF Firewall Ruleset Optimization

Posted on in Categories Howto, OpenBSD last updated September 28, 2006

OpenBSD journal has published excellent PF Firewall Ruleset Optimization tutorial.

From the article:
“This is the first installment in a series of three articles about PF. I originally wrote them as chapters for a book, but then publication was cancelled. Luckily, the rights could be salvaged, and now you get to enjoy them as exclusives.”

Firewall Ruleset Optimization topics:

  • Goals
  • The significance of packet rate
  • When pf is the bottleneck
  • Filter statefully
  • The downside of stateful filtering
  • Ruleset evaluation
  • Ordering rulesets to maximize skip steps
  • Use tables for address lists
  • Use quick to abort ruleset evaluation when rules match
  • Anchors with conditional evaluation
  • Let pfctl do the work for you
  • Testing Your Firewall (read)
  • Firewall Management (upcoming)

Read more at OpenBSD journal

Inside a compact embedded Linux – Hacking MediaMVP

Posted on in Categories Beyond nixCraft, Linux, News last updated September 28, 2006

The Hauppauge MediaMVP is a network media player. It consists of a hardware unit with remote control, along with software for a Windows PC. Out of the box, it is capable of playing video and audio, displaying pictures, and “tuning in” to Internet radio stations. Alternative software is also available to extend its capabilities. It can be used as a front end for various PVR projects.

The MediaMVP is popular with some PVR enthusiasts because it’s inexpensive and relatively easy to modify.

As an MP3 and MPEG player, the Hauppauge MediaMVP lets you play digital media through your television set. As a tightly purposed embedded device, it is an excellent example of a compact Linux implementation on minimal hardware.

The MediaMVP is similar in some ways to the TiVo: it’s intended to be a pure appliance, performing only specified functions. Unlike the TiVo, it doesn’t make a significant effort to prevent users from loading their own software, and sure enough, users have done just that.

This article looks at the “standard” install provided by Hauppage and also looks at the MediaMVP Media Center (MVPMC) project, which is developing a custom user environment for the system.

Linux mail server: How to fight image spam

Posted on in Categories Howto, Linux, Security, UNIX last updated September 28, 2006

This is getting out of control these days.

There is an interesting article at LWN about Fighting image spam.

From the article:
A number of spammers have been evading filters like SpamAssassin (SA) recently by encoding their messages as images. SA already has a set of rules that are meant to combat image spam, but the more recent messages (typically for stock scams or pharmacy products) have been crafted to avoid them. This would indicate, once again, that spammers are using SA to pre-test their messages and are modifying them to get through. SA developers, however, are up to the challenge and two specific countermeasures have been released.

How to MySQL backup and data recovery with mysql-zrm

Posted on in Categories Backup, Data recovery, Howto, MySQL last updated September 28, 2006

This guide describes how to back up and recover your MySQL databases with mysql-zrm on a Debian Sarge system. mysql-zrm is short for Zmanda Recovery Manager for MySQL, it is a new tool that lets you create full logical or raw backups of your databases (regardless of your storage engine and MySQL configuration), generate reports about the backups, verify the integrity of the backups, and recover your databases. It can also send email notifcations about the backup status, and you can implement multiple backup policies (based on your applications and based on time (e.g. daily, weekly, etc.)).

Linux add ethtool duplex settings to a network card permanently

Posted on in Categories Linux, Networking, Tips, Ubuntu Linux last updated July 16, 2007

I have already written about how to find and change your network interface speed (NIC), duplex or auto negotiate settings on Linux using ehttool command line options.

However, these settings are not permanent. If you reboot the system or if you just
need to upgrade/downgrade your port speed, run the ehtool command once your port change by network administrator. (If you want to read about how to make Windows 2000/2003 server port speed change, read my previous article.)

Changing your Network Interface Speed, Duplex or Auto Negotiate settings on Red Hat Linux

To set the interface speed, duplex or auto negotiation on Linux system boot up (make settings permanent), you need edit /etc/sysconfig/network-scripts/ifcfg-eth0 file for eth0 interface. This file used by Red Hat enterprise Linux, Fedora core, Cent Os etc.

Open the file:
# vi /etc/sysconfig/network-scripts/ifcfg-eth0

Append following line:
ETHTOOL_OPTS="speed 100 duplex full autoneg off"

Save and close the system. It will set the eth0 device to 100Mbs, full duplex, with the auto negotiation off at boot time. You can simply restart the networking (it will disconnect all ssh or ftp session) or restart the server. Depend upon traffic and load it may take upto 1 minute to setup a new port speed:
# /etc/init.d/network restart

If you want 1000Mbs set line as follows:
ETHTOOL_OPTS="speed 1000 duplex full autoneg off"Update: if above command failed to work for 1000Mbps use following command (see below in comment sections for discussion) :ETHTOOL_OPTS="speed 1000 duplex full autoneg on"

Debian or Ubuntu Linux permanent settings

Under Debian or Ubuntu Linux just create a script as follows:
# vi /etc/init.d/100Mbs
$ sudo vi /etc/init.d/100Mbs
Append following lines:
SPEED="100 duplex full"
case "$1" in
echo -n "Setting eth0 speed 100 duplex full...";
$ETHTOOL -s $DEV speed $SPEED;
echo " done.";;
exit 0
Save and close the file. Setup executable permission:
# chmod +x /etc/init.d/100MbsOR$ sudo chmod +x /etc/init.d/100Mbs

Now run script when Debian or Ubuntu Linux boots up. Use update-rc.d command install System-V style init script links:# update-rc.d 100Mbs defaultsOR# sudo update-rc.d 100Mbs defaultsOutput:

 Adding system startup for /etc/init.d/100Mbs ...
   /etc/rc0.d/K20100Mbs -> ../init.d/100Mbs
   /etc/rc1.d/K20100Mbs -> ../init.d/100Mbs
   /etc/rc6.d/K20100Mbs -> ../init.d/100Mbs
   /etc/rc2.d/S20100Mbs -> ../init.d/100Mbs
   /etc/rc3.d/S20100Mbs -> ../init.d/100Mbs
   /etc/rc4.d/S20100Mbs -> ../init.d/100Mbs
   /etc/rc5.d/S20100Mbs -> ../init.d/100Mbs

Reboot the system to take effect or just type scrit name:
# /etc/init.d/100Mbs startOR$ sudo /etc/init.d/100Mbs start

See also: