Catching performance bottlenecks

Well I've been involved in several Performance issues and 90% of the time what I have seen is that asking the right questions would solve the problem and the other 10% is hope, pray, trial and error.

So I decided the I would share some of my experience in order help any IT personal who maybe praying, hoping and asking god to give some sort of solutions to a performance bottleneck.

Being a Java guy, I have been mostly involved in performance issues pertaining to JEE apps and surprise, surprise... 99% of the time the issue is with the application or the DB structures....
 
So here goes my two cents....

When I am called to check on performance issues I always go with a blank page... no pre-assumptions, no ideas why this is happening or what could be causing the issue.

The first set of questions I ask are

What does the user see as a performance issue?
When does he see it?
What is he doing when he see performance issues?
What has he done before getting the performance issues?

Well most of the time the performance issues comes in two folds,

1. The system is really slow in giving out a desired response
2. The system just crashes

I will cover the sluggish / slowness performance issues here and will have another dedicated article on system crashes and what I have done to handle them.

Well once I get to know that the issue is a slowness problem, I try to ask my second and third questions


When does he see it?
What is he doing when he see performance issues?

Most of the time the answers would be,

1. When I try to go to certain screen or do some action in the screen
2. When I run the application for a certain time
3. At a specific time of day
4. Or a real disgruntled customer would say ALL THE TIME 

From the above three questions and the answers that I get, I can start formulating some solutions.
 

PL/TCL Trigger For Auditing with PostgreSQL

I was recently involved in identifying an issue with a PL/TCL audit trigger which was not working properly. The issue was whenever a modification was done to the table which was having the trigger, the triggers gets disabled and we would need to recreate the tables to be able t activate the triggers. Of course this was a major issue as each time something is done to the table we would need to rebuild the tables in order get the triggers working. Also just dropping and creating the triggers was also not a possibility and did not work.

Hence I started digging in to the issue and following is some insight into the way that we can create a PL / TCL trigger.

Why PL/TCL

Well in PsotgreSQL, there are many languages to create triggers from. PL/SQL, PL/TCL. PL/Python and PL/Perl. Of the four PL/SQL is the most famous for procedural program blocks in SQL DBMS. However there is a limitation in PL/SQL in that we can not have dynamic table names or columns names to be used within the trigger.

i.e. if we are to use the column that was changed and the table that was changed dynamically we would have an issue, because in PL/SQL we will not be able to pick the columns which are changed dynamically and they need to be statically coded in the PL/SQL function. This would cause us to have a separate PL/SQL function for each and every table we are going to add the trigger to or create separate trigger functions.

Hence PL/TCL to the rescue. With PL/TCL we can use C functions in PostgreSQL to get an array of changed columns and tables. Of course we would need to combine PL/TCL with PostgreSQL catalogue as well.

Set up

First we need to add PL/TCL capability to the database. In order to do this we need to compile PostgreSQL installation with adding PL/TCL. Once that is done we need to enable PL/TCL to the database.

i.e. using the CREATELANG function

createlang pltcl dbname or createlang pltclu dbname

Code

Following is an expert from a code that would create trigger function, and how that function can be attached to a table.

CREATE OR REPLACE FUNCTION xxx.log_to_audit_table()
RETURNS "trigger" AS
$BODY$


spi_exec "SELECT CURRENT_USER AS tguser"


spi_exec "SELECT c.relname AS tgname,n.nspname AS schema_name
FROM pg_class c , pg_namespace n
WHERE n.oid = c.relnamespace
AND c.oid = $TG_relid"



spi_exec "insert into xxx.testlogtable values ('inserted in log_to_audit_table $tgname')";

set pk_name ""

spi_exec "SELECT a.attname AS pk_name FROM pg_class c, pg_attribute a, pg_index i
WHERE c.relname = '$tgname'
AND c.oid=i.indrelid
AND a.attnum > 0
AND a.attrelid = i.indexrelid
AND i.indisprimary='t'"

spi_exec "SELECT audit_table_name AS m_aud_tbl_name
FROM $main_schema.audit_table_mapping
where schema_name = '$schema_name'
and tabel_name = '$tgname'"


.....

return OK
$BODY$
LANGUAGE 'pltcl' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

And following is the trigger created to a table to audit

CREATE TRIGGER trig_audit_admin_user
AFTER INSERT OR UPDATE OR DELETE
ON xxx."user"
FOR EACH ROW
EXECUTE PROCEDURE "xxx"."log_to_audit_table"();

Well that's it. Whenever you need to add  audit feature to a table, add the trigger to the table.

Survey Reveals How Smart Phones Are Utilized By U.S. Travelers For Things Other Than Talking And Texting

Survey Reveals How Smart Phones Are Utilized By U.S. Travelers For Things Other Than Talking And Texting :: Travel Industry Wire

Connecting to Microsoft Exchange Server from a Java Client

Recently I was involved in a project to connect from one of our company product to MS Exchange server. This was not as easy as I though it would be. There were lot of articles in net, however nothing was there with the end to end information I needed. Hence after I finally completed the project I created a blogspot dedicated to connecting to MS Exchange from multiple clients. I have done the Java connectivity via EWS and published. I will be updating the blog with a WEBDAV- Java version as well as a Python version soon.

You can get the full details in http://msexchange-javaconnectivity.blogspot.com/

Hope this helps all of you