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..."

Monday, November 3, 2014

What's New In Foxhound 3

A new version of the Foxhound database monitor for SQL Anywhere is now available; here's what's new...

1. Release-Defining Features
2. New Features
3. Usability Improvements
4. Behavior Changes
5. Bug Fixes

1. Release-Defining Features    

  1. Support for SQL Anywhere 16 and OnDemand 1.0 target databases has been added.

  2. The new Connection History page shows the performance of a single connection over time.

  3. The Blocked Connections section of the Foxhound Monitor page has been merged into the Connections section.

2. New Features    

  1. The new Change Target Settings page lets you set RememberLastPlan, RememberLastStatement and RequestTiming.

  2. The Machine, Server, Database names have been added to page titles and Alert messages.

  3. The new OS User column has been added to help identify connections: Conn #, ID, OS User, IP, Name.

  4. The new Busy, Wait, Idle columns have been added for each connection.

  5. The Conns connection count column has been expanded to show Parent and Child Conns.

  6. The new total Waiting Time column has been added to the Foxhound Monitor.

  7. The new Last Plan Text column has been added to the connection section.

  8. The new Favorable? YYY column has been added as a link to open the Change Target Settings window.

  9. You can now specify Purge after [xx] day(s) instead of being limited to 1 day, 1 week, 1 month or 1 year.

  10. The new Purge - Off / After xx days column has been added as a link to the Purge section of the Foxhound Options page.

  11. CPU Time calculations have been improved for connections using intra-query parallelism.

  12. The new total Rollback Log and Uncommitted columns have been added to the Foxhound Monitor page.

  13. The separate Req, Commits and Bytes In/Out columns have been gathered into the new connection-level Volume... Req, Commits, Bytes columns.

  14. The new Connection Id String can be used to uniquely identify connections in adhoc queries; e.g., '1-1-20140504073924-692'.

  15. A new emergency patch process is built in to Foxhound for fixing bugs without running a full upgrade.

  16. Five new Alerts have been added:
    Database read-only,
    Database updatable,
    Rollback log usage,
    Uncommitted operations and
    Long uncommitted.

  17. The new DB File and Used columns have been added to the Foxhound Monitor.

3. Usability Improvements    

  1. Major layout changes have been made to the Foxhound Monitor and Sample History pages, including the connection sections.

  2. The new Hide Details and Show Details buttons let you reduce scrolling on the Sample History and Connection History pages.

  3. The new Show More and Show Less buttons let you expand and contract the Last Statement and Last Plan Text connection data.

  4. A new warning about duplicate Foxhound sampling sessions has been added to the Foxhound Monitor page.

  5. The Foxhound option to show the Help frameis turned back on when Foxhound is upgraded, as a reminder that new Help content is available.

  6. Descriptive text has been added to all menu items on the Monitor Options page.

  7. The Peaks since timestamp has radically abbreviated to reduce horizontal scrolling.

  8. Alert email error messages have been improved.

  9. Concise Alert titles are included in all Alert-related messages; e.g., Alert #10: Partner unreachable.

  10. The Foxhound post-setup process has been streamlined.

  11. A new Start Foxhound via default browser shortcut has been added.

  12. There's no more "Can't open Message window log file" message when starting Foxhound.

  13. The Adhoc Schema connection string has been renamed to Foxhound 3 Adhoc Schema - autostart and connect.

  14. The handling of the diagnostic text files produced during the Foxhound Post Setup process has been improved.

  15. Scrolling on the Sample History page is faster.

  16. Color highlighting of Foxhound Monitor data has been improved.

  17. The sampling id and DSN or Connection String name are now appended to all target-related email exception diagnostics.

  18. The Running Time,
    Waiting Time,
    Transaction Time
    and other durations have been abbreviated to make them easier to read.

  19. There are no longer any limitations on the number or names of multiple Extended Edition Foxhound servers.

  20. Some Help topics previously found only in the Foxhound FAQ have been moved to the Help.

  21. The number of processors has been moved from the heading section to the CPU Time xx% of x column, with a link from the Peaks since line.

  22. The HTML generated by Foxhound now works better with the zoom features in Chrome, Firefox and IE.

