首页
网站首页
公司简介
资讯中心
推荐内容
返回顶部
MySQL高可用之PXC安装部署,MySQL查询显示连续的结果
发布时间:2019-12-28 20:43
浏览次数:

#mysql中 对于查询结果只体现n条一而再再而三行的难题#

在领扣上超越的贰个难题:求满足条件的总是3行结果的来得

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, date, people;
Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).
For example, the table stadium:
+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+

For the sample data above, the output is:
+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+

1.率先先举行理并了结果集的询问

select id,date,people from stadium where people>=100;

2.给查询的结果集扩大三个自增列

SELECT @newid:=@newid+1 AS newid,test.* 
FROM(SELECT @newid:=0)r, test WHERE people>100

3.自增列和id的差值 相仿即三番两次

SELECT @newid:=@newid+1 AS newid,test.* ,@cha:=id-@newid AS cha 
FROM(SELECT @newid:=0)r, test WHERE people>100

4.将同意气风发的差值 放在同样张表中,并抽出三番两次数量超越3的

select if(count(id)>=3,count_concat(id),null)e from(
SELECT @newid:=@newid+1 AS newid,test.* ,@cha:=id-@newid AS cha 
FROM(SELECT @newid:=0)r, test WHERE people>100)
as d group by cha

5.将上步拿到的表和主表 拿到所要求的

SELECT id,DATE,people FROM test,
(SELECT IF (COUNT(id)>3,GROUP_CONCAT(id),NULL)e 
FROM (SELECT @newid:=@newid+1 AS newid,test.* ,@cha:=id-@newid AS cha 
FROM(SELECT @newid:=0)r, test WHERE people>100)AS d   GROUP BY cha ) AS f 
WHERE f.e IS NOT NULL AND FIND_IN_SET(id,f.e);

听新闻说还足以用存款和储蓄进程来成功,可是自个儿没尝试,稍后尝试

以上

一. 概述

  上次在介绍品质调优中讲到了I/O的费用查看及维护,本次介绍CPU的支付及爱慕, 在调优方面是足以从八个维度去发掘难题如I/O,CPU,  内部存款和储蓄器,锁等,不管从哪个维度去化解,都能达到规定的规范调优的功力,因为sql server系统作为一个全体性,它都以牢牢相连的,譬如:消除了sql语句中I/O费用超级多的难点,那对应的CPU花销也会收缩,反之解决了CPU开支最多的,那对应I/O费用也会压缩。消除I/O开销后CPU耗费时间也减弱,是因为CPU下的Worker线程必要扫描I/O页数就少了,现身的能源锁的窒碍也回退了,具体可仿照效法cpu的原理

  下面sql语句的dmv:sys.dm_MySQL高可用之PXC安装部署,MySQL查询显示连续的结果。exec_query_stats和sys.dm_exec_sql_text 已经在上篇”sql server 质量调优 I/O开支分析“中有讲到。

--查询编译以来 cpu耗时总量最多的前50条(Total_woker_time)
SELECT TOP 50
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],
    execution_count [运行次数],
    qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
    last_execution_time AS [最后一次执行时间],
    max_worker_time /1000 AS [最大执行时间(ms)],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
        (CASE WHEN qs.statement_end_offset = -1 
        THEN DATALENGTH(qt.text) 
        ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) 
    AS [使用CPU的语法], qt.text [完整语法],
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY  total_worker_time DESC

询问如下图所示,彰显CPU耗费时间总的数量最多的前50条

图片 1

在排行第38条,拿出耗费时间的sql脚本来深入分析,开采未走索引。如下图

图片 2

SELECT [PO_NO],[Qty] FROM [ORD_PurchaseLine] WITH(NOLOCK) WHERE ([PO_NO] IN (' ')) 

图片 3

前日一起事反馈使用SQL Server 2012 Management Studio老是SQL Server 二零一四后,接受数据库中有个别表,然后单击右键时,就能够遭逢上面错误:

 

二. 维护注意点

  1.  在生育数据库下,CPU耗费时间查询,并不限量只逐个审查总耗费时间前50条,能够是前100~200条。具体看sql脚本未有没优化的急需,并非各样表的查询都必得走索引。如:有的表不走索引时并不会深感很耗费时间平均I/0次数少,表中已建的目录原来就有多少个,增删改也往往,还会有索引占用空间,这时候要求衡量。 

-- 快速查看索引数量
sp_help [RFQ_PurDemandDetail]

  图片 4

 2. 不用在办事时间保安徽大学表索引

    当大家各个核实到有的大表缺点和失误索引,数据在100w以上,假若在干活时间来维护索引,不管是创制索引依旧重新建立索引都会诱致表的围堵, 这里表的响应会变慢或许直接卡死,前端应用程序直接呼吁超时。这里须求专一的。来看下新建三个索引的脚本会开采开启了行锁与页锁(ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)。

CREATE NONCLUSTERED INDEX [ix_createtime] ON [dbo].[PUB_Search_Log] 
(
    [CreateTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
 IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

 

Preface

图片 5

 

 

    Yesterday I implemented a three-nodes PXC,but there were some errors when proceeding it.So I decide to research what's wrong with it.The operations and evidences shows below.

本条似是而非初看以为是权力难点,后面验证、检查、查找资料过后,发掘那几个是贰个bug来的,需求提高或利用SQL Server Management Studio工具的补丁来减轻那些主题材料。下边链接有描述那些主题材料的相干资料,特此记录一下,方便遭受此类难题的人追寻、检索。

 

 

Procedure

http://dba.stackexchange.com/questions/118452/error-message-view-server-state-permission-was-denied-when-connecting-to-sql

 

 

Fisrt of all,I find out that yesterday's error is due to lack of "socat" package on first node zlm2.

https://connect.microsoft.com/SQLServer/feedback/details/791630/cannot-view-properties-of-database-when-using-db-owner-account

 1 //Here's the error log of it.
 2 2018-08-08T08:48:18.198487+01:00 0 [Note] WSREP: Initiating SST/IST transfer on DONOR side (wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21')
 3 2018-08-08T08:48:18.200272+01:00 2 [Note] WSREP: DONOR thread signaled with 0
 4 which: no socat in (/usr/sbin:/sbin:/opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/zabbix/bin:/usr/local/zabbix/sbin)
 5     2018-08-08T06:48:18.782344Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
 6     2018-08-08T06:48:18.784043Z WSREP_SST: [ERROR] socat not found in path: /usr/sbin:/sbin:/opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/zabbix/bin:/usr/local/zabbix/sbin
 7     2018-08-08T06:48:18.785926Z WSREP_SST: [ERROR] ****************************************************** 
 8 2018-08-08T08:48:18.786496+01:00 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21': 2 (No such file or directory)
 9 2018-08-08T08:48:18.786587+01:00 0 [ERROR] WSREP: Command did not run: wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21'
10 2018-08-08T08:48:18.787610+01:00 0 [Warning] WSREP: 1.0 (zlm2): State transfer to 0.0 (zlm3) failed: -2 (No such file or directory)
11 
12 [root@zlm2 08:53:31 /data/mysql/mysql3308/data]
13 #rpm -qa|grep socat
14 
15 [root@zlm2 09:11:08 /data/mysql/mysql3308/data]
16 #yum install socat
17 ... //Omitted.
18 
19 Installed:
20   socat.x86_64 0:1.7.3.2-2.el7                                                                                                                       
21 
22 Complete!

 

Restart the PXC mysqld on zlm2.

友情链接: 网站地图
Copyright © 2015-2019 http://www.nflfreepicks.net. 新葡萄京娱乐场网址有限公司 版权所有