April 2020 - users-list

New client library for PHP
by Tamas Bolner 10 Apr '20

10 Apr '20

Hi, I spent the past nights creating a client library for PHP, because I found too many problems with the official one. Just for the heads up: https://github.com/bolner/MonetDB-PHP-Deux Happy Easter! Tamas

1 0

RE: System Catalog - Objects creation date and time.
by Suresh Devarakonda 08 Apr '20

08 Apr '20

Hi, I just wanted to see when the objects such as tables created in MonetDB. I have looked at below note, but couldn't find the catalog tables that provides the table creation time so wondering if MonetDB has that capability. Can someone let me know at the earliest. Your early response is very much appreciated. https://www.monetdb.org/Documentation/SystemCatalog [cid:image002.jpg@01D60C19.28554180] Suresh Devarakonda 84.51<http://www.8451.com/>° TDO - Database Support Team D: 513.632.3692 M: 859.512.1839 A: 100 W. 5th Street, Cincinnati, Ohio 45202 Cincinnati | Chicago | New York | Portland Facebook<https://www.facebook.com/8451group/?ref=aymt_homepage_panel> LinkedIn<https://www.linkedin.com/company/84-51/?trk=biz-companies-cym> Instagram<https://www.instagram.com/84.51/> Twitter<https://twitter.com/8451group> Please consider the environment before printing this email.

2 1

Running two databases with forward set to redirect (monetdbd)
by stephenb 08 Apr '20

08 Apr '20

We have an environment that does not support Unix domain sockets well. We thus configured monetdbd with forward=redirect, as we note from the monetdbd man page that "for local databases, the proxy method uses a UNIX domain socket feature" We can create two database, but trying to connect (on the default 50000 port) to them only the first-started database is available. If we try to connect to the second we get the error "request for database 'dev_sb2', but this is database 'dev_sb', did you mean to connect to monetdbd instead?". With some experimentation we have found that starting the databases directly using mserver5 and specifying different ports (--set mapi_port=50001 etc) we can connect to both databases, but this does complicate things and we would prefer to use the monetdb command for starting, stopping, creating etc. How can we configure monetdbd (or perhaps individual databases?) so that we can connect to two local databases through monetdbd and manage them using monetdb? Thanks Steve

2 2

Fwd: [Help] create a new db user but need to add prefix 'sys.' in order to execute timestamp_to_str() function
by Kevin Chen 07 Apr '20

07 Apr '20

Hi, I have a new db user created by following scripts: CREATE USER "imdb" WITH PASSWORD 'imdb' NAME 'IMDB Explorer' SCHEMA "sys"; CREATE SCHEMA "imdb" AUTHORIZATION "imdb"; ALTER USER "imdb" SET SCHEMA "imdb"; When I tried to execute the function "timestamp_to_str", I have to add "sys." prefix to make it effective. "date_trunc" also needed to do so. The default user "monetdb" can run the function directly without the prefix. What should I do to perform the same way as user "monetdb" ? sql>select timestamp_to_str(localtimestamp, '%Y-%m-%d %H:%M:%S %z'); SELECT: no such binary operator 'timestamp_to_str(timestamp,char)' sql>select sys.timestamp_to_str(localtimestamp, '%Y-%m-%d %H:%M:%S %z'); +---------------------------+ | L2 | +===========================+ | 2020-04-06 20:37:39 +0800 | +---------------------------+ 1 tuple sql>select date_trunc('minute', timestamp '2119-02-17 02:08:12.345678'); SELECT: no such binary operator 'date_trunc(char,timestamp)' sql>select sys.date_trunc('minute', timestamp '2119-02-17 02:08:12.345678'); +----------------------------+ | L2 | +============================+ | 2119-02-17 02:08:00.000000 | +----------------------------+ 1 tuple

2 1

Fwd: BULK UPLOAD very poor performance
by Ariel Abadi 01 Apr '20

01 Apr '20

