DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Header Right

performance

10 Database Performance Monitoring Tools You Can Get For Free

by Leave a Comment

Database performance monitoring is something every DBA worth their salt should be doing on a regular basis.

It should be adopted as a proactive task to help identify issues early on before they become too serious and be part of a post code deployment monitoring process.

Bundled in with linux based operating systems are a heap of great tools that you can use as a DBA to help performance monitor your database server. If you are not happy with what you get “out of the box”, you can also find some great database monitoring tools online that are available to download for free.

For this post, I’m going to talk about both MySQL and Linux operating system performance monitoring tools. In many scenarios, you’ll need both types in order to get a complete understanding of where the delays are in your system.

MySQL Performance Monitoring Tools

1/ MySQL slow query log
The mysql slow query log is absolutely brilliant for capturing slow queries hitting your MySQL databases.

You can log queries whose durations match the number you specify in my.cnf. So you can analyze queries which take more than 3 seconds for example.

Activate in my.cnf with customizable settings for log location, long query time and whether to log queries that do not use any indexes.

#slow query logging
slow-query-log = 1
slow-query-log-file = /var/log/mysql/slow-log
long-query-time = 3
log-queries-not-using-indexes = 0

Once you have been logging for a while you can aggregate the results with the mysqldumpslow utility,  optimize them and then monitor for improvements! 🙂

2/ MySQL Performance Schema
Introduced in version 5.5, the performance_schema database provides a way of querying internal execution of the server at run-time.

To enable add “performance_schema” to my.cnf

There are many objects to query, too many to talk about in this post. Check out the documentation here.

3/ The MySQL process list

To get an idea of how many processes are connected to your MySQL instance, what they are running and for how long, you can run SHOW FULL PROCESSLIST or alternatively read from the information_schema.processlist table.

mysql> SELECT user, host, time, info FROM information_schema.processlist;
+-------------+------------+-------+-------------------------------------------------------------------+
| user        | host       | time  | info                                                              |
+-------------+------------+-------+-------------------------------------------------------------------+
| root        | localhost  |     0 | SELECT user, host, time, info FROM information_schema.processlist |
| replication | srv1:46892 | 11843 | NULL                                                              |
+-------------+------------+-------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

4/ mtop
I love this utility, it provides a real-time view of the MySQL process list and updates according to the number of seconds your specify when you run it.

What I really like about it is that you can have it running on one screen and as problems occur, the colours of the threads change colour with red indicating that something has been running for some time.

There is a great article here about how to install it on different flavours of Linux as well as some detail on how to run it.

5/ SHOW STATUS
Like other command line tools, such as SHOW PROCESSLIST, you run these to get moment in time reports on different variable status’s.

For example, if you want to get information about the query cache, you can run :

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 9353       |
| Qcache_free_memory      | 93069936   |
| Qcache_hits             | 9719103977 |
| Qcache_inserts          | 1451857238 |
| Qcache_lowmem_prunes    | 897050960  |
| Qcache_not_cached       | 222234089  |
| Qcache_queries_in_cache | 20856      |
| Qcache_total_blocks     | 52497      |
+-------------------------+------------+
8 rows in set (0.00 sec)

This type of reporting can help you monitor specific areas of your MySQL instance. For example, if you wanted to know the query cache hit rate, you could get the numbers from above and calculate based on this formula:

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

For more information, see this link.

Operating System Performance Monitoring Tools

6/ TOP
This will list running processes and the resources that they are consuming. It updates real-time and you can quickly gage if there are processes which are consuming large areas of resource in CPU and memory at a very high level.

top - 17:33:48 up 7 min,  1 user,  load average: 0.03, 0.04, 0.04
Tasks:  64 total,   1 running,  63 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:    604332k total,   379280k used,   225052k free,    11724k buffers
Swap:        0k total,        0k used,        0k free,   135064k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
  809 tomcat7   20   0 1407m 149m  13m S  0.3 25.4   0:10.99 java
 1153 ubuntu    20   0 81960 1592  756 S  0.3  0.3   0:00.01 sshd
 1318 root      20   0 17320 1256  972 R  0.3  0.2   0:00.07 top
    1 root      20   0 24340 2284 1344 S  0.0  0.4   0:00.39 init
    2 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kthreadd
    3 root      20   0     0    0    0 S  0.0  0.0   0:00.03 ksoftirqd/0
    4 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kworker/0:0
    5 root      20   0     0    0    0 S  0.0  0.0   0:00.01 kworker/u:0

