블로그 이미지
LifeisSimple

calendar

1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30

Notice

2016. 6. 22. 09:57 Brain Trainning/DataBase



Graphing MySQL performance with Prometheus and Grafana


출처 : https://www.percona.com/blog/2016/02/29/graphing-mysql-performance-with-prometheus-and-grafana/


   | February 29, 2016 |  Posted In: MonitoringMySQLPrometheus

This post explains how you can quickly start using such trending tools as Prometheus and Grafana for monitoring and graphing of MySQL and system performance.

First of all, let me mention that Percona Monitoring and Management beta has been released recently which is an easy way you can get all of this.

I will try to keep this blog as short as possible, so you can quickly set things up before getting bored. I plan to cover the details in the next few posts. I am going to go through the installation process here in order to get some really useful and good-looking graphs in the end.

Overview

PrometheusPrometheus is an open-source service monitoring system and time series database. In short, the quite efficient daemon scrapes metrics from remote machines using HTTP protocol and stores data in the local time-series database. Prometheus provides a simple web interface, a very powerful query language, HTTP API etc. However, the storage is not designed to be durable for the time being.

The remote machines need to run exporters to expose metrics to Prometheus. We will be using the following two:

GrafanaGrafana is an open source, feature-rich metrics dashboard and graph editor for Graphite, Elasticsearch, OpenTSDB, Prometheus and InfluxDB. It is a powerful tool for visualizing large-scale measurement data and designed to work with time-series. Grafana supports different types of graphs, allows for custom representation of individual metrics on the graph and various methods of authentication including LDAP.

Diagram

Here is a diagram of the setup we are going to use:
Prometheus + Grafana diagram

Prometheus setup

To install on the monitor host.

Get the latest tarball from Github.

Create a simple config:

where 192.168.56.107 is the IP address of the db host we are going to monitor and db1 is its short name. Note, the “alias” label is important here because we rely on it in the predefined dashboards below to get per host graphs.

Start Prometheus in foreground:

Now we can access Prometheus’ built-in web interface by http://monitor_host:9090

Prometheus web interface
If you look at the Status page from the top menu, you will see that our monitoring targets are down so far. Now let’s setup them – prometheus exporters.

Prometheus exporters setup

Install on the db host. Of course, you can use the same monitor host for the experiment. Obviously, this node must run MySQL.

Download exporters from here and there.

Start node_exporter in foreground:

Unlike node_exporter, mysqld_exporter wants MySQL credentials. Those privileges should be sufficient:

Create .my.cnf and start mysqld_exporter in foreground:

At this point we should see our endpoints are up and running on the Prometheus Status page:
Prometheus status page

Grafana setup

Install on the monitor host.

Grafana has RPM and DEB packages. The installation is as simple as installing one package.
RPM-based system:

or APT-based one:

Open and edit the last section of /etc/grafana/grafana.ini resulting in the following ending:

Percona has built the predefined dashboards for Grafana with Prometheus for you.

Let’s get them deployed:

It is important to apply the following minor patch on Grafana 2.6 in order to use the interval template variable to get the good zoomable graphs. The fix is simply to allow variable in Step field on Grafana graph editor page. For more information, take a look at PR#3757 and PR#4257. We hope the last one will be released with the next Grafana version.

Those changes are idempotent.

Finally, start Grafana:

At this point, we are one step before being done. Login into Grafana web interface http://monitor_host:3000 (admin/admin).

Go to Data Sources and add one for Prometheus:
Grafana datasource

Now check out the dashboards and graphs. Say choose “System Overview” and period “Last 5 minutes” on top-right. You should see something similar:
Grafana screen
If your graphs are not populating ensure the system time is correct on the monitor host.

Samples

Here are some real-world samples (images are clickable and scrollable):
 
 
 
 

Enjoy!

Conclusion

Prometheus and Grafana is a great tandem for enabling monitoring and graphing capabilities for MySQL. The tools are pretty easy to deploy, they are designed for time series with high efficiency in mind. In the next blog posts I will talk more about technical aspects, problems and related stuff.


posted by LifeisSimple
2016. 6. 6. 23:22 Brain Trainning/DataBase


