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
query:
explain SELECT `id` FROM `attachment` WHERE
`sig`='ade5de3ad755a61885b7c2c41dc8101794743324ce06f411a926ca9bc705d401'
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.
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
