Learnosity Logo
Learnosity Banner Image

Remote collection of vmstat log files

We've been doing a lot of load testing recently and are planning on doing a lot more, so we've developed a simple little script to make the collection of the vmstat (performance statistics) a bit quicker.

multivmstat is a php command line script which makes it easy to collect the statistics. Download the script

To run it you specify a list of servers to check:

./multivmstat server1,server2,server3 5

This will run vmstat on each of the servers with a 5 second interval between samples.

It will create the following files:

server1-vmstat.log
server2-vmstat.log
server3-vmstat.log

Note: You mush have ssh access to each machine - ideally with a certificate so that no interactive authentication is required.

Download the script

MySQL and Temporary tables and CASE syntax

For some reason I've always avoided the use of temporary tables in SQL (MySQL or otherwise) but today I came across a difficult problem that temporary tables made very simple.

I needed to use a case function on a subquery value to return a tri-state value. I.e. yes, no or partial.

The cut down query below demonstrates how it works.

  • First we drop the temporary table in case one has been left lying around.
  • Then we select a query into the temporary table which include some complex subqueries.
  • We can then query the temporary table to do further processing on the result, which in this case depending on the values of the class_count and class_count_assigned allows us to set a field in three states.
    • 'yes' if all users for a class are selected
    • 'partial' if some users are selected
    • 'no' if none are selected.
<cfquery name="q" datasource="#variables.myDSN#">
   DROP TEMPORARY TABLE IF EXISTS tmp_exams;
   /* Create temporary table query which includes student counts */
   CREATE TEMPORARY TABLE tmp_exams
   SELECT   e.id
         , e.name
         , (SELECT count(1) FROM tbl_user_classes uXc
WHERE uXc.school_class_id = <cfqueryparam value="#arguments.school_class_id#" cfsqltype="cf_sql_integer">) AS class_count
, (SELECT count(1) FROM tbl_user_classes uXc
INNER JOIN tbl_users_exams uXe ON uXe.user_id = uXc.user_id
WHERE uXc.school_class_id = <cfqueryparam value="#arguments.school_class_id#" cfsqltype="cf_sql_integer">
AND exam_id = e.id) AS class_count_assigned
   FROM   tbl_exams e;
   /* Select out the relevant info */
   SELECT e.id
         , e.name
         , e.class_count
         , e.class_count_assigned
       ,(CASE WHEN class_count_assigned = 0 THEN 'no'
          WHEN class_count = class_count_assigned THEN 'yes'
          ELSE 'partial' END) AS active
   FROM tmp_exams e
   WHERE   1=1
   ORDER BY e.name
</cfquery>

Mostly posted for my future reference but hope it helps out.

Cheers, Mark

CF Sandbox Security Tricks and Tips

I've been working on getting CF Sandbox security working. It's trickier than I first thought so here's how to do it for future reference:

Enable security Manager

Instructions from Steven Erat's blog:
Stop ColdFusion.
Locate the jvm.config file in jrun_root/bin.
Back up the file.
Open the file in a text editor.
Add the following lines to the java.args section:
-Djava.security.manager
"-Djava.security.policy=[cf_webapp_root]/WEB-INF/cfusion/lib/coldfusion.policy"
"-Djava.security.auth.policy=[cf_webapp_root]/WEB-INF/cfusion/lib/neo_jaas.policy"

NOTE the example from the adobe site has the quotes in the wrong place. Note you also need to change [cf_webapp_root] to match the location on your machine.

Datasources

After I enabled Datasource security I began to receive the following error, a bit of digging reminded me that the MySQL connector tries to do some autoconfiguration for coldfusion to optimise it.

Message: Can't find configuration template named 'coldFusion'
Type: java.sql.SQLException

It was failing to load the configuration file which is inside the mysql connector jar file /com/mysql/jdbc/configs/coldFusion.properties

I haven't gotten to the bottom of why it couldn't be loaded but adding the following to the datasource query string fixed it up:

autoConfigureForColdFusion=false

I would suggest adding some of the settings from this file as paramaters in your datasource settings as per previous post

Note: This was using the following:

  • ColdFusion 7.0.2 Cumulative Hotfix 1 Multiserver install
  • MySQL connector/J 5.0.8