Hi! After a big battle against the DB, I found 2 issues. 1) Running the COPY RECORDS FROM *STDIN *and from *FILE*. Is totally different in terms of performance. While the first one last 24 minutes, the second one last only *90 secs.* 2) As I had generated all those files thru msqldump (which it states the "CREATE TABLE" instruction and then the COPY RECORDS)... I try ro run the COPY *XXXX OFFSET *RECORDS (in order to avoid the create table statement)... and the query, because of the word "STDIN" in the file. It was not OFFSETTING all the rows I was asking for. I hope I've been clear... if not please let me know! Ariel On Tue, Mar 24, 2020 at 11:33 PM Martin Kersten <martin.kersten(a)cwi.nl> wrote: > Hi > > I am not sure what issue you are now dealing with, but since you are a > company > that seems to use (or considering) MonetDB in your commercial offerings, > I gladly refer you to MonetDBSolutions(*), where we can give you the > commercial > support needed to diagnose your issues. > > regards Martin > > (*) https://www.monetdbsolutions.com/ > > On 24/03/2020 23:55, Ariel Abadi wrote: > > Thanks Martin for your prompt response > > But I did and msqldump from other database, and now the upload is being > done thru the BULK LOAD. > > > > /COPY 14173375 RECORDS INTO "sb_traf"."nrm_20180818" FROM stdin USING > DELIMITERS '\t','\n','"';/ > > > > > > Any other idea ? :'( > > > > > > > > > > On Tue, Mar 24, 2020 at 7:37 PM Martin Kersten <martin.kersten(a)cwi.nl > <mailto:martin.kersten@cwi.nl>> wrote: > > > > Hi, > > > > given the numbers I suspect you are loading them as SQL insert > statements under autocommit mode, > > Please read the documentation on Bulk loading. > > > > regards,Martin > > Sent from my iPad > > > >> On 24 Mar 2020, at 23:12, Ariel Abadi <aabadi(a)starbi.com <mailto: > aabadi(a)starbi.com>> wrote: > >> > >> > >> Hi! > >> Maybe somebody can help me, please! > >> > >> I'm trying to upload into Monet a file with 14MM records. Is taking > more than *24 minutes.* > >> It is a fresh new database. > >> > >> Enclosed you will find the server configuration. > >> > >> Monetdb Version Nov2019-SP3 > >> > >> > >> *[root@mn tmp]$ lscpu* > >> Architecture: x86_64 > >> CPU op-mode(s): 32-bit, 64-bit > >> Byte Order: Little Endian > >> CPU(s): 40 > >> On-line CPU(s) list: 0-39 > >> Thread(s) per core: 2 > >> Core(s) per socket: 10 > >> Socket(s): 2 > >> NUMA node(s): 2 > >> Vendor ID: GenuineIntel > >> CPU family: 6 > >> Model: 85 > >> Model name: Intel(R) Xeon(R) Silver 4114 CPU @ 2.20GHz > >> Stepping: 4 > >> CPU MHz: 800.479 > >> BogoMIPS: 4400.00 > >> Virtualization: VT-x > >> L1d cache: 32K > >> L1i cache: 32K > >> L2 cache: 1024K > >> L3 cache: 14080K > >> NUMA node0 CPU(s): > 0,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38 > >> NUMA node1 CPU(s): > 1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39 > >> Flags: fpu vme de pse tsc msr pae mce cx8 apic sep > mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe > syscall nx pdpe1gb rdtscp lm constant_tsc art arch_perfmon pebs bts > rep_good nopl xtopology nonstop_tsc > >> cpuid aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est > tm2 ssse3 sdbg fma cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic movbe > popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm > 3dnowprefetch cpuid_fault epb cat_l3 cdp_l3 > >> invpcid_single pti intel_ppin ssbd mba ibrs ibpb stibp tpr_shadow > vnmi flexpriority ept vpid ept_ad fsgsbase tsc_adjust bmi1 hle avx2 smep > bmi2 erms invpcid rtm cqm mpx rdt_a avx512f avx512dq rdseed adx smap > clflushopt clwb intel_pt avx512cd avx512bw > >> avx512vl xsaveopt xsavec xgetbv1 xsaves cqm_llc cqm_occup_llc > cqm_mbm_total cqm_mbm_local dtherm ida arat pln pts pku ospke md_clear > flush_l1d > >> > >> > >> *[root@mn tmp]$ cat /proc/meminfo* > >> > >> MemTotal: 83886080 kB > >> MemFree: 10535812 kB > >> MemAvailable: 80159744 kB > >> Buffers: 0 kB > >> Cached: 69623932 kB > >> SwapCached: 0 kB > >> Active: 6625436 kB > >> Inactive: 63737872 kB > >> Active(anon): 324456 kB > >> Inactive(anon): 417184 kB > >> Active(file): 6300980 kB > >> Inactive(file): 63320688 kB > >> Unevictable: 0 kB > >> Mlocked: 5320 kB > >> SwapTotal: 8388604 kB > >> SwapFree: 8381988 kB > >> Dirty: 220 kB > >> Writeback: 0 kB > >> AnonPages: 13626052 kB > >> Mapped: 186368 kB > >> Shmem: 1800 kB > >> KReclaimable: 4134960 kB > >> Slab: 0 kB > >> SReclaimable: 0 kB > >> SUnreclaim: 0 kB > >> KernelStack: 16720 kB > >> PageTables: 56500 kB > >> NFS_Unstable: 0 kB > >> Bounce: 0 kB > >> WritebackTmp: 0 kB > >> CommitLimit: 74123472 kB > >> Committed_AS: 18337812 kB > >> VmallocTotal: 34359738367 kB > >> VmallocUsed: 415836 kB > >> VmallocChunk: 0 kB > >> Percpu: 39552 kB > >> HardwareCorrupted: 0 kB > >> AnonHugePages: 0 kB > >> ShmemHugePages: 0 kB > >> ShmemPmdMapped: 0 kB > >> CmaTotal: 0 kB > >> CmaFree: 0 kB > >> HugePages_Total: 0 > >> HugePages_Free: 0 > >> HugePages_Rsvd: 0 > >> HugePages_Surp: 0 > >> Hugepagesize: 2048 kB > >> Hugetlb: 0 kB > >> DirectMap4k: 816396 kB > >> DirectMap2M: 82513920 kB > >> DirectMap1G: 52428800 kB > >> > >> > >> > >> > >> _______________________________________________ > >> users-list mailing list > >> users-list(a)monetdb.org <mailto:users-list@monetdb.org> > >> https://www.monetdb.org/mailman/listinfo/users-list > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org <mailto:users-list@monetdb.org> > > https://www.monetdb.org/mailman/listinfo/users-list > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > >

5 11