• plugin 설치
INSTALL SONAME 'metadata_lock_info'; Metadata Lock 확인
INSTALL PLUGIN server_audit SONAME 'server_audit'; Audit 작업을 위한 플러그인
INSTALL PLUGIN query_cache_info SONAME 'query_cache_info'; Query Cache 상태 모니터링
INSTALL SONAME 'locales'; Locale 정보
INSTALL SONAME 'query_response_time'; 쿼리 응답시간 모니터링
INSTALL PLUGIN SQL_ERROR_LOG SONAME 'sql_errlog'; SQL error log 모니터링
INSTALL SONAME 'ha_mroonga'; 전문 검색을 위한 mGroonga 엔진
  • 시스템 변수 performance_schema의 값은 “ON”으로 설정한다.

CREATE OR REPLACE

ALGORITHM=TEMPTABLE

DEFINER = 'root'@'localhost'

SQL SECURITY INVOKER

VIEW sys.`v$thread`

AS

SELECT p.id AS thread_id

  ,concat\(p.user, '@', substring\_index\(p.host, ':', 1\)\) AS user

  ,p.db as schema\_name

  ,p.command as thread\_status

  ,p.time as thread\_sec

  ,p.memory\_used

  ,p.state as thread\_state

  ,p.info as thread\_info

  ,trx.trx\_state

  ,trx.trx\_wait\_started as lock\_wait\_started

  ,timestampdiff\(SECOND, trx.trx\_wait\_started, now\(\)\) AS lock\_wait\_sec

  ,locks.lock\_table

  ,locks.lock\_index

  ,locks.lock\_type

  ,\(SELECT group\_concat\(concat\(table\_schema

                                   ,'.'

                                   ,table\_name

                                   ,'\('

                                   ,REPLACE\(lock\_type, ' metadata lock', ''\)

                                   ,'\)'\) separator ','\)

      FROM information\_schema.metadata\_lock\_info meta

     WHERE meta.thread\_id = trx.trx\_mysql\_thread\_id\) as meta\_locks

FROM information_schema.processlist p

LEFT JOIN information_schema.innodb_trx trx

ON p.id = trx.trx\_mysql\_thread\_id

LEFT JOIN information_schema.innodb_locks locks

ON trx.trx\_id = locks.lock\_trx\_id

;

CREATE OR REPLACE

ALGORITHM=TEMPTABLE

DEFINER = 'root'@'localhost'

SQL SECURITY INVOKER

VIEW sys.`v$meta_lock`

AS

SELECT meta.table_schema AS schema_name

  ,meta.table\_name AS object\_name

  ,REPLACE\(meta.lock\_type, ' metadata lock', ''\) AS meta\_lock\_type

  ,meta.lock\_mode AS meta\_lock\_mode

  ,meta.lock\_duration AS meta\_lock\_duration

  ,ps.id AS thread\_id

  ,concat\(ps.user, '@', substring\_index\(ps.host, ':', 1\)\) AS user

  ,ps.command AS thread\_status

  ,ps.time AS thread\_time\_sec

  ,info AS thread\_info

  ,concat\('KILL ', ps.id\) as kill\_thread

FROM information_schema.metadata_lock_info meta

LEFT OUTER JOIN information_schema. processlist ps

ON meta.thread\_id = ps.id

;

CREATE OR REPLACE

ALGORITHM=TEMPTABLE

DEFINER = 'root'@'localhost'

SQL SECURITY INVOKER

VIEW sys.`v$innodb_only_lock_trx_id`

AS

SELECT DISTINCT hold.blocking_trx_id AS hold_trx_id

           ,NULL                 AS wait\_trx\_id

           ,hold.blocking\_trx\_id AS trx\_id

FROM (information_schema.innodb_lock_waits hold LEFT JOIN

    information\_schema.innodb\_lock\_waits wait

    ON\(\(hold.blocking\_trx\_id = wait.requesting\_trx\_id\)\)\)

WHERE isnull(wait.requesting_trx_id)

UNION ALL

SELECT hold.blocking_trx_id AS hold_trx_id

  ,hold.requesting\_trx\_id AS wait\_trx\_id

  ,hold.requesting\_trx\_id AS trx\_id

FROM (information_schema.innodb_lock_waits hold LEFT JOIN

    information\_schema.innodb\_lock\_waits wait

    ON\(\(hold.blocking\_trx\_id = wait.requesting\_trx\_id\)\)\)

WHERE isnull(wait.requesting_trx_id)

;

DROP VIEW IF EXISTS sys.`v$innodb_lock`;

