tombom's questions

在 MySQL 命令行客户端 (Linux) 中，当我通过按CTRL+终止查询时，c这通常工作正常。

mysql (slave) > select sleep(5);
^CCtrl-C -- sending "KILL QUERY 117920686" to server ...
Ctrl-C -- query aborted.
+----------+
| sleep(5) |
+----------+
+----------+
1 row in set (0.78 sec)

然而，当我在我的实时主服务器（在所有服务器中，它必须是这一个......）上做同样的事情时，它以某种方式发送了错误的线程 ID。

mysql (master) > select sleep(5);
^CCtrl-C -- sending "KILL QUERY 770543254" to server ...
Ctrl-C -- query aborted.
+----------+
| sleep(5) |
+----------+
+----------+
1 row in set (5.00 sec) /* <- it obviously hasn't aborted at all */

ID太低了 其他线程的 ID 类似于 26534760326。所有服务器的版本相同：

# yum list installed mysql*
Loaded plugins: dellsysid, rhnplugin, security, ulninfo
This system is receiving updates from RHN Classic or Red Hat Satellite.
Installed Packages
MySQL-python.x86_64                                             1.2.3-0.3.c1.1.el6                              @public_ol6_latest
mysql-community-client.x86_64                                   5.6.27-2.el6                                    @mysql56-community
mysql-community-common.x86_64                                   5.6.27-2.el6                                    @mysql56-community
mysql-community-libs.x86_64                                     5.6.27-2.el6                                    @mysql56-community
mysql-community-libs-compat.x86_64                              5.6.27-2.el6                                    @mysql56-community
mysql-community-release.noarch                                  el6-5                                           @/mysql-community-release-el6-5.noarch
mysql-community-server.x86_64                                   5.6.27-2.el6                                    @mysql56-community

附加信息：

然而，该connection_id()函数返回正确的值。

root@ods01:(none) > select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|     26542310314 |
+-----------------+
1 row in set (0.00 sec)

root@ods01:(none) > select sleep(5);
^CCtrl-C -- sending "KILL QUERY 772506538" to server ...
Ctrl-C -- query aborted.
+----------+
| sleep(5) |
+----------+
+----------+
1 row in set (5.00 sec)

有谁知道如何解决这一问题？

每隔几周或几个月，我的 MySQL 服务器 (5.6.21) 就会使用整个内存，包括交换，尽管它不应该。
服务器仅用作数据仓库的备份从站。
我们还有其他几台 MySQL 服务器，都在同一操作系统上运行相同版本（CentOS 6 运行内核 2.6.32-358.14.1.el6.x86_64），但这是唯一显示此症状的服务器。

我无法观察到具体的查询。但它发生在凌晨 05:00 左右的复制过程中。数据仓库更新在 00:00 到 07:00 之间运行。
除了备份之外，此服务器上也没有读取。备份在 21:00 运行，这不是原因。

目前只有重启mysqld服务才能解决问题。我只是好奇，这是什么原因造成的。

这是目前的情况：

# free -m
             total       used       free     shared    buffers     cached
Mem:         48268      47549        718          0        115        872
-/+ buffers/cache:      46562       1706
Swap:        12087      12087          0

从top输出中可以清楚地看出，只有 MySQL 使用了所有内存：

Tasks:   3 total,   0 running,   3 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni, 99.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  49427328k total, 48695740k used,   731588k free,   119452k buffers
Swap: 12378104k total, 12377212k used,      892k free,   894452k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
21387 root      20   0  103m  344  340 S  0.0  0.0   0:00.05 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql/data ...
22234 mysql     20   0 57.9g  44g 4856 S  0.0 94.8   7501:59 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql/data ...

服务器实际上不应该使用超过 36GB：

        @@key_buffer_size / (1024 * 1024): 32.0000
       @@query_cache_size / (1024 * 1024): 0.0000
@@innodb_buffer_pool_size / (1024 * 1024): 33792.0000
 @@innodb_log_buffer_size / (1024 * 1024): 8.0000
                        @@max_connections: 10
       @@read_buffer_size / (1024 * 1024): 0.1250
   @@read_rnd_buffer_size / (1024 * 1024): 0.2500
       @@sort_buffer_size / (1024 * 1024): 0.2500
       @@join_buffer_size / (1024 * 1024): 0.2500
      @@binlog_cache_size / (1024 * 1024): 0.0313
           @@thread_stack / (1024 * 1024): 0.2500
         @@tmp_table_size / (1024 * 1024): 256.0000
                            MAX_MEMORY_GB: 35.5504

