Performance question

[Jan Møller]

I have a very simple table:

CREATE TABLE myTable(binA BINARY(34) PRIMARY KEY, binB BINARY(21), value BIGINT);

With an index (which should be unrelated to this question)

CREATE INDEX myTableIndex ON myTable(binB);

It contains > 8 million records (binA and binB contain basically random bytes, but again this is unrelated to my question).

On startup I wish to read through it all to generate a graph, so basically: read next element, do something, rinse and repeat

My problem is that it takes about 10 minutes before i get the first result, once I get the first result everything is snappy.

Here is my statement: SELECT binA, binB FROM myTable

and my Java code looks like this:

ResultSet result = psLoad.executeQuery();

while(result.next()) {

   byte[] binA = result.getBytes(1);

   byte[] binB = result.getBytes(2);

   ... do something ...

}

My guess is that everything is read into memory in the first line. Is there any way to only read one record at a time or somehow speed it up?

I have tried "SET MAX_MEMORY_ROWS = 10000000", but that didn't seem to have any effect.

I have tried to do multiple select statements where I LIMIT the result set combined with OFFSET, but apparently the offset  takes linear time in the number of rows to skip.

I have another DB with about 400,000 records where the first results come within one or a few seconds.

[Noel Grandin]

You could so something like:

SELECT binA, binB FROM myTable ORDER BY binA LIMIT 10000

and then store the last binA value you receive and go:

SELECT binA, binB FROM myTable WHERE binA > lastBinAValueFromPreviousSelect ORDER BY binA LIMIT 10000

and repeat that until you have processed all the data.

Which should be reasonably snappy because it can use the primary index to locate the data efficiently.

[Jan Møller]

Ahh... very clever.

Just to get it right. Since the index is on binB I assume that binA and binB should be switched in your examples.

Thanks a lot, I'll try it out

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/KhbJSGdhLcM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

[Jan Møller]

On Wednesday, February 5, 2014 11:04:15 AM UTC+1, Noel Grandin wrote:

You could so something like:

SELECT binA, binB FROM myTable ORDER BY binA LIMIT 10000

and then store the last binA value you receive and go:

SELECT binA, binB FROM myTable WHERE binA > lastBinAValueFromPreviousSelect ORDER BY binA LIMIT 10000

and repeat that until you have processed all the data.

Which should be reasonably snappy because it can use the primary index to locate the data efficiently.

There is a caveat. There is an index on binB, but it is not unique, so I am afraid that I won't get the right result.

[Noel Grandin]

On 2014-02-05 12:38, Jan Møller wrote:
> Ahh... very clever.
> Just to get it right. Since the index is on binB I assume that binA and binB should be switched in your examples.
> Thanks a lot, I'll try it out
>

You declared the table like this:

CREATE TABLE myTable(binA BINARY(34) PRIMARY KEY, binB BINARY(21), value BIGINT);

with
binA BINARY(34) PRIMARY KEY
which means that H2 would have implicitly created an index on binA

[Jan Møller]

Doh... you are right. Now it works. Thanks.

With this setup I get the queries split into smaller chunks, but the total execution time is about a factor of 2. It was worth a shot.

I am a bit amazed that the throughput of my 8m record database is so much lower than my 400k record database, but then I read in another post that there might be an issue if the records have been heavily inserted/deleted, which they have, in particular in the big DB.

I'll try and backup/re-build the DB to see if it makes any difference

 

[Roger Thomas]

I am a bit amazed that the throughput of my 8m record database is so much lower than my 400k record database, but then I read in another post that there might be an issue if the records have been heavily inserted/deleted, which they have, in particular in the big DB.

I'll try and backup/re-build the DB to see if it makes any difference

 

Look at what you are doing it is likely that this is because your 400K record test database is being cached in memory while you scan across it, while the 8m+ record database is so large that the caching is ineffective and so a far higher percentage of disk I/O is required.

To be clear about the indexes on this table, you end up with

      - Hidden, system generated index for a BIGINT that is used as the primary key
      - Index on binA
      - Index on binB

This is because H2 can only create primary indexes on INT based fields -  see here http://stackoverflow.com/questions/3312857/h2-database-clustered-indexes-support 


Things to try

    1 - up the page size of the table, the default is just 2K which means a lot of small reads take place during your scan    http://www.h2database.com/html/features.html#page_size

    2 - create the 2 addtional indexes after you have populated the table and not during the process.

    3 - increase the H2 cache, as you are doing a table scan this may not help as I think its a record cache and not a block cache.