Graphing MySQL performance with Prometheus and Grafana

   | February 29, 2016 |  Posted In: MonitoringMySQLPrometheus

출처 : https://www.percona.com/blog/2016/02/29/graphing-mysql-performance-with-prometheus-and-grafana/

This post explains how you can quickly start using such trending tools as Prometheus and Grafana for monitoring and graphing of MySQL and system performance.

First of all, let me mention that Percona Monitoring and Management beta has been released recently which is an easy way you can get all of this.

I will try to keep this blog as short as possible, so you can quickly set things up before getting bored. I plan to cover the details in the next few posts. I am going to go through the installation process here in order to get some really useful and good-looking graphs in the end.

Overview

PrometheusPrometheus is an open-source service monitoring system and time series database. In short, the quite efficient daemon scrapes metrics from remote machines using HTTP protocol and stores data in the local time-series database. Prometheus provides a simple web interface, a very powerful query language, HTTP API etc. However, the storage is not designed to be durable for the time being.

The remote machines need to run exporters to expose metrics to Prometheus. We will be using the following two:

GrafanaGrafana is an open source, feature-rich metrics dashboard and graph editor for Graphite, Elasticsearch, OpenTSDB, Prometheus and InfluxDB. It is a powerful tool for visualizing large-scale measurement data and designed to work with time-series. Grafana supports different types of graphs, allows for custom representation of individual metrics on the graph and various methods of authentication including LDAP.

Diagram

Here is a diagram of the setup we are going to use:
Prometheus + Grafana diagram

Prometheus setup

To install on the monitor host.

Get the latest tarball from Github.

Create a simple config:

where 192.168.56.107 is the IP address of the db host we are going to monitor and db1 is its short name. Note, the “alias” label is important here because we rely on it in the predefined dashboards below to get per host graphs.

Start Prometheus in foreground:

Now we can access Prometheus’ built-in web interface by http://monitor_host:9090

Prometheus web interface
If you look at the Status page from the top menu, you will see that our monitoring targets are down so far. Now let’s setup them – prometheus exporters.

Prometheus exporters setup

Install on the db host. Of course, you can use the same monitor host for the experiment. Obviously, this node must run MySQL.

Download exporters from here and there.

Start node_exporter in foreground:

Unlike node_exporter, mysqld_exporter wants MySQL credentials. Those privileges should be sufficient:

Create .my.cnf and start mysqld_exporter in foreground:

At this point we should see our endpoints are up and running on the Prometheus Status page:
Prometheus status page

Grafana setup

Install on the monitor host.

Grafana has RPM and DEB packages. The installation is as simple as installing one package.
RPM-based system:

or APT-based one:

Open and edit the last section of /etc/grafana/grafana.ini resulting in the following ending:

Percona has built the predefined dashboards for Grafana with Prometheus for you.

Let’s get them deployed:

It is important to apply the following minor patch on Grafana 2.6 in order to use the interval template variable to get the good zoomable graphs. The fix is simply to allow variable in Step field on Grafana graph editor page. For more information, take a look at PR#3757 and PR#4257. We hope the last one will be released with the next Grafana version.

Those changes are idempotent.

Finally, start Grafana:

At this point, we are one step before being done. Login into Grafana web interface http://monitor_host:3000 (admin/admin).

Go to Data Sources and add one for Prometheus:
Grafana datasource

Now check out the dashboards and graphs. Say choose “System Overview” and period “Last 5 minutes” on top-right. You should see something similar:
Grafana screen
If your graphs are not populating ensure the system time is correct on the monitor host.

Samples

Here are some real-world samples (images are clickable and scrollable):
 
 
 
 

Enjoy!

Conclusion

Prometheus and Grafana is a great tandem for enabling monitoring and graphing capabilities for MySQL. The tools are pretty easy to deploy, they are designed for time series with high efficiency in mind. In the next blog posts I will talk more about technical aspects, problems and related stuff.


posted by LifeisSimple
2016. 6. 6. 22:11 Brain Trainning/DataBase

50 Important Queries in SQL Server


출처 : http://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/


In this article I will explain some general purpose queries. I think each developer should have knowledge of these queries. These queries are not related to any specific topic of SQL. But knowledge of such queries can solve some complex tasks and may be used in many scenarios, so I decided to write an article on these queries.

