How to Vacuum SQLite

This post is a summary on how to effectively VACUUM SQLite databases. Actually open source project like Firefox and Liferea were significantly hurt by not efficiently VACUUMing their SQLite databases. For Firefox this was caused by the Places database containing bookmarks and the history. In case of Liferea it was the feed cache database. Both projects suffered from fragmentation caused by frequent insertion and deletion while not vacuuming the database. This of course caused much frustration with end users and workarounds to vacuum manually.

In the end both projects started to automatically vacuum their sqlite databases on demand based on free list threshold thereby solving the performance issues. Read on to learn how to perform vacuum and why not to use auto-vacuum in those cases!

1. Manual VACUUM

First for the basics: with SQLite 3 you simply vacuum by running:

sqlite3 my.db "VACUUM;"

Depending on the database size and the last vacuum run it might take a while for sqlite3 to finish with it. Using this you can perform manual VACUUM runs (e.g. nightly) or on demand runs (for example on application startup).

2. Using Auto-VACCUM

Note: SQLite Auto-VACUUM does not do the same as VACUUM! It only moves free pages to the end of the database thereby reducing the database size. By doing so it can significantly fragment the database while VACUUM ensures defragmentation. So Auto-VACUUM just keeps the database small!

You can enable/disable SQLite auto-vacuuming by the following pragmas:

PRAGMA auto_vacuum = NONE;
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA auto_vacuum = FULL;

So effectively you have two modes: full and incremental. In full mode free pages are removed from the database upon each transaction. When in incremental mode no pages are free'd automatically, but only metadata is kept to help freeing them. At any time you can call

PRAGMA incremental_vacuum(n);

to free up to n pages and resize the database by this amount of pages.

To check the auto-vacuum setting in a sqlite database run

sqlite3 my.db "PRAGMA auto_vacuum;"

which should return a number from 0 to 2 meaning: 0=None, 1=Incremental, 2=Full.

3. On Demand VACUUM

Another possibility is to VACUUM on demand based on the fragmentation level of your sqlite database. Compared to peridioc or auto-vaccum this is propably the best solution as (depending on your application) it might only rarely be necessary. You could for example decide to perform on demand VACUUM upon startup when the empty page ratio reaches a certain threshold which you can determine by running

PRAGMA page_count;
PRAGMA freelist_count;

Both PRAGMA statements return a number of pages which together give you a rough guess at the fragmentation ratio. As far as I know there is currently no real measurement for the exact table fragmentation so we have to go with the free list ratio.

How-to Dump Keys from Memcache

You spent already 50GB on the memcache cluster, but you still see many evictions and the cache hit ratio doesn't look good since a few days. The developers swear that they didn't change the caching recently, they checked the code twice and have found no problem.

What now? How to get some insight into the black box of memcached? One way would be to add logging to the application to see and count what is being read and written and then to guess from this about the cache efficiency. For to debug what's happening we need to set how the cache keys are used by the application.

An Easier Way

Memcache itself provides a means to peek into its content. The memcache protocol provides commands to peek into the data that is organized by slabs (categories of data of a given size range). There are some significant limitations though:

  1. You can only dump keys per slab class (keys with roughly the same content size)
  2. You can only dump one page per slab class (1MB of data)
  3. This is an unofficial feature that might be removed anytime.

The second limitation is propably the hardest because 1MB of several gigabytes is almost nothing. Still it can be useful to watch how you use a subset of your keys. But this might depend on your use case.

If you don't care about the technical details just skip to the tools section to learn about what tools allow you to easily dump everything. Alternatively follow the following guide and try the commands using telnet against your memcached setup.

How it Works

First you need to know how memcache organizes its memory. If you start memcache with option "-vv" you see the slab classes it creates. For example

$ memcached -vv
slab class   1: chunk size        96 perslab   10922
slab class   2: chunk size       120 perslab    8738
slab class   3: chunk size       152 perslab    6898
slab class   4: chunk size       192 perslab    5461
[...]

In the configuration printed above memcache will keep fit 6898 pieces of data between 121 and 152 byte in a single slab of 1MB size (6898*152). All slabs are sized as 1MB per default. Use the following command to print all currently existing slabs:

stats slabs

If you've added a single key to an empty memcached 1.4.13 with

set mykey 0 60 1
1
STORED

you'll now see the following result for the "stats slabs" command:

stats slabs
STAT 1:chunk_size 96
STAT 1:chunks_per_page 10922
STAT 1:total_pages 1
STAT 1:total_chunks 10922
STAT 1:used_chunks 1
STAT 1:free_chunks 0
STAT 1:free_chunks_end 10921
STAT 1:mem_requested 71
STAT 1:get_hits 0
STAT 1:cmd_set 2
STAT 1:delete_hits 0
STAT 1:incr_hits 0
STAT 1:decr_hits 0
STAT 1:cas_hits 0
STAT 1:cas_badval 0
STAT 1:touch_hits 0
STAT active_slabs 1
STAT total_malloced 1048512
END

The example shows that we have only one active slab type #1. Our key being just one byte large fits into this as the smallest possible chunk size. The slab statistics show that currently on one page of the slab class exists and that only one chunk is used.

Most importantly it shows a counter for each write operation (set, incr, decr, cas, touch) and one for gets. Using those you can determine a hit ratio!

You can also fetch another set of infos using "stats items" with interesting counters concerning evictions and out of memory counters.

stats items
STAT items:1:number 1
STAT items:1:age 4
STAT items:1:evicted 0
STAT items:1:evicted_nonzero 0
STAT items:1:evicted_time 0
STAT items:1:outofmemory 0
STAT items:1:tailrepairs 0
STAT items:1:reclaimed 0
STAT items:1:expired_unfetched 0
STAT items:1:evicted_unfetched 0
END

What We Can Guess Already...

Given the statistics infos per slabs class we can already guess a lot of thing about the application behaviour:

  1. How is the cache ratio for different content sizes?
    • How good is the caching of large HTML chunks?
  2. How much memory do we spend on different content sizes?
    • How much do we spend on simple numeric counters?
    • How much do we spend on our session data?
    • How much do we spend on large HTML chunks?
  3. How many large objects can we cache at all?

Of course to answer the questions you need to know about the cache objects of your application.

Now: How to Dump Keys?

Keys can be dumped per slabs class using the "stats cachedump" command.

stats cachedump <slab class> <number of items to dump>

To dump our single key in class #1 run

stats cachedump 1 1000
ITEM mykey [1 b; 1350677968 s]
END

The "cachedump" returns one item per line. The first number in the braces gives the size in bytes, the second the timestamp of the creation. Given the key name you can now also dump its value using

get mykey
VALUE mykey 0 1
1
END

This is it: iterate over all slabs classes you want, extract the key names and if need dump there contents.

Dumping Tools

There are different dumping tools sometimes just scripts out there that help you with printing memcache keys:

PHP simple script Prints key names.
Perl simple script Prints keys and values
Ruby simple script Prints key names.
Perl memdump Tool in CPAN module Memcached-libmemcached
PHP memcache.php Memcache Monitoring GUI that also allows dumping keys
libmemcached peep

Does freeze your memcached process!!!

Be careful when using this in production. But can thereby workaround the 1MB limitation and really dump all keys.

Save tarball space with "make dist-xz"

I read about Ubuntu considering it for 13.04 and I think compressing with XZ really makes a difference. When creating a tarball for Liferea 1.9.6 the following compression ratios can be achieved:

Compression Size Extract Tarball With
uncompressed 8,0MB
make dist 1,88MB tar zxf ...
make dist-bzip2 1,35MB tar jxf ...
make dist-zlma 1,16MB tar Jxf ...
make dist-xz 1,14MB tar Jxf ...

XZ as well as zlma is supported by automake starting with version 1.12.

Also check here for an in-depth speed and efficiency comparison of the Linux compression zoo.

Filtering dmesg Output

Many administrators just run "dmesg" to check a system for problems and do not bother with its options. But actually it is worth to know about the filtering and output options of the most recent versions (Note: older distros e.g. CentOS5 might not yet ship these options!).

You always might want to use "-T" to show human readable timestamps:

$ dmesg -T
[...]
[Wed Oct 10 20:31:22 2012] Buffer I/O error on device sr0, logical block 0
[Wed Oct 10 20:31:22 2012] sr 1:0:0:0: [sr0]  Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE
[...]