7/ free
This utility helps to give you an idea whether you have a memory issue. Again this is another great tool for getting a high level view. I like to use “free -m” as it returns the numbers to me in megabytes instead of bytes. The information returned shows you in use, free and swap usage. It also shows what is in use by the kernel and buffers.

[email protected]:~# free -m
             total       used       free     shared    buffers     cached
Mem:           590        373        216          0         11        131
-/+ buffers/cache:        229        360
Swap:            0          0          0

8/ vmstat
This utility is very useful for monitoring many areas of the system, CPU, IO blocks and swap. I find it particularly good to monitor swap file usage.

Whilst “free” might tell you if there are any pages in the swap file, vmstat will tell you if your system is actively swapping.  Computers and servers do need to use their swap file but the less this happens, the better it is for your applications performance.

When you have a problem with swap, it is when it is being used constantly and can be a sign that you don’t have enough memory installed in your system.

By default, running vmstat will not give you a real time view of your system. So you need to add a figure to the command to give you a fresh read out in the number of seconds specified. In this example, I am specifying every 2 seconds.

[email protected]:~# vmstat 2
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 0  0      0 221324  12556 135252    0    0    93    19   40   75  1  0 98  0
 0  0      0 221324  12556 135276    0    0     0     0   34   65  0  0 100  0
 0  0      0 221324  12564 135280    0    0     0    24   38   64  0  0 100  0
 0  0      0 221324  12564 135280    0    0     0     0   32   56  0  0 100  0
 0  0      0 221324  12564 135280    0    0     0     0   33   56  0  0 100  0
 0  0      0 221324  12564 135280    0    0     0     0   30   55  0  1 100  0
 0  0      0 221324  12564 135280    0    0     0     0   35   59  0  0 100  0

The columns you are interested in are swap si and so. Which stands for “swap in” and “swap out”. These figures tell you what is being read in from disk swap file (si) and what is being swapped out to the swap file (so). Swapping is very slow I/O intensive process and you want to be doing some optimization somewhere or adding more memory if this is a problem.

Run “man vmstat” for a full list of features and documentation.

9/ sar
I love sar! It will capture you a whole bunch of metrics based on CPU time, CPU queues, RAM, IO and network activity. It will give you a point in time view of the resource usage in the form of a historical report.

The default time between report lines is 10 minutes but you can change that. It’s great for seeing whether you have any particularly heavy areas of resource pressure at any time in the day. You can also use it as a performance monitoring tool to measure the effects of optimizations to your system.

Some examples, run “man sar” for a full list of features and documentation on what each column header means.

sar -q (check CPU queue length)

11:20:01 AM   runq-sz  plist-sz   ldavg-1   ldavg-5  ldavg-15
11:30:01 AM         1       201      0.00      0.00      0.00
11:40:01 AM         1       200      0.00      0.00      0.00
11:50:01 AM         1       201      0.00      0.00      0.00
12:00:01 PM         2       201      0.00      0.00      0.00

sar -r (check RAM usage)

11:20:01 AM kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit   %commit
11:30:01 AM    151308   3765480     96.14     91416   1054136   2961684     49.25
11:40:01 AM    151076   3765712     96.14     91664   1054136   2961012     49.24
11:50:01 AM    150680   3766108     96.15     91888   1054148   2961152     49.24
12:00:01 PM    150704   3766084     96.15     92104   1054152   2961340     49.24

10/ iostat
This tool will you give you statistics for CPU and I/O for devices, partitions and network file systems. Great for knowing where the busiest drives are for example.

[email protected] ~# iostat
Linux 2.6.32-431.11.2.el6.x86_64 (vm1)        06/27/2014      _x86_64_        (4 CPU)
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.23    0.00    0.07    0.10    0.00   99.60Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda              11.78       785.38       450.12 1437054564  823620760
dm-0              1.00         1.35         6.67    2472280   12211040
dm-1             64.52       783.30       441.42 1433252442  807699512
dm-2              0.00         0.00         0.02       7658      29336
dm-3              0.27         0.53         2.01     978626    3680440

