Meta
-
Recent Posts
Categories
Recent Comments
- Oleg Popelyaev on Quick overview of the Informix Database Scheduler
- Ram S on Craving a bit of update statistics low performance?
Archives
How to Track the Resources used by Database Users
Often time there is there is a requirement to track when users log-in to a database, how long they were accessing the database and what resources were consumed. This information can be used for auditing or charging for use of a database by the resources consumed by the user.
Accomplishing this is simple and utilizes the sysdbopen/sysdbclose procedures along with the sysmaster database.
The sysdbopen procedure is executed whenever users successfully issue the DATABASE or CONNECT statement to explicitly connect to a database where the procedures are installed. You can also create the sysdbclose SPL procedure, which is executed when a user issues the CLOSE DATABASE or DISCONNECT statement to disconnect from the database.
The sysmaster database contains the system-monitoring interface (SMI) tables. The SMI tables provide information about the state of the database server. You can query these tables to identify processing bottlenecks, determine resource usage, track session or database server activity, and so on.
First, we create a table to hold all the records of user connections and the resources they consumed. This table needs to be created in the database that needs to monitor the users connections.
CREATE TABLE connect_log
(
cl_id bigserial,
cl_login VARCHAR(32) DEFAULT USER,
cl_sid BIGINT,
cl_pid BIGINT,
cl_connect datetime YEAR TO SECOND
DEFAULT CURRENT YEAR TO SECOND,
cl_disconnect datetime YEAR TO SECOND
DEFAULT NULL,
cl_program varchar(255),
cl_hostname varchar(255),
cl_inserts BIGINT,
cl_updates BIGINT,
cl_deletes BIGINT,
cl_selects BIGINT,
cl_commits BIGINT,
cl_rollback BIGINT,
cl_sorts BIGINT,
cl_lgrecs BIGINT,
cl_numios BIGINT,
cl_iowait float,
cl_lockwait float,
cl_cputime float
) lock mode row;
Second, create a procedure which insert a row when a user connects to the database recording when the user logs in along with the program name, client application process id and the hostname for which the client is connecting from. Please note that the program name and client application process id has been added to 11.70.xC4.
DROP PROCEDURE IF EXISTS public.sysdbopen();
CREATE PROCEDURE public.sysdbopen()
SET ISOLATION TO DIRTY READ;
INSERT INTO connect_log
(cl_sid,cl_pid, cl_program,cl_hostname)
SELECT sid, pid, progname, hostname
FROM sysmaster:sysscblst
WHERE sid = DBINFO("sessionid");
END PROCEDURE;
Lastly, create the procedure which will be executed upon closing/disconnecting from a database. This procedure will update the row inserted in the sysdbopen procedure with the log out time and sessions statistics.
DROP PROCEDURE IF EXISTS public.sysdbclose();
CREATE PROCEDURE public.sysdbclose()
UPDATE connect_log
SET ( cl_disconnect, cl_inserts, cl_updates, cl_deletes,
cl_selects, cl_commits, cl_rollback, cl_sorts, cl_lgrecs,
cl_numios, cl_iowait, cl_lockwait, cl_cputime
) =
( ( SELECT CURRENT, upf_iswrite, upf_isrwrite, upf_isdelete,
upf_isread, upf_iscommit, upf_isrollback , upf_totsorts,
upf_lgrecs, upf_niowaits, iowaittime, lkwaittime , cpu_time
FROM sysmaster:sysrstcb R, sysmaster:systcblst T
WHERE sid = DBINFO("sessionid") AND R.tid = T.tid ) )
WHERE cl_sid = DBINFO("sessionid")
AND cl_disconnect IS NULL;
END PROCEDURE;
Posted in Performance, Stored Procedures
Leave a comment
OpenAdmin Tool is now availible in the CSDK installer
The openadmin tool (aka OAT) is now available with the CSDK product and bundled with 11.70.xC4. While the product is not installed by default you can choose the custom installer and select the OpenAdmin Tool to be installed. OAT is available on windows 32 bit, linux 32 & 64 bit and on the mac. This also means that IBM will officially accept support calls on OAT. For more information please see OAT’s http://www.openadmintool.org
Posted in openadmin Tool (aka OAT)
Leave a comment
Evolution of the Update Statistics command and tasks
Update statistics has under gone many changes through out the years and this will take a high level look at these changes.
Version 4.0 Statistics only
Version 6.0 included distributions (update statistics medium and high)
Version 7.31 Significant performance improvement
- Update statistics low runs in parallel on fragmented indexes
- Update distributions can utilize more memory to run multiple columns in parallel
Version 11.10 Auto update statistics
- Schedule the statistics and distribution to be run only on the tables which have changed.
Version 11.50 Builds statistics and distributions inline with create index
Version 11.70 Smarter update statistics
- Statistics and distributions skipped automatically only if underlying tables has been modified
- Statistics and distributions are stored at a fragment level and only fragments which are changed are update
11.70.xC3 Faster Update Statistics Low
11.70.UC4 Auto Update Statistics now will support non-logging and ANSI
Posted in Performance
Leave a comment
In case you didn’t hear about automatic readahead in 11.70.xC3
For those of you that missed the talk on automatic readahead at IIUG this spring (your loss!), here’s some information that will introduce you to a new feature released in 11.70.
Automatic readahead is designed to alleviate the need to have to worry about configuring Informix to get optimal i/o readahead for queries. It works by turning readahead on and off during query execution whenever the data being read dictates what’s best for the query. It works for the 3 basic types of scans; index (keyonly), data (sequential) and index/data (retrieve rids from the index and look up the full row from the data partition). As a query is scanning data, if Informix encounters the need to read a page in from disk, it will immediately start issuing readahead requests. If the query scans a significant number of pages and finds that they are already cached, Informix will stop issuing readahead requests for that query until it encounters another page needing to be read from disk.
To best utilize this feature, remove RA_PAGES and RA_THRESHOLD from the onconfig file and add AUTO_READAHEAD 1.
For those who want to play with configuring automatic readahead a bit further…
The default ONCONFIG settings for readahead are AUTO_READAHEAD 1,128.
The ‘1’ indicates the use of passive mode for readahead during query execution where Informix won’t issue a readahead until it encounters a page not in the bufferpool. AUTO_READAHEAD ‘2’ indicates to use aggressive mode where Informix will immediately issue readahead requests at the start of the query. In either mode, readahead will be turned off and on during query execution as the query needs. If the query encounters a large pocket of already cached pages, readahead will be turned off. If readahead is off, as soon as the first i/o to disk is encountered, readahead will be turned on. AUTO_READAHEAD 0 will turn readahead off for all queries and it remains off. Readahead can also be controlled at the session level using the SET ENVIRONMENT statement (see documentation for complete details). The SET ENVIRONMENT statement can be used to override the ONCONFIG settings.
The ‘128’ indicates the number of pages that readahead will asynchronously fetch. As the query executes and consumes the pages, it will issue another readahead request when the remaining pages to be consumed is ½ of the readahead request size. This use to be known as RA_THRESHOLD.
Ex).
A sequential scan starts and issues a readahead for 128 pages. After the scan has processed 64 pages, it will issue another readahead request for the next 128 pages. After the scan has processed 128 pages, it will issue another request for 128 pages. And so on…
Readahead request for pages 1-128
Scan processes pages 1-64
Readahead request for pages 129-256
Scan processes pages 64-192
Readahead request for page 257-384
…
If your application typically uses less than 128 pages to satisfy a query, you might want to adjust the amount of readahead to be done. You can detect this by seeing that your readahead utilization is low. Readahead does use the where clause to bound readahead but if the query is cut short, like using the FIRST N clause, readahead may read more than what is necessary to satisfy the query.
In lab testing, we found very little difference between passive and aggressive mode. We found no degradation from having passive readahead turned on and running oltp applications in a cached environment. But, as always, your experience may differ.
Enjoy!
Posted in Performance
Leave a comment
Quick overview of the Informix Database Scheduler
The database scheduler allows a DBA to organizes jobs to run repetitively at predictable times without worrying about platform specific issues and permissions. These jobs come in four different categories:
- Tasks – work that needs to occur at a specific time or time interval
- Sensors – A job which collects information about an object at a specific time interval
- Startup Task – A task which is performed only when the instance starts up. (specifically every time the server moves from quiescent mode to online mode)
- Startup Sensor – A sensor which collect information only when the instance is started up up.
A task can be a simple or complex SQL statement or a user defined routine (UDR) written in Informix SPL, JAVA or C. An example of a task is the weekly purging of data from a table. This is a job which must be preformed on a regular schedule an no output is produce. A sensor is a specialized task which is geared to collecting information and storing that information in a table. Examples of a sensor would be to capture the number of sales that occur hourly or the amount of I/O done by each disk drive daily. While many of the default task and sensor deal with administration and the collection of database statistics, there is no reason why tasks and sensors can not be utilized by end user applications.
The database scheduler has a significant amount of supporting infrastructure which allows for an administrator to simply and quickly build a tasks or sensor. Some of this supporting infrastructure includes:
- The ability to associate tasks and sensor into functional groups
- Detail tracking of the execution of each invocation of a task or sensors
- The ability to create alerts with varying severity
To create a task or sensor the DBA needs only to insert a single row into the sysmaster:ph_task table. Each row in the ph_task table is a unique task or sensor. The column in this table can be grouped into three distinct parts of a task or sensor:
- Name and description
- Execution
- Schedule of execution
There are two main categories of threads which comprise the database scheduler. The master threads called the db_sched thread monitors the ph_task table and detemines the next task or sensor to be executed. There is only one db_sched threads and it will not execute any from the task or sensor but rather monitor the schedule of the tasks. To view the information from the db_sched threads view, run the onstat -g dbc commands. The second set of threads are the db_worker threads. These threads execute the actions of each task or sensor. When the system starts up there are two db_worker threads by default, but their number may increase depending on the workload.
Have data which is oriented by time, consider Timeseries ?
Check this article out about Informix timeseries capabilities. It provides the basic
about timeseries, including the expected savings in both disk space and performance.
Posted in Performance, Uncategorized
Leave a comment
Happy 10th Birthday Informix-IBM
July 1 will mark the 10 year anniversary of Informix becoming part of IBM.
Posted in Uncategorized
Leave a comment
Informix Chat with the Lab – Unravel the Mysteries of Informix’s New Offerings & Features
| Event Date: | Jun 30, 2011 |
| Event Time: | 10:30 AM – 12:00 PM (Central Time) |
| Hosted By: | Anita McKeithen (IBM) |
| Presented By: | Jerry Keesee – Director, IBM Informix Database Development (IBM), John F. Miller III – STSM, Embedability Architect (IBM) |
| Slides: | Chat with Lab New Features 11.70.xC3 and 11.70.xC2 |
This chat covers the new product offerings and new features that have been introduced in the last year since Informix v11.70.xC1. You will hear about the exciting performance enhancements, including some that can improve query response time by a factor of 100. You will also hear about the many new usability features to help customers simplify the building of applications. Listen to the talk to find out the amazing advantages of the new Informix releases.
Register for the Event
Register for this event at:
https://events.webdialogs.com/register.php?id=5963992ea1&l=en-US
If clicking a link above does not work, please copy the entire link and paste it into your Web browser.
For technical questions, contact support at support@lotuslive.com.
For questions about this event, contact the host at: mckeithe@us.ibm.com.
Posted in Uncategorized
Leave a comment
Craving a bit of update statistics low performance?
Update statistics low gathers information about an index by traversing
across the leaves of the btree. Prior to 11.70.xC3, Informix would
read each leaf page individually without the use of any readahead
functionality. This meant that for each leaf page encountered, unless
the leaf page happened to be in the bufferpool cache, the update
statistics low process would have to wait for that leaf page to be
brought into the bufferpool cache before processing it and moving to
the next leaf page.
With the introduction of automatic readahead (also coming in
11.70.xC3), we’ve enhanced update statistics to utilize the same
readahead functionality that is provided to user queries that do
range scans. This dramatically improves the performance. In the lab
testing shows a 300% speed up. Of course, your milage will vary.
What do you have to do to take advantage of this?
Remove from your ONCONFIG file the onconfig parameters RA_PAGES and
RA_THRESHOLD. Then set AUTO_READAHEAD to 1.
AUTO_READAHEAD 1
BUT WAIT… There’s more.
We’ve also added a new algorithm for gathering statistics to use
sampling which avoids having to traverse the entire btree. We’ve gone
to great lengths to make sure we handle skewed data with a proprietory
algorithm. That 300% speed jumps to 2000% and best of all, unless the
data is skewed, the time it takes to get stats on and index remains
fairly constant regardless of the size of the index. That 1M page index
will take you about 30s-50s to get stats. Same for that 5M page index.
No more waiting hours for update statistics to complete.
What do you have to do to take advantage of this?
onmode -wm SQL_FEAT_CTRL=0
Enjoy!
Mr.GrumpyPants
Posted in Performance
1 Comment