Dolan Antenucci's questions

我正在使用在 Windows Server 2012 RTM 上运行的 MS-SQL Server 2012 Standard。我使用 SSMS 创建了一个扩展事件会话，我在其中使用了内置的“批量查询跟踪”模板并将结果保存到一个文件中。

我有一个名为“mydb”的数据库，其中有一个“dbo.mytable”表。有一次我在另一个盒子上测试了这个，按照上面的步骤，查询将被记录到扩展事件跟踪中，但是在这个新盒子上（使用相同的 AWS AMI，一天后），任何关于“dbo”的查询.mytable" 没有被记录。

默认情况下，批查询跟踪模板在 batch_sql_completed（is_system=0 和 dbid>4）上有一个过滤器。我删除了这些，以及其他系统查询日志。我验证了扩展事件会话正在运行。我还尝试了“详细查询跟踪”模板，以确保它不是我以前使用的那个模板。

我的目标是简单地审计在数据库上运行的所有查询。我运行的测试查询是对“mytable”和全选的简单插入。我正在通过 SSMS 运行查询。

关于为什么不记录这些查询的任何想法？让我知道我可以提供哪些其他信息。谢谢！

有关我的测试的更多详细信息

创建扩展事件会话：

• 转到 SSMS 中的“New Session..”右键单击菜单选项（在 Management -> Extended Events 下）
• 输入以下信息：
  • 会话名称：日志查询
  • 模板：查询批量跟踪
  • Schedule：在服务器启动时启动事件会话；创建后立即开始
  • 因果关系跟踪：跟踪事件如何相互关联。
  • 数据存储：event_file，C:\logs\log-queries.xel
• 验证它正在运行：
  • 从 sys.dm_xe_sessions 中选择 *；
  • 已验证：我在列表中看到它

关于我的测试数据库：

• 数据库：mydb
• 表：dbo.mytable (cid nchar(10), cname nchar(10), city nchar(10))
  • （“dbo”是模式；我创建时的默认选项之一）
• 查询：SELECT TOP 1000 [cid], [cname], [city] FROM [mydb].[dbo].[mytable]

验证日志记录是否有效：

• 按照本教程，从日志文件中提取语句，然后在结果中查找我的查询：

IF OBJECT_ID('tempdb..#ExEvent') IS NOT NULL DROP TABLE #ExEvent

SELECT IDENTITY(INT,1,1) AS RowId, object_name AS event_name, CONVERT(XML,event_data) AS event_data
    INTO #ExEvent
FROM sys.fn_xe_file_target_read_file(N'C:\logs\*.xel', null, null, null);


SELECT ISNULL(t_action.RowId, t_data.RowId) AS RowId
        , ISNULL(t_action.event_name, t_data.event_name) AS event_name
        , t_action.[client_hostname], t_action.[collect_system_time], t_action.[database_name], t_action.[query_plan_hash], t_action.[server_principal_name], t_action.[session_id]
        , t_data.[collect_statement], t_data.[connection_reset_option], t_data.[cpu_time], t_data.[data_stream], t_data.[duration], t_data.[last_row_count], t_data.[line_number], t_data.[logical_reads], t_data.[object_name], t_data.[offset], t_data.[offset_end], t_data.[output_parameters], t_data.[parameterized_plan_handle], t_data.[physical_reads], t_data.[result], t_data.[row_count], t_data.[statement], t_data.[writes]
    FROM (
            SELECT RowId, event_name, [client_hostname], [collect_system_time], [database_name], [query_plan_hash], [server_principal_name], [session_id]                  
                FROM (
                        SELECT RowId
                                , event_name
                                , T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')AS att_name
                                , T2.Loc.query('.').value('(/action/value)[1]', 'varchar(max)')AS att_value
                        FROM   #ExEvent
                        CROSS APPLY event_data.nodes('/event/action') as T2(Loc)
                        WHERE T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')
                            IN ('client_hostname', 'collect_system_time', 'database_name', 'query_plan_hash', 'server_principal_name', 'session_id')
                    ) AS SourceTable
                        PIVOT(
                            MAX(att_value)
                            FOR att_name IN ([client_hostname], [collect_system_time], [database_name], [query_plan_hash], [server_principal_name], [session_id])
                    ) AS PivotTable
            ) AS t_action

        -- Full outer because it might be no events selected only the payload
        FULL OUTER JOIN (
            SELECT RowId, event_name, [collect_statement], [connection_reset_option], [cpu_time], [data_stream], [duration], [last_row_count], [line_number], [logical_reads], [object_name], [offset], [offset_end], [output_parameters], [parameterized_plan_handle], [physical_reads], [result], [row_count], [statement], [writes]
                FROM (
                        SELECT RowId
                                , event_name
                                , T3.Loc.query('.').value('(/data/@name)[1]', 'varchar(max)') AS att_name
                                , T3.Loc.query('.').value('(/data/value)[1]', 'varchar(max)') AS att_value

                            FROM   #ExEvent
                            CROSS APPLY event_data.nodes('/event/data') as T3(Loc)
                            WHERE T3.Loc.query('.').value('(/data/@name)[1]', 'varchar(max)')
                                IN ('collect_statement', 'connection_reset_option', 'cpu_time', 'data_stream', 'duration', 'last_row_count', 'line_number', 'logical_reads', 'object_name', 'offset', 'offset_end', 'output_parameters', 'parameterized_plan_handle', 'physical_reads', 'result', 'row_count', 'statement', 'writes')
                        ) AS SourceTable
                            PIVOT (
                            MAX(att_value)
                                FOR att_name IN ([collect_statement], [connection_reset_option], [cpu_time], [data_stream], [duration], [last_row_count], [line_number], [logical_reads], [object_name], [offset], [offset_end], [output_parameters], [parameterized_plan_handle], [physical_reads], [result], [row_count], [statement], [writes])
                        ) AS PivotTable

            ) AS t_data
            ON t_data.RowId = t_action.RowId

