Large tables and fragmentation

[Peter W]

Hi, consider this time series table:

CREATE CACHED TABLE item_value
(
item_id SMALLINT NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
value DOUBLE NOT NULL,
PRIMARY KEY (custom_item_id, created)
);

Added 12 million rows, the table takes 1500mb, index takes 900 mb.

Range queries on the primary key are often slow (up to 60 seconds):

SELECT * FROM item_value WHERE item_id = ? AND created BETWEEN ? AND ?
ORDER BY custom_item_id, created

Some questions:
-Any way to reduce the table and index size? 90% overhead = lots of I/
O
-Does table fragmentation affect query speed? Can tables be
defragmented?
-Other hints to improve execution speed (e.g. "partitioning" by
item_id)?

Thanks,
Peter

[Brish]

On Dec 30, 10:31 am, Peter W <peter.wilso...@gmail.com> wrote:
> Some questions:
> -Any way to reduce the table and index size? 90% overhead = lots of I/
> O

H2 doesn't shrink the data, or index files so it's possible there is
empty space in it. Did you delete and/or update a lot of records after
the original insert? If not that you probably won't be able to reduce
the size.

If you have done a lot of updates/deletes you can do a script to like
this:

script to '/temp/backup.sql';

Backup the original database, then deleted it.

Connect to the same database connection (it will created a new empty
database).

Then do something like this:

runscript from '/temp/backup.sql';

That will rebuild the entire database so there shouldn't be a lot of
empty space in it.

> -Does table fragmentation affect query speed? Can tables be
> defragmented?

Yes it affects the performance.

You can't defrag a database but you can rebuild it using the above
method.

> -Other hints to improve execution speed (e.g. "partitioning" by
> item_id)?

Try putting "explain" in front of your query to see what h2 is doing.

You might want to try to analyze the database. That helps sometimes.

You might also want to try to change the order of the primary key so
that created is first, and item_id is second.

H2 is slow for large queries. If the query returns a lot of values
there isn't anything you can do to speed it up.

Brish

[Peter W]

On 31 Dec 2008, 05:00, Brish <bris...@gmail.com> wrote:
> H2 doesn't shrink the data, or index files so it's possible there is
> empty space in it. Did you delete and/or update a lot of records after
> the original insert? If not that you probably won't be able to reduce
> the size.

I did inserts only. I read another post about minimum record size of
128 bytes which I'm suspecting to be the problem here. I was thinking
maybe there's a way around it (different table types or data types).

> You can't defrag a database but you can rebuild it using the above
> method.

Thanks. I tried that and the queries run faster (but still 15 seconds
or so). I'm suspecting records to be stored in a scattered way due to
the compound key. That could lead to a lot of page reads (or what it's
called in h2) when I read a lot of records that don't follow the order
in which the data was inserted (data is inserted by time, so querying
with a fixed ITEM_ID will retrieve records scattered over the table).
But I don't know how h2 works so I'm just guessing here.

> > -Other hints to improve execution speed (e.g. "partitioning" by
> > item_id)?
>
> Try putting "explain" in front of your query to see what h2 is doing.

It's using the primary key and its using index sorting, so it seems
ok.

> You might want to try to analyze the database. That helps sometimes.

Didn't work this time.

> You might also want to try to change the order of the primary key so
> that created is first, and item_id is second.

Yes, I'll definitely try that!

> H2 is slow for large queries. If the query returns a lot of values
> there isn't anything you can do to speed it up.

Ok, didn't know that. I just looked at the performance comparison page
where it seemed to be faster than most other engines. Maybe someone
could add another test to that page which compares table sizes and
large table queries? I suspect, in this case MySQL would be much
smaller and therefore faster.

In the meantime, I might try denormalizing data by storing say 20
values per record and doing part of the selecting in the code...

Thanks a lot,
Peter W

[Sebastien]

> Maybe someone could add another test to that page

You can found some results here :
http://fodomust.u-strasbg.fr/~derivaux/chart20090105.png
using jdbc and an embedded server.