Background reasons for the above. As you add your 8M records the records are added to the table as 2K pages, at the same time 2K pages are allocated to each of the additional indexes as well. All these pages are stored in a single file that is extended as required. As the btree indexes are grown they are rebalanced, which means additional pages and old pages being freed up.

The result is that your data is now spread across a very large file as small 2K pages in something of a random order, intermixed with 2K index pages that you do not which to use at this time. If you have a file system set to 4,8,16K pages each requested read is not going to bring back much useful information (hence the idea of increasing the page size). Also as the data is not in any natural order its very unlikely that the extra pages read via the OS read ahead feature will help. By adding the indexes after the main data pump you separate the data pages they create from the data pages, so when the OS does a disk read it is going to be bringing back mostly data pages rather than a mix of data and index pages.

The one limitation is that page size is set per database, rather than per table (all the tables in a database are all held within a single file) so other parts of your database may have performance problems if you use very large pages, but at least try matching the disk page size and maybe 2x the disk page size.

[Thomas Mueller]

Hi,

> binA and binB contain basically random bytes, but again this is unrelated to my question

Well, this is actually relevant. You have two indexes (one primary key, and a secondary key) on randomly distributed data. There is no way to make it really fast, unless it fits in memory. Having to use an index on randomly distributed data is a "cache killer". All you can really do in this case is try to load everything in memory, or use a solid state disk.

Because of that, I would avoid an index on randomly distributed data (no matter what database is storage system you use), unless you know in advance that the number of entries will never grow beyond a certain size, or unless the amount of data you store is so big that the lookup cost (at most 200 per second for a regular hard disk) is lower than the amount of data. That means, each entry is about 2 MB in size or larger.

See also the documentation at http://h2database.com/html/datatypes.html#uuid_type

Regards,

Thomas

On Wed, Feb 5, 2014 at 11:04 AM, Noel Grandin <noelg...@gmail.com> wrote:

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

[Roger Thomas]

Well, this is actually relevant. You have two indexes (one primary key, and a secondary key) on randomly distributed data. There is no way to make it really fast, unless it fits in memory. Having to use an index on randomly distributed data is a "cache killer". All you can really do in this case is try to load everything in memory, or use a solid state disk.

Because of that, I would avoid an index on randomly distributed data (no matter what database is storage system you use), unless you know in advance that the number of entries will never grow beyond a certain size, or unless the amount of data you store is so big that the lookup cost (at most 200 per second for a regular hard disk) is lower than the amount of data. That means, each entry is about 2 MB in size or larger.

 

Regards,

Thomas

Does this mean that H2 now creates primary indexes on non integer based columns? The only info I could find was from a post of yours on stackoverflow from Oct 2010. This indicated that a hidden BigInt column would be created and that would become the primary index.

[Thomas Mueller]

Hi,

> Does this mean that H2 now creates primary indexes on non integer based columns?

> This indicated that a hidden BigInt column would be created and that would become the primary index.

Yes, if the primary key is not a bigint, a hidden column is created (the _rowid_ column), and the data is stored with this as the key. The question is whether you want to call this column the "primary key" as it's not the (user visible) primary key. I wrote: "You have two indexes (one primary key, and a secondary key)", in this case I meant the user visible primary key.

Regards,

Thomas

--

You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[Jan Møller]

Look at what you are doing it is likely that this is because your 400K record test database is being cached in memory while you scan across it, while the 8m+ record database is so large that the caching is ineffective and so a far higher percentage of disk I/O is required.

To be clear about the indexes on this table, you end up with

      - Hidden, system generated index for a BIGINT that is used as the primary key
      - Index on binA
      - Index on binB

This is because H2 can only create primary indexes on INT based fields -  see here http://stackoverflow.com/questions/3312857/h2-database-clustered-indexes-support 


Things to try

    1 - up the page size of the table, the default is just 2K which means a lot of small reads take place during your scan    http://www.h2database.com/html/features.html#page_size

    2 - create the 2 addtional indexes after you have populated the table and not during the process.

    3 - increase the H2 cache, as you are doing a table scan this may not help as I think its a record cache and not a block cache.


Background reasons for the above. As you add your 8M records the records are added to the table as 2K pages, at the same time 2K pages are allocated to each of the additional indexes as well. All these pages are stored in a single file that is extended as required. As the btree indexes are grown they are rebalanced, which means additional pages and old pages being freed up.

