Hello Janos, thank you for your're quick reply. I've checked the index last weekend and watched the behavior of the machine for the last days and I'm lad to say that CPU load is back to a normal level. (see the attached images) :-)
Problem was that our previous version was 1.2.0-pre1 and not the final. To be precise it was this id : https://bitbucket.org/jsuto/piler/src/20ed9b9e6d87?at=fixed_id And the index in that version for the attachments table looked like : create index `attachment_idx` on `attachment`(`piler_id`); create index `attachment_idx2` on `attachment`(`sig`); create index `attachment_idx3` on `attachment`(`ptr`); Here is what I've done to fix it (if anyone else needs this) : DROP INDEX `attachment_idx` on `attachment`; DROP INDEX `attachment_idx2` on `attachment`; DROP INDEX `attachment_idx3` on `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`); OPTIMIZE TABLE attachment; I'm not sure if the "OPTIMIZE TABLE attachment;" is really needed but as it won't do any harm I ran it in addition. So, this one is solved. I've some minor other problems regarding to the webui but that's not that important right now. As soon as I have some time I'll report here to the list. Thanks again. Mario Am 06.10.18 um 19:44 schrieb s...@acts.hu: > 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
