Mysql query

[s s]

Hello,

Our server was overloaded today when these 2 process happened: 1. Long mysql query: +-------+-------+-----------+-------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+-------+-----------+-------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ | 81339 | piler | localhost | piler | Query | 5578 | Sorting result | SELECT sent AS oldest_record_ts FROM metadata WHERE deleted=0 and sent > 837381600 ORDER BY | | 81355 | piler | localhost | piler | Sleep | 5545 | | NULL

2. System process: 3374 ? Ss 1:08 /usr/local/sbin/piler -d 18410 ? S 7:18 _ /usr/local/sbin/piler -d 19642 ? S 0:00 | _ sh -c /usr/bin/pdftotext -enc UTF-8 4000000052cd5f032a87eaa40014163ba4f0.a3.bin - 19643 ? R 23:32 | _ /usr/bin/pdftotext -enc UTF-8 4000000052cd5f032a87eaa40014163ba4f0.a3.bin - 24196 ? S 2:27 _ /usr/local/sbin/piler -d 16850 ? S 0:00 | _ sh -c /usr/bin/pdftotext -enc UTF-8 4000000052cd4cc012837aa400cf4428a788.a3.bin - 16851 ? R 89:25 | _ /usr/bin/pdftotext -enc UTF-8 4000000052cd4cc012837aa400cf4428a788.a3.bin -

When i killed mysql query 81339 things got better. But i had to kill pdftotext processes also to appease the server.

Can you please check if index should be created DB or what settings can be tuned for "Sorting result " process?

Thanks.

[Janos SUTO]

I assume you have upgraded the gui. In this case please run the following sql query (it may take a while to complete with a huge database, so perhaps it's a good idea to stop piler while it's running):

create index metadata_idx9 on metadata(sent);

With pdftotext unfortunately I can't do much.

[Janos SUTO]

Any update on the matter?

[s s]

We added index metadata_idx9 and at the same time upgraded hardware: from 4 to 8 CPU cores (wmvare). For now we see load spike only at the time when indexer.main.sh working.

[Janos SUTO]

OK, that's a good news