- users-list - monetdb.org

Large query taking a long time
by Dennis Pallett 16 Jul '14

16 Jul '14

Hi all, I'm executing a very large query with MonetDB and it takes very long before the result is calculated (e.g. several minutes) whereas PostgreSQL does it in less than 100 ms. The query itself is a very simple select + sum query but the WHERE condition has more than a thousand equality tests on the primary key (ckey) of the table. It basically looks like this: SELECT sum(countAggr) FROM sys.london_hav_neogeo_pa WHERE ckey=16947611436 OR ckey=16947611437 OR ckey=16947611438 OR ckey=16947611439 OR ckey=16947611440 OR ckey=16947617908 OR ckey=16947617909 OR ckey=16947617910 OR ckey=16947617911 OR ckey=16947617912 [.. several hundreds of lines removed ..] OR ckey=289411309830 OR ckey=289411309831 OR ckey=289411309832 OR ckey=289411309833 OR ckey=289411309834 OR ckey=289411309835 OR ckey=289411309836 OR ckey=289411309837; Does anyone know why it's taking so long with MonetDB and if there is a way to significantly improve performance? Best regards, Dennis

4 7

TMP space deallocation
by Radovan Bičiště 08 Jul '14

08 Jul '14

Hi, My database started consuming disk space. Here is how: 1. run procedure. 2. procedure fails with server crash due to insufficient disk space. It eats up all the space and crash. 3. restart the database but the disk space remain allocated. All changes by the procedure are rolled back. There are inserts but not that many to eat up 10 GB of space. When I check sys.storage, it does not show any large table. My guess is that it allocates temporary space and it does not deallocate it after database start. It deallocates a bit but not everything. Is there any way how to shrink it manually? It already ate around 40 GB space. The whole database is around 130 GB so the quick dump and restore is not possible. Thank you for any tips, Radovan Biciste

1 0

MonetDB and cpu resources management
by Dimitris Karampinas 03 Jul '14

03 Jul '14

Hi, I'm trying to set the maximum number of cores using gdk_nr_threads parameter but it doesn't seem to work for me. I've tried "variants" of this command: monetdbd set gdk_nr_threads=4 start /my/dbfarm but it seems like gdk_nr_threads is not a supported property. Additionally to this, I would like to ask how does MonetDB handle concurrent user sessions and the available cpu resources ? The manual says there is support for "inter- and intra- parallelism". Can someone roughly explain me what to expect or give me a pointer to the code that is responsible for resource management ? Cheers! Dimitris

6 12

Index usage
by Danila B. Savchuk 27 Jun '14

27 Jun '14

I started using Monetdb two weeks ago. Since this is not RDBMS , what is the general strategy of using and building indexes on tables ?

2 1

Problem at start dbfarm
by Dimitris Karampinas 18 Jun '14

18 Jun '14

Hi, I've built from source MonetDB and I'm following the steps at the tutorial: https://www.monetdb.org/Documentation/UserGuide/Tutorial I've successfully created the dbfarm but I can't proceed beyond the second step. Monetdb returns after ~30 seconds with the following message: $ dbms/bin/monetdbd start data/ another monetdbd is already running I 'm sure no other monetdb instance is running in the system. For the record, get all looks as follows: $ dbms/bin/monetdbd get all data/ property value hostname sgs-r815-02 dbfarm data status a monetdbd is serving this dbfarm, but a pidfile was not found/is corrupt mserver unknown (failed to connect to monetdbd) logfile data/merovingian.log pidfile data/merovingian.pid sockdir /tmp port 50000 exittimeout 60 forward proxy discovery true discoveryttl 600 control no passphrase <unknown> mapisock /tmp/.s.monetdb.50000 controlsock /tmp/.s.merovingian.50000 I had no problems with the whole process when I installed the system on my desktop a few days ago. Any hints ? Dimitris

4 6

Unresponsive mserver
by Pete Hollobon 18 Jun '14

18 Jun '14