Hope it helps. Cheers, Mark

Managing long running requests on ColdFusion

The Problem

We've been working on an application which needed significant integration with an external system which is done via web services.

During out load testing we came across a major issue with the web services when the application was under load which caused the ColdFusion application server to crash very badly.

The problem was caused by the fact that the web service calls typically took about 1 second to complete whereas pages that didn't need to use a web-service completed in about 100ms.

When we put the application under load if the number of requests that needed web-services ran at a sustained high rate then very soon all the running requests would be doing web service calls. This meant that all 25 java threads were getting swallowed up the "long running" web-service calls.

This caused all the other threads to queue up and very shortly the application fell over in a heap.

The Solution

Initially we looked a using cflock tags to handle it but this would essentially serialize all web service requests and only one web-service thread would run at any one time. This meant that the application would not be able to handle the required load.

After a bit of a brainstorming session we came up with the idea to develop a semaphore type object which would limit the amount of threads that could get tied up with long running web-service requests.

Ideally it works like this:

  • A web-service request comes in
  • It requests a thread from the Semphore object
  • If it gets one it runs the Web Service and then releases the thread

However, if the system is busy it works like this:

  • A web-service request comes in
  • It requests a thread from the Semphore object
  • It doesn't get a thread so doesn't run the web service and quickly returns and lets the user know the app is busy

Usage

In order to use the semaphore you need to set it up in a shared scope - typically application or server scope. We use it in our webservice wrapper CFC which is stored in the application scope.

<cfset variables.instance.Semaphore = createObject("component", "Semaphore").init("unique_name", 15,"logfilename")>

Here is a code example of how this looks in practice:

<cfset threadID = variables.instance.Semaphore.acquireThread()>
<cfif threadID GT 0>
   <cftry>
   <!--- We handle all errors in here to ensure we release the thread afterwards --->
      <cfinvoke webservice here>
   
      <cfcatch type="any">
      <!--- Log errors but continue so we release thread--->
      </cfcatch>
   </cftry>
   <!--- Make sure we release the thread - even if everything above explodes --->
   <cfset variables.instance.Semaphore.releaseThread(threadID)>
</cfif>

Note: One of the issues that we came across during the implementation of this was that when long running requests were caught by the long running request timeout the releaseThread function would not get called as the webservice code was timing out and it was getting killed before it hit the releaseThread line. To work around this we implemented an internal garbage collection mechanism to ensure that we didn't leak threads, or when we did we could recover from it.

This has allowed us to manage the number thread by limiting the number of long running requests so as the server gets busy it will now reject web service calls before it runs out of memory.

You can get the full code for the Semaphore object from our Opensource CF Library

"Show Full Columns" problem with CFMX and MySQL solved

We've been performing some load testing on a new website we've developed and our helpful sysadmin noticed lots of queries happening on the DB that looked like this:

SHOW FULL COLUMNS FROM `dbname`.`tablename`

We weren't intentionally doing these queries but they were coming from somewhere. A bit of detective work via google found a very enlightening article about this problem.

A quick read of this explained:

It turns out ColdFusion was asking Connector/J for the metadata on every field, which in turn triggered a SHOW FULL COLUMNS query for every varchar and text column returned.

It also went on to say that is had been fixed as of Connector/J 5.07.

On reading the release notes it mentions that the

Driver detects when it is running in a ColdFusion MX server (tested with version 7), and uses the configuration bundle coldFusion, which sets useDynamicCharsetInfo to false (see previous entry), and sets useLocalSessionState and autoReconnect to true.

However, from my testing it wasn't doing it as we were still seeing the problem. However, we are running the MultiServer version of ColdFusion which sits atop Jrun4.

So I added the parameter directly to the querystring:

&useDynamicCharsetInfo=false
So it now looks like:

This has stopped the problem, and given a significant performance gain. On one of the sites I was load testing it gave a 12% throughput increase and on another one which had less queries it gave a 4% increase.

Hope it helps.

Cheers, Mark

Job Vacancy Sydney AU - Graduate or Junior OO Programmer

We're looking for a Computer Science Graduate or similar with strong Object Oriented programming skills and the ability to grasp cutting edge technologies quickly.