data :
create table fact (clientId int, productId int, dateId int, qty int,
price float, primary key (dateId, productId, clientId))
10.000.000 rows
some dimensions tables (small)

queries:

insert =>
insert 200.000 rows

scan =>
select * from fact

sum =>
select sum(qty*unitPrice) from fact f join product p on f.productId =
p.id

joinsum =>
select c.country, c.city, sum(qty) from fact f join client c on
f.clientId = c.id group by country, city

join2 =>
select d.yeard, c.country, c.city, sum(qty) from fact f join client
c on f.clientId = c.id join dated d on f.dateId = d.id group by
yeard, country, city

H2 seems CPU bound. It does a table scan on dated instead of fact
(which is in my opinion more clever as it doesn't fit in memory).

[Peter W]

> > Maybe someone could add another test to that page
>
> You can found some results here :http://fodomust.u-strasbg.fr/~derivaux/chart20090105.png
> using jdbc and an embedded server.

Great, thanks a lot Sebastien. Interesting results. H2 kicks ass when
inserting data, but not when performing selects over a lot of rows,
correct?

Are you sure its cpu bound when scanning the table?

[Sebastien]

> Great, thanks a lot Sebastien. Interesting results. H2 kicks ass when
> inserting data, but not when performing selects over a lot of rows,
> correct?

Well ... it's embedded and there is many commands send to the database
(200 batch of 1000 inserts). Putting H2 in it's own server shows quite
different result (similar to other dbms if i remember well).

I have an oltp test where H2 is similar to innodb, myisam or
postgresql. Good work.

> Are you sure its cpu bound when scanning the table?

doing iostat while running :

scan (creating temp table)
avg-cpu: %user %nice %system %iowait %steal %idle
31,93 0,00 15,03 13,16 0,00 39,88
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 285,00 29,89 10,86 149 54

There is a lot of time in system due to FileObjectDisk having no
cache, but the system has one.
It seems to me that it wait not so much for I/O. The drive can go up
to 70MB/s reads.

scan (reading temp table)
avg-cpu: %user %nice %system %iowait %steal %idle
29,58 0,00 23,80 0,70 0,00 45,92
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 119,20 13,99 0,00 69 0

Should be a lot of client/server communication.

sum
avg-cpu: %user %nice %system %iowait %steal %idle
30,72 0,00 19,62 5,75 0,00 43,90
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 365,60 42,90 0,02 214 0

Not so bad, but the hard drive is still sleeping. Postgresql achieve
60MB/s and 34% iowait. Reading from the system cache cost too. It
reads more than 50% useless data (.BLOCK_SIZE = 128 bytes and my row
takes all-in 64 bytes), so it's 20MB/s of real data.

join fact 1 dim
avg-cpu: %user %nice %system %iowait %steal %idle
40,53 0,00 12,52 6,90 0,00 40,04
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 195,00 22,49 0,02 112 0

The dimension table is in memory, there is just a table scan. I
suppose the btree took most of the time.
Postgresql is at 40MB/s and 10% iowait.


join fact 2 dim
avg-cpu: %user %nice %system %iowait %steal %idle
31,64 0,00 9,11 13,42 0,00 45,83
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 224,20 21,50 0,05 107 0

The parser does random read in the fact table using the btree. The OS
cache is less used (as it's more random).

I can get better results using a cache in FileObjectDisk (reading 16KB
each time), up to 10% in some queries, a little bit slower when it's
more random. This allow me to use DiskFile.BLOCK_SIZE = 16 instead of
128 without any cost (and thus cutting my DB size by 2 in my case as a
row takes 64 bytes in my case).

But the I/O seems not the main problem in this case.

[Peter W]

Here's my figures when running a typical SELECT (see first post).
Flushed disk caches before (see http://linux-mm.org/Drop_Caches),
shows I/O bottleneck:

avg-cpu: %user %nice %system %iowait %steal %idle

6,40 0,00 2,46 41,87 0,00 49,26

Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn

sdb 227,00 19,64 0,88 19 0

Running the query again (when the 1,5gb table is cached by os) shows
user cpu bottleneck:

avg-cpu: %user %nice %system %iowait %steal %idle

45,77 0,00 10,95 0,00 0,00 43,28

Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn

sdb 0,00 0,00 0,00 0 0

So I'd say, depending on how much memory you have, large tables can
have big I/O bottlenecks.

What do you think?

[Sebastien]

I see a problem in your results.

You have a 41,87% iowait and only 20MB/s read and a marginal write.

4 cases :
* An untypical select (I have try the sum to avoid temp table
creation and select * from table) which lead to random reads
(btrees?). Can you post your query and maybe table creation please
(maybe the generation script)?
* A very poor hard drive or misconfigured.
* The database file which is fragmented.
* You have another idea

I tried on two computers, using jdk 6 from sun and the openjdk. Same
results.

I try dropping cache (thanks for the tips) with
watch -n 1 "echo 3 > /proc/sys/vm/drop_caches"
I doesn't change things so much.
Nevertheless I was able to test to give enough system cache to load
the whole data file. iowait @ 0%. Doesn't change performances so much
as iowait was low already.

On 9 jan, 15:31, Peter W <peter.wilso...@gmail.com> wrote:
> Here's my figures when running a typical SELECT (see first post).

> Flushed disk caches before (seehttp://linux-mm.org/Drop_Caches),

[Sebastien]

Here is my bench for large table scan if you want to try. Tell me if
you spot a mistake.

package org.h2.test.bench;

import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
* Test the table scan performance for a table that fit in memory.
* Test also aggregation as it return the sum.
*/
public class BenchScanLarge implements Bench {
private Database db;
private int size;

/**
* Use a number not so small in order
* to not fit in memory
*/
public static int NUMBER_OF_FACTS = 10*1000*1000;

public void init(Database db, int size) throws SQLException {
this.db = db;
this.size = Math.max(1, size/1000);

db.start(this, "Init");
db.openConnection();

try{
db.update("CREATE TABLE FACTS_LARGE(ID INT NOT NULL PRIMARY KEY,
FACT1 DECIMAL(15,2), FACT2 DECIMAL(15,2))");

PreparedStatement prep;
db.setAutoCommit(false);
int commitEvery = 1000;
prep = db.prepare("INSERT INTO FACTS_LARGE(ID,FACT1,FACT2) VALUES
(?,?,?)");
for (int i = 0; i < NUMBER_OF_FACTS; i++) {
prep.setInt(1, i);
prep.setDouble(2, i);
prep.setDouble(3, i);
db.update(prep, "insertFacts");
if (i % commitEvery == 0) {
db.commit();
}
}
db.commit();
} catch(Exception e) {
// nothing to do, the table already exists, it's cool.
}

db.commit();
db.closeConnection();
db.end();

}

public void runTest() throws SQLException {

db.start(this, "Scanning");
db.openConnection();
processQuery();
db.closeConnection();
db.end();

db.openConnection();
processQuery();
db.logMemory(this, "Memory Usage");
db.closeConnection();

}

private void processQuery() throws SQLException {
PreparedStatement scan1 = db.prepare("SELECT SUM(FACT1) FROM
FACTS_LARGE");
db.setAutoCommit(false);

for (int i = 0; i < size; i++) {
db.queryReadResult(scan1);
db.commit();
}

scan1.close();
}

public String getName() {
return "ScanLarge";
}

}

[priyanka]

hai,

as mentioned above...

script to '/temp/backup.sql';

Backup the original database, then deleted it.

Connect to the same database connection (it will created a new empty
database).

Then do something like this:

  runscript from '/temp/backup.sql';

As mentioned above i tried the same procedure but iam getting like this

Result:

Out of memory.; SQL statement:
SCRIPT [90108-169] 90108/90108 (Help)
thanks in advance...

[priyanka]

sorry and thank you...all...for replying and helping me...
The script tool worked i tried in the command prompt reading the document

Backup using the Script Tool

and it worked thank you