Hello Mario,

On 2018-10-05 21:03, Mario Pastoor wrote:

first of all a huge "thank you" for this piece of software, Janos! :-)

you are welcome :-)

Well, it's a nice troubleshooting anyway. The issue is that the select
query may not use the proper index, so it takes more and more time to
get the result.

For starters, I suggest to stop piler, then run explain against the sql

explain SELECT `id` FROM `attachment` WHERE
AND `ptr`=0 AND `size`=5044;

I assume it involves a full table scan which is slow.

Check if you have the following indices on table attachment:

create index `attachment_idx` on `attachment`(`piler_id`);
create index `attachment_idx2` on `attachment`(`sig`, `size`, `ptr`);
create index `attachment_idx3` on `attachment`(`ptr`);

Add if either of them is missing, then explain command should return something like

MariaDB [piler]> explain select * from attachment where sig='167bf31b0167aa4e74659a93735b2f66de46c327af080ece73b770c8f0b8b4d2' and ptr=0 and size=1182;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | attachment | ref | attachment_idx2,attachment_idx3 | attachment_idx2 | 270 | const,const,const | 1 | Using index condition |
1 row in set (0.001 sec)

Let me know how it goes.


I'm chasing a mysqld cpu load problem caused by queries from piler right
Hope that someone can help me on this.

Here's the full story :

I've installed our piler-system may 2015 on debian and it worked really
well over the past years!
As time passed by I recognised high CPU load peaks that grew during this
year. (see attached graphs - piler_cpu_load_0_1year /

A few hardware details :
Bare metal installation
HP ProLiant DL380 G6
2 x Intel Xeon 4-Core-CPU X5560  @2.80GHz
24GB RAM installed
SAS - Direct attach storage - RAID5

As you can see in "piler_cpu_load_0_1week_may_2018" these peaks occure
during normal business days/hours when there's a lot of e-mail traffic.

To avoid "have you already tried the current version?" I upgraded piler
from 1.2.0 build to current 1.3.4 yesterday and hoped this would fix the
high load problem. But you guess it ... it did not ;-)

I poked around a bit and the source of the hight load was always the
mysqld process. Same with 1.2.0 and 1.3.4

top output in that situation looks like this  :

top - 11:30:49 up  3:31,  3 users,  load average: 9,81, 9,91, 7,76
Tasks: 254 total,   2 running, 252 sleeping,   0 stopped,   0 zombie
%Cpu(s): 64,2 us,  0,9 sy,  0,0 ni, 34,8 id,  0,0 wa,  0,0 hi,  0,1 si,
 0,0 st
KiB Mem : 24680092 total, 5910708 free, 2615884 used, 16153500 buff/cache

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 719 mysql 20 0 2915788 2,224g 16948 S 1037 9,4 709:44.10 mysqld

MariaDB version info output :
mysql -V
mysql  Ver 15.1 Distrib 10.1.26-MariaDB, for debian-linux-gnu (x86_64)
using readline 5.2
mysqld -V
mysqld  Ver 10.1.26-MariaDB-0+deb9u1 for debian-linux-gnu on x86_64
(Debian 9.1)

Then i had a look what queries run during that load period :
I used "SHOW FULL PROCESSLIST" to view the running queries.

The following query showed up 10 times (see attached

SELECT `id` FROM `attachment` WHERE
AND `ptr`=0 AND `size`=5044

10 rows in set (18.17 sec) is the result running that query when there's
no load.
These queries stop apearing a few minutes after stopping piler
(rc.piler) and mysqld comes back to normal.

The I tried to figure out what
might be and it seems to be our logo that is attached / embeeded in
nearly every mail.

Question is .. why is piler running this query?
And can performance for that queries be optimized?
An aditional index needed or may our index is broken/wrong?
(see attached MariaDB_attachment_index.txt)

Any help would be appreciated :-)

Have a nice weekend folks


Reply via email to