If you are looking to work with the latest technogies and are a motivated self starter with a positive "can do" attitude this is the job for you. The ability to take problems and deliver solutions is a must.

There will be significant on the job learning as we are always pushing the boundaries and using the latest and greatest technologies. We are currently working on projects using Actionscript/Flex, ColdFusion, PHP, and Javascript, and are leveraging technologies such as VOIP, SMS, Instant Messenger (Jabber XMPP) to deliver cutting edge solutions.

Suitable applicants will have a Bachelors Degree in Computer Science or Engineering, or similar tertiary qualification. Remuneration commensurate with experience.

Responsibilities would include:

  • Developing new functionality for Asterisk and VOIP applications
  • Creating Rich Internet Applications with Adobe Flex
  • Creation of Desktop applications with Adobe AIR
  • Design and development of new Web applications

Must haves:

  • A robust foundation in Object Oriented programming
  • Demonstrated experience in at least one OO language
  • Motivation to learn and push the boundaries
  • Understanding of XHTML and CSS

Preferable:

  • Experience with PHP, ColdFusion, Farcry CMS, XML, Web Standards etc
  • Competency using linux-based tools (SSH, bash, etc)
  • Familiar with using source control tools (Subversion, etc)
  • Familiarity with Actionscript or Flash

This role may be full time, part-time or on a contract basis depending on the candidates skills and experience. You will be working in a casual workplace with flexible hours in the centre of Sydney.

If this sounds like the job for you email your resume to mark@lynchconsulting.com.au if interested - no agencies please.

CFMX "The Graphing service is not available" - solved

I've just completed setting up a new ColdFusion server on Ubuntu Server (I know it's not a supported config) and ran into the following problem when I went to the admin page:

The Graphing service is not available

A bit of googling turned up this Technote from Adobe which mentioned the same problem on RedHat Linux.

The redhat solution is also documented on Talking Tree but it doesn't cover Ubuntu.

For Ubuntu it's very simple - you need to install 3 libraries and you are good to go - one single line.

sudo apt-get install libXp6 libXt6 libXtst6

This will install a dozen or so packages - then restart ColdFusion and you have a shiny working install.

Cheers, Mark Lynch

CF 7.0.2 Cumulative Hotfix 2 breaks MySQL Multiple Queries

I've just spent a while figuring out a painful bug in CF and am posting here so that I can reference it from the Adobe bug tracker as I can't explain the problem in 2000 characters.

Summary of Problem

CF 7.0.2 Cumulative Hotfix 2 and 3 breaks the use of Multiple SQL statements with MySQL connectorJ connector.

This is a regression as it works perfectly in CF 7.0.2 and CF 7.0.2 with Cumulative hotfix 1.

I have tested the following configurations:

  • CF7.0.2 MySQL connectorJ 5.0.8 - works
  • CF7.0.2 hotfix 1 MySQL connectorJ 5.0.8 - works
  • CF7.0.2 hotfix 2 MySQL connectorJ 5.0.8 - broken
  • CF7.0.2 hotfix 3 MySQL connectorJ 5.0.8 - broken
  • CF7.0.2 MySQL connectorJ 3.1.10 - works
  • CF7.0.2 hotfix 1 MySQL connectorJ 3.1.10 - works
  • CF7.0.2 hotfix 2 MySQL connectorJ 3.1.10 - broken
  • CF7.0.2 hotfix 3 MySQL connectorJ 3.1.10 - broken

Steps to reproduce bug:

1. Set up a CF 7.0.2 Multiserver install with built in webserver

2. Download and install ConnectorJ JDBC driver for MySQL and install as per Adobe Technote http://www.adobe.com/go/6ef0253

3. Create a mysql database named "test" the and create a table with the following script:

CREATE TABLE `tbl_test` (
   `id`    int(11) AUTO_INCREMENT NOT NULL,
   `data`   varchar(50) NULL,
   PRIMARY KEY(`id`)
)

4. In CF Admin create a DSN named dsn_test with following settings

  • JDBC Url: jdbc:mysql://127.0.0.1:3306/test?allowMultiQueries=true
  • Driver Class: com.mysql.jdbc.Driver
  • Driver name: (leave blank)
  • username: (valid user for your db - default: root)
  • password: (valid password for your db - default: 'blank')
  • Place the following file in the /opt/jrun4/servers/cfusion/cfusion-ear/cfusion-war directory

