On 20 February 2017 at 16:56, Ying Zhang wrote:

Hai Roberto,

...

On 20 Feb 2017, at 09:57, Roberto Cornacchia < roberto.cornacchia@gmail.com> wrote:

Hi,

I'm confused about the single-user mode (working with latest Dec2016 from hg).

Question/suggestion 1 'mserver5 --single-user' can also be set with 'monetdb set singleuser=yes'. However, 'monetdb get all' doesn't show 'singleuser' as one of the options, I had to guess it myself. It would be nice if monetdb could show an actual list of available options, instead a manually compiled one.

Development of mserver5 and monetdbd is not synced, so some features added later to mserver5 are not (completely) added to monetdbd. We should update monetdbd, and its documentation/help message/etc. Mind firing a new feature request?

check

...

Question 2 is 'mserver5 --single-user' expected to put the database in a locked

...

If the answer is yes, then it doesn't do it consistently: - it does allow to use COPY INTO.... LOCKED (from which you would think

state, equivalent to 'monetdb lock' ? the database is locked)

...

but: - it doesn't show the status of the database as locked - it indeed allows new client connections

Could you clarify how 'single-user' and 'locked' relate?

I never used the --single-user option, but some time ago I was using COPY INTO…LOCKED.

You can use COPY INTO…LOCKED whenever you are *sure* that this is the only client connection, no matter mserver5 is running in single-user mode or not. While executing COPY INTO…LOCKED, mserver5 will lock some things (but I don’t know exactly what). However, mserver5 doesn’t reject new connections, or gives error if there are existing connections. So I have noticed that if I accidentally have multiple connections while trying to do COPY INTO…LOCKED, strange things might happen without clear error messages (e.g. query hangs).

I see. It's an "optimistic locked copy into" ;) I still don't have very clear what --single-user does, though.

...

Question 3 (long-ish) I am pushing, via JDBC, the entire English DBpedia via COPY INTO

...

It is about 200GB raw data and it takes ~9 hours.

Data goes through a pipeline of light pre-processing (feeding thread) and then inserted into the stream of COPY INTO (output thread). What I observe is that, at the beginning, the feeding thread is mush active, and the output thread very little. After a couple of hours, the output thread is mostly active, and the feeding thread mostly idle. I told myself that MonetDB is taking more and more time to flush appends to disk.

That's where I though using 'COPY INTO .. LOCKED' would help. Not only it didn't help, but it got the symptoms worse. The output

(without 'LOCKED'), into a dbfarm on local SSD. thread is almost 100% active, and the feeding thread almost 100% idle.

...

Indeed, it is already running for 12h and still going on.

Was I wrong to expect that 'LOCKED' would help?

LOCKED might improve performance, because it avoids the creation and copying of temporary BATs. But if you do COPY INTO…LOCKED repeatedly, you might still trigger BATextend.

True. What I find odd is that it behaves worse than non-locked (non-locked: 9h, locked: 21h and counting) - do you have constraints, e.g. primary keys, on your table?

no

- how much data do you COPY INTO every time?

That's dynamically decided, and perhaps it's not ideal indeed. I would say each COPY INTO is something around 32K rows. With about 1G rows to insert, that's probably too many. Still, the fact that LOCKED hurts is unexpected. Cheers, Roberto

Regards, Jennie

...

Thanks, Roberto

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list