Leveraging Vertica Performance by Reducing CPU System Calls

Bentzi Mor

Bentzi Mor

Bentzi is a big data DBA, who specializes in big data solutions such as Vertica, Cassandra and Hadoop. Overall system performance is his favorite challenge. In his spare time, Bentzi likes to ride his mountain bike and travel to new destinations.

Bentzi Mor | 25 Jun 2017 | System

Tags: CPU, web development

What is the connection between kernel system calls and database performance, and how can we improve performance by reducing the number of system calls?

Performance of any database system depends on four main system resources:

  • CPU
  • Memory
  • Disk I/O
  • Network

Performance will increase while tuning or scaling each resource – this blog will cover the CPU resource.It’s important to note that whenever we release a bottleneck in the system, we might just encounter another one. For example, when improving CPU performance the database load shifts to IO, so unless our storage is capable of delivering more IOPS, we might not actually see the improvement we hoped for. But don’t be discouraged, performance tuning is sometimes a game of whack-a-mole…

We all know that the more processing power available for your server, the better the overall system is likely to perform. Especially when the CPU spends the majority of its time in user state rather in kernel state. At Taboola we struggled with two cases which caused Kernel state to consume a lot of CPU; your system might have the same problems.

How do we collect CPU metrics at Taboola?

Metrics are collected using Sensu and Graphite, dashboards are built with GrafanaThe Sensu CPU plugin cpu-metrics.rb collects the following CPU metrics: user, nice, system, idle, iowait, irq, softirq, steal, guest. Since the Vertica process runs in a CPU user space that has been niced, we will mostly focus on the nice and system metrics.

System calls CPU impact by calling Vertica new_time function

What are system calls?

System calls are how a program enters the kernel to perform a task. Programs use system calls to perform a variety of operations such as: creating processes, performing network and file IOs, and much more.

If a system call is such a basic internal interface between an application and the Linux kernel, why should we care about it?

  • Sometimes this is the case, However if the system calls’ CPU usage remains high for long periods of time, then it could be an indication that something isn’t right. A possible cause of system calls’ spikes could be a problem with a driver/kernel module or sometimes due to high concurrency
  • To run a system call the CPU needs to switch from the user to kernel mode, that’s called context switch, context switching itself has performance costs.

 At Taboola, all timestamp data is saved in UTC timezone. Aggregated reports for raw data tables produce reports converted to the publisher (customer) timezone using Vertica’snew_time function  to convert between time zones.

For example, aggregating actions by hours:

The following image represents a server’s overall CPU usage over time, stacking all types of CPU metrics:

System CPU Usage:

We can see regular spikes in usage reaching up to 74%. We need to profile our CPU calls to find the root cause.

How do we profile CPU calls?

Linux perf is a powerful tool to diagnose this, and a FlameGraph can be used to virtualize the perf report by drawing a graph.

Useful perf commands:

  • perf top – realtime events
  • perf record  – run a command and record its profile into perf.data
  • perf report – read perf.data (created by perf record) and display the profile


Running the following commands will record and report CPU calls for a duration of 5 minutes:

The /tmp/report.out file output shows the following:

If flame graph is installed, we can run:

Flame Graph (kernel.svg) example:

Each call to new_time function triggers the OS call getTZoffset, while many simultaneous calls are causing kernel contention expressed by spin locks.


We removed the use of the new_time function, and started joining our data to  “conversion tables” instead.  

We created two timezone tables that contain a local time for each timezone and the corresponding time in UTC:

1. time_zone_daily_conversion

2. time_zone_hourly_conversion

Daily Table Definition

Sample Content

Hourly Table Definition

Sample Content

Example of aggregating actions by hours using the conversion tables:


Query time reduced by 50-75%, system calls CPU metric graph shows:


  • It is essential to monitor server metrics (CPU, memory, load, and others) to identify performance bottlenecks.
  • Perf is a great profiling tool to drill down into the major system calls.
  • In our case we found the issue was caused by spin locks, but other customers’ clusters or use cases might suffer from other kernel calls.