MySQL driver version:
<cftry>
<cfset oDriver = createObject("java","com.mysql.jdbc.Driver")>
<cfoutput> #oDriver.getMajorVersion()#.#oDriver.getMinorVersion()#</cfoutput>
<cfcatch type="any">
   Unknown
</cfcatch>
</cftry>

<cfquery name="qTest" datasource="dsn_test">
   INSERT INTO tbl_test (data)
   VALUES ('Test String');
   SELECT LAST_INSERT_ID() as lastInsertID;
</cfquery>
<cftry>
<cfdump var='#qTest#'>
<cfcatch type="any">
   No Query set returned from query.
</cfcatch>
</cftry>

This code should function correctly and return a cfdump of the auto generated primary key as per expected screenshot.

Next apply Coldfusion Cumulative hotfix 2 and restart coldfusion as per Adobe Technote http://www.adobe.com/go/kb400996

Re run the test and no query set will be returned from the driver. The qTest variable does not get set as per the broken screenshot.

Related Resources

Technote re upgrading MySQL JDBC Drivers http://www.adobe.com/go/6ef0253

CF Updates page http://www.adobe.com/support/coldfusion/downloads_updates.html#mx7

CF 7.0.2 Cumulative hot 3 http://www.adobe.com/go/kb402465

CF 7.0.2 Cumulative hot 2 http://www.adobe.com/go/kb400996

CF 7.0.2 Cumulative hot 1 http://www.adobe.com/go/kb400074

MySQL ConnectorJ http://www.mysql.com/products/connector/j/

MySQL ConnectorJ 3.1.10 Download: http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-3.1.10.tar.gz/from/pick#mirrors

CFMX - SOAP vs REST benchmarks

One of the applications that we have developed relies quite heavily on web services and I've been doing some benchmarking to figure out how we can improve the performance of it.

Currently the application consists of a AIR/Flex frontend and a CF backend which is using SOAP web services to communicate. I wanted to understand the level of overhead that the SOAP layer added to each coldfusion request and understand if it would be beneficial to move away from using SOAP to using a REST type direct XML format.

Test 1: SOAP CFC vs REST CFM

Initally I created two files which were a very simple hello world example to test each scenario.

soapservice.cfc:

<cfcomponent>
<cffunction name="hello" access="remote" returntype="string">
   <cfreturn 'hello world'>
</cffunction>
</cfcomponent>

and the CFM/REST version

xmlservice.cfm:

<cfsetting enablecfoutputonly="true">
<cfcontent reset="true" type="text/xml">
<cfparam name="url.method" default="">
<cfswitch expression="#url.method#">
   <cfcase value="hello">
      <cfoutput><?xml version="1.0" encoding="utf-8"?><str>Hello World</str></cfoutput>
   </cfcase>
   <cfdefaultcase>
      <cfoutput><?xml version="1.0" encoding="utf-8"?><str>Unknown Method</str></cfoutput>
   </cfdefaultcase>
</cfswitch>

The results of the load tests were striking

The rest version of the code was 4 to 5 times faster, which meant 1000 second per minute instead 200 requests per second.

However, looking at the code I thought it was an unfair comparison as the SOAP one was using OO-style code which was more maintainable. I'm very much an OO person so I didn't want to have to live with horribly unmaintainable code just to get performance.

Test 2: SOAP CFC vs REST CFM with CFC

I created a new version of the CFM file which this time leveraged the soapservice.cfc which meant I could use the same code but bypass the seemingly very slow SOAP layer in Coldfusion.

xmlservicewithcfc.cfm:

<cfsetting enablecfoutputonly="true">
<cfcontent reset="true" type="text/xml">
<cfparam name="url.method" default="">
<cfswitch expression="#url.method#">
   <cfcase value="hello">
      <cfset oSS = createObject("component","soapservice")>
      <cfoutput><?xml version="1.0" encoding="utf-8"?><str>#oSS.hello()#</str></cfoutput>
   </cfcase>
   <cfdefaultcase>
      <cfoutput><?xml version="1.0" encoding="utf-8"?><str>Unknown Method</str></cfoutput>
   </cfdefaultcase>
