June 2018 - users-list

Temporal Function Issues
by Doherty, Adam 13 Jun '18

13 Jun '18

Hello all: I am trying to find the most efficient way to filter results for those matching today only. My events table has 550 million rows and I want a view as follows- CREATE VIEW events_today AS SELECT * FROM events WHERE str_to_date(timestamp_to_str(event_timestamp, '%Y-%m-%d'), '%Y-%m-%d') = CURRENT_DATE which should give me all events with today's date. Running the SELECT by itself also never returns. However, the query just hangs without ever returning. I'm running 11.29.3 (Mar2018) on CentOS 7 with 128GB and 16 Cores. Adam

2 2

High Memory Usage - Sever Not Releasing Resources?
by Doherty, Adam 08 Jun '18

08 Jun '18

Hi all: I ran into an issue yesterday when I ran a simple group count query - The query took 7 minutes, 13 seconds to run. Query was as follows - SELECT zone, count(*) FROM chronicle.events WHERE event_type = 'mb' GROUP BY zone This table, which a merge table currently has 546 million events. The merge table is made up of currently two merge table each representing a year (events_2018, and events_2017). Those merge tables are each made up of four tables, events_year_zone_(1-4) I have a scheduled task which inserts events into the relevant tables every 5 minutes from 5 Microsoft SQL Servers. Roughly 12,000 events broken across 5 inserts (one for each server). The import process is handled with a C# Windows service that utilizes ODBC to talk to MonetDB. The server is currently MonetDB v11.27.11 (July2017-SP3) running on VMware 6.5 in a CentOS 7 VM with 64GB of RAM, and 16 CPU cores. When I ran into this issue yesterday, the mserver5 process was utilizing 90% of available RAM. This was making my insertion task take longer and longer each time, and simple queries like the one above took minutes. Logged into the server via SSH, the input lag as a result of the almost total resource usage caused me to reboot the server. I restarted the VM, and the same query took 20 seconds. I'd like to sort where this issue might be coming from, but I'm not sure where to look. This server is currently part of a proof-of-concept project that will soon be going to production. Any guidance is greatly appreciated. Adam

2 2

ODBC Error on Insert
by Doherty, Adam 06 Jun '18

06 Jun '18

Hi All: I'm having some issues using ODBC with C#. I am trying to insert the contents of a DataTable into Monet using a foreach loop with MonetDB ODBC client under Windows. This is the error I receive... System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [MonetDB][ODBC Driver 11.27.11]syntax error, unexpected ',' in: "execute 5 (timestamp '2018-06-04 21:22:25',8651,'EVENT','','SYSTEM','1810_INFRA_" This is my loop which is wrapped in a transaction - foreach (DataRow row in events.Rows) { cmd.CommandText = String.Format("INSERT INTO chronicle.events_{0}_zone_{1} VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", year, zone); cmd.Parameters.AddWithValue("@date_time", row["Date_Time"]); cmd.Parameters.AddWithValue("@fracsec", row["FracSec"]); cmd.Parameters.AddWithValue("@event_type", row["Event_Type"]); Truncated for brevity. I'm not sure where the error in my insert statement is because the error message itself appear to be truncated. Very frustrating as it hangs on the first row. Adam

2 1