Please unsubscribe me from this list. Thank you Get Outlook for iOS<https://aka.ms/o0ukef>
On Sat, Oct 6, 2018 at 8:44 PM +0300, "s...@acts.hu" <s...@acts.hu<mailto:s...@acts.hu>> wrote: 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