Query 1: Retrieve List of All Database

  1. EXEC sp_helpdb  

Example:

Example

Query 2: Display Text of Stored Procedure, Trigger, View 

  1. exec sp_helptext @objname = 'Object_Name'  

Example:

Example

Query 3: Get All Stored Procedure Relate To Database

  1. SELECT DISTINCT o.name, o.xtype  
  2.   
  3. FROM syscomments c  
  4.   
  5. INNER JOIN sysobjects o ON c.id=o.id  
  6.   
  7. WHERE o.xtype='P'  

Example:

Example

To retrieve the View use “V” instead of “P” and for functions use “FN.

Query 4: Get All Stored Procedure Relate To Table

  1. SELECT DISTINCT o.name, o.xtype  
  2.   
  3. FROM syscomments c  
  4.   
  5. INNER JOIN sysobjects o ON c.id=o.id  
  6.   
  7. WHERE c.TEXT LIKE '%Table_Name%' AND o.xtype='P'  

Example:

Example

To retrieve the View use “V” instead of “P” and for functions use “FN.

Query 5: Rebuild All Index of Database

  1. EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"  
  2.   
  3. GO  
  4.   
  5. EXEC sp_updatestats  
  6.   
  7. GO  

Example:

Example

Query 6: Retrieve All dependencies of Stored Procedure: 

This query return all objects name that are using into stored procedure like table, user define function, another stored procedure.

Query:

  1. ;WITH stored_procedures AS (  
  2.   
  3. SELECT  
  4.   
  5. oo.name AS table_name,  
  6.   
  7. ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.nameAS row  
  8.   
  9. FROM sysdepends d  
  10.   
  11. INNER JOIN sysobjects o ON o.id=d.id  
  12.   
  13. INNER JOIN sysobjects oo ON oo.id=d.depid  
  14.   
  15. WHERE o.xtype = 'P' AND o.name LIKE '%SP_NAme%' )  
  16.   
  17. SELECT Table_name FROM stored_procedures  
  18.   
  19. WHERE row = 1  

Example:

Example

Query 7: Find Byte Size Of All tables in database

  1. SELECT sob.name AS Table_Name,  
  2.   
  3. SUM(sys.length) AS [Size_Table(Bytes)]  
  4.   
  5. FROM sysobjects sob, syscolumns sys  
  6.   
  7. WHERE sob.xtype='u' AND sys.id=sob.id  
  8.   
  9. GROUP BY sob.name  

Example:

Example

Query 8: Get all table that don’t have identity column:

Query:

  1. SELECT  
  2.   
  3. TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  
  4.   
  5. where  
  6.   
  7. Table_NAME NOT IN  
  8.   
  9. (  
  10.   
  11. SELECT DISTINCT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c  
  12.   
  13. INNER  
  14.   
  15. JOIN sys.identity_columns ic  
  16.   
  17. on  
  18.   
  19. (c.COLUMN_NAME=ic.NAME))  
  20.   
  21. AND  
  22.   
  23. TABLE_TYPE ='BASE TABLE'  

Example:

Example

Query 9: List of Primary Key and Foreign Key for Whole Database

  1. SELECT  
  2.   
  3. DISTINCT  
  4.   
  5. Constraint_Name AS [Constraint],  
  6.   
  7. Table_Schema AS [Schema],  
  8.   
  9. Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
  10.   
  11. GO  

Example:

Example

Query 10: List of Primary Key and Foreign Key for a particular table

  1. SELECT  
  2.   
  3. DISTINCT  
  4.   
  5. Constraint_Name AS [Constraint],  
  6.   
  7. Table_Schema AS [Schema],  
  8.   
  9. Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
  10.   
  11. WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='Table_Name'  
  12.   
  13. GO  

Example:

Example

Query 11: RESEED Identity of all tables

  1. EXEC sp_MSForEachTable '  
  2.   
  3. IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1  
  4.   
  5. DBCC CHECKIDENT (''?'', RESEED, 0)  

Example:

Example

