June, 2017 | Kaizen - Continuous Improvement

MYSQL / AURORA Database Troubleshooting

Posted on June 28, 2017 by

Raju Venkataraman Posted in AWS RDS, MySQL, Technology — No Comments ↓

Number of Connections by Host

SELECT SUBSTRING(HOST, 1, 10) , DB,USER , COUNT(*) AS Count
FROM information_schema.processlist 
group by SUBSTRING(HOST, 0, 10) , DB,USER 
ORDER BY Count desc ;

-- '10.10.50.22', 'Portal', 'webguest-dev', '46'

SELECT SUBSTRING(HOST, 1, 10) , DB,USER , COUNT(*) AS Count

FROM information_schema.processlist

group by SUBSTRING(HOST, 0, 10) , DB,USER

ORDER BY Count desc ;

-- '10.10.50.22', 'Portal', 'webguest-dev', '46'

Aurora Max Connections

select AURORA_VERSION();

select * from mysql.slow_log 
where sql_text not like '%LOAD DATA%'
order by query_time desc
limit 1000 ;

select count(*) from mysql.general_log 
where user_host not like 'rdsadmin%'
and user_host not like '[rdsadmin]%'
and event_time > '2017-06-15 18:51:14';

select current_timestamp();

desc mysql.general_log  ;

select @@MAX_CONNECTIONS
-- '4000'

select *  from mysql.general_log 
where command_type like '%Connect%';

select *  from mysql.general_log_backup 
where command_type like '%Connect%' ;

SHOW GLOBAL STATUS LIKE '%Connection_errors%';

SHOW STATUS WHERE `variable_name` = 'Threads_connected';

select AURORA_VERSION();

select * from mysql.slow_log

where sql_text not like '%LOAD DATA%'

order by query_time desc

limit 1000 ;

select count(*) from mysql.general_log

where user_host not like 'rdsadmin%'

and user_host not like '[rdsadmin]%'

and event_time > '2017-06-15 18:51:14';

select current_timestamp();

desc mysql.general_log ;

select @@MAX_CONNECTIONS

-- '4000'

select * from mysql.general_log

where command_type like '%Connect%';

select * from mysql.general_log_backup

where command_type like '%Connect%' ;

SHOW GLOBAL STATUS LIKE '%Connection_errors%';

SHOW STATUS WHERE `variable_name` = 'Threads_connected';

Memory-Optimized Tables

Posted on June 23, 2017 by

Raju Venkataraman Posted in AWS, AWS RDS, Interview Questions, Scaling SQL Server, SQL Server — No Comments ↓

Monitor Memory Optimized Table Space Usage :

;
WITH    system_allocated_memory ( system_allocated_memory_in_mb )
          AS ( SELECT   ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb)
                                                              + SUM(TMS.memory_allocated_for_indexes_kb) )
                                        / 1024.00)
                                 FROM   [sys].[dm_db_xtp_table_memory_stats] TMS
                                 WHERE  TMS.object_id <= 0
                               ), 0.00)
             ),
        table_index_memory ( table_used_memory_in_mb, table_unused_memory_in_mb, index_used_memory_in_mb, index_unused_memory_in_mb )
          AS ( SELECT   ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_used_by_table_kb)
                                                              / 1024.00 ))
                               ), 0.00) AS table_used_memory_in_mb ,
                        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb)
                                                              - SUM(TMS.memory_used_by_table_kb) )
                                        / 1024.00)
                               ), 0.00) AS table_unused_memory_in_mb ,
                        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_used_by_indexes_kb)
                                                              / 1024.00 ))
                               ), 0.00) AS index_used_memory_in_mb ,
                        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_indexes_kb)
                                                              - SUM(TMS.memory_used_by_indexes_kb) )
                                        / 1024.00)
                               ), 0.00) AS index_unused_memory_in_mb
               FROM     [sys].[dm_db_xtp_table_memory_stats] TMS
               WHERE    TMS.object_id > 0
             )
    SELECT  s.system_allocated_memory_in_mb ,
            t.table_used_memory_in_mb ,
            t.table_unused_memory_in_mb ,
            t.index_used_memory_in_mb ,
            t.index_unused_memory_in_mb ,
            ISNULL(( SELECT DATABASEPROPERTYEX(DB_NAME(DB_ID()),
                                               'IsXTPSupported')
                   ), 0) AS has_memory_optimized_filegroup
    FROM    system_allocated_memory s ,
            table_index_memory t

SELECT  t.object_id ,
        t.name ,
        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_used_by_table_kb )
                        / 1024.00)
               ), 0.00) AS table_used_memory_in_mb ,
        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_allocated_for_table_kb
                                                  - TMS.memory_used_by_table_kb )
                        / 1024.00)
               ), 0.00) AS table_unused_memory_in_mb ,
        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_used_by_indexes_kb )
                        / 1024.00)
               ), 0.00) AS index_used_memory_in_mb ,
        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_allocated_for_indexes_kb
                                                  - TMS.memory_used_by_indexes_kb )
                        / 1024.00)
               ), 0.00) AS index_unused_memory_in_mb
