CPU vs MySQL Query Performance

In this post, I want to share a case I worked recently to increase a query speed from 3~4 sec to 0.3~0.4 sec.

An user compared the query loading speed for a small size of VPS vs a large siez of VPS.

The spec for small size VPS


The spec for large size VPS


Small VPS processes 0.5 sec and large VPS processes 3~4 sec for a query with small no of data rowset. By using EXPLAIN command, the query needs to process 51k of data row. It does not really looks much and SSD disk can handle well but why slow?

Let do some tune of this large size VPS by adjusting KVM cpu model to host-model. There are 2 cpu model of host-model and host-passthrough. Neither of it produce a siginificant performance difference, so I keep it as host-model. This link consists more of KVM cpu model definition.

I played a little bit on Centos 7 tuned command by setting the recommeded profile “virtual-guest”. Okay. The speed reduced to 0.7~0.8 sec.

User didn’t seem fine with the speed. What’s next?

I have migrated this vps to a different node with different CPU on node level. The speed reduced till 0.35~0.5 sec. Woah. That’s a big difference this time.

Original node CPU – Intel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz

New node CPU – Intel(R) Xeon(R) Silver 4208 CPU @ 2.10GHz

View the CPU comparison at here. So, the winner here is Intel(R) Xeon(R) Silver 4208 CPU.

In conclusion, CPU performance plays a role on database query performance. Of course, well organized database query with MySQL Indexing can further speed up the query duration.

<span class="entry-utility-prep entry-utility-prep-cat-links">Posted in</span> Uncategorized | Leave a comment

Rspamd Introduction

Rspamd is an advanced spam filtering system that allows evaluation of messages by a number of rules including regular expressions, statistical analysis and custom services such as URL black lists. Each message is analysed by Rspamd and given a spam score.

According to this spam score and the user’s settings, Rspamd recommends an action for the MTA to apply to the message, for example, to pass, reject or add a header. Rspamd is designed to process hundreds of messages per second simultaneously, and provides a number of useful features.

What Can RSPAMD do?

  1. https://rspamd.com/features.html
  2. Check emails for DKIM, DMARC, SPF, IP Address reputation, Greylisting, Rate limiting and much more.
  3. Has a cool web interface for checking scanning statistic.
  4. need a lot of testing, tweaking and research to unlock its full potential.
  5. comparison with spamassasin https://rspamd.com/comparison.html
  6. Integration with Postfix, Exim and lot more.

Web Interface

Bounced back to the spammer.

Mail log

milter-reject: END-OF-MESSAGE from unknown[x.x.x.x]: 5.7.1 Spam message rejected;

<span class="entry-utility-prep entry-utility-prep-cat-links">Posted in</span> Uncategorized | Leave a comment

How to Set up SSH Tunneling

A Secure Shell (SSH) tunnel consists of an encrypted tunnel created through an SSH protocol connection. Users may set up SSH tunnels to transfer unencrypted traffic over a network through an encrypted channel.

Testing environment

Host A : Can’t connect cPanel port 2086 of Host B. Behind firewall connection that restricts outgoing port 2086.

Host B : cPanel server

Creating SSH tunneling from Host A

ssh -f -N -p8288 root@HostB -L 1188:HostB:2086

-N flag means do not execute a remote command.

-f flag instruct ssh to go to background just before command execution.

The ssh tunnel command will run at background. P/S : Can kill the PID 1363 when do not need this connection.
Run netstat command to check the forwarding port 1188.
Executing curl localhost:forwardingport to test connecting Host B port 2086 through forwarding port 1188.

Creating SSH tunneling through Putty.

Enter HostB IP and HostB SSH port
Expand Connection > SSH > Tunnels. Enter a source port that is allowed at local connection. Destination will be HostB IP and the port to connect.

After that, connect to HostB. Keep the tunnel session active by running top command.

Run netstat to check port 1122 is listened on local server.
Browse from a browser.