CREATE

ALGORITHM=UNDEFINED

SQL SECURITY INVOKER

VIEW sys.`v$innodb_lock`

AS

SELECT concat(CASE

            WHEN locks.wait\_trx\_id IS NOT NULL THEN

             '    '

            ELSE

             ''

          END

         ,trx.trx\_mysql\_thread\_id\) AS thread\_id

  \#,trx.trx\_id

  ,ps.command as thread\_status

  ,trx.trx\_state

  ,trx.trx\_started

  ,trx.trx\_wait\_started

  ,timestampdiff\(SECOND, trx.trx\_wait\_started, now\(\)\) as wait\_secs

  ,concat\(locks.lock\_mode, ' \(', locks.lock\_type, '\)' \) as lock\_type

  ,locks.lock\_table

  ,locks.lock\_index

  ,trx.trx\_query

  ,trx.trx\_operation\_state

  ,trx.trx\_rows\_locked as waiting\_trx\_rows\_locked

  ,trx.trx\_rows\_modified AS waiting\_trx\_rows\_modified

  ,trx.trx\_lock\_memory\_bytes

  ,concat\('KILL ', trx.trx\_mysql\_thread\_id\) as kill\_thread

FROM sys.`v$innodb_only_lock_trx_id` locks

JOIN information_schema.innodb_trx trx

ON locks.trx\_id = trx.trx\_id

JOIN information_schema.processlist ps

ON trx.trx\_mysql\_thread\_id = ps.id

LEFT JOIN information_schema.innodb_locks locks

ON trx.trx\_id = locks.lock\_trx\_id

ORDER BY hold_trx_id, wait_trx_id

;

CREATE OR REPLACE

DEFINER = 'root'@'localhost'

SQL SECURITY INVOKER

VIEW sys.`v$db_configured_memory`

AS

SELECT 'Configured Max Memory Limit' AS category,

   '' AS variable\_name,

   concat\(

      round\(

           \(  \(SELECT   \(SELECT sum\(variable\_value\)

                           FROM information\_schema.global\_variables

                          WHERE variable\_name IN \('read\_buffer\_size',

                                                  'read\_rnd\_buffer\_size',

                                                  'sort\_buffer\_size',

                                                  'thread\_stack',

                                                  'join\_buffer\_size',

                                                  'binlog\_cache\_size'\)\)

                      \* \(SELECT variable\_value

                           FROM information\_schema.global\_variables

                          WHERE variable\_name = 'max\_connections'\)\)

            + \(SELECT sum\(variable\_value\)

                 FROM information\_schema.global\_variables

                WHERE variable\_name IN

                         \('innodb\_buffer\_pool\_size',

                          'innodb\_additional\_mem\_pool\_size',

                          'innodb\_log\_buffer\_size',

                          'key\_buffer\_size',

                          'query\_cache\_size'\)\)\)

         / 1024

         / 1024

         / 1024,

         2\),

      ' G'\)

      AS value

UNION ALL

SELECT 'Configured Max Per-thread Buffers' AS category,

   '' AS variable\_name,

   concat\(round\(  \(SELECT sum\(variable\_value\)

                     FROM information\_schema.global\_variables

                    WHERE variable\_name IN \('read\_buffer\_size',

                                            'read\_rnd\_buffer\_size',

                                            'sort\_buffer\_size',

                                            'thread\_stack',

                                            'join\_buffer\_size',

                                            'binlog\_cache\_size'\)\)

                \* \(SELECT variable\_value

                     FROM information\_schema.global\_variables

                    WHERE variable\_name = 'max\_connections'\)

                / 1024

                / 1024,

                1\),

          ' M'\)

      AS value

UNION ALL

SELECT 'Configured Max Global Buffers' AS category,

   '' AS variable\_name,

   concat\(round\(sum\(variable\_value\) / 1024 / 1024, 1\), ' M'\) AS value

FROM information_schema.global_variables

WHERE variable_name IN ('innodb_buffer_pool_size',

                     'innodb\_additional\_mem\_pool\_size',

                     'innodb\_log\_buffer\_size',

                     'key\_buffer\_size',

                     'query\_cache\_size'\)

UNION ALL

SELECT 'Global Buffers' AS category,

   variable\_name,

   concat\(round\(variable\_value / 1024 / 1024, 1\), ' M'\) AS value

FROM information_schema.global_variables

WHERE variable_name = 'innodb_buffer_pool_size'

UNION ALL

SELECT 'Global Buffers' AS category,

   variable\_name,

   concat\(round\(variable\_value / 1024 / 1024, 1\), ' M'\) AS value