Query 12: List of tables with number of records

  1. CREATE TABLE #Tab  
  2.   
  3. (  
  4.   
  5. Table_Name [varchar](max),  
  6.   
  7. Total_Records int  
  8.   
  9. );  
  10.   
  11. EXEC sp_MSForEachTable @command1=' Insert Into #Tab(Table_Name, Total_Records) SELECT ''?'', COUNT(*) FROM ?'  
  12.   
  13. SELECT * FROM #Tab t ORDER BY t.Total_Records DESC;  
  14.   
  15. DROP TABLE #Tab;  

Example:

Example

Query 13: Get the version name of SQL Server

  1. SELECT @@VERSION AS Version_Name  

Example:

Example

Query 14: Get Current Language of SQL Server

  1. SELECT @@LANGUAGE AS Current_Language;  

Example:

Example
Query 15: Disable all constraints of a table

  1. ALTER TABLE Table_Name NOCHECK CONSTRAINT ALL  

Example:

Example

Query16: Disable all constraints of all tables

  1. EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  

Example:

ExampleQuery 17: Get Current Language Id

  1. SELECT @@LANGID AS 'Language ID'  

Example:

Example

Query18: Get precision level used by decimal and numeric as current set in Server:

  1. SELECT @@MAX_PRECISION AS 'MAX_PRECISION'  

Example:

Example

Query 19: Return Server Name of SQL Server

  1. SELECT @@SERVERNAME AS 'Server_Name'  

Example:

Example

Query 20: Get name of register key under which SQL Server is running

  1. SELECT @@SERVICENAME AS 'Service_Name'  

 

Example:

Example

Query 21: Get Session Id of current user process

  1. SELECT @@SPID AS 'Session_Id'  

Example:

Example

Query22: Get Current Value of TEXTSIZE option

  1. SELECT @@TEXTSIZE AS 'Text_Size'  

Example:

Example

Query 23: Retrieve Free Space of Hard Disk

  1. EXEC master..xp_fixeddrives  

Example:

example

Query24: Disable a Particular Trigger

Syntax:

  1. ALTER TABLE Table_Name DISABLE TRIGGER Trigger_Name  

Example:

  1. ALTER TABLE Employee DISABLE TRIGGER TR_Insert_Salary  

Query 25: Enable a Particular Trigger

Syntax:

  1. ALTER TABLE Table_Name ENABLE TRIGGER Trigger_Name  

Example:

  1. ALTER TABLE Employee ENABLE TRIGGER TR_Insert_Salary  

Query 26: Disable All Trigger of a table

We can disable and enable all triggers of a table using previous query, but replacing the "ALL" instead of trigger name.

Syntax:

  1. ALTER TABLE Table_Name DISABLE TRIGGER ALL  

Example:

  1. ALTER TABLE Demo DISABLE TRIGGER ALL  

Query 27: Enable All Trigger of a table

  1. ALTER TABLE Table_Name ENABLE TRIGGER ALL  

Example:

  1. ALTER TABLE Demo ENABLE TRIGGER ALL  

Query 28: Disable All Trigger for database

Using sp_msforeachtable system stored procedure we enable and disable all triggers for a database.

Syntax:

  1. Use Database_Name  
  2.   
  3. Exec sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"  

Example:

example

Query29: Enable All Trigger for database

  1. Use Demo  
  2.   
  3. Exec sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"  

Example:

example

Query30: List of Stored procedure modified in last N days

  1. SELECT name,modify_date  
  2.   
  3. FROM sys.objects  
  4.   
  5. WHERE type='P'  
  6.   
  7. AND DATEDIFF(D,modify_date,GETDATE())< N  

Example:

example

Query31: List of Stored procedure created in last N days

  1. SELECT name,sys.objects.create_date  
  2.   
  3. FROM sys.objects  
  4.   
  5. WHERE type='P'  
  6.   
  7. AND DATEDIFF(D,sys.objects.create_date,GETDATE())< N  

Example:

Example

Query32: Recompile a stored procedure

  1. EXEC sp_recompile'Procedure_Name';  
  2.   
  3. GO  

Example:

Example

Query 33: Recompile all stored procedure on a table

  1. EXEC sp_recompile N'Table_Name';  
  2.   
  3. GO  

Example:

Example

