Server side Prepared statements in MYSQL + Java

One thing that we generally forget when using MySQL prepared statements in Java is the setting up of the parameter "useServerPrepStmts". This variable must be set at the JDBC connection level to take advantage of MySQL server side prepared statements. Otherwise, though we follow all the standards of coding in writing client-side prepared statements, MySQL ultimately treats them as a regular statement only.

Below is an example datasource setting in JBOSS




SomeMySQLPool
true
jdbc:mysql://HOSTNAME:3306/SomeMYSQLDb
com.mysql.jdbc.Driver
NON ROOT USER NAME
PASSWD
true
org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter

mySQL

JBoss Performance Tuning

The first thing that we need to do as a part of this exercise is to identify the parameters that we have to work upon. Below is a list of different things that we can look upon before actually going into the tuning part.

JVM Settings
a) JAVA HEAP SIZE
b) PERM GEN SPACE
c) Parallel GC? (Good for multi CPU systems)

DB settings
a) Max Pool and Min Pool sizes for all Oracle and MySQL Pools.
b) idle-timeout-minutes
c) blocking-timeout-millis

HTTP connector Settings (Thread Pool Settings)
a) Max Threads -- Concurrency
b) Accept Count -- Queue size of each thread
c) Compression -- True/False

JSP Compilation Settings
a) We don't need to check for compiled JSP's every now and then unlike in dev.
b) No debugging for JSP's.

Security Settings
a) Remove universal access to JMS console and WEB consoles.
b) We can even try to restrict the access to this from the local machine.

Log4J Settings
a) Make sure we log only the critical errors.
http://tomcat.apache.org/tomcat-5.5-doc/config/http.html

Load Testing the application
a) This is one critical part that needs lot of application context. First of all we have to be really clear on what we are going to load test and the most probable pain points to look for. Then we can think of the data to test with.

Hibernate Settings
a) Cache Size

OS Settings
a) Large Pages (2.6 kernel supports it seems)

Apache for serving Static Content
a) Traditionally App servers are not great at serving static content. Apache is generally good when it comes to a serving static content. Some reasons for this are web servers having their own disk caching etc.
b) Over time, app servers have improved a lot in serving static content. But going for a web server for serving static content gives us the flexibility of creating a new domain and the content download can happen much faster.

References

http://www.jayson.in/wp-content/uploads/2008/08/jboss-folder-structure.jpg

Motivation!!!

Sometimes we want to improve the state of the things but ends up in a vain attempt. There are lot of factors that contributes to this. Not much of help from peers, micro-mgmt from top-level, lack of self-motivation, lack of will to take the ownership/leadership are things which I feel are the key factors that determine the performance of an employee.
Of course things should also be communicated in a better manner!! And guys should be motivated every now and then!! I always wonder if I ever learn the definition of "motivation" :( But what's motivation!! Is it pushing people to get the chores done!! Or is it trying to extract "more" from someone!! What's "more"? How much is that? If we can't even get the basic things done, can we even/ever get to the extent of extracting "more". I doubt!! But why should we even "motivate"? If someone is paid for and expected to do certain things by following certain well-established practices, do we additionally need to "push" them also? If the answer is "yes", I hate being a manager.
For me "motivation" means a positive way of influencing peers by setting some high standards!! Actions but not the words should speak.!!
Stopping here as I get irritated more and more to work when I think of these things!!

Sigh!!!
Sujay

What the heck is going on in life!!!

I don't know why exactly but I am feeling different today..definitely different but don't know exactly what..disturbed by something..definitely not a bad feeling though.. :) went up for a chai to think "that"!! i got it....not doing anything really constructive in life!!.. this thought is killing me since the last few years and increasingly turning out to be a huge menace for the survival of my psyche. can't take it anymore..i am going to do something.. get a pen and paper....lemme give it a try again today ... hope to add some good news by tomorrow..!!

My favorite leader

