How to Password Protect your DBTuna GUI

If you run DBTuna on a publically accessible server, or if you'd like to lock down usage of it internally, then the simplest solution is to username/password protect access to the GUI. DBTuna uses Apache Tomcat to serve it's content, therefore you can simply use the included Tomcat security.

To do this you need to edit a couple of XML files in the [dbtuna install
dir]/jakarta-tomcat-5.0.28/conf directory:

To create a username/password edit the tomcat-users.xml file and insert two lines:

<role rolename="dbtuna"/>
<user username="dbtuna" password="password" roles="dbtuna"/>

N.B. Change your username and password to be whatever you want, but keep the rolename as dbtuna.

Then insert the following lines in the web.xml file before the closing </web-apptag:

<!--
Password protect dbtuna pages
-->
<security-constraint>
 <web-resource-collection>
   <web-resource-name>
     DBTuna Application
   </web-resource-name>
   <url-pattern>/*</url-pattern>
 </web-resource-collection>
 <auth-constraint>
     <role-name>dbtuna</role-name>
 </auth-constraint>
</security-constraint>
<!-- Define the Login Configuration for this Application -->
<login-config>
 <auth-method>BASIC</auth-method>
 <realm-name>DBTuna Admin Users Only</realm-name>
</login-config>

When you have done this you need to re-start the agent and GUI. So, stop your agent via Agent Manager, and then either run the ./stopDBTuna.sh followed by ./startDBTuna.sh script from the installation root directory (if running DBTuna on Linux) or stop the "DBTuna GUI" Windows Service (if running DBTuna on Windows).

Enot finds and fixes MySQL performance problems with DBTuna!

Find out how Enot Amsterdam uses DBTuna to find and fix MySQL performance issues and guarentee their great application performance for their staff and customers DBTuna_eNot_Case_Study.pdf

Download the Quickstart Installation Guide here:

Quickstart Installation Guide 

Database Load Test Analysis

New Load Test Comparison Reports in DBTuna

Comparing any two database loads in a changing environment can be complex. This is especially true in a load testing environment where many different scenarios are evaluated for performance and scalability. Being able to quickly see what has changed between the different scenarios is vital. This is where the DBTuna load test comparison report excels. It immediately highlights where things have changed either positively or negatively saving you valuable analysis time.   The report gives a side-by-side comparison of two load tests, clearly showing the key performance indicators of each test along with the differences. It is easy to spot any change in CPU utilization, wait time or number of executions for each individual SQL statement as well as for the database instance overall. The DBTuna load test comparison reports are not limited only to load testing and can simplify the task of comparing any two scenarios. For example you could compare a QA load with production, or compare two nodes of a cluster. Or you might want to compare a period of time before and after a change such as the addition of a new index. Whatever your reason for using it, the DBTuna load test comparison report is a real time saver.

The DBTuna Load Test report is currently available for MySQL, SQL 2005/2008 and Oracle. Contact us to arrange a demo.

What Privileges are needed for DBTuna for MySQL?

DBTuna for MySQL needs access to the full processlist. In MySQL version 5 and above this comes with the "SUPER" privilege, the user will also need SELECT, PROCESS and SHOW DATABASE privileges. The easiest way to create a dbtuna user to use for the monitoring agent is as follows:

GRANT SELECT,SUPER,PROCESS,SHOW DATABASES on *.* to 'dbtuna'@'host' identified by 'password';

FLUSH privileges;

* Remember to change 'host' to the hostname or ip address of the machine running the dbtuna software, and 'password' to a suitable secure password.
See http://dev.mysql.com/doc/refman/5.0/en/adding-users.html for more details on how to create a user which can remotely access mysql.

If you have an earlier version of MySQL which does not have the SUPER privilege (to check for this you can look for the Super_Priv column in the mysql.user table), you will have to grant ALL to the dbtuna user i.e.

grant ALL on *.* to dbtuna@'%' identified by 'password';

flush privileges;

N.B. Make sure you change the 'password' to something more secure, and also change the '%' so that the dbtuna user can only access the database from one host (i.e. the host machine where the dbtuna monitoring software is installed.).

CXPACKET Wait in SQL Server

CXPacket wait in sql server occurs during parallel query execution, when a session is waiting on a parallel process to complete.

MSDN says that CXPACKET "Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem". Whilst this is true, it's also often the case that CXPACKET waits are the result of inefficient queries or stored procedures.

Here is an example of a SQL Server instance with high CXPACKET wait:

In this real-life example, several stored procedures had high CXPACKET waits because they included inneficient SQL statements which were not correctly indexed. A typical well-tuned database instance would not parallelize a query unless there was a missing index or there is an incomplete WHERE clause etc.

Potential Solutions to CXPACKET Wait

1. Is the problem related to inefficient SQL which can be tuned?

Use a tool such as DBTuna to quickly find out which stored procedures or batches are taking the time, and which have high CXPACKET wait. Once these have been identified, drill-down to establish which individual SQL's the wait is on. Once isolated, use a tool such as the SQL Server Index Tuning Wizard to check for missing indexes, or out of date statistics. Fix if possible. This was the process used to solve the above real-life example. The top stored procedure included multiple select statements, but just one was the bottleneck which included an unindexed sub-query.

2. If the problem cannot be tuned with Indexing

If the statement cannot be tuning using normal mechanisms e.g. Indexing, re-writing etc. then it may be that the solution is to turn off parallelism, either for an individual query or for the whole server.

To find out the current configuration of parallelism you can run the following command: sp_Configure "max degree of parallelism".

If max degree of parallelism = 0, you might want to turn off parallelism completely for the instance by setting max degree of parallelism to 1. You could also limit parallelism by setting max degree of parallelism to some number less than the total number of CPUs. For example if you have 4 processors, set max degree of parallelism to 2.

See the MSDN page for more information on CXPACKET and the Max Degree of Parallelism.

If you would like to find out more about the performance of your SQL Server instances then why not download DBTuna today! Or arrange a web meeting at a convenient time via our contact page.

direct path read

Direct path reads occur when a session reads buffers from disk directly into the PGA (as opposed to the buffer cache in SGA). If the I/O subsystem does not support asynchronous I/Os, then each wait corresponds to a physical read request.

If the I/O subsystem supports asynchronous I/O, then the process is able to overlap issuing read requests with processing the blocks already existing in the PGA. When the process attempts to access a block in the PGA that has not yet been read from disk, it then issues a wait call and updates the statistics for this event. Hence, the number of waits is not necessarily the same as the number of read requests (unlike db file scattered read and db file sequential read).

Below is a screenshot of a system with a large amount of direct path read waits:

Direct path reads can happen in the following situations:

Parallel slaves are used for scanning data.

Sorts are too large to fit in memory and therefore data is sorted on disk. This data is later read back using direct reads.

The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system.

If you would like to find out more about the performance of your Oracle instances then why not download DBTuna today! Or arrange a web meeting at a convenient time via our contact page.

RESOURCE_SEMAPHORE

RESOURCE_SEMAPHORE waits occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.

High waits on RESOURCE_SEMAPHORE usually result in poor response times for all database users, and need to be addressed.

The "DBTuna Waits Report" displays a time-series profile of wait events for your monitored instance. The example below is taken from a SQL Server 2000 instance which suffered from sporadic problems which resulted in a spike in RESOURCE_SEMAPHORE waits.

It is also useful to correlate high waits on RESOURCE_SEMAPHORE with the Memory Grants Pending and Memory Grants Outstanding SQL Memory Mgr performance counters. Higher values for these counters indicate a definite memory problem especially a non-zero value for Memory Grants Pending.

The root cause of this type of memory problem is when memory-intensive queries, such as those involving sorting and hashing, are queued and are unable to obtain the requested memory. The solution would be to tune the offending queries, or manage their workload so that they are executed at less busy times.

If you would like to find out more about the performance of your SQL Server instances then why not download DBTuna today! Or arrange a web meeting at a convenient time via our contact page.

Timestamp vs Datetime in MySQL

A Performance Comparison

This article compares the differences between querying a table containing a timstamp column versus an identical query on a table containing a  datetime column, the findings are quite surprising. This article does not make any conclusions on when to use which datatype, or even suggest any reasons for the differences in performance, it is just meant as a experiment. It does of course show why you should performance test everything within your application, and shows the importance of using good tools to gain visibility into performance testing.

Experiment Test Table:

mysql> desc test;
+-----------+---------------+------+-----+---------------------+-------+
| Field     | Type          | Null | Key | Default             | Extra |
+-----------+---------------+------+-----+---------------------+-------+
| timeslice | timestamp     | YES  |     | 0000-00-00 00:00:00 |       |
| col1      | varchar(20)   | YES  |     | NULL                |       |
| col2      | int(11)       | YES  |     | NULL                |       |
| col3      | varchar(50)   | YES  |     | NULL                |       |
| col4      | varchar(50)   | YES  |     | NULL                |       |
| col5      | int(11)       | YES  |     | NULL                |       |
| col6      | decimal(12,2) | YES  |     | NULL                |       |
| col7      | varchar(50)   | YES  |     | NULL                |       |
| col8      | varchar(100)  | YES  |     | NULL                |       |
| col9      | varchar(50)   | YES  |     | NULL                |       |
+-----------+---------------+------+-----+---------------------+-------+
10 rows in set (0.04 sec)

mysql> show indexes from test;
Empty set (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|   241853 |
+----------+
1 row in set (0.00 sec)

mysql> optimize table test;
+-------------+----------+----------+----------+
| Table       | Op       | Msg_type | Msg_text |
+-------------+----------+----------+----------+
| dbtuna.test | optimize | status   | OK       |
+-------------+----------+----------+----------+
1 row in set (0.00 sec)

Size on Disk
test.MYD 12,986 KB

N.B. The MySQL Query Cache is turned off so that caching does not factor in the results. i.e set global query_cache_size=0

Experiment Test query

This query does a full table scan to return a single row with the sum of col2 based on a 10 hour date range.

SELECT sum(col2)
FROM test
WHERE timeslice > date_add( current_timestamp(), interval -600 minute )
AND timeslice < date_add( current_timestamp(), interval 0 minute )

This statement was then executed 100 times against the database in order to get a nice rounded average response time of 0.31 seconds.

The timeslice column was then converted to a datetime column, and the test was repeated.

alter table test modify column timeslice datetime;

mysql> alter table test modify column timeslice datetime;
Query OK, 241853 rows affected (1.29 sec)
Records: 241853  Duplicates: 0  Warnings: 0

mysql> optimize table test;
+-------------+----------+----------+----------+
| Table       | Op       | Msg_type | Msg_text |
+-------------+----------+----------+----------+
| dbtuna.test | optimize | status   | OK       |
+-------------+----------+----------+----------+
1 row in set (0.00 sec)

N.B. The size of the data file on disk is now 13,931 KB i.e. 945kb larger. This is accounted for by the fact that a datetime column occupies 8 bytes whereas the timestamp column is 4 bytes in size.

This statement was again executed 100 times against the database in order to get a nice rounded average response time, this time the average response time was 0.20 seconds. i.e. 0.11 seconds per execution faster, or in percentage terms 55% faster!

So the conclusion is this... It would appear that for queries which involve date range scans then the datetime column is much much faster.

We also have a DBTuna screenshot which displays the cumulative times for the queries side by side. The first bar shows the time for 100 executions against the timestamp column, the second against the datetime column.

If you'd like find out about the performance of your MySQL Servers 24x7 why not trial dbtuna now? Download Here

db file sequential read

This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read.

Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache. These waits would also show up as 'db file sequential read'.

For further information on db file sequential read you can check the following V$SESSION_WAIT parameter columns:

P1 - The absolute file number
P2 - The block being read
P3 - The number of blocks (should be 1)

The dbtuna Wait State report will display a profile of Oracle wait events over time, and will allow you to quickly spot excessive waits.

If you would like to find out more about the performance of your Oracle instances, then why not download DBTuna today! Or arrange a web meeting at a convenient time via our contact page.

PAGEIOLATCH_EX

PAGEIOLATCH_EX is a wait event often seen in SQL Server related to I/O, It actually corresponds to an "Exclusive I/O page latch".

This wait event occurs when a user needs a page that is not in the buffer cache, SQL Server has to first allocate a buffer page, and then puts a exclusive PAGEIOLATCH_EX latch on the buffer while the page is transferred from disk to cache. After the write to cache finishes, the PAGEIOLATCH_EX latch is released.

N.B. Waits of this type are to be expected whenever SQL Server carries out I/O operations, but if you see excessive waits of this type then it may indicate problems with the disk subsystem.

DBTuna screenshot displaying an instance with PAGEIOLATCH_EX waits.

If you would like to find out more about the performance of your SQL Server instances then why not download DBTuna today! Or arrange a web meeting at a convenient time via our contact page.

Oracle Monitoring Tools

An Introduction to DBTuna for Oracle

Now that the DBTuna performance management solution has been extended to cover Oracle 8i, 9i, 10g and 11g database we have great coverage of the most popular and widely used database technologies of today.

DBTuna for Oracle uses exactly the same resource consumption and wait state approach to monitoring as the MySQL and SQL Server versions. Tiny snapshots are taken sub-second which builds up a complete picture of what is happening on your Oracle instance. Within minutes DBTuna can capture and display which users, programs, machines, modules and sessions are active within the instance, also which schema they are active in, and most importantly what SQL they are executing.

DBTuna is designed to monitor busy production databases 24x7 without impacting on the performance of the instance. It uses agentless technology which allows it to monitor remotely, meaning that there is no agent consuming resource on the database machine. Being agentless, it also means that the installation is rapid; all you need to do is provide an Oracle user and network connectivity to the instance, nothing else.

The depth of data collected from DBTuna is comprehensive, and allows detailed drilldown. The Database Administrator can view the resource consumption profile of his Oracle instance, drill into a performance spike and then find the underlying root cause in seconds.

DBTuna maintains a repository of historical performance data. This gives the DBA the ability not only to see what is happening now, but what has happened over the last day, the last week or the last month. Having historical performance data greatly facilitates problem resolution and allows the DBA to answer important questions such as: i) What happened to the online application yesterday to make it slow down ii) Why is the overnight batch job still running this morning at 8:55 etc.

DBTuna is available to try now! Click on the download link to start your evaluation process.

The world's leading betting exchange, Betfair, has selected DBTuna to help guarantee Oracle, SQL Server and MySQL database performance in production, performance testing and development environments

Read the Betfair DBTuna Case Study Here

enq: TX - allocate ITL entry

Enqueues are locks that coordinate access to database resources. This event indicates that the session is waiting for a lock that is held by another session.

The name of the enqueue is included as part of the wait event name, in the form enq: enqueue_type - related_details. In some cases, the same enqueue type can be held for different purposes, such as the following related TX types:

enq: TX - allocate ITL entry

Waits for TX in mode 4 can occur if the session is waiting for an ITL (interested transaction list) slot in a block. This happens when the session wants to lock a row in the block but one or more other sessions have rows locked in the same block, and there is no free ITL slot in the block. Usually, Oracle dynamically adds another ITL slot. This may not be possible if there is insufficient free space in the block to add an ITL. If so, the session waits for a slot with a TX enqueue in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - allocate ITL entry.

The solution is to increase the number of ITLs available, either by changing the INITRANS or MAXTRANS for the table (either by using an ALTER statement, or by re-creating the table with the higher values).

Here is an example screenshot from DBTuna which visualizes a serious problem occuring:

To monitor the performance of Oracle (and SQL Server and MySQL) try DBTuna today! Click here to register for a free download.

query_cache_size

What are the Query Cache Key Performance Indicators?

* See previous DBTuna.com article for a basic description of the MySQL Query Cache   http://www.dbtuna.com/article.asp?id=29 

This article addresses what metrics to look for when assessing the benefits of your query cache.

1. Current Size compared with maximum available size. To calculate the percentage used value for the query cache you can use the following formula:

((query_cache_size-Qcache_free_memory)/query_cache_size)*100

N.B. query_cache_size is a variable, which can be found from a show variables like 'query_cache_size'; command. Qcache_free_memory is a status variable which can be retrieved from show status like 'Qcache_free_memory';

2. The Query Cache Hit Rate

The percentage hit rate on the cache can be calculated as follows:

((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100)

This percentage figure shows how much the query cache is used e.g. the figure in the screenshot of 33% says that of all select statements executed, 33% of them can be satisfied by the cache and hence do not have to be re-executed.

3. Hits to Insert Ratio and Insert to Prune Ratio

These two ratios are calculated by the following two formulae:

Qcache_hits/Qcache_inserts

Qcache_inserts/Qcache_prunes

A ratio of Hits to Inserts is displayed in order to show the Query Cache effectiveness. A high ratio of hits to inserts tells us that there are lots of identical SQL statements being run on the database and are therefore being serviced directly from cache. A low ratio (as indicated in the sceenshot above) shows that the cache is not much utilized.

The ratio of Inserts to Prunes represents how many times SQL queries are being inserted into the cache compared with how many times a query is being removed from the cache (pruned). This is also a good indicator of SQL reuse on the database and hence query cache effectiveness.

To find out your query cache effectiveness, and to monitor your MySQL Servers 24x7 why not trial dbtuna now? Download Here

Database Monitoring Solutions

What to look for....

There is a plethora of database monitoring software products available in today's market place, so choosing the right one is often time consuming. Another difficulty is actually getting to the bottom of what each product can do, as it's often the case that the product website does not contain anywhere near enough technical information to make the decision easy! To be honest, I can sympathize with this to some extent, working for a vendor myself, as afterall we want to show people enough information to hook them in and then hopefully get them to try out the software as part of a proof of concept, or trial evaluation.

After working in the database performance management field for a good number of years (getting on for 8 years at last count), I've been in a great position to see what works and what doesn't. One of the observations I've made over the years is that many traditional tools and methodologies of looking at database performance don't work. In fact lots of companies have wasted a lot of time looking at the wrong statistics! and even more have wasted a lot of money looking at the wrong statistics!

Here is a list of my top 5 Key Features for a database monitoring tool

1. Low Overhead - the monitoring software must be production safe and not impact in any way on the workload of the databse
2. Detailed Data Collection - I guess this point is fairly obvious, but unless your monitoring software provides the level of detail required to locate the root cause of performance issues then it's fairly useless! The type of performance data that is of most use is in terms of resource consumption and wait events e.g. how much CPU a program has used during a specific time period, or which wait events were most prevalent during a problematic period. Other information of great use is statistics correlated to the SQL Statements themselves, e.g. how many buffer gets a statement does over time or per execution.
3. Current and Historical Data - many products give you a snapshot of what is happening now. Other products give you historical information, but with a lag. The best product will show you current performance and also a detailed history of performance, facilitating diagnostics after the event, and also giving you the ability to trend performance over time.
4. Low Total Cost of Ownership (TCO) - many performance monitoring products are a complete nightmare to implement! Some are so big they come on multiple DVD's, and some are so difficult to install that you need to spend lots of money implementing and maintaining.
5. Common GUI for all Database Types - most companies these days have more than one enterprise database platform. Of course some will standardize on one type e.g. Oracle, it's more than likely that they have at least a couple.

To find out more about DBTuna, or download now.

The SQL Server Execution Plan

If you want to analyse why your queries or stored procedures are performing poorly, the first place to look is the execution plan. You can visualise the plan in sql server query analyzer, or other products, and of course in DBTuna.

From within Query Analyzer you have the option of seeing the Estimated Execution Plan or the Actual Execution Plan. If you want to see an estimation of how SQL Server will execute your query you can simply select the "Display Estimated Execution Plan" button on the toolbar, this will show you the plan without actually executing the query (which should be very similar if not identical to the real plan anyway). For long running queries, or those involving DML i.e. inserts/updates/deletes etc. this is the best option. The downside to the Estimate Plan is if you have a stored procedure, or other batch Transact-SQL code that uses temp tables, you cannot use the "Display Estimated Execution Plan". The reason for this is that the Optimizer, which is what is used to generate the estimated plan, doesn't execute the T-SQL, so since the query has not been executed the temporary table does not exist. An error message of this type will be displayed if you do try to display an estimated plan for a query containing temp tables:

Msg 208, Level 16, State 1, Line 107

Invalid object name '#temp_table'

What to look for in the Execution Plan?

If you have a poorly performing piece of T-SQL that you are trying to tune, the obvious place to start it to look at the most costly step of the plan. The Screenshot below (taken from DBTuna) displays the plan for a stored procedure in the MSPetShop database. We can see the step of the plan with the greatest cost, and therefore the step which we can try and optimize. Tuning T-SQL is of course a vast topic, but a couple of things to look out for could include:

*Index or table scans: May indicate a need for better or additional indexes.
*Bookmark Lookups: Consider changing the current clustered index, consider using a covering index, limit the number of columns in the SELECT statement.
*Filter: Remove any functions in the WHERE clause, don't include views in your Transact-SQL code, may need additional indexes.
*Sort: Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently?

(*taken from sql-server-performance.com)

MySQL Monitor

Monitoring MySQL Performance with DBTuna

DBTuna is a MySQL monitor which gives a database administrator (DBA) the ability to monitor what is happening, in real time and historically, within the MySQL server.

DBTuna is an essential tool for any IT organisation using MySQL; it allows the DBA to work re-actively to resolve current/past mysql performance issues, and potentially more importantly to work pro-actively by giving visibility into the user activity of the database which allows the team to prioritize and focus on the needs of the business.

Today's web-based applications are getting more and more complex; and that multi-tiered complexity often leads to a performance challenge. Research shows that when performance issues occur in one of today's typical multi-tier web applications, it can often take many man hours of effort to locate the root cause of the issue. DBTuna is one tool which can dramatically reduce the time needed to find the issue, by correlating historical database activity and focussing on the problem. Reducing the mean time to resolution (MTTR) is the key value proposition of DBTuna.

Download DBTuna for MySQL Today!

"Anyone who is using MySQL in a production environment should invest in DBTuna."

Download the latest DBTuna customer case study to find out how Splut.com benefitted from using DBTuna

Download Splut Case Study

sp_who and sp_who2

Use sp_who or sp_who2 (sp_who2 is not documented in the SQL Server Books Online, but offers more details than sp_who) to provide locking and performance-related information about current connections to SQL Server. Sometimes, when SQL Server is very busy, you can't use Enterprise Manager or Management Studio to view current connection activity via the GUI, but you can always use these two commands from Query Analyzer or Management Studio, even when SQL Server is very busy.

DBTuna offers a detailed current activity screen which uses information from the sp_who2 command. It also displays a graphical view of SQL Server CPU consumption and Wait Events for the last 5 minutes and a snapshot of current memory usage broken down by category.

What are 'read by other session' waits in Oracle?

This is a new wait event in Oracle 10.1

Definition: When information is requested from the database, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same information, the first session will read the data into the buffer cache while other sessions wait. In previous versions this wait was classified under the "buffer busy waits" event. However, in Oracle 10.1 and higher this wait time is now broken out into the "read by other session" wait event. Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.

Find out all session wait events with DBTuna for Oracle. Click here to download your evaluation copy today.

STA Travel DBTuna Case Study

Read how STA Travel have used DBTuna and dynaTrace to help improve Oracle and Java performance during testing - which was critical to ensuring that the vital Globe Project could go live prior to Christmas 2008

Download the STA Travel Case Study now (pdf Format)

MySQL Query Cache Performance

The MySQL Query Cache is a powerful feature which when used correctly can give big performance gains on your MySQL instance. The Query Cache works by storing in memory the SQL query (or Stored Procedure) along with the retrieved results. Whilst in cache, if the SQL query is called again, MySQL does not have to rerun it, it can quickly retrieve the results from the cache and send these back. Of course if there has been an insert, update or delete on a table which is referenced by the cached query then the statement will be forced to rerun. Whilst the Query Cache is great for applications with a high number of reads versus writes, there are also a couple of reasons which will make your queries un-cacheable.

1. Use of functions, such as CURRENT_DATE, RAND and user defined functions
2. Queries that uses bind variables

 DBTuna for MySQL will show the Key Performance Indicators of the MySQL Query Cache in graphical format.

• Graphical View of current allocation compared to maximum
• Percentage Utilization figures
• A ratio of Hits to Inserts is displayed in order to show the Query Cache effectiveness. A high ratio of hits to inserts tells us that there are lots of identical SQL statements being run on the database and are therefore being serviced directly from cache. A low ratio shows that the cache is not much utilized.
• The ratio of Inserts to Prunes represents how many times SQL queries are being inserted into the cache compared with how many times a query is being removed from the cache (pruned). This is also a good indicator of SQL reuse on the database and hence query cache effectiveness.

N.B. The maximum size (in MB) of the Query Cache is set in the database variable query_cache_size. A setting of 0 will automatically disable the cache.

To monitor all aspects of MySQL performance, why not download a copy of DBTuna today? Click here to download the MySQL Monitor

SQL Monitor

Monitor the SQL, and the rest will become apparent!

In database performance management, the monitoring of SQL statements is of primary importance. If you pay attention to what SQL is consuming resource, waiting for I/O, or waiting in internal database states, then you can completely understand how your database is performing, and if, and when, problems occur.

It is the SQL that is a direct indicator of the response time of the end-user, program, or batch process. If you know the response time for the SQL is bad, then it is obvious that you have a problem further upstream, which the user will no doubt notice. Similarly, if SQL response times are fast, then do not waste time trying to tune! Many DBA's make the mistake that they use "traditional" indicators of performance such as cache hit ratio's to determine when to tune, but it's often the case that you can have a poor hit ratio (or similar metric) but the application can be running fine.

Only tune when the needs of the application dictate that you have to tune!

The DBTuna database performance management solution monitors SQL resource consumption 24x7 for Oracle, SQL Server and MySQL.

Why not download a copy today?

The MySQL Optimize Table Command

The MySQL Optimize Table command will effectively de-fragment a mysql table and is very useful for tables which are frequently updated and/or deleted.

Example:

We have a table called articles which has many thousands of rows which are often inserted, updated and deleted. We can see from the table description below that the table contains variable length column data types:

mysql> desc articles;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| content        | text         | NO   |     | NULL    |                |
| author_id      | int(11)      | YES  |     | NULL    |                |
| article_title  | varchar(120) | YES  |     | NULL    |                |
| article_hash   | int(11)      | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

If we look at the size of the table on disk we can see that it around 190MB. If we query the table on a column which is indexes we can see the average query response time:

e.g.

mysql> select count(*) from articles where article_title like 'The%';
+----------+
| count(*) |
+----------+
|    15830 |
+----------+
1 row in set (0.63 sec)

If we now optimize the table with the following command:

mysql> optimize table articles;
+-----------------------+----------+----------+----------+
| Table                 | Op       | Msg_type | Msg_text |
+-----------------------+----------+----------+----------+
| books.articles        | optimize | status   | OK       |
+-----------------------+----------+----------+----------+
1 row in set (6.27 sec)

This has the effect of defragmenting the table and reducing the size of the table on disk down to 105MB. It also has a very positive affect on query performance, reducing the select query response time from 0.63 to 0.39 seconds. N.B. the mysql query cache was turned off to demonstrate.

mysql> select count(*) from articles where article_title like 'The%';
+----------+
| count(*) |
+----------+
|    15830 |
+----------+
1 row in set (0.39 sec)

To monitor the performance of MySQL (and Oracle and SQL Server) try DBTuna today! Click here to register for a free download.

Web Application Monitoring

WebTuna - Web Performance Analytics

WebTuna is a scalable solution that provides performance monitoring and metrics for all kinds of websites, internet applications and services.

WebTuna now has a performance histogram view which allows you to visualize web performance in a very powerful way. For each collected entity, be that User, Country, URL, Page Title etc. you can view all the response times as a histogram distribution.

WebTuna lets you know exactly how each page on your website is performing and for each individual user. It can also be set up to measure your performance against a pre-defined Service Level Agreement (SLA). We can provide long term reporting to inform you of historical trends on your site performance or usage; and can provide pro-active email alerting to let you know immediately when there are current performance or availability problems on your site.

The WebTuna web application monitoring solution is available as a fully hosted service, so please contact us for more information.

WebTuna in the News!

Read the Bloor Research article on WebTuna Performance Analytics.

http://www.it-director.com/technology/applications/content.php?cid=10587

If you're interested in what WebTuna can do for your web application, then contact us to arrange a demo and/or trial.

How to monitor SQL Server Performance?

SQL Server has much improved over the years in allowing the Database Administrator (DBA) access to detailed performance information. In Service Pack 3 of SQL Server 2000 a new function was introduced called fn_get_sql which exposed information about which SQL text is executing at any one time on the database. This information, along with information gathered from sysprocesses and other internal SQL Performance tables allows the DBA to gather detailed information about database performance bottlenecks. Further information is exposed in 2005 and later in 2008 which allows even more detailed performance data to be monitored.

DBTuna for SQL Server is a tool which takes advantage of these database features in order to monitor SQL server 24x7 and give the DBA visibility into which are his slowest or biggest resource consuming queries and fundementally what is the root cause of any database slow downs.

Information on DBTuna Features are available here

How to Check, Repair and Optimize MySQL Tables

The Repair Table command can be used to repair a corrupted MyISAM table.

http://dev.mysql.com/doc/refman/5.0/en/repair-table.html

Here is a simple command to auto repair, check and optimize all the tables in all databases running on a MySQL server:

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a quick and easy way. It determines which statements to use for the operation you want to perform, and then sends the statements to the server to be executed. The exact operations are determined by the underlying storage engine used.

You must execute it on a running database. It is recommended that you kill other incoming queries (like from your website) before running this which makes it a lot faster.

SQL Server Lock Wait Types

There are many different types of lock within SQL Server. The table below gives a short description of each:

Wait Type Description LCK_M_SCH_S Schema stability LCK_M_SCH_M Schema modification LCK_M_S Share LCK_M_U Update LCK_M_X Exclusive LCK_M_IS Intent-Share LCK_M_IU Intent-Update LCK_M_IX Intent-Exclusive LCK_M_SIU Shared intent to update LCK_M_SIX Share-Intent-Exclusive LCK_M_UIX Update-Intent-Exclusive LCK_M_BU Bulk Update LCK_M_RS_S Range-share-share LCK_M_RS_U Range-share-Update LCK_M_RI_NL Range-Insert-NULL LCK_M_RI_S Range-Insert-Shared LCK_M_RI_U Range-Insert-Update LCK_M_RI_X Range-Insert-Exclusive LCK_M_RX_S Range-exclusive-Shared LCK_M_RX_U Range-exclusive-update LCK_M_RX_X Range-exclusive-exclusive

DBTuna will monitor all time spent in any type of lock, so if you see excessive wait times on any of these lock types for a particular stored procedure or statement, then you need to investigate the blocking session.

Proactively Monitor Oracle performance with DBTuna

With hundreds of configuration parameters and thousands of metrics to monitor, it’s no small task for Oracle DBA's to monitor the overall health of their Oracle databases.

DBTuna takes a time-based approach to monitoring, allowing you to examine the performance of SQL statements over time and where that time was spent, for example, fetching, sorting or waiting on a lock. The statements are then ranked with the worst performing at the top. Data is also by Client, session, user, database, Program, Module and host, allowing you to quickly drill up and down. Once you have identified a problematic statement you can click on the text and it will take you to the SQL screen where you can examine the execution plan.

Other screens within DBTuna allow you to see the performance of sessions currently connected, view database statistics and browse database objects.

Using DBTuna's approach to monitoring you only need to address problems when you see they are affecting your application SQL i.e. if your SQL is running fast why bother focussing on configuration parameters and metrics??

With WebTuna and DBTuna you can get an End to End view of your application's web performance

If you combine the performance data of WebTuna and DBTuna you can start to build up a picture of how your web application is performing end-to-end. WebTuna collects response time measurements for each web page for each end user, whilst DBTuna collects complete service time for the database tier. If you merge the two you can quickly see how much time is spent for the whole transaction, but most important from a diagnosis point of view, you can see how much time is spent in each tier.

For more information on WebTuna & DBTuna please contact us

WebTuna Coming Soon!

WebTuna is a brand new product to the DBTuna portfolio with an aim to monitor Web Application performance from the real users perspective. It uses Page tagging technology to capture web page response times, and then sends detailed information back to the WebTuna server for archiving and analysis. WebTuna will capture and correlate the following information:

• Client Name
  
• Client IP Address
  
• Connection Type (modem or broadband)
  
• Country of origin of the user request
  
• Domain
  
• Location (a custom grouping of client IP addresses)
  
• Protocol (HTTP or HTTPS)
  
• Search Criteria (parameter capture of URL request)
  
• Page URL
  
• Page Title
  
• Page referrer

To request further information or to get an early view of WebTuna contact us

For more information on what Web Analytics is, visit the Wikipedia page below:

http://en.wikipedia.org/wiki/Web_analytics

DBTuna: a Light Hearted Introduction!

http://www.youtube.com/watch?v=BzpCZZs2FIE

DBTuna Recommended Architecture

DBTuna is intended to run on a centralised server running either Solaris, Linux or Windows. The network requirements for this server are that it has connectivity for the DBTuna agents to be able to connect to the monitored database servers and that the Database Administrator (DBA) users will be able to connect to the DBTuna GUI.

Changing the DBTuna GUI Port

The DBTuna Web GUI will listen on port 8090 by default. If for some reason you need to modify this e.g. if you have a firewall between your client PC and the machine where you have installed DBTuna and are unable to open port 8090. Then you need to do the following:

Monitor Oracle with DBTuna for Oracle

Now that the DBTuna performance management solution has been extended to cover Oracle 8i, 9i, 10g and 11g database we have great coverage of the most popular and widely used database technologies of today.

DBTuna for Oracle uses exactly the same resource consumption and wait state approach to monitoring as the MySQL and SQL Server versions. Tiny snapshots are taken sub-second which builds up a complete picture of what is happening on your Oracle instance. Within minutes DBTuna can capture and display which users, programs, machines, modules and sessions are active within the instance, also which schema they are active in, and most importantly what SQL they are executing.

DBTuna is designed to monitor busy production databases 24x7 without impacting on the performance of the instance. It uses agentless technology which allows it to monitor remotely, meaning that there is no agent consuming resource on the database machine. Being agentless, it also means that the installation is rapid; all you need to do is provide an Oracle user and network connectivity to the instance, nothing else.

The depth of data collected from DBTuna is comprehensive, and allows detailed drilldown. An expert DBA can view the resource consumption profile of his Oracle instance, drill into a performance spike and then find the underlying root cause in seconds.

DBTuna maintains a repository of historical performance data. This gives the DBA the ability not only to see what is happening now, but what has happened over the last day, the last week or the last month. Having historical performance data greatly facilitates problem resolution and allows the DBA to answer important questions such as: i) What happened to the online application yesterday to make it slow down ii) Why is the overnight batch job still running this morning at 8:55 etc.

DBTuna is available to try now! Click on the download link to start your evaluation process.

DBTuna for SQL Server Requirements

DBTuna for SQL Server will monitor any SQL Server database later in version than SQL Server 2000 service pack 3.

Requirements for the DBTuna monitoring user:

SQL Server Authentication: If your SQL Server database allows mixed-mode authentication, then the DBTuna user can use a SQL Server username/password authenticated account, or you can use the default sa account. If you create a new account, then make sure it has access to all databases (so that you can run explain plans, and browse objects etc.)

Windows Authentication: If you would like to use a Windows authenticated account to connect to the SQL Server database, the following is required:

• When creating the agent in agent manager, do not specify a username and password.
• Configure the DBTuna agent windows service to log on as the desired windows account with SQL Server access. Go to the Windows Services GUI from Control Panel. Select the DBTuna Agent service and right click to display the properties. Click on "Log On" and then enter the credentials for the user.
• Configure the DBTuna GUI windows service to log on as the desired windows account with SQL Server access. Go to the Windows Services GUI from Control Panel. Select the DBTuna GUI service and right click to display the properties. Click on "Log On" and then enter the credentials for the user.

N.B. If you are running your DBTuna software on Linux or Solaris, then you must use SQL Server authentication.

Installation of DBTuna on Linux

N.B. DBTuna cannot run as the root user!

The reason DBTuna cannot run as root is because it contains mysql which it uses as it's repository for performance data; and mysql itself cannot run as root. If for some reason you do run the dbtuna processes as root you may see the following problems:

[root@localhost dbtuna]# ./startDBTuna.sh
DBTuna Starting.....
[root@localhost dbtuna]# ./showStatus.sh

---------- DBTuna Status ----------
-----------------------------------
DBTuna Web GUI             RUNNING
DBTuna Repository          STOPPED

Although there is no error message when you run the startDBTuna.sh script; when you run the showStatus.sh script to verify the status of the processes the DBTuna repository is displayed as "STOPPED".

If you try running a collector agent when the DBTuna Repository is stopped then you will encounter a problem such as the following:

[root@dms1 agent]# ./startOracleAgent.sh dms2
Now tailing Agent start log for dms2
control-c to quit viewing the log
Cannot connect to DBTuna repository.java.sql.SQLException: Server connection failure during transaction. Due to underlying exception: 'java.net.SocketException: java.net.ConnectException: Connection refused'.

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: java.net.ConnectException: Connection refused

STACKTRACE:

java.net.SocketException: java.net.ConnectException: Connection refused
        at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:156)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:284)
        at com.mysql.jdbc.Connection.createNewIO(Connection.java:2680)
        at com.mysql.jdbc.Connection.<init>(Connection.java:1485)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
        at java.sql.DriverManager.getConnection(DriverManager.java:525)
        at java.sql.DriverManager.getConnection(DriverManager.java:171)
        at com.dbtuna.oracle.oracle_Collector.main(oracle_Collector.java:112)

** END NESTED EXCEPTION **

Attempted reconnect 3 times. Giving up.

This error message and stack trace is showing that the collector agent cannot connect to the DBTuna repository, and therefore stopped albeit ungracefully!

Looking at the MySQL Explain Plan

The MySQL explain plan shows how the MySQL optimizer has decided to run a SELECT statement and access the data. N.B. MySQL can only explain SELECT statements.

The command syntax for viewing the EXPLAIN output is:

explain [select statement]

e.g. explain select count(*) from statistics where id = 5

Here we can see the DBTuna screen displaying the explain output for this command:

The columns in the Explain plan table are:

• id The SELECT identifier. This is the sequential number of the SELECT within the query.
  
• select_type The type of SELECT, which can be any of those shown in the following table: 
  
  

  SIMPLE	Simple SELECT (not using UNION or subqueries)
  PRIMARY	Outermost SELECT
  UNION	Second or later SELECT statement in a UNION
  DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query
  UNION RESULT	Result of a UNION.
  SUBQUERY	First SELECT in subquery
  DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query
  DERIVED	Derived table SELECT (subquery in FROM clause)

• table The table to which the row of output refers.
• type The join type
• possible_keysThe possible_keys column indicates which indexes MySQL can choose from use to find the rows in this table.
• KeyThe key column indicates the key (index) that MySQL actually decided to use. The key is NULL if no index was chosen. To force MySQL to use or ignore an index listed in the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in your query
• key_lenThe key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key column says NULL
• refThe ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.
• rowsThe rows column indicates the number of rows MySQL believes it must examine to execute the query.
• Extra This column contains additional information about how MySQL resolves the query
  

MySQL Too Many Connections

If you get a "Too many connections" error when you try to connect to the mysqld server, this means that all available connections are in use by database clients.

The number of connections allowed is controlled by the max_connections system variable. Its default value is 100. If you need to support more connections, you should set a larger value for this variable.

mysqld actually allows max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the SUPER privilege. Your DBTuna user should have SUPER privilege, which means that the current screen within DBTuna will highlight the issue with Too many connections (as displayed in the screenshot above).