这类似于我的另一篇文章，但现在我有一个不同的表结构，并且仍然不清楚应该更改哪些 MySQL 参数以利用我的机器拥有的额外 ram---所以如果它看起来像重复的，让我知道如何最好地改写我要问的内容。

我有下表：create table mytable (id1 int, id2 int, score float) engine MyISAM,有 500 亿条记录。

我计划在第一列 ( alter table mytable add index myindex (id1)) 上添加一个索引，我想知道可以更改哪些 MySQL 参数以利用这个额外的内存（例如缓冲区？）？

注意：引擎类型不需要是 MyISAM，如果有区别的话。

我有一台运行 Ubuntu 10.04 的服务器，通过包安装了 Mysql 5.1x。该系统有 128GB 内存，8 个内核，并有 4TB 的可用空间用于存储 Mysql 和 Mysql tmp。

我有一个这样的 MyISAM：

CREATE TABLE `data_store` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uniqname` varchar(150) NOT NULL,
  `data` blob,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM CHARSET=latin1;

我插入了 8 亿条记录（插入前大约 350gb 数据），然后尝试添加以下索引：

ALTER TABLE data_store DISABLE KEYS;
ALTER TABLE data_store ADD INDEX uniqname_index (uniqname);
ALTER TABLE data_store ENABLE KEYS;

（关于DISABLE KEYS命令，我看到其他地方建议在插入数据之前使用，并且由于该命令从未到达ENABLE KEYS，我认为它对我没有任何用途。我主要只是在我对我的描述中包含了彻底的内容。我在做。）

当我启动索引作业时，显示的第一个状态SHOW PROCESSLIST是“正在复制到 tmp 表”。

几个小时后，我检查 bac，即使在 24 小时后，状态仍会显示在“Repair With Keycache”消息中。我尝试在稍旧的服务器上运行该作业，3 天后，它仍然保持“使用 keycache 修复”状态。因此，我取消了这台较新机器上的 create-index 命令。

我读过“使用 Keycache 修复”可能非常慢，在许多情况下，“通过排序修复”是首选。

基于一些 Stack Exchange 和网上的随机帖子，我在 Mysql 服务器中添加了以下设置：

myisam_sort_buffer_size = 80G
bulk_insert_buffer_size = 80G
myisam_repair_threads   = 8
max_heap_table_size     = 20G
myisam_max_sort_file_size = 500G
tmp_table_size          = 20G
key_buffer_size         = 20G
sort_buffer_size        = 20G
join_buffer_size        = 20G

我重新启动了作业，同样的过程再次发生（复制到 tmp 文件，然后通过密钥缓存修复）。

在我终止作业后，我注意到在 mysql/error.log 中有一条消息，“myisam_sort_buffer_size 太小”。这发生在当天早些时候，而不是我杀死工作的时候。

问题

• 我会走错路吗？我只是希望能够通过某个键（uniqname）快速查找我的数据。

• 从我的表开始，从头开始添加索引，使用DISABLE KEYS命令，插入我的 8 亿条记录，然后再开始，有什么好处ENABLE KEYS吗？我在其他地方读到这可以防止复制 tmp 表（这可能只能节省我几个小时？）

• 我想要这个“通过排序修复”吗？

这是How to best store Google ngrams in a database?的延续。，其中介绍了如何存储Google Ngram Book 数据。

我正在寻找存储格式略有不同的Google NGram Web 数据（没有页面/年份信息；只是计数）：

...
ceramics collectables collectibles 55
ceramics collectables fine 130
...
serve as the incoming 92
serve as the incubator 99

由于这是一个非常简单的数据结构，什么是存储此数据的好方法，可以相当快速地导入，并快速检索特定 ngram 的计数？

我喜欢关系数据库的想法，仅仅是因为访问它的常用方法，但我猜大多数其他非关系数据库（例如 tokyo hashtable）也有非常常用的方法。

更新

查询示例：

# primary query
> SELECT ngram_count FROM ngram_table WHERE ngram = 'ceramics collectables fine';

ceramics collectables collectibles 55
ceramics collectables fine 130

# secondary query (not needed, but nice if have option)
SELECT ngram_count FROM ngram_table WHERE ngram LIKE '%collectables%';

ceramics collectables collectibles 55