Query 34: Get all columns of a specific data type:

Query:

  1. SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as Column_Name  
  2.   
  3. FROM sys.columns AS c  
  4.   
  5. JOIN sys.types AS t ON c.user_type_id=t.user_type_id  
  6.   
  7. WHERE t.name = 'Data_Type'  

Example:

Example

Query 35: Get all Nullable columns of a table

  1. SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as Column_Name  
  2.   
  3. FROM sys.columns AS c  
  4.   
  5. JOIN sys.types AS t ON c.user_type_id=t.user_type_id  
  6.   
  7. WHERE c.is_nullable=0 AND OBJECT_NAME(c.OBJECT_ID)='Table_Name'  

Example:

Example

Query 36: Get All table that don’t have primary key

  1. SELECT name AS Table_Name  
  2.   
  3. FROM sys.tables  
  4.   
  5. WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0  
  6.   
  7. ORDER BY Table_Name;  

Example:

Example

Query 37: Get All table that don’t have foreign key

  1. SELECT name AS Table_Name  
  2.   
  3. FROM sys.tables  
  4.   
  5. WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 0  
  6.   
  7. ORDER BY Table_Name;  

Example:

Example

Query 38: Get All table that don’t have identity column

  1. SELECT name AS Table_Name  
  2.   
  3. FROM sys.tables  
  4.   
  5. WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIdentity') = 0  
  6.   
  7. ORDER BY Table_Name;  

Example:

Example
Query 39: Get First Date of Current Month

  1. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE()))+1,GETDATE()),105) First_Date_Current_Month;  

Example:

Example

Query 40: Get last date of previous month

  1. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())),GETDATE()),105) Last_Date_Previous_Month;  

Example:

Example

Query 41: Get last date of current month

  1. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())),105) Last_Date_Current_Month;  

Example:

Example

Query 42: Get first date of next month

  1. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())+1),105) First_Date_Next_Month;  

Example:

Example

Query 43: Swap the values of two columns

  1. UPDATE Table_Name SET Column1=Column2, Column2=Column1  

Example:

Example

Query 44: Remove all stored procedure from database

  1. Declare @Drop_SP Nvarchar(MAX)  
  2.   
  3. Declare My_Cursor Cursor For Select [nameFrom sys.objects where type = 'p'  
  4.   
  5. Open My_Cursor  
  6.   
  7. Fetch Next From My_Cursor Into @Drop_SP  
  8.   
  9. While @@FETCH_STATUS= 0  
  10.   
  11. Begin  
  12.   
  13. Exec('DROP PROCEDURE ' + @Drop_SP)  
  14.   
  15. Fetch Next From My_Cursor Into @Drop_SP  
  16.   
  17. End  
  18.   
  19. Close My_Cursor  
  20.   
  21. Deallocate My_Cursor  

Example:

Example
Query 45: Remove all views from database

  1. Declare @Drop_View Nvarchar(MAX)  
  2.   
  3. Declare My_Cursor Cursor For Select [nameFrom sys.objects where type = 'v'  
  4.   
  5. Open My_Cursor  
  6.   
  7. Fetch Next From My_Cursor Into @Drop_View  
  8.   
  9. While @@FETCH_STATUS = 0  
  10.   
  11. Begin  
  12.   
  13. Exec('DROP VIEW ' + @Drop_View)  
  14.   
  15. Fetch Next From My_Cursor Into @Drop_View  
  16.   
  17. End  
  18.   
  19. Close My_Cursor  
  20.   
  21. Deallocate My_Cursor  

Example:

Example

Query 46: Drop all tables

  1. EXEC sys.sp_MSforeachtable @command1 = 'Drop Table ?'  

Example:

Example

Query 47: Get information of tables’ columns

  1. SELECT * FROM INFORMATION_SCHEMA.COLUMNS  
  2.   
  3. WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME=’Table_Name’  

Example:

Example

Query 48: Get all columns contain any constraints

  1. SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE  

Example:

Example

Query 49: Get all tables that contain a view

  1. SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE  

Example:

Example

Query 50: Get all columns of table that using in views

  1. SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE  

Example:

Example

Read more articles on SQL Queries:


posted by LifeisSimple
prev 1 next