Version 3 of the Foxhound Database Monitor is now available...

Wednesday, November 26, 2014

SQL Anywhere 16 Crushes Oracle, DB2 in TPC-C Price/Performance

SQL Anywhere 16 has rocked the TPC-C benchmark at 19 cents per transaction per minute...

    Total System Cost:  U$ 21,160.00 
          Performance:  112,890 transactions per minute
    Price/Performance:  19 cents per transaction per minute  
Here's what the overall ranking looks like, with Oracle coming in a distant second at 47 cents...

Want a historical perspective?

Have a look at this list to see how TPC-C numbers have improved over the years, including SQL Anywhere 11...
    Total System Cost:  U$ 17,540.00 
          Performance:  20,705 transactions per minute
    Price/Performance:  85 cents per transaction per minute  

Friday, November 21, 2014

Visualizing Connection-Level Resource Consumption

Question: How do I determine which connections use the most resources? Sybase Central 12 doesn't show CPU and RAM consumption.

Answer: Sybase Central and other tools all get their connection-level performance statistics from the builtin SQL Anywhere connection properties, and closest thing to a connection property for "RAM consumption" are TempFilePages and TempTablePages, with TempFilePages being the more useful of the two.

TempFilePages Returns the number of temporary file pages used by the connection.

TempTablePages Returns the number of pages in the temporary file used for temporary tables.
The closest connection property to "CPU consumption" is ApproximateCPUTime, and there are many other properties like RollbackLogPages that may also be of interest to you:
ApproximateCPUTime Returns an estimate of the amount of CPU time accumulated by a given connection, in seconds. The value returned may differ from the actual value by as much as 50%, although typical variations are in the 5-10% range. On multi-processor computers, each CPU (or hyperthread or core) accumulates time, so the sum of accumulated times for all connections may be greater than the elapsed time. This property is supported on Windows and Linux.

RollbackLogPages Returns the number of pages in the rollback log.
Unfortunately, Connections pane in Sybase Central 12 doesn't show any of those connection properties and there's no way to tell it to.

You can, however, tell the SQL Anywhere 12 DBConsole utility to display all those connection properties:

Here's how to launch DBConsole and connect to a running SQL Anywhere 12 database:
  -c "ENG=inventory12_envy;DBN=inventory12;UID=dba;PWD=sql;"
Unfortunately, unlike Sybase Central, DBConsole doesn't show the 8 internal connections that were spawned when the "k.delacruz" connection used intra-query parallelism to execute a runaway query. It also doesn't show any CPU time at all being consumed by the "k.delacruz" connection, which is true but profoundly misleading: the "k.delacruz" connection may not be using any CPU time but its eight minions are using up everything that eight CPUs have to offer.

The Foxhound 3 Database Monitor shows it all: all the internal connections, all the CPU time they are using, plus the total CPU time is also shown against "k.delacruz":

Foxhound also saves all the data it captures so you can look back in time to see what an individual connection was doing...

You can read more about Foxhound in the white paper here.

Wednesday, November 5, 2014

The New Foxhound 3 Connection History Page

