0

While testing Monetdb on Linux I can see a lot of IO write activity. The data set is 2GB (30 Million rows) and system has 120GB free RAM

The query: 

select sum(hits) from (select "hour","minute","second", server, count(*) as hits from iislogs group by server, "hour","minute","second") as xxx;

I run a single query it takes ~400 ms to scan 30 Mil rows, but if i run same query 10 times in parallel each takes 30 s! The 48 CPUs are at 15% and I can see lots of disk writes, no swap activity. I've looking for tweaking some kind of setting or limitation without any luck.

Any idea about why ?

UPDATE:

I've disabled logging and write active has lowered but still having some and no improvement at all.

GBrian
  • 1,031
  • 11
  • 28

1 Answers1

1

You could try to allow more dirty pages in the page cache. We had some success with the following virtual memory settings:

sudo sysctl -w vm.swappiness=0
sudo sysctl -w vm.dirty_expire_centisecs=6000000
sudo sysctl -w vm.dirty_background_ratio=90
sudo sysctl -w vm.dirty_writeback_centisecs=6000000
sudo sysctl -w vm.dirty_ratio=90
Hannes Mühleisen
  • 2,542
  • 11
  • 13
  • Thanks, should I have to restart the service or something else after ? *vm.dirty_background_ratio=90* Was not present but i set anyway. Sadly no improvement at all right now, tons of unused resources there :( – GBrian Aug 26 '15 at 05:25
  • Restart MonetDB, yes. No improvement either? – Hannes Mühleisen Aug 26 '15 at 05:53
  • Not at all :( check that link http://tinypic.com/r/mtl547/8 single query is ~400ms, multiquery is ~4s running 10 parallel queries, I tried desperate a funnel/multiplexer no improvement. Let me know which details can help you. This is the script I'm using (maybe the error is there): https://docs.google.com/document/d/1G0aMtvxysMYXlH2E8ytOTt-kupAbnLfP5YsWgwcnj7c/edit?usp=sharing – GBrian Aug 26 '15 at 09:28
  • Well, 400ms times 10 is 4 seconds, so not particularly surprised there. What you could also try is to disable inter-query parallelism: `set optimizer='sequential_pipe';` after connecting to the DB. – Hannes Mühleisen Aug 26 '15 at 10:08
  • Ops, that killed the server thanks! ;). Now only one CPU is working 100% per query and query time is 2 seconds, but now I know a bit more about this optimizer, i'll play around those settings. – GBrian Aug 26 '15 at 14:43
  • What about mounting a ramdisk for the db farm ? I'd like to load all the data in memory. – GBrian Aug 30 '15 at 14:21
  • @GBrian, have tried that. It works great, but obviously your data will be gone if the machine reboots.... – Hannes Mühleisen Aug 31 '15 at 12:22
  • Hannes: good news! I've been digging into the optimizer documentation and tested with different values and types of queries. I realized that same optimization has benefits or counterparts depending the query. At the end I'm having that amazing performance saw on my initial tests! I'll keep you posted! Thanks so much. – GBrian Sep 04 '15 at 07:53