4. Behavior Changes    

  1. SQL Anywhere or later is required to run Foxhound 3.

  2. The Cumulative CPU Time and Total Waits columns have been removed from connection-level displays.

  3. The Client Requests, Time columns have been removed from connection-level displays.

  4. The Total, Current Prepares and Rollbacks columns have been removed from connection-level displays.

  5. The Bytes In / Out columns have been removed from connection-level displays and summarized in Volume... Req, Commits, Bytes.

  6. Alert #11 HA failover has been renamed to Alert #11 ServerName change.

  7. The Waiting Req column has been renamed to Unsch Req to reduce confusion with SQL Anywhere documentation.

  8. Alert #14 Waiting Requests has been renamed to Alert #14 Unscheduled Requests.

  9. The Last Statement sort order is only changed when you click on the title text, not the data value.

  10. The security rules are different for connecting to SQL Anywhere 16 target databases.

  11. The dbsrv16 -ufd restart option has been added to the desktop shortcuts to keep Foxhound running.

  12. Foxhound is no longer being extensively tested on Windows XP or IE8.

  13. The Rollbacks column has been removed from database-level displays.

  14. The Checkpoints column has been merged into the Checkpoints, Checkpoint Urgency, Recovery Urgency column set because the values are related.

  15. The Log Writes column has been merged into the Disk Reads, Disk Writes, Log Writes column set because the values are related.

  16. The Free Disk Space Sys, Temp, Log, Other columns have been moved to the heading section because the values don't change very often.

  17. The Throughput... Commits column has been rounded so rates like 0.099/s are displayed as 0.1/s, because the extra precision was not helpful.

5. Bug Fixes    

  1. Support for Snapshot Isolation has been added, thus eliminating the fatal "Cannot convert 'Snapshot' to a decimal" error.

  2. The "Column '@alert11_actual_current_ServerName' not found" error has been fixed.

  3. A bogus Alert #9 Arbiter unreachable is no longer issued for an OnDemand database.

  4. The ALTER MATERIALIZED VIEW statement has been moved after CREATE INDEX on the Display Schema page.

  5. The click here to sort on list has been made version-specific.

  6. A bogus Alert #1 Database unresponsive is no longer issued when Foxhound is started after being upgraded.

  7. The email status is no longer displayed as "Email sent, status unknown." before the attempt to send an email is actually made.

  8. The popup tips for several Alert and AutoDrop criteria fields on the Monitor Options page have been changed to "Percent, 1 to 100".

  9. The minimum input value for the AutoDrop #3 Long transaction duration field on the Monitor Options pages has been changed to 1.0s.

  10. Bad input values entered on the Monitor Options page for integer Alert and AutoDrop criteria fields are now silently changed to the minimum allowed values.

  11. The Foxhound Options - Purge Run report no longer displays 2 as the number of Orphan/Old Sample Set Rows Deleted when no change is reported in the total number of rows.

  12. The Start Foxhound via Chrome shortcut no longer has trouble finding Chrome.

  13. The No Transaction Log curiosity message is no longer displayed for read-only databases.

  14. An error message is displayed instead of suppressing the sample when Foxhound can't call sa_locks() to diagnose a blocked connection.

  15. The handling of invalid SQL Anywhere property values coming from target databases has been improved.

  16. Heartbeat durations are no longer rounded upwards; instead, values between .001s and 0.099s are changed to 0.1s to distinguish them from 0s.

  17. Foxhound no longer tries to AutoDrop the same connection more than once.

  18. The CPOOL=NO connection parameter has been added to the connection string Foxhound uses to connect to target databases.

  19. It is now possible to start Foxhound in Safe Mode when it is run as a service.

  20. Safe Mode startup now marks all timed out sampling sessions as no longer timed out, just stopped.

  21. The Foxhound Options Timeout Retry - never retry setting now works properly.

  22. Concurrent usage of the Global Overrides sections of the Foxhound Options and Monitor Options pages is now properly handled.