Hello Janos, hey list,
first of all a huge "thank you" for this piece of software, Janos! :-)
I'm chasing a mysqld cpu load problem caused by queries from piler right
now.
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 /
piler_cpu_load_0_1week_may_2018)
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
MariaDB_process_list_during_load.txt):
SELECT `id` FROM `attachment` WHERE
`sig`='ade5de3ad755a61885b7c2c41dc8101794743324ce06f411a926ca9bc705d401'
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
`sig`='ade5de3ad755a61885b7c2c41dc8101794743324ce06f411a926ca9bc705d401'
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
Mario
+------+-------+-----------+-------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info
| Progress |
+------+-------+-----------+-------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------+----------+
| 1055 | piler | localhost | piler | Execute | 6 | Sending data | SELECT
`id` FROM `attachment` WHERE
`sig`='ade5de3ad755a61885b7c2c41dc8101794743324ce06f411a926ca9bc705d401' AND
`ptr`=0 AND `size`=5044 | 0.000 |
| 1056 | piler | localhost | piler | Execute | 17 | Sending data | SELECT
`id` FROM `attachment` WHERE
`sig`='ade5de3ad755a61885b7c2c41dc8101794743324ce06f411a926ca9bc705d401' AND
`ptr`=0 AND `size`=5044 | 0.000 |
| 1057 | piler | localhost | piler | Execute | 25 | Sending data | SELECT
`id` FROM `attachment` WHERE
`sig`='ade5de3ad755a61885b7c2c41dc8101794743324ce06f411a926ca9bc705d401' AND
`ptr`=0 AND `size`=5044 | 0.000 |
| 1059 | piler | localhost | piler | Execute | 17 | Sending data | SELECT
`id` FROM `attachment` WHERE
`sig`='ade5de3ad755a61885b7c2c41dc8101794743324ce06f411a926ca9bc705d401' AND
`ptr`=0 AND `size`=5044 | 0.000 |
| 1060 | piler | localhost | piler | Execute | 25 | Sending data | SELECT
`id` FROM `attachment` WHERE
`sig`='ade5de3ad755a61885b7c2c41dc8101794743324ce06f411a926ca9bc705d401' AND
`ptr`=0 AND `size`=5044 | 0.000 |
| 1061 | piler | localhost | piler | Execute | 25 | Sending data | SELECT
`id` FROM `attachment` WHERE
`sig`='ade5de3ad755a61885b7c2c41dc8101794743324ce06f411a926ca9bc705d401' AND
`ptr`=0 AND `size`=5044 | 0.000 |
| 1062 | piler | localhost | piler | Execute | 17 | Sending data | SELECT
`id` FROM `attachment` WHERE
`sig`='ade5de3ad755a61885b7c2c41dc8101794743324ce06f411a926ca9bc705d401' AND
`ptr`=0 AND `size`=5044 | 0.000 |
| 1063 | piler | localhost | piler | Execute | 17 | Sending data | SELECT
`id` FROM `attachment` WHERE
`sig`='ade5de3ad755a61885b7c2c41dc8101794743324ce06f411a926ca9bc705d401' AND
`ptr`=0 AND `size`=5044 | 0.000 |
| 1064 | piler | localhost | piler | Execute | 25 | Sending data | SELECT
`id` FROM `attachment` WHERE
`sig`='ade5de3ad755a61885b7c2c41dc8101794743324ce06f411a926ca9bc705d401' AND
`ptr`=0 AND `size`=5044 | 0.000 |
| 1067 | root | localhost | NULL | Query | 0 | init | SHOW FULL
PROCESSLIST
| 0.000 |
| 1084 | piler | localhost | piler | Execute | 14 | Sending data | SELECT
`id` FROM `attachment` WHERE
`sig`='ade5de3ad755a61885b7c2c41dc8101794743324ce06f411a926ca9bc705d401' AND
`ptr`=0 AND `size`=5044 | 0.000 |
+------+-------+-----------+-------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------+----------+MariaDB [piler]> SHOW INDEX FROM attachment;
+------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
Index_comment |
+------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| attachment | 0 | PRIMARY | 1 | id | A
| 12301148 | NULL | NULL | | BTREE | |
|
| attachment | 1 | attachment_idx | 1 | piler_id | A
| 12301148 | NULL | NULL | | BTREE | |
|
| attachment | 1 | attachment_idx2 | 1 | sig | A
| 12301148 | NULL | NULL | | BTREE | |
|
| attachment | 1 | attachment_idx3 | 1 | ptr | A
| 1537643 | NULL | NULL | YES | BTREE | |
|
+------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