Additionally the severity and source of the messages is interesting (option -x):

$ dmesg -xT
[...]
kern  :err   : [Wed Oct 10 20:31:21 2012] Buffer I/O error on device sr0, logical block 0
kern  :info  : [Wed Oct 10 20:31:21 2012] sr 1:0:0:0: [sr0]  Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE
[...]

Now we see that only one of those example message lines was an actual error. But we can even filter for errors or worse ignoring all the boot messages (option -l):

$ dmesg -T -l err,crit,alert,emerg
[...]
[Wed Oct 10 20:31:21 2012] Buffer I/O error on device sr0, logical block 0
[...]

In the same way it is possible to filter the facility (the first column in the -x output). For example this could return:

$ dmesg -T -f daemon
[...]
[Wed Oct 10 19:57:50 2012] udevd[106]: starting version 175
[Wed Oct 10 19:58:08 2012] udevd[383]: starting version 175
[...]

In any case it might be worth remembering:

  • -xT for a quick overview with readable timestamps
  • -T -l err,crit,alert,emerg to just check for errors

I recently created a simple dmesg Nagios plugin to monitor for important messages with Nagios. You can find it here.

How to Test for Colors in Shell Scripts

When watching thousands of log lines from some long running script you might want to have color coding to highlight new sections of the process or to have errors standing out when scrolling through the terminal buffer.

Using colors in a script with tput or escape sequences is quite easy, but you also want to check when not to use colors to avoid messing up terminals not supporting them or when logging to a file.

How to Check for Terminal Support

There are at least the following two ways to check for color support. The first variant is using infocmp

$ TERM=linux infocmp -L1 | grep color
[...]
	max_colors#8,
[...]

or using tput

$ TERM=vt100 tput colors
-1

$ TERM=linux tput colors
8

tput is propably the best choice.

Checking the Terminal

So a sane check for color support along with a check for output redirection could look like this

#!/bin/bash

use_colors=1

# Check wether stdout is redirected
if [ ! -t 1 ]; then
    use_colors=0
fi

max_colors=$(tput colors)
if [ $max_colors -lt 8 ]; then 
    use_colors=0
fi

[...]

This should ensure no ANSI sequences ending up in your logs while still printing colors on every capable terminal.

Use More Colors!

And finally if normal colors are not enough for you: use the secret 256 color mode of your terminal! I'm not sure how to test for this but it seems to be related to the "max_pairs" terminal capability listed by infocmp.

Liferea 1.9.6 released

Fixes mass downloading enclosures. Introduces support for downloading using the steadyflow download manager. Removes support for curl/wget. Improves spacing of browser tab buttons. Prevent DnD problems with Google Reader.

Download the recent Liferea source from: liferea.sf.net

Overview on Automated Linux Package Vulnerability Scanning

I got some really helpful comments on my recent post Scan Linux for Vulnerable Packages. The suggestions on how to do it on Debian and Redhat made me wonder: which distributions provide tools and what are they capable of? So the goal is to check wether each distribution has a way to automatically check for vulnerable packages that need upgrades.

Below you find an overview of the tools I've found and the distributions that might not have a good solution yet.

Distribution Scanner Rating Description
Debian debsecan superb Easy to use. Maintained by the Debian testing team. Lists packages, CVE numbers and details.
Ubuntu debsecan useless They just packaged the Debian scanner without providing a database for it!
And since 2008 there is a bug about it being 100% useless.
CentOS
Fedora
Redhat
"yum list-security" good Provides package name and CVE number. Note: On older systems there is only "yum list updates".
OpenSuSE "zypper list-patches" ok Provides packages names with security relevant updates. You need to filter the list yourself or use the "--cve" switch to limit to CVEs only.
SLES "rug lu" ok Provides packages names with security relevant updates. Similar to zypper you need to do the filtering yourself.
Gentoo glsa-check bad There is a dedicated scanner, but no documentation.
FreeBSD Portaudit superb No Linux? Still a nice solution... Lists vulnerable ports and vulnerability details.

I know I didn't cover all Linux distributions and I rely on your comments for details I've missed.

