2016-04-17 17:13 GMT+02:00 Keith Medcalf <kmedcalf at dessus.com>: > > Perfectly linear. The time waster in creating the records is the index > with the completely separate copy of all the data and the native primary > key (record number) into a duplicate structure (the index btree). Creating > the index and the base table at the same time, while linear as well, is > even slower (as would be expected since you are doing multiple times the > I/O for each row inserted). > > Anyway, behaviour is linear, both in data insertion, index generation, and > dropping the table (which, as one would expect, takes only as much time as > one would take to walk the pages and move them to the free list, which may > include writing them to the journal). > > I don't see the issue you are having and "dropping" a table with 1e8 > records and a single unique index takes about 30 seconds. > > Perhaps you have really slow busy-wait style I/O? The laptop this was run > on has the same CPU as you do, and the single thread ran maxxed out (100% > of a core) using about 12% total of the CPU (one core single execution > unit). I/O is irrelevant for me as this has a very fast SSD. (As a side > note, a very fast and well cached SSD is indeed faster than a well cached > spinning disk -- not by a lot, but it is faster -- especially on cache > misses -- which, with a good cache, only occur when the cache is cold). > > NOTE that I killed the 1e9 insert with the index update at insert time. > Clearly doing it all in a single transaction does not work very well. >
?I should change the program to not do all in one swoop. Auto commit is a bad idea, but no commit also.? The strange thing is that the blob variant takes a lot of time now also. First it took only 4? hour, now it is already busy for eight hours and only has come to 8.9E7. 14:36:01: Inserted 8.40e+07 UUID's 14:54:47: Inserted 8.50e+07 UUID's 15:30:19: Inserted 8.60e+07 UUID's 15:54:02: Inserted 8.70e+07 UUID's 16:17:01: Inserted 8.80e+07 UUID's 17:24:20: Inserted 8.90e+07 UUID's Something else I find strange (but maybe is not): there are 19 threads beside the process: ps -lL -p 26455 F S UID PID PPID LWP C PRI NI ADDR SZ WCHAN TTY TIME CMD 0 S 1000 26455 28670 26455 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 D 1000 26455 28670 26458 14 80 0 - 1718302 - pts/12 01:06:35 java 1 S 1000 26455 28670 26459 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26460 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26461 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26462 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26463 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26464 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26465 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26466 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26467 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26468 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26469 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26470 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26471 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26472 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26473 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26474 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26475 0 80 0 - 1718302 futex_ pts/12 00:00:00 java 1 S 1000 26455 28670 26476 0 80 0 - 1718302 futex_ pts/12 00:00:13 java The D of the first thread signifies uninterruptable sleep (usually IO). The others are waiting for an event to complete. It is a command line program and I do not create threads. Or is this normal in Java programs? If it is of interest: I am using Java 8. ?And the sluggish behaviour is back again. :'-(? -- Cecil Westerhof
