Informix TimeSeries - Some Best Practices for Beginners

I have been working with quite a few partners and customers who are using Informix TimeSeries. However, I realized that there is a set of instructions which is common and very much useful for all new users to take best out of the TimeSeries implementation, we will call it as best practices. And hence, I thought of writing this blog which will be useful for those whom I am working with as well whom I don’t know but they are using Informix TimeSeries. These tips will at least get you things done right in first go and later you can tune your server or queries as per your setup. Basic assumption is that, that you are using Informix Enterprise or Advance Enterprise edition on multi-core server. If you are using lower version, fragmenting table and setting PDQPRIORITY won’t be applicable.

A)      About containers, you can create multiple containers across same dbspace but try to keep container which keeps meta information of container, different for each container.
Example:
execute procedure TSContainerCreate('ts_tab1', 'data8dbs_1','ts_row',1024, 1024,
'2012-01-01 00:00:00.00000'::datetime year to fraction(5),'month',0,0,'data8dbs1,data8dbs2',1024,1024,0)

execute procedure TSContainerCreate('ts_tab2', 'data8dbs_2','ts_row',1024, 1024,
'2012-01-01 00:00:00.00000'::datetime year to fraction(5),'month',0,0,'data8dbs3,data8dbs4',1024,1024,0)

B) Create timeseries table say ts_tab with first and next extent size 1024
create table ts_tab
  (
    device_id varchar(32),
    meter_data timeseries(ts_row),
    primary key (device_id) 
  ) fragment by round robin in dbs1, dbs2
extent size 1024 next size 1024 lock mode row;

C) While initializing timeseries, you can assign containers in round robin fashion to each timeseries like:
insert into ts_tab values('tag1', TSCreateIrr('ts_1min','2014-10-01 00:00:00.00000',0,0,0, 'ts_tab1),
insert into ts_tab values('tag2', TSCreateIrr('ts_1min','2014-10-01 00:00:00.00000',0,0,0, 'ts_tab2'),
insert into ts_tab values('tag3', TSCreateIrss('ts_1min','2014-10-01 00:00:00.00000',0,0,0, 'ts_tab1'),
insert into ts_tab values('tag4', TSCreateIrr('ts_1min','2014-10-01 00:00:00.00000',0,0,0, 'ts_tab2'),
And so on..
This is example, use appropriate calendar, origin etc as per your setup.

Ensure you run “update statistics high for table ts_tab” after initializing all timeseries and before starting data upload.

D) As far as onconfig is concerned, I observed setting following parameters gives excellent performance. I have used onconfig which I used for 100K meter IDs. These values might be different for your setup, but this section gives you an idea about the parameters to be looked at:

Parameter             Values                    Remarks

LOGSIZE              102400                Just changing parameter in onconfig won't help. You have to create new log

 files with this size and delete older ones.

PHYSFILE            4194304             Create a dbspace of size 4GB with default page size. And use onparams

                                                          command to create new PHYSFILE of 4GB. You have to take 0 level backup

                                                          after this step. You can run ontape -s -L 0 -t /dev/null to take dummy backup.

VP_MEMORY_CACHE_KB 256

CKPTINTVL        600

RTO_SERVER_RESTART               0

STACKSIZE         128

BUFFERPOOL     size=**,buffers=??,lrus=8,lru_min_dirty=80,lru_max_dirty=90

#?? - Set this value to 2.5 * number of meters. 

#** page size of a dbspace/s in which containers are created

MULTIPROCESSOR 1

VPCLASS cpu,num=??,noage      #Change ?? to the number of cores you have on your machine

VP_MEMORY_CACHE_KB 128

SINGLE_CPU_VP 0

E) Set PDQPRIORITY to 60 in your environment or in SQL sessions.

F) While querying data from timeseries set :

set isolation to dirty read;

set lock mode to wait 10;

<after this you can add your select query>

Disclaimer: The postings on this site are my own and don't necessarily represent IBM's positions,strategies or opinions