Ubuntu doesn't look good here, but maybe there will be some solution one day :-)

Scan Linux for Vulnerable Packages

How do you know wether your Linux server (which has no desktop update notifier or unattended security updates running) does need to be updated? Of course an

apt-get update && apt-get --dry-run upgrade

might give an indication. But what of the package upgrades do stand for security risks and whose are only simple bugfixes you do not care about?

Check using APT

One useful possibility is apticron which will tell you which packages should be upgraded and why. It presents you the package ChangeLog to decided wether you want to upgrade a package or not. Similar but less details is cron-apt which also informs you of new package updates.

Analyze Security Advisories

Now with all those CERT newsletters, security mailing lists and even security news feeds out there: why can't we check the other way around? Why not find out:

  1. Which security advisories do affect my system?
  2. Which ones I have already complied with?
  3. And which vulnerabilities are still there?

My mad idea was to take those security news feeds (as a start I tried with the ones from Ubuntu and CentOS) and parse out the package versions and compare them to the installed packages. The result was a script producing the following output:

screenshot of lpvs-scan.pl

In the output you see lines starting with "CEBA-2012-xxxx" which is CentOS security advisory naming schema (while Ubuntu has USN-xxxx-x). Yellow color means the security advisory doesn't apply because the relevant packages are not installed. Green means the most recent package version is installed and the advisory shouldn't affect the system anymore. Finally red, of course meaning that the machine is vulnerable.

Does it Work Reliably?

The script producing this output can be found here. I'm not yet satisfied with how it works and I'm not sure if it can be maintained at all given the brittle nature of the arbitrarily formatted/rich news feeds provided by the distros. But I like how it gives a clear indication of current advisories and their effect on the system.

Maybe persuading the Linux distributions into using a common feed format with easy to parse metadata might be a good idea...

How do you check your systems? What do you think of a package scanner using XML security advisory feeds?

visudo: #includedir sudoers.d

WTF. Today I fell for this sudo madness and uncommented this "comment" in /etc/sudoers:

#includedir /etc/sudoers.d

which gives a

visudo: >>> /etc/sudoers: syntax error near line 28 <<<

Let's check the "sudoers" manpage again: full of EBNF notations! But nothing in the EBNF about comments being commands. At least under Other special characters and reserved words one finds

The pound sign ('#') is used to indicate a comment (unless it is part
of a #include directive or unless it occurs in the context of a user
name and is followed by one or more digits, in which case it is treated
as a uid).  Both the comment character and any text after it, up to the
end of the line, are ignored.

Cannot this be done in a better way?

How-to Write a Chef Recipe for Editing Config Files

Most chef recipes are about installing new software including all config files. Also if they are configuration recipes they usually overwrite the whole file and provide a completely recreated configuration. When you have used cfengine and puppet with augtool before you'll miss a possibility to edit files.

In cfengine2...

You could write

editfiles:
{ home/.bashrc
   AppendIfNoSuchLine "alias rm='rm -i'"
}

While in puppet...

You'd have:

augeas { "sshd_config":
  context => "/files/etc/ssh/sshd_config",
  changes => [
    "set PermitRootLogin no",
  ],
}

Now how to do it in Chef?

Maybe I missed the correct way to do it until now (please comment if this is the case!) but there seems to be no way to use for example augtool with chef and there is no built-in cfengine like editing. The only way I've seen so far is to use Ruby as a scripting language to change files using the Ruby runtime or to use the Script ressource which allows running other interpreters like bash, csh, perl, python or ruby.

To use it you can define a block named like the interpreter you need and add a "code" attribute with a "here doc" operator (e.g. <<-EOT) describing the commands. Additionally you specify a working directory and a user for the script to be executed with. Example:

bash "some_commands" do
    user "root"
    cwd "/tmp"
    code <<-EOT
       echo "alias rm='rm -i'" >> /root/.bashrc
    EOT
end

While it is not a one-liner statement as possible as in cfengine it is very flexible. The Script resource is widely used to perform ad-hoc source compilation and installations in the community codebooks, but we can also use it for standard file editing.

Finally to do conditional editing use not_if/only_if clauses at the end of the Script resource block.

Syndicate content Syndicate content