FROM information_schema.global_variables

WHERE variable_name = 'innodb_additional_mem_pool_size'

UNION ALL

SELECT 'Global Buffers' AS category,

   variable\_name,

   concat\(round\(variable\_value / 1024 / 1024, 1\), ' M'\) AS value

FROM information_schema.global_variables

WHERE variable_name = 'innodb_log_buffer_size'

UNION ALL

SELECT 'Global Buffers' AS category,

   variable\_name,

   concat\(round\(variable\_value / 1024 / 1024, 1\), ' M'\) AS value

FROM information_schema.global_variables

WHERE variable_name = 'key_buffer_size'

UNION ALL

SELECT 'Global Buffers' AS category,

   variable\_name,

   concat\(round\(variable\_value / 1024 / 1024, 1\), ' M'\) AS value

FROM information_schema.global_variables

WHERE variable_name = 'query_cache_size'

UNION ALL

SELECT 'Connections' AS category, variable_name, variable_value AS value

FROM information_schema.global_variables

WHERE variable_name = 'max_connections'

UNION ALL

SELECT 'Per-thread Buffers' AS category,

   variable\_name,

   concat\(round\(variable\_value / 1024, 1\), ' KB'\) AS value

FROM information_schema.global_variables

WHERE variable_name = 'read_buffer_size'

UNION ALL

SELECT 'Per-thread Buffers' AS category,

   variable\_name,

   concat\(round\(variable\_value / 1024, 1\), ' KB'\) AS value

FROM information_schema.global_variables

WHERE variable_name = 'read_rnd_buffer_size'

UNION ALL

SELECT 'Per-thread Buffers' AS category,

   variable\_name,

   concat\(round\(variable\_value / 1024, 1\), ' KB'\) AS value

FROM information_schema.global_variables

WHERE variable_name = 'sort_buffer_size'

UNION ALL

SELECT 'Per-thread Buffers' AS category,

   variable\_name,

   concat\(round\(variable\_value / 1024, 1\), ' KB'\) AS value

FROM information_schema.global_variables

WHERE variable_name = 'join_buffer_size'

UNION ALL

SELECT 'Per-thread Buffers' AS category,

   variable\_name,

   concat\(round\(variable\_value / 1024, 1\), ' KB'\) AS value

FROM information_schema.global_variables

WHERE variable_name = 'thread_stack'

UNION ALL

SELECT 'Per-thread Buffers' AS category,

   variable\_name,

   concat\(round\(variable\_value / 1024, 1\), ' KB'\) AS value

FROM information_schema.global_variables

WHERE variable_name = 'binlog_cache_size'

;

CREATE OR REPLACE

ALGORITHM=TEMPTABLE

DEFINER = 'root'@'localhost'

SQL SECURITY INVOKER

VIEW sys.`v$innodb_buffer_hit_rate`

AS

SELECT concat(

     format\( \(SELECT variable\_value AS DataPages

             FROM information\_schema.global\_status

            WHERE variable\_name = 'Innodb\_buffer\_pool\_pages\_data'\)

         / \(SELECT variable\_value AS TotalPages

              FROM information\_schema.global\_status

             WHERE variable\_name = 'Innodb\_buffer\_pool\_pages\_total'\)

         \* 100

       , 2

        \), ' %'

         \) AS hit\_rate

  ,\(SELECT variable\_value / 1024

      FROM information\_schema.global\_status

     WHERE variable\_name = 'Innodb\_page\_size'\) AS page\_kbytes

  ,\(SELECT variable\_value

      FROM information\_schema.global\_status

     WHERE variable\_name = 'Innodb\_buffer\_pool\_pages\_total'\) AS total\_pages

  ,\(SELECT variable\_value

      FROM information\_schema.global\_status

     WHERE variable\_name = 'Innodb\_buffer\_pool\_pages\_data'\) AS data\_pages

  ,\(SELECT variable\_value

      FROM information\_schema.global\_status

     WHERE variable\_name = 'Innodb\_buffer\_pool\_pages\_dirty'\) AS dirty\_pages

  ,\(SELECT variable\_value

      FROM information\_schema.global\_status

     WHERE variable\_name = 'Innodb\_buffer\_pool\_pages\_free'\) AS free\_pages

  ,\(SELECT variable\_value

      FROM information\_schema.global\_status

     WHERE variable\_name = 'Innodb\_buffer\_pool\_pages\_misc'\) AS misc\_pages

;

results matching ""

    No results matching ""