One of the new features in Foxhound 3 is the Connection History page. Previously, if you wanted to see the performance history of one single connection over time you had two choices, both unattractive:

  • Repeatedly redisplay the History page to see all the connection data for each successive sample, then repeatedly scroll through the (hundreds of?) connections to find the one of interest, or

  • code an adhoc query for the connection of interest... if you could figure out what column(s) to use to uniquely identify a single connection (connection number isn't enough).
The Connection History page does all that for you; here's an example showing three samples for a single connection that executed a SELECT statement (one sample) followed by a CALL (two samples):

The next example shows a connection "347 / g.mikhailov" that
  • was first blocked by a row lock held by a second connection "346 / h.barbosa",

  • and then blocked again by a third connection "389 / s.winkler" after the Foxhound AutoDrop feature dropped the second connection:

You can still code an adhoc query to show the same thing, and Foxhound 3 makes it easier with the new sample_connection.connection_id_string column.

Here's what the Foxhound 3 Help says about the Connection Id String:

The Connection Id String column is an artificial identifier constructed by Foxhound to uniquely identify each target connection within the entire Foxhound database.

Foxhound uses this string to create URLs for opening new Connection History pages. You may find it useful when creating adhoc queries to select all the history for one connection. It is stored in the sample_connection.connection_id_string column, and there is an index on that column.

Here's the format of the Connection Id String:
ss                   sample_connection.sampling_id - unique identifier to each target database sampling session
cc                   sample_connection.connection_number  
yyyymmddhhmmss-sss   DATEFORMAT ( sample_connection.LoginTime, 'YYYYMMDDHHNNSS-SSS' ) 

Here's an adhoc query that uses a simple (and efficient) WHERE clause using the value '1-347-20141029161934-088' from the screenshot above:
SELECT CAST ( sample_connection.connection_number AS INTEGER ) AS "Conn#",
       CAST ( sample_connection.UserId AS VARCHAR ( 20 ) ),
       DATEFORMAT ( sample_header.sample_finished_at, 'Mmm Dd yyyy Hh:nn:ss AA' ) AS "Connection Samples",
       CAST ( sample_connection.BlockedOn AS INTEGER ) AS "Blocked By",
       CAST ( sample_connection.blocker_table_name AS VARCHAR ( 10 ) ) AS "Table",
       CAST ( sample_connection.blocker_reason AS VARCHAR ( 50 ) ) AS "Reason"
  FROM sample_connection
       INNER JOIN sample_header
               ON sample_header.sample_set_number
                = sample_connection.sample_set_number
 WHERE sample_connection.connection_id_string = '1-347-20141029161934-088'
 ORDER BY sample_connection.sample_set_number DESC;

      Conn# UserId               Connection Samples       Blocked By Table      Reason                                             
----------- -------------------- ----------------------- ----------- ---------- -------------------------------------------------- 
        347 g.mikhailov          Oct 29 2014 4:20:38 PM          419 inventory  Row Transaction Intent, Row Transaction WriteNoPK  
        347 g.mikhailov          Oct 29 2014 4:20:28 PM          389 inventory  Row Transaction Intent, Row Transaction WriteNoPK  
        347 g.mikhailov          Oct 29 2014 4:20:17 PM          389 inventory  Row Transaction Intent, Row Transaction WriteNoPK  
        347 g.mikhailov          Oct 29 2014 4:20:07 PM          346 inventory  Row Transaction Intent, Row Transaction WriteNoPK  
        347 g.mikhailov          Oct 29 2014 4:19:58 PM          346 inventory  Row Transaction Intent, Row Transaction WriteNoPK  
        347 g.mikhailov          Oct 29 2014 4:19:47 PM            0 (NULL)     (NULL)                                             
        347 g.mikhailov          Oct 29 2014 4:19:37 PM            0 (NULL)     (NULL)    

Tuesday, November 4, 2014

Foxhound 3 Versus The Alternatives

(The following is an excerpt from The Foxhound 3 Database Monitor white paper.)

A database monitor is a computer program that measures the activity of a database management system and displays those measurements in a meaningful way so you can see everything's OK... or quickly learn about problems and threats to performance and availability.

Foxhound 3 is a third-party database monitor for SAP® SQL Anywhere®. Here's how it works:
  • Every 10 seconds Foxhound retrieves performance statistics from your database.

  • Foxhound then

    • stores these statistics in its own SQL Anywhere 16 database,

    • performs summarization and other value-added calculations, and

    • displays the results via HTML using SQL Anywhere's built-in HTTP server.
Figure 1 shows the Foxhound Monitor page for a lightly-loaded SQL Anywhere server with 134 connections, one of which is blocked by an uncommitted operation made by a long-running transaction on another connection.

Figure 1. The Foxhound Monitor Page Showing An Idle Server With One Blocked Connection

Table 1 compares Foxhound Version 3 with four alternative products:
Table 1. Comparing Foxhound 3 With Alternative Products
SQL Anywhere Monitor 16Sybase Central Performance Monitor 16DBConsole Utility 16Windows 7 Performance MonitorFoxhound 3
Primary purposeHealth and availability monitorPerformance monitorConnection monitorPerformance monitorHealth, availability and performance monitor
Setup requiredA lotEverythingSomeEverythingVery little
Collection interval30 sec default, 10 sec minimum1 sec fixed 4 sec default, 1 sec minimum1 sec default, 1 sec minimum10 sec fixed
PresentationGraphs, TextGraphsTextGraphsText
ImplementationAdobe FlashJavaNative WindowsNative WindowsHTML
Historical dataYes---Yes
Adhoc reporting----Yes
SQL Anywhere statisticsSome, variableAll, variableAll, variableAll, variableSome, fixed
Latency measured----Yes
Peaks recorded----Yes
Database Alerts9 point-in-time events---32 conditions
Alert "All Clear"----Yes
Drop connectionmanual-manual-AutoDrop
Target databases supported11, 12, 16, On Demand 1.011, 12, 16, On Demand 1.011, 12, 16, On Demand 1.05.5, 6, 7, 8, 9, 10, 11, 12, 16, On Demand 1.05.5, 6, 7, 8, 9, 10, 11, 12, 16, On Demand 1.0
Target hosts supportedlocal, networklocal, networklocal, networklocallocal, network
MobiLink and Relay Server supportYes----
What's good?PrettyFreeFreeFreeBusinesslike
What else?ConfusingHideousBasicHideousCrowded

Primary purpose: The SQL Anywhere Monitor omits the phrase "performance monitor" from the docs as does DBConsole.

Setup required: Both DBConsole and Foxhound let you "connect and go" to see something useful while the others require varying degrees of effort just to get started.

Collection interval: Only one Foxhound customer has ever asked for "faster", nobody wants "slower".

Presentation: Nobody's ever asked for graphs so they remain a low-priority item for Foxhound.

Implementation: How a product was built can explain a lot about how it looks and feels and works.

Historical data: This sets the SQL Anywhere Monitor and Foxhound far, far apart from the others.

Adhoc reporting: This is about historical data, not retrieving current statistics from SQL Anywhere or Windows.

SQL Anywhere statistics: Like the collection interval, Foxhound's choice of which statistics to display is fixed.

Latency measured: Response time isn't one of the statistics provided by SQL Anywhere.

Peaks recorded: Peak values are displayed as hypertext links into Foxhound's historical data.

Database Alerts: Events happen once, conditions go into and out of effect.

Alert "All Clear": It's important to know when an Alert condition is no longer in effect.

Drop connection: Runaway connections are a big deal in some shops... not all, but some.

Target databases supported: Foxhound support for Version 5.5 databases will disappear someday, but not yet.

Target hosts supported: The Windows 7 Performance Monitor reports on the computer it's running on.

MobiLink and Relay Server support: The SQL Anywhere Monitor is the only game in town for this.

What's good? "If you had to use a single word to describe what you like about..."

What else? "Give me another word, this time describing what you don't like about..."