以下是 的重要部分my.cnf：

[mysqld]
max-connections                 =       10
max_connect_errors              =       10000
thread_cache_size               =       50
key_buffer_size                 =       32M
max_allowed_packet              =       128M
query_cache_size                =       0
query_cache_type                =       0
table_open_cache                =       2048
open-files-limit                =       65535
table-definition-cache          =       1024
log_bin                         =       /var/lib/mysql/mysql-bin
binlog-format                   =       MIXED
sync_binlog                     =       0
binlog-checksum                 =       CRC32
binlog-rows-query-log_events    =       1
expire-logs-days                =       1
gtid-mode                       =       on
log-slave-updates               =       true
master-info-repository          =       TABLE
master-verify-checksum          =       1
sync-master-info                =       1
relay-log-info-repository       =       TABLE
innodb_log_group_home_dir       =       /var/lib/mysql/
innodb-file-format              =       BARRACUDA
innodb-log-files-in-group       =       2
innodb_buffer_pool_instances    =       4
innodb_log_file_size            =       512M
innodb_lock_wait_timeout        =       43200
innodb_flush_log_at_trx_commit  =       1
innodb_flush_method             =       O_DIRECT
innodb_buffer_pool_size         =       33G
innodb_file_per_table           =       1
tmp_table_size                  =       256M
max-heap-table-size             =       256M
tmpdir                          =       /var/lib/mysql/tmp
enforce-gtid-consistency        =       true

的输出show engine innodb status：

=====================================
2017-03-10 13:37:31 7f2727bdf700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 27 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 499196 srv_active, 0 srv_shutdown, 1999252 srv_idle
srv_master_thread log flush and writes: 2498448
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 147502997
OS WAIT ARRAY INFO: signal count 268030727
Mutex spin waits 483748333, rounds 5969256182, OS waits 132176123
RW-shared spins 40672399, rounds 210459357, OS waits 2338871
RW-excl spins 112924649, rounds 647917761, OS waits 7715821
Spin rounds per wait: 12.34 mutex, 5.17 RW-shared, 5.74 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 8637239322
Purge done for trx's n:o < 8637239322 undo n:o < 0 state: running but idle
History list length 2341
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 611991, OS thread handle 0x7f2727bdf700, query id 12787999 localhost root init
show engine innodb status
---TRANSACTION 8637239308, not started
MySQL thread id 599812, OS thread handle 0x7f273c0c3700, query id 12781840 Slave has read all relay log; waiting for the slave I/O thread to update it
---TRANSACTION 8637239320, not started
MySQL thread id 1, OS thread handle 0x7f30059a8700, query id 0 Waiting for master to send event
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
38113420 OS file reads, 692678361 OS file writes, 295468168 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 244028, seg size 244030, 1929352 merges
merged operations:
 insert 7499128, delete mark 734782843, delete 66426401
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 73015559, node heap has 35208 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 130331695761980
Log flushed up to   130331695761980
Pages flushed up to 130331695761980
Last checkpoint at  130331695761980
0 pending log writes, 0 pending chkp writes
279646075 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 36263952384; in additional pool allocated 0
Dictionary memory allocated 1991419
Buffer pool size   2162684
Free buffers       516257
Database pages     1611219
Old database pages 594842
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 21809666, not young 8333442679
0.00 youngs/s, 0.00 non-youngs/s
Pages read 37539158, created 237144171, written 399299350
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1611219, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   540671
Free buffers       129625
Database pages     402230
Old database pages 148499
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5461997, not young 2089105440
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9402002, created 59409061, written 106858985
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 402230, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   540671
Free buffers       128947
Database pages     402917
Old database pages 148753
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5443941, not young 2080427002
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9381960, created 59195515, written 96676260
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 402917, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   540671
Free buffers       129695
Database pages     402173
Old database pages 148475
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5453587, not young 2090513432
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9386136, created 59292596, written 96867291
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 402173, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   540671
Free buffers       127990
Database pages     403899
Old database pages 149115
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5450141, not young 2073396805
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9369060, created 59246999, written 98896814
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 403899, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 22234, id 139806582294272, state: sleeping
Number of rows inserted 23128153712, updated 2139463431, deleted 353665279, read 10407801812
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

当 xtrabackup 杀死从属 SQL 线程时，我在错误日志中收到的唯一消息是关于每日备份的。

我不知道还有什么地方可以看，或者 innodb 监视器输出中可能有什么可疑之处。谷歌搜索失败了，因为我得到的只是 MySQL 如何使用内存的常见文章。