FROM    sys.tables t
        JOIN sys.dm_db_xtp_table_memory_stats TMS ON ( t.object_id = TMS.object_id )

;

WITH system_allocated_memory ( system_allocated_memory_in_mb )

AS ( SELECT ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb)

+ SUM(TMS.memory_allocated_for_indexes_kb) )

/ 1024.00)

FROM [sys].[dm_db_xtp_table_memory_stats] TMS

WHERE TMS.object_id <= 0

), 0.00)

table_index_memory ( table_used_memory_in_mb, table_unused_memory_in_mb, index_used_memory_in_mb, index_unused_memory_in_mb )

AS ( SELECT ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_used_by_table_kb)

/ 1024.00 ))

), 0.00) AS table_used_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb)

- SUM(TMS.memory_used_by_table_kb) )

/ 1024.00)

), 0.00) AS table_unused_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_used_by_indexes_kb)

/ 1024.00 ))

), 0.00) AS index_used_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_indexes_kb)

- SUM(TMS.memory_used_by_indexes_kb) )

/ 1024.00)

), 0.00) AS index_unused_memory_in_mb

FROM [sys].[dm_db_xtp_table_memory_stats] TMS

WHERE TMS.object_id > 0

)

SELECT s.system_allocated_memory_in_mb ,

t.table_used_memory_in_mb ,

t.table_unused_memory_in_mb ,

t.index_used_memory_in_mb ,

t.index_unused_memory_in_mb ,

ISNULL(( SELECT DATABASEPROPERTYEX(DB_NAME(DB_ID()),

'IsXTPSupported')

), 0) AS has_memory_optimized_filegroup

FROM system_allocated_memory s ,

table_index_memory t

SELECT t.object_id ,

t.name ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_used_by_table_kb )

/ 1024.00)

), 0.00) AS table_used_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_allocated_for_table_kb

- TMS.memory_used_by_table_kb )

/ 1024.00)

), 0.00) AS table_unused_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_used_by_indexes_kb )

/ 1024.00)

), 0.00) AS index_used_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_allocated_for_indexes_kb

- TMS.memory_used_by_indexes_kb )

/ 1024.00)

), 0.00) AS index_unused_memory_in_mb

FROM sys.tables t

JOIN sys.dm_db_xtp_table_memory_stats TMS ON ( t.object_id = TMS.object_id )

All Memory Used by Memory Optimized Table across Database Engine

-- this DMV accounts for all memory used by the hek_2 engine 
SELECT type ,
name ,
memory_node_id ,
pages_kb / 1024 AS pages_MB
FROM sys.dm_os_memory_clerks
WHERE type LIKE '%xtp%'

-- this DMV accounts for all memory used by the hek_2 engine

SELECT type ,

name ,

memory_node_id ,

pages_kb / 1024 AS pages_MB

FROM sys.dm_os_memory_clerks

WHERE type LIKE '%xtp%'

Enable Natively Compiled Stored Procedure Stats Collection

EXEC [sys].[sp_xtp_control_proc_exec_stats] @new_collection_value = 1  
DECLARE @c BIT  
EXEC sp_xtp_control_proc_exec_stats @old_collection_value = @c OUTPUT  
SELECT  @c AS 'collection status'

EXEC [sys].[sp_xtp_control_proc_exec_stats] @new_collection_value = 1

DECLARE @c BIT

EXEC sp_xtp_control_proc_exec_stats @old_collection_value = @c OUTPUT

SELECT @c AS 'collection status'

DBCC FREEPROCCACHE does not remove natively compiled stored procedures from Plan Cache

-- https://connect.microsoft.com/SQLServer/Feedback/Details/3126441

DECLARE @sql NVARCHAR(MAX) = N''

SELECT  @sql += N'EXECUTE sp_recompile N'''
        + QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) + '''
'
FROM    sys.sql_modules sm
        JOIN sys.objects o ON sm.object_id = o.object_id
WHERE   uses_native_compilation = 1

EXECUTE sp_executesql @sql

	
-- Reset wait and latch statistics.
DBCC SQLPERF('sys.dm_os_latch_stats' , CLEAR)
DBCC SQLPERF('sys.dm_os_wait_stats' , CLEAR)

-- https://connect.microsoft.com/SQLServer/Feedback/Details/3126441

DECLARE @sql NVARCHAR(MAX) = N''

SELECT @sql += N'EXECUTE sp_recompile N'''

+ QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) + '''

FROM sys.sql_modules sm

JOIN sys.objects o ON sm.object_id = o.object_id

WHERE uses_native_compilation = 1

EXECUTE sp_executesql @sql

-- Reset wait and latch statistics.

DBCC SQLPERF('sys.dm_os_latch_stats' , CLEAR)

DBCC SQLPERF('sys.dm_os_wait_stats' , CLEAR)

…

Month: June 2017

MYSQL / AURORA Database Troubleshooting

Memory-Optimized Tables

Brute force attack on SQL Server

When Does SQL Server Trial Edition Expire?

SQL Injection attack on Website hosted on EC2 Machine

Recent Posts

Categories

Recent Posts

Archives