</cfswitch>

So this version now uses a REST type request but leverages the CFC code for the functionality of "hello world".

This was not quite as good performance as the pure CFM code with only 3 to 4 times the performance of the SOAP version - but still not bad at all.

Test 3: SOAP CFC vs REST CFM with CFC with caching

One thing that is known to be reasonably costly in CF is the instantiation of CFC objects. A typical way to avoid this is to instantiate the objects once and then store them in the persistent application scope. This technique will work for lots of the code I am looking to optimise so I wanted to see how much impact it would have.

xmlservicewithcfccached.cfm:

<cfsetting enablecfoutputonly="true">
<cfcontent reset="true" type="text/xml">

<cfapplication name="xmlservicewithcfcached" sessionmanagement="false" clientmanagement="false">

<cfparam name="url.method" default="">
<cfswitch expression="#url.method#">
   <cfcase value="hello">   
      <cfif NOT structKeyExists(application,'oSS')>
         <cfset application.oSS = createObject("component","soapservice")>
      </cfif>
      <cfoutput><?xml version="1.0" encoding="utf-8"?><str>#application.oSS.hello()#</str></cfoutput>
   </cfcase>
   <cfdefaultcase>
      <cfoutput><?xml version="1.0" encoding="utf-8"?><str>Unknown Method</str></cfoutput>
   </cfdefaultcase>
</cfswitch>

The performance of this version of the code was almost identical to the 1st version - i.e. 4 to 5 times faster than using the SOAP version but it more easily maintainable.

Is this a valid benchmark?

In a word no - in the strict sense of a reproducible benchmark. It was done on my development laptop as I don't have lots of spare machines lying around.

The specs of the machine are as follows:

  • Intel Core 2 Duo T7200 @ 2GHz
  • 3GB Ram
  • Ubuntu 7.10 (gutsy) with standard 2.6.22-14-generic kernel
  • Coldfusion 7.0.2 - Multiserver install - default config
  • Apache JMeter 2.3.1 for load testing

Each thread in the tests ran 10,000 loops - so for example the tests where I ran 50 threads would be 10,000 x 50 = 500,000 requests.

The entire test was conducted over the course of one day and there were no reboots during the tests. Mostly the same programs were running but it was connected to the internet so minor variations in load and traffic could have affected the results.

What about CF8?

After I finished the testing on CF7 (which is what we currently have on our Production servers) I tried it out on the CF8 Install to see how it compared - the details are detailed below.

Show me the stats

So here are the pretty stats and charts that I created to understand all the data:

Graph 1:

Table 1 - figures for Graph 1:

Graph 2:

Table 2 - figures for Graph 2:

Need more stats?

I have also uploaded the spreadsheet that I created to generate these stats in the original OpenDocument format and also as an Excel file.

Conclusion

For me - I'll be definitely looking at using the REST methods further on any high traffic sites where I might previously have used SOAP.

What are we gaining

  • Lots of performance - a 5 fold performance increase can't be ignored.
  • Smaller data being transferred as no SOAP envelope overhead
  • More control over data transferred
  • Ability to use conventional debugging and caching techniques

What are we giving up

  • Automatic handling of complex objects
  • Lots of overhead

I'd love to hear your thoughts on this - what else am I giving up?

Cheers, Mark

Multiple queries with CFMX-MySQL JDBC connector

I found a little snippet the other day and included it in the tail end of a previous post - however, on reflection I thought it deserved a post of it's own.

If you want to utilise multiple queries per sql statement and you are using the MYSQL JDBC connector you need to add the following to your jdbc connector querystring.

allowMultiQueries=true
So you querystring would look like:

This will allow you to do queries like the following to return autoincrement values:

<cfquery name="qInsert" datasource="mydsn">
INSERT INTO tbl_demo (name)
VALUES ( <cfqueryparam value="#myName#">);
SELECT last_insert_id() as newID
</cfquery>
<cfoutput>ID of value is: #qInsert.newID#</cfoutput>

Please note however that this functionality is disabled as it can leave the door open for SQL Injection attacks. However, as long as you always use cfqueryparam for all the dynamic parts of your query you will be fine.

More Entries