Hi, We have recently seen MonetDB become unresponsive several times. Upon restarting, after mserver has been sent the TERM signal, merovingian.log contains lots of "unknown state: 4" entries, repeated many times without linefeeds. We're running Jan2014-SP2 (official binaries) on Ubuntu server 14.04. We had the same database running on Jan2014-SP1 on Ubuntu 12.10 for several months without this issue. I have submitted as a bug ( https://www.monetdb.org/bugzilla/show_bug.cgi?id=3499) Thanks, Pete.

1 0

odbc driver compliance: NULL strings or "" in SQLTables and SQLColumns API calls
by László Sándor 18 Jun '14

18 Jun '14

Stata fixed how they will talk to the MonetDB driver in the future, but they say the MonetDB driver is less standard, so perhaps we should be aware of this: We were passing a NULL string to the ODBC > api call SQLTables and SQLColumns where the MonetDB driver wants an empty > string > (""). Some other drivers need a NULL string passed in to work (Oracle > being > one) so I believe this is a driver bug, but I have patched the problem in > Stata. > I have added code in -odbc- to check for the MonetDB driver and pass an > empty > string in that case.

2 1

How do I monitor or speed up insert from aggregation query?
by Joe Wass 17 Jun '14

17 Jun '14

I'm running the following query over a database of 1,000,000,000 items. insert into date_aggregate (count, a, date) select count(date) as count, a, date, from input group by date, a order by date; I expect it to take a certain length of time, but I have no way of knowing how long. The system queue table has a 'null' estimate. All I know from that table is that the query is running. Is there a way to get any more information about its progress? I would be happy to just wait, but I don't know if I should be waiting for hours or days or weeks. The monetdbd process is only using 14% CPU (on a 6 core machine) and 14% RAM (the machine has 7GB and lots of SSD swap). My machine is basically idling when I want it to run as fast as possible. Running Jan2014-SP2, 64-bit on Ubuntu 14.04. Any hints about measuring progress and improving resource utilisation? Joe

2 1

MonetDB just reads and reads when restoring a dump
by Bryan Senseman 16 Jun '14

16 Jun '14

All, I know I have asked this before, but I am still plagued by this error. I am restoring a 50GB database from Linux to Windows, the dump file created via mclient is ~53GB. I am reading that file from an SSD and loading the database onto another SSD. This has been running for 2 DAYS! Using almost all available memory 16GB. Using Windows Resoure Monitor, the mserver5.exe process is just reading tail files for hours. The pattern is that the disk usage will grow by some amount say 5gb (I’m assuming the size of the table actually being loaded) and then it will spend 6-12 hours reading what it just wrote. Why? Can this be eliminated? I removed all indexes on the fact tables so it isn’t indexing (although that runs almost instantly anyway). Only about 10% cpu utilization. Thanx, Bryan -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

2 1

"LIMIT + ORDER BY" TypeException
by George Ramonov 15 Jun '14

15 Jun '14

Hi, I am still learning about monetdb internals, so I hope someone can enlighten me here. I was playing around with monetdb, and run into the following problem: Suppose we have a table CREATE TABLE test.simple_table("id" INTEGER); INSERT INTO test.simple_table VALUES (1),(2),(3),(4),(5); Let's say I want to run a simple query: SELECT COUNT(*) AS val FROM test.simple_table ORDER BY val DESC; +----+ | v | +==+ | 5 | +----+ Runs fine. However, if add a *LIMIT* clause: SELECT COUNT(*) AS val FROM test.simple_table ORDER BY val DESC LIMIT 5; I get the following TypeException:user.s37_7[17]:'algebra.markT' undefined in: _30:any := algebra.markT(_27:oid, _29:oid) program contains errors Now things let's add a primitive *HAVING* clause SELECT COUNT(*) AS val FROM test.simple_table HAVING 1 > 0 ORDER BY val DESC LIMIT 5; +----+ | v | +==+ | 5 | +----+ And things are magically fine again. Can someone explain what's going on? Thanks, George Ramonov UC Berkeley B.S. Electrical Engineering & Computer Science '13 <http://www.linkedin.com/in/georgeramonov>

2 2