Finally

So there you have it – 10 really useful tools which you can utilize in your database performance monitoring efforts. There are many more but I’ve run out of time now. 🙂

When are Innodb Table Statistics Updated?

by Leave a Comment

Innodb statistics are used by the query optimizer to assist it in choosing an efficient query execution plan. They are estimated values relating to each Innodb table and index.

But what updates them? Let’s take a look.

Operations that update Innodb table statistics

Typically this happens during metadata statements such as SHOW INDEX or SHOW TABLE STATUS.

It also occurs when querying INFORMATION_SCHEMA tables such as TABLES and STATISTICS.

This is default behaviour however a variable was introduced in version 5.5.4 which allowed the administrator to override this. The variable name is innodb_stats_on_metadata.

When it is turned off, Innnodb statistics are not updated during those operations. For schemas that have a large number of tables or indexes, this can have a positive effect on access speeds. It can also improve the stability of execution plans that involve Innodb tables.

If it is turned off the alternative is to run ANALYZE TABLE which is a similar operation to those mentioned involving INFORMATION_SCHEMA etc. It will update the statistics but place a read lock on the table during the process.

Using DBCC FREEPROCCACHE to Clear SQL Server Procedure Cache

by Leave a Comment

It is possible to clear out the entire SQL Server procedure cache using DBCC FREEPROCCACHE

The procedure cache is where SQL Server will cache execution plans after they have been compiled. The benefit of this caching is that there is no need for the execution plans to be compiled at run time. This compiling operation typically consumes resource and slows down the execution time of the query.

The obvious disadvantage of clearing out the plan cache is that all execution plans for your SQL Server instance are recompiled upon execution which may slow things down temporarily until the cache is re-populated. Great for development, give thought before executing in production.

You may be under pressure to quickly get performance back to normal but it’s better to laser target the offending queries if you have time and address accordingly. You can clear out the procedure cache for specific queries as we will see further down the post.

Running DBCC FREEPROCCACHE


DBCC FREEPROCCACHE;

…and you should be presented with the following informational message:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This is optional – WITH NO_INFOMSGS and will simply suppress the informational message above.


DBCC FREEPROCCACHE WITH NO_INFOMSGS;

Upon executing, messages with be written to the SQL Server error log for each cache store in the plan cache.

Here is an example:

SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘Object Plans’ cachestore (part of plan cache) due to ‘DBCC FREEPROCCACHE’ or ‘DBCC FREESYSTEMCACHE’ operations.

Using DBCC FREEPROCCACHE to clear specific execution plans from the cache

You first need to pinpoint the identifier of the execution plan which you want to clear out of the cache. This is known as a “plan handle” and you can find the plan handles and the cached SQL by issuing a query against sys.dm_exec_cached_plans and sys.dm_exec_sql_text


SELECT cp.plan_handle, st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE st.text LIKE N'%ThePlanYouAreLookingFor%'

So here I will clear a plan from the cache, firstly by running this query to get the plan inserted to the plan cache.


SELECT TOP 1 * FROM Person.Person;

Now I will attempt to find the plan_handle for the execution plan.


SELECT cp.plan_handle, st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE st.text LIKE '%Person.Person%'

dbcc freeproccache clear sql server plan cache

So I can see that I want to clear out the cache for the second row as displayed in the screenshot.

So add the plan_handle and say bye to that query plan. 🙂 (The plan handle has been deliberately shortened in the code below otherwise my CMS has trouble displaying it 🙂 )


DBCC FREEPROCCACHE (0x060006002FE61B1D40FDAFF501000000010000....);

The OLEDB Wait Type and How to Reduce It

by Leave a Comment

reduce oledb wait type

In this post, I look at the OLEDB wait type.

Books Online lists this as

“Occurs when SQL Server calls the SQL Server Native Client OLEDB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider.”

“OLEDB” stands for Object Linking Embedding Database. OLEDB is an API used by SQL Server when retrieving data from a remote server or datasource.

Linked server queries, remote procedure calls, BULK INSERT operations, full text search and queries to external data sources, for example Excel all contribute to OLEDB wait type.