Leaders are born, not made. I believe in this and right from the day Obama had started his campaign I found a "leader" in him. He may not be having all the experience in the world like the McCain's or the Bush's but he is pretty clear on what he is going to do and what is expected out of him. He is more than a person with ambitions. He is a "force" and I just can't stop the rush of adrenaline when listening to his speeches. Especially when he delivers his trademark phrases "we are going to do it", "change is going to come", the adrenaline just rushes to the peak.

The election day was finally there. I woke up an hour early than my regular schedule with the anxiety of catching up with the election results. I opened cnn.com and to all my delight the top story reads out "Obama is gaining momentum across all the states". Minutes later Dems took a clear lead of Ohio, the battleground state and a traditional strong hold of the Reps. As the time pass by, more and more states are taken by the Dems and all that Mc Cain could do is to "concede". It's official and Obama is the new American President. Three cheers to the "leader"!!!

The first phase and probably the easiest phase is over and he has done with ease by keeping his cool all the way. But the road ahead for him may not be any smooth. He is probably getting into the hot seat in the toughest of times. Nothing is going well for the Americans. I believe that he has all the capabilities to take care of these serious problems and wish him all the best in his tenure at white House!!

Try to blog regularly!!

I don't know how I am away from blogging for such a long time. Might be I was preparing for my GMAT for so long that I was not able to do anything else. Neither did I prepare well and of course the final score reflected that very well :)

But now I should be free and will update my blog regularly!!

Multiple columns having default TIMESTAMP

Based on the last two statements in the above section, we can get a workaround for this (date_modified, date_modified) combination problem, where both the columns should default to sysdate.

Follow the below example.

mysql> create table multicolsdefaultts (name varchar(100),date_modified timestamp,date_added timestamp);
Query OK, 0 rows affected (0.03 sec)

mysql> show create table multicolsdefaultts;

CREATE TABLE `multicolsdefaultts` (
  `name ` varchar(100) NULL,
  `date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `date_added` timestamp NOT NULL default '0000-00-00 00:00:00'
)

mysql> insert into multicolsdefaultts values ('sujay', null,null);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from multicolsdefaultts;
+-------+---------------------+---------------------+
| name  | date_modified       | date_added          |
+-------+---------------------+---------------------+
| sujay | 2007-01-09 23:52:31 | 2007-01-09 23:52:31 |
+-------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> update multicolsdefaultts set name='sujay1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from multicolsdefaultts;
+--------+---------------------+---------------------+
| name   | date_modified       | date_added          |
+--------+---------------------+---------------------+
| sujay1 | 2007-01-09 23:52:44 | 2007-01-09 23:52:31 |
+--------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into multicolsdefaultts(name) values ('Andale');
Query OK, 1 row affected (0.00 sec)

mysql> select * from multicolsdefaultts;
+--------+---------------------+---------------------+
| name   | date_modified       | date_added          |
+--------+---------------------+---------------------+
| sujay  | 2007-01-09 23:52:31 | 2007-01-09 23:52:31 |
| Andale | 2007-01-30 17:02:01 | 0000-00-00 00:00:00 |
+--------+---------------------+---------------------+

mysql> insert into multicolsdefaultts(name, date_added) values ('Andale2', null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from multicolsdefaultts;
+---------+---------------------+---------------------+
| name    | date_modified       | date_added          |
+---------+---------------------+---------------------+
| sujay   | 2007-01-09 23:52:31 | 2007-01-09 23:52:31 |
| Andale  | 2007-01-30 17:02:01 | 0000-00-00 00:00:00 |
| Andale2 | 2007-01-30 17:12:01 | 2007-01-30 17:12:01 |
+---------+---------------------+---------------------+

The important things to remember here are

• DATE_MODIFIED field should be declared before DATE_ADDED is declared during the table creation. Else provide CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP for the default value.
• The date columns should be inserted with 'NULL' and should not be left out to be filled with default value. If left out then DATE_ADDED will not have appropriate date but "0000-00-00 00:00:00"

• MySQL gives warnings or errors if you try to insert an illegal date. But by using the ALLOW_INVALID_DATES SQL mode, we can still store illegal dates.

•  CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 

  . This allows to update the column to currenttime during row creation as well as updation.