waiting for free pages when insert a table

[xie_jiong]

Hi all,

When I load a table including a blob filed using many "insert" , commit only at the end and under SET SESSION WITH ON_LOGFULL = COMMIT, the thread will be stuck waiting for free pages after running for a while, and then there is no response. I traced the DBMS code, the stop postion is in fault_page:

fault_page()
{
......

/* If you are here, this thread is stuck waiting for free pages.
** Wake up the write behind threads since there is no guarantee
** another user thread is able to free a page
*/
wb_cycle_start(bm, lock_list,
BM_WB_SINGLE, "FAULT_PAGE");
dm0p_ewait(lock_list, DM0P_FWAIT_EVENT, bm->bm_cache_ix, 0, 0, 0);
.....
}

I use following parameters:

log file size: 1024M
etab and main table Page size are both 16K, cache_guideline = middle, dmf_write_behind=true, dmf_cache_size=3000 pages, all the other is default.

When I decrease log file size to 256M or increase dmf_cache_size, then there is no porblem.

I'm finding the cause:
If I don't manually commit at some interval, and use ON_LOGFULL = COMMIT, then all the new insert buffer pages will not write to disk unless log file is full. Under such case, when log file's size is very big or dmf_cache_size is small, then the page cache will fill up and the rountine will pending.

Is my analysis ture?

PS:
But when I use 2K page, there is no problem, its very strange!


thanks!

Jiong

[kschendel]

What is the config.dat parameter cp_interval_mb in the configuration where it gets stuck?

I'm not sure of the answer myself but I've asked Jon; he knows that part of the code very well.

[bilgihan]

Hello Jiong,

beside it is not expected behavior with sticking.

But knowingly requesting new page is expensive. I was involved a few areas especially if database had to be filled up with blob data.
I'd advice to make best estimation to calculate the amount of blob data:
say you have 500 mb blob data:
each blob table has a page_size of 16384 bytes.
500 mb / 16383 = 32.000
Required page size for blob table will be around 32.000 pages.

If you know the table will grow permanently, you could preallocate 40.000 or more ...
Example:
modify iietab_<blob> to btree on
per_key, per_segment0, per_segment1
with page_size = 16384,
allocation = 32000;

Preallocation will take time, but once it completes, blob processing will be quite fast.
Anyway I'd hear your feedback!

Bilgihan

[xie_jiong]

Originally Posted by kschendel

What is the config.dat parameter cp_interval_mb in the configuration where it gets stuck?

I'm not sure of the answer myself but I've asked Jon; he knows that part of the code very well.

Thanks Karl.

What does cp_interval_mb stand for? It's 40 in my config.dat.

Jiong

[xie_jiong]

Originally Posted by bilgihan

Hello Jiong,

beside it is not expected behavior with sticking.

But knowingly requesting new page is expensive. I was involved a few areas especially if database had to be filled up with blob data.
I'd advice to make best estimation to calculate the amount of blob data:
say you have 500 mb blob data:
each blob table has a page_size of 16384 bytes.
500 mb / 16383 = 32.000
Required page size for blob table will be around 32.000 pages.

If you know the table will grow permanently, you could preallocate 40.000 or more ...
Example:
modify iietab_<blob> to btree on
per_key, per_segment0, per_segment1
with page_size = 16384,
allocation = 32000;

Preallocation will take time, but once it completes, blob processing will be quite fast.
Anyway I'd hear your feedback!

Bilgihan

Thanks Bilgihan, this is a good performance method...