最近我注意到我的 ETL 过程将数据加载到暂存区的部分有时需要更长的时间，有时更短。

通过以下查询（在集成服务目录数据库中执行），我比较了两个不同的运行并发现重新创建表语句花费的时间更长（有时也是加载部分，但我认为主要问题是这个） . 这是比较不同运行的查询：

select
es1.execution_path,
es1.execution_duration as es1dura,
es2.execution_duration as es2dura,
(es2.execution_duration * 1.0) / es1.execution_duration * 100
from
catalog.executable_statistics es1
join catalog.executable_statistics es2 on es1.execution_path = es2.execution_path
where es1.execution_id = 239
and es2.execution_id = 10290
and es1.execution_path like '%create table%'
order by 
--(es2.execution_duration * 1.0) / es1.execution_duration * 100 desc
es1.execution_path

部分结果是这样的：

这对应于 ETL 的以下部分：

请不要介意屏幕截图中的额外绿线，这只是 Visual Studio 中的混乱格式。没有更多的任务并行运行，作业在午夜左右运行，没有其他作业同时在数据库上运行。

这些任务非常简单。例如，其中一项Recreate table任务如下所示：

IF EXISTS (
    SELECT * FROM sys.tables
    WHERE name = 'admin_perso_abteilung'
)
DROP TABLE admin_perso_abteilung
GO
CREATE TABLE admin_perso_abteilung (
    [id] int,
    [perso_abteilung] nvarchar(50)
)
GO

总之，问题是，从查询结果来看，整个流程卡住了一段时间，才并行执行recreate table语句，请问是什么原因导致的呢？我可以进一步检查什么以缩小问题范围？

由于我更像是一名开发人员而不是管理员，所以我在这里有点迷路，请多多指导。谢谢。

我进行了全新的 SQL Server 2014 安装。接下来，我想从以前的 SQL Server 2012 服务器附加一些数据库。我将所有mdf和ldf文件复制到数据目录，但是当我尝试附加它们时，我收到了以下错误消息：

CREATE FILE在尝试打开或创建物理文件
“D:\SQLSERVERDATA\MSSQL12.BIDEV\MSSQL\DATA\controlling_demo.mdf”时遇到操作系统错误 5（拒绝访问） 。
（Microsoft SQL Server，错误：5123）

当我检查文件安全设置（右键单击文件、属性、安全）时，它说我没有读取权限。我用来复制文件的帐户在管理员组中。我可以完全控制该DATA文件夹，但是，只有我在那里复制的文件受到限制。

当我尝试对整个文件夹重新应用安全权限时，它说：

无法枚举容器中的对象。访问被拒绝。

当我将文件复制到其他地方时，我拥有访问权限。

问题是，我怎样才能获得文件DATA夹中这些文件的访问权限？

服务器“servername”的还原失败。(Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError：RESTORE 无法启动数据库“ECP”。(Microsoft.SqlServer.Smo)

数据库已创建，但无法使用对象资源管理器访问：

无法访问数据库 ECP。（对象浏览器）

感谢您提供有关如何使数据库正常工作的任何建议。

编辑：这个查询

select state_desc from sys.databases where name = 'ECP';

返回RECOVERY_PENDING。

我再次删除了数据库并重新尝试了

restore verifyonly from disk = 'path\file';

这返回

尝试还原此备份可能会遇到存储空间问题。后续消息将提供详细信息。
“F:\ECPDATA1\ECPDATA1.mdf”指定的路径不在有效目录中。
文件“F:\ECPDATA2\ECPDATA2.ndf”的目录查找失败，出现操作系统错误 3（无法检索此错误的文本。原因：15100）。
文件“F:\ECPDATA3\ECPDATA3.ndf”的目录查找失败，出现操作系统错误 3（无法检索此错误的文本。原因：15105）。
文件“F:\ECPDATA4\ECPDATA4.ndf”的目录查找失败，出现操作系统错误 3（无法检索此错误的文本。原因：15105）。
文件“E:\ECPLOG1\ECPLOG1.ldf”的目录查找失败，出现操作系统错误 21（无法检索此错误的文本。原因：15105）。
文件 1 上的备份集有效。

既然它说备份集是有效的，我只需要指定文件，让它工作，对吧？但是在这台服务器上我没有驱动器E:或F:. 如何在备份文件中进行调整？还是有可能（我对 windows-server-2008-r2 不太熟悉）在 linux 上拥有某种符号链接？