With this tunneling, I can connect to HostB WHM although outgoing port 2087 is blocked at local connection.

<span class="entry-utility-prep entry-utility-prep-cat-links">Posted in</span> Uncategorized | Leave a comment

Network speed testing method

1. speedtest-cli or https://www.speedtest.net/


2. iperf3

Download at https://iperf.fr/iperf-download.php.

Can test the network connectivity speed in between 2 host or source/destination.

Server command

iperf3 -s

Client command

iperf3 -c x.x.x.x -V -t 10

Replace x.x.x.x with server IP

Based on above test, the connection bandwidth speed in between these 2 host can hit up to 50Mbps.

<span class="entry-utility-prep entry-utility-prep-cat-links">Posted in</span> Uncategorized | Leave a comment

Observing Hyper-v Guest VM CPU Usage

I would like to share that the host CPU usage in task manager does not show the CPU usage from guest machine level. You may refer https://blogs.msdn.microsoft.com/virtual_pc_guy/2008/02/28/hyper-v-virtual-machine-cpu-usage-and-task-manager/ for a better explanation.
To get the performance bottleneck, performonce monitor function is needed. A counter name Hyper-V Hypervisor Logical Processor(_Total)\% Total Runtime must be referred. According to https://docs.microsoft.com/en-us/windows-server/administration/performance-tuning/role/hyper-v-server/detecting-virtualized-environment-bottlenecks, if the Hyper-V Hypervisor Logical Processor(_Total)\% Total Runtime counter is over 90%, the host is overloaded. In this example, it is necessary to consider upgrade to a higher CPU spec host server to get a better CPU performance. Please refer below graph where the reported value from this particular counter is always exceed 90% as sample.

​Also, the CPU usage reported from Hyper-v manager also reflect the usage as like above counter’s purpose.

In this example, this server CPU is overloading and is not enough to sustain the usage from the guest virtual machine.

<span class="entry-utility-prep entry-utility-prep-cat-links">Posted in</span> Uncategorized | Leave a comment

WordPress Site Caching for improving TTFB

We are going to looking on optimizing WordPress site through caching plugin to improve TTFB.

What is TTFB?

Time to first byte (TTFB) is a metric for determining the responsiveness of a web server. It measures the amount of time between creating a connection to the server and downloading the contents of a web page. TTFB is impacted by these factors : 1) sending a request from a client machine to the server, 2) processing that request on the server and generating a response, and 3) sending the response from the server to the client.

Sample TTFB for a demo site https://wordpress2.marslert.com/. cPanel environment with

Apache Event mode

Test Run A

Test Run B

Other cache plugin like WP Rocket (paid), W3 total cache, WP Super Cache, Cache Enabler, etc are popular as well. Catch them at here. Also, we can use cache application like memcache, varnish, etc. At the end of the post, I will demonstrate the result of varnish cache.

I will demonstrate the after caching effect through W3 total cache.

Test Run A

Test Run B

TTFB speed is improved with the deployment of cache plugin.

Sample TTFB for a demo site https://wordpress1.marslert.com/. Plesk environment with

Apache Event Mode

Test Run A without nginx cache and w3 total cache.Test Run B without nginx cache and w3 total cache.

Turning on nginx proxy mode from Plesk Apache & nginx Settings.

Test Run A with nginx cache.Test Run B with nginx cache.

What if trying it Varnish caching? I loaded Varnish engine under docker on the Plesk server.

HTTP header that show varnish.

Test Run A with varnish cache proxy.Test Run B with varnish cache proxy.

Varnish is a great caching tool which not just caching the content, reduce bandwidth and also can defense DDOS attack. Read https://varnish-cache.org/intro/ for more information.It can further optimizing website loading speed, TTFB, etc.

Stay tune for remote varnish caching server post that serve different content websites from multiple origin server ( web server ). Cheers.

<span class="entry-utility-prep entry-utility-prep-cat-links">Posted in</span> Uncategorized | Leave a comment