The wait type occurs when the remote system or network connection is not fast enough and so the calling server has to wait for the results to be returned.

Guidance on what to check and ideas on how to reduce OLEDB wait type

If the stats for OLEDB wait type are high then there are some things you can do:

  1. Look at your data distribution – are the linked server calls absolutely necessary?
  2. If the data being referenced is static then consider copying the data to local database and query it there.
  3. Consider using replication to bring the data local.
  4. If the data source is some file such as Excel, can you reference it locally instead of over a network connection?
  5. Is it feasible to import the Excel file into the database?
  6. Tune the remote query if you can. If it is a server which you are able to administer or you can speak with the DBA of that system, then find ways to make that query run faster – check indexes, check statistics etc.
  7. Have your network admins check over the connection between the server and data source. Is it fast enough?
  8. OLEDB may be a symptom of another wait type. For example check RESOURCE_SEMAPHORE wait type – is this high?

Reducing SQL Server CXPACKET Wait Type

by 10 Comments

cxpacket wait type parallelismIn my last post, I wrote about how SQL Server schedules tasks to be executed. It’s important to be  able to understand this when when trying to analyse wait types and statistics in SQL Server.

For this post, I will be looking at the CXPACKET wait type and what you can do to reduce it.

This is one wait type that can be misjudged.

I mentioned before that the wait types need translation – MSDN describes this as:

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.

This is a parallel processing wait type where many threads are used to satisfy the query. Each thread deals with a subset of the data. The idea is that if there are more threads working on different parts of the data set, it can be more efficient.

Parallel execution can therefore be a highly effective way of dealing with large sets of data, for example in a data warehouse/reporting server. However, in OLTP systems, it could well have a negative impact on performance.

CXPACKET wait type occurs when there are threads taking longer to execute than the other threads in that query. The completed threads are left in waiting state until the last thread can complete.

I’ll go into more detail about these settings in the next section but by default your SQL Server is configured for parallelism as the “max degree for parallelism setting” is set at zero.

For a query then to qualify for parallel execution, its estimated (or cached) cost needs to exceed the “cost threshold for parallelism” setting.

So in an example scenario on a server with 8 cores, if a parallel query executes with “max degree of parallelism” set at zero, then up to 8 cores can be used in the query. As each core completes its work, it waits until all cores have completed.

The result is CXPACKET waiting and the potential to delay other SPIDs wanting a piece of CPU time to process their request.

Assessing causes of CXPACKET wait type is vitally important

You have to remember that CXPACKET waits are normal for multiprocessor servers because of parallel execution. So simply adjusting your server configuration in an effort to reduce this wait type could have an overall negative impact on performance.

Understanding whether the system is an OLTP, DSS or combination of both is critical.

It’s no good simply assuming that because you have CXPACKET waits, that it is automatically a problem. If CXPACKET is one of the top reported wait types on your SQL Server, the next thing to consider is, what are the causes and is this wait type normal for my workload?

There may be other contributing factors. Is CXPACKET a symptom of something else? For example, it could be as simple as a missing index or out of date statistics. Your SQL Server then tries to compensate by generating a query plan that uses parallelism in an effort to optimize the query, or rather, to make the scan operation go faster.

How to reduce CXPACKET wait type by changing settings

There are some settings which can affect how your queries use parallelism.

  1. Server level – “Max Degree of Parallelism”
  2. Server level – “Cost Threshold for Parallelism”
  3. Query hint – MAXDOP

Max Degree of Parallelism – specifies the number of CPU cores to be used in parallel query execution at the server level. The default value is 0 which means that SQL Server will use all available cores. Setting this to 1 turns off parallelism. Here is an example which instructs the SQL Server to use no more than 2 cores for parallel execution:


EXEC sp_configure 'max degree of parallelism',2
GO
RECONFIGURE WITH OVERRIDE
GO

Cost Threshold for Parallelism –  the estimated elapsed time in seconds before the server will consider parallelism in the query execution. Here is an example setting this value to the estimated elapsed time of 10 seconds:


EXEC sp_configure 'cost threshold for parallelism', 10
GO
RECONFIGURE WITH OVERRIDE
GO