The result is that your data is now spread across a very large file as small 2K pages in something of a random order, intermixed with 2K index pages that you do not which to use at this time. If you have a file system set to 4,8,16K pages each requested read is not going to bring back much useful information (hence the idea of increasing the page size). Also as the data is not in any natural order its very unlikely that the extra pages read via the OS read ahead feature will help. By adding the indexes after the main data pump you separate the data pages they create from the data pages, so when the OS does a disk read it is going to be bringing back mostly data pages rather than a mix of data and index pages.

The one limitation is that page size is set per database, rather than per table (all the tables in a database are all held within a single file) so other parts of your database may have performance problems if you use very large pages, but at least try matching the disk page size and maybe 2x the disk page size.

Thanks for the elaborate explanation. I see that by adding the index later I get them in one chunk (or at least in the vicinity of each other) which helps performance. Unfortunately it is a live system that continuously gets pretty random inserts/deletes/gets (into several other tables as well), and I believe that I need the index to sppedup lookups during runtime. I haven't tried changing the page size yet though.

However, I made an experiment. I made a "script to" / "runscript from" combo and recreated the data in an empty database (took hours, approx 60 GB database files). This creates the indexes at then ends and makes the data of each table come nicely in sequence, basically defragmentation I guess. Selecting all the rows of the table is increased by a factor of 10, and now CPU bound. This is however a bit like cheating, as the table will slowly get fragmented during runtime.

[Jan Møller]

I failed to mention that I am using an SSD.

Apart from wanting to make a pass over all rows during program start I am doing continuous random selects on binA (for getting zero or one row) and binB (for getting zero, one or more rows) at runtime (plus random inserts/deletes). So I figured that an index would speed up those selects.

So, to make certain I understand: Was your index comment only meant for the "read all rows during startup" comment, or do you think that there is generally no use in indexes on random data?

In that case I might as well get rid of them and save some space.

Thanks for your help. 

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/KhbJSGdhLcM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[Jan Møller]

I did an experiment where I select for a specific randomly chosen binB value with and without an index on binB:

explain analyze SELECT binA, value FROM mytable WHERE binB =  X'00234d33def71358d61895649e6ab3912c0448214f'

With index:

SELECT

    BINA,

    VALUE

FROM PUBLIC.MYTABLE

    /* PUBLIC.MYTABLEINDEX: BINB = X'00234d33def71358d61895649e6ab3912c0448214f' */

    /* scanCount: 2 */

WHERE BINB = X'00234d33def71358d61895649e6ab3912c0448214f'

/*

total: 6

UNSPENT.MYTABLEINDEX read: 4 (66%)

UNSPENT.MYTABLE_DATA read: 2 (33%)

*/

Without index:

SELECT

    BINA,

    VALUE

FROM PUBLIC.MYTABLE

    /* PUBLIC.MYTABLE.tableScan */

    /* scanCount: 8237239 */

WHERE BINB = X'00234d33def71358d61895649e6ab3912c0448214f'

/*

total: 428969

UNSPENT.MYTABLE_DATA read: 428969 (100%)

*/

The result is pretty clear. The index is of tremendous help for this kind of operation which is paramount for my setup.

[Roger Thomas]

Thanks for the elaborate explanation. I see that by adding the index later I get them in one chunk (or at least in the vicinity of each other) which helps performance. Unfortunately it is a live system that continuously gets pretty random inserts/deletes/gets (into several other tables as well), and I believe that I need the index to sppedup lookups during runtime. I haven't tried changing the page size yet though.

However, I made an experiment. I made a "script to" / "runscript from" combo and recreated the data in an empty database (took hours, approx 60 GB database files). This creates the indexes at then ends and makes the data of each table come nicely in sequence, basically defragmentation I guess. Selecting all the rows of the table is increased by a factor of 10, and now CPU bound. This is however a bit like cheating, as the table will slowly get fragmented during runtime.

OK its good to hear that you do see a level of improvement, you are right in so much that it is cheating, but may allow a system to keep running, while other solutions are put into place . The first thing to note is that this work does not result in the data tables being 'in sequence' but rather that when a disk page is read in there is a much higher chance of it containing a number of data pages (rather than a mix of data and index pages). If you can try changing the page size you should see much the same result. Also as you are working against an SSD you may like to try a large page size such as 8K or even 16K and also increase the file system block size to the same.

The next thing to look at is the amount of cache available to H2, details can be found here    http://www.h2database.com/html/features.html#cache_settings

The correct size will be hard to tell, but its unlikely that you will have enough memory to hold the whole of this table so this table scan will always result in a lot of I/O. What I can say is that you are likely to see improvements if you throw a lot of memory at the problem :)