Re: High CPU load of mysqld - still after upgrade to 1.3.4

2018-10-10 Thread sj 




Hello Mario,

it's great news :-)

Janos

On 2018-10-10 20:42, Mario Pastoor wrote:


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.

Re: High CPU load of mysqld - still after upgrade to 1.3.4

2018-10-10 Thread Mario Pastoor 
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+
>>

Re: High CPU load of mysqld - still after upgrade to 1.3.4

2018-10-06 Thread Lucian Lazar 
Please unsubscribe me from this list.
Thank you

Get Outlook for iOS



On Sat, Oct 6, 2018 at 8:44 PM +0300, "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  NIVIRTRESSHR 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

Re: High CPU load of mysqld - still after upgrade to 1.3.4

2018-10-06 Thread sj 




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  NIVIRTRESSHR 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