MAXDOP – a query hint where the number of cores is specified for the query. Here is an example using MAXDOP for 2 cores:


SELECT Column1, Column
FROM SomeTable
WHERE (Column1 > @Value1 AND Column2 < @Value2)
OPTION (MAXDOP 2)

Note that you can also change max degree of parallelism and cost threshold for parallelism settings using Management Studio. These settings can be found under the Properties->Server Properties->Advanced section.

Summary and guidance points for CXPACKET wait type and parallelism

  • OLTP – parallelism can have a negative impact on performance.
  • OLAP/reporting benefits from parallelism.
  • Look at your plan cache, which of your queries use parallelism?
  • Check query plans – do you have missing indexes or out of date statistics?
  • What other waits are occurring that might be causing CXPACKET?
  • Consider using MAXDOP to fine tune specific queries.
  • Set max degree of parallelism and cost threshold for parallelism to best suit your server’s function.
  • Do not adjust max degree of parallelism without proper analysis of your servers workload/queries.

An Overview of SQL Server Task Scheduling

by 1 Comment

In my previous post, I touched upon SQL Server wait types and in order to understand wait types in SQL Server, it’s important to know first how the server schedules tasks.

On each CPU core, only a single thread or “worker” can be running at any given time. Each CPU, be it logical or physical inside your SQL Server is assigned a scheduler and it is responsible for managing the work which is carried out by threads.

You can view the schedulers on your SQL Server by running this code:


SELECT * FROM sys.dm_os_schedulers;

Schedulers are responsible for managing user threads and internal operations. If you run that DMV, you can see these types of status in there:

  • VISIBLE ONLINE (DAC) – thread scheduler used to manage the DAC (Dedicated Administator Connection)
  • VISIBLE ONLINE – user thread schedulers
  • HIDDEN ONLINE – internal operation thread schedulers

So depending on how many CPUs you have in your SQL Server, you will see from this DMV that there can be quite a few schedulers available.

More CPUs = more schedulers = more work can get done 🙂

There are some other status’ which you might see:

  • HOT_ADDED – schedulers created in response to a CPU which was added whilst the server was online (hot added CPU)
  • VISIBLE OFFLINE – user schedulers which are mapping to CPUs which are offline in the affinity mask.
  • HIDDEN OFFLINE – schedulers assigned to internal operations mapped to CPUs which are offline in the affinity mask.

Thread status

If you’ve ever looked at your process list in SQL Server either by using sp_who2 or activity monitor, you may have noticed that there is a column in there called “Status” (activity monitor in Management Studio 2012 calls this “Task State”)

There are some status’ which help you to understand how the queries execute, they are RUNNING, SUSPENDED and RUNNABLE

RUNNING – the thread is actively running on the CPU.

SUSPENDED – thread is waiting for a resource to become available. Thread is on the “waiter list”.

RUNNABLE – resources are now available to the thread but as only one thread can execute on a CPU at any given time, the thread has to wait its turn. It goes to the bottom of the “RUNNABLE queue” and finally returns to a RUNNING state when it is its turn to run as controlled by the CPU scheduler.

It goes to the bottom of the “RUNNABLE queue”

Please note that this behaviour can change if using RESOURCE GOVERNOR (Enterprise Edition)

The size of the “RUNNABLE queue” for each scheduler can be seen by looking at the value for the “runnable_tasks_count” column from the sys.dm_os_schedulers DMV.

Threads can transition around the different states until their work is completed. When on the waiter list, a thread can wait for some time before it changes state. There is no set order in which they are then changed from SUSPENDED to RUNNABLE.

In order to view what each thread is waiting for, you can look at the sys.dm_os_waiting_tasks DMV:


SELECT * FROM sys.dm_os_waiting_tasks;

 Summary points

  • Each CPU (logical or physical) is assigned a scheduler.
  • Only one thread can be running on a CPU at any given moment.
  • Threads are either running on the CPU (RUNNING), on the waiter list (SUSPENDED) or in the runnable queue (RUNNABLE).
  • Use sys.dm_os_schedulers to view schedulers on your SQL Server.
  • Use sys.dm_os_waiting_tasks to view the tasks which are in the wait queue, waiting for resource to become available.

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close