Configure CentOS 7 to use SSD TRIM on encrypted filesystems

Today most systems use SSD:s in some way, so surely a modern OS supports them out of the box? Well, yes and no. CentOS 7 (and hence Red Hat 7 and probably most other Linux distros) correctly identifies that a SSD device supports the TRIM command, which is essential for good long-term performance, but it doesn’t use it and if you add encryption or LVM on top of the device the TRIM support is not automatically retained. See this blog for a very good explanation.

In case the link stops working, you basically need to add discard to all lines for SSD devices with encryption in /etc/crypttab and if you are using LVM you need to add issue_discards = 1 to /etc/lvm/lvm.conf. That enables support for discard/TRIM. To issue TRIM commands in real time, add discard in /etc/fstab as well. However, that is not recommended for optimal performance. It is much better to wait and use fstrim with a weekly cron job. Something like:

#!/bin/sh
for fs in $(lsblk -o MOUNTPOINT,DISC-MAX,FSTYPE | \
       grep -E '^/.* [1-9]+.* ' | awk '{print $1}' | \
       sort -u); do
   fstrim "$fs"
done

The code has been copied and adapted a bit from the link above.

Improve logging performance with Log4j

Log4j 1.2 is old, but still in very wide use. It works and for most applications performance is good enough. For log-intensive applications there are some tricks. The AsyncAppender can act as a buffer between the application and slower loggers (including FileAppender in some cases – be sure to use a large buffer though!), but that only helps for short bursts.

The FileAppender itself and its subclasses can work in buffered or immediate mode. Buffered mode is enabled with the BufferedIO parameter. When true the appender uses an 8k buffer and flushes the entire buffer to disk when it is full. This is much faster than the default operation, which flushes immediately for every log event. The disadvantage is that log entries are delayed and unless the appender is closed cleanly (a problem with most applications I’ve seen) many log events may be lost.

I have written a flush appender that can help. It can flush the file appenders when an event with high severity is logged (ensuring that errors are written to disk immediately). It can also flush and convert the file appenders to immediate mode when a specific message is logged, for example “Shutting down…”. That way no messages are lost during the shutdown process. The appender is available from Github and Maven Central (name.wramner.log4j:FlushAppender).

Beware of OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

What can make a simple query that normally runs in split seconds using the primary key turn into a 19 minute full scan? Well, many things, but in this case non of the usual suspects. The primary key was usable and rebuilding it made no difference. We updated the already recent statistics to no avail. There was no SQL Profile defined and the query did not use hints. It turned out that at some point the parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES had been set and as a result Oracle had happily collected baselines in order to prevent performance regressions, as the sales message goes. Deleting the baseline fixed the problem and the optimizer immediately switched to an index range scan on the primary key.

SQL baselines may serve a purpose, but if you don’t trust the optimizer to do the right thing, why would you trust it to collect baselines automatically in order to guide itself in the future? Beware of OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES and either create the baselines under controlled circumstances or let the optimizer do its job and correct it with profiles where necessary!

Oracle performance tuning scripts

As a developer I rarely get access to the graphical consoles for Oracle. The DBA:s tend to keep them locked down. It is usually easier to get access to the built-in performance views. Over the years I have gathered a few scripts that help me diagnose performance issues. They are nothing fancy, I’m posting them partly for my own benefit as this makes them easy to find when I need them.

I have used the scripts for a long time, so I don’t remember their origin. A likely source is Tom Kyte’s excellent Effective Oracle by Design, a must read for anyone into Oracle performance tuning.

High-level view

select  wait_class, total_waits,
  round(100 * (total_waits / sum_waits),2) pct_waits,
  time_waited, round(100 * (time_waited / sum_time),2) pct_time
from (select wait_class, total_waits, time_waited
        from v$system_wait_class where wait_class != 'idle'),
     (select sum(total_waits) sum_waits, sum(time_waited) sum_time
        from v$system_wait_class where wait_class != 'idle')
  order by 5 desc;

Top wait events

select h.event "wait event",
       sum(h.wait_time + h.time_waited) "total wait time"
  from v$active_session_history h, v$event_name e
  where h.sample_time between sysdate - 1/24 and sysdate
  and h.event_id = e.event_id
  and e.wait_class  'idle'
  group by h.event
  order by 2 desc;

Table scans

select to_char(sn.begin_interval_time,'dd mon hh24:mi:ss') start_date,
       to_char(sn.end_interval_time,'dd mon hh24:mi:ss') end_date,
       newmem.value-oldmem.value fts
  from dba_hist_sysstat oldmem,
       dba_hist_sysstat newmem,
       dba_hist_snapshot sn
  where sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
    and newmem.snap_id = sn.snap_id
    and oldmem.snap_id = sn.snap_id-1
    and oldmem.stat_name = 'table scans (long tables)'
    and newmem.stat_name = 'table scans (long tables)';

Top SQL

select h.user_id, u.username, sql.sql_text,
       sum(h.wait_time + h.time_waited) "total wait time"
  from v$active_session_history h,
       v$sqlarea sql,
       dba_users u,
       v$event_name e
  where h.sample_time between sysdate - 1/24 and sysdate
    and h.sql_id = sql.sql_id
    and h.user_id = u.user_id
    and h.sql_id is not null
    and e.event_id = h.event_id
    and e.wait_class  'idle'
   group by h.user_id,sql.sql_text, u.username order by 4 desc;

This is usually enough to point me in the right direction. It is even easier with the graphical user interface, but SQL always works!

Logback configuration overhead

The Logback logging framework for Java is great, but one thing I personally miss when I compare it to its venerable Log4j ancestor is the property configurator. Sure, both XML and Groovy are more powerful and there are convenient converters that can produce configuration files based on the old Log4j property files, but what does it cost? XML is much more expensive than simple properties, not to mention loading an entire scripting engine!

I decided to find out. My test case is far from perfect, but it should at least give a hint. It is a simple program. It first measures the time in nanoseconds used for initializing logging; then it allocates memory in a loop in order to force garbage collections. After a few minutes and several collections it triggers a full gc, reports the amount of memory used and sleeps, making it possible to connect and perform additional measurements using jstat or jconsole or some other tool.

I ran the program ten times with each configuration and tested with the simple fallback configuration, which logs to the console, as well as a similar XML and Groovy configuration. The results were not very surprising.

Initialization with the simple configuration was roughly 100 ms. XML configuration required about 200 ms, doubling the startup time. Groovy needed a full second. Heap usage for the application was about 7M with the simple configurator, 10M with XML and 16M with Groovy. Finally, the permanent generation (classes and so on) was 4.4M with the simple configuration, 6.5M with XML and 13M with Groovy.

For an Enterprise application the overhead is trivial, I would worry more about the possible stability issues with a complex scripting engine than with the performance, but for a small application where startup time and footprint matters it makes sense to use a simple method. Perhaps a property-based configurator would be a good addition after all!