Finology 大数据金融

通过大数据以量化金融

0%

发表于 更新于 分类于 ， 阅读次数： 评论数：

通过Navicat客户端连接Mysql数据库时，出现too many connections的提示，可以进行如下操作

登录mysql数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2965
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

查看连接数

1
2
3
4
5
6
7
8
mysql> show processlist;
+------+------+---------------------+--------------+---------+------+----------+------------------+
| Id   | User | Host                | db           | Command | Time | State    | Info             |
+------+------+---------------------+--------------+---------+------+----------+------------------+
| 2821 | root | 192.168.1.124:58526 | testdb       | Sleep   | 7180 |          | NULL             |
| 2822 | root | 192.168.1.124:58527 | testdb       | Sleep   | 7180 |          | NULL             |
| 2840 | root | 192.168.1.124:58741 | testdb       | Sleep   | 5790 |          | NULL             |
| 2841 | root | 192.168.1.124:58742 | testdb       | Sleep   | 5820 |          | NULL             |

将会发现有很多连接是处于Sleep状态的，这些连接是暂时没有用的，可以删除掉。如何删除请继续往下看。

查看最大连接数

1
2
3
4
5
6
7
mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.01 sec)

当processlist的数量大于查询到的max_connections时，就会出现too many connections的提示信息。

修改最大连接数

1
set GLOBAL max_connections=1000;

这不是一劳永逸的方法，应该要让它自动杀死那些sleep的进程。

查看关闭一个连接的等待时间，并修改

1
2
show global variables like 'wait_timeout';
set global wait_timeout=300;

修改这个数值，这里可以随意，最好控制在几分钟内。

其他待验证

set global interactive_timeout=500;
修改这个数值，表示mysql在关闭一个连接之前要等待的秒数，至此可以让mysql自动关闭那些没用的连接，但要注意的是，正在使用的连接到了时间也会被关闭，因此这个时间值要合适

批量kill之前没用的sleep连接，在网上搜索的方法对我都不奏效，因此只好使用最笨的办法，一个一个kill

select concat(‘KILL ‘,id,’;’) from information_schema.processlist where user=’root’;

先把要kill的连接id都查询出来
复制中间的kill id;内容到word文档
替换掉符号“|”和回车符（在word中查询^p即可查询到回车符）
把修改过的内容复制回终端，最后按回车执行

产生这种问题的原因是：

连接数超过了 MySQL 设置的值，与 max_connections 和 wait_timeout 都有关系。wait_timeout 的值越大，连接的空闲等待就越长，这样就会造成当前连接数越大。

解决方法：

修改MySQL配置文件/etc/my.cnf,设置成max_connections=1000,wait_timeout=5。如果没有此项设置可以自行添加，修改后重启MySQL服务即可。要不经常性报此错误，则要对服务器作整体性能优化

注：

为了防止发生too many connections时候无法登录的问题，mysql manual有如下的说明：

mysqld actually allows max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have theSUPER privilege. By granting the SUPER privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected.

因此, 必须只赋予root用户的SUPER权限，同时所有数据库连接的帐户不能赋予SUPER权限。前面说到的报错后无法登录就是由于我们的应用程序直接配置的root用户

总结，解决问题的最终方法：

1.修改配置文件/etc/my.cnf,调整连接参数

2.检查程序代码，对于没有关闭的链接及时进行关闭

发表于 更新于 阅读次数： 评论数：

下载MAC版本安装包

进入页面https://www.elastic.co/downloads/apm ，选择mac平台的安装包 下载文件到/usr/local/elasticsearch/elasticsearch-6.4.2.tar.gz

解压文件

1
$ sudo tar -zxvf elasticsearch-6.4.2.tar.gz

运行

1
2
3
4
5
6
7
8
9
10
11
$ bin/elasticsearch
Exception in thread "main" java.nio.file.AccessDeniedException: /usr/local/elasticsearch/elasticsearch-6.4.2/config/jvm.options
	at sun.nio.fs.UnixException.translateToIOException(UnixException.java:84)
	at sun.nio.fs.UnixException.rethrowAsIOException(UnixException.java:102)
	at sun.nio.fs.UnixException.rethrowAsIOException(UnixException.java:107)
	at sun.nio.fs.UnixFileSystemProvider.newByteChannel(UnixFileSystemProvider.java:214)
	at java.nio.file.Files.newByteChannel(Files.java:361)
	at java.nio.file.Files.newByteChannel(Files.java:407)
	at java.nio.file.spi.FileSystemProvider.newInputStream(FileSystemProvider.java:384)
	at java.nio.file.Files.newInputStream(Files.java:152)
	at org.elasticsearch.tools.launchers.JvmOptionsParser.main(JvmOptionsParser.java:60)

权限不够，查看一下，登录用户是没有修改文件的权限的

1
2
3
4
5
6
7
8
9
10
11
$ ls -l elasticsearch-6.4.2

-rw-r--r--@  1 root  wheel   13675 Sep 26 21:30 LICENSE.txt
-rw-r--r--@  1 root  wheel  401465 Sep 26 21:38 NOTICE.txt
-rw-r--r--@  1 root  wheel    8511 Sep 26 21:30 README.textile
drwxr-xr-x@ 43 root  wheel    1376 Sep 26 21:38 bin
drwxr-xr-x@  9 root  wheel     288 Sep 26 21:38 config
drwxr-xr-x@ 42 root  wheel    1344 Sep 26 21:38 lib
drwxr-xr-x@  2 root  wheel      64 Sep 26 21:38 logs
drwxr-xr-x@ 27 root  wheel     864 Sep 26 21:38 modules
drwxr-xr-x@  2 root  wheel      64 Sep 26 21:38 plugins

修改权限

1
$ sudo chown -R simon elasticsearch-6.4.2

再次启动elasticsearch程序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
$ bin/elasticsearch
[2018-10-17T10:04:22,029][INFO ][o.e.n.Node               ] [] initializing ...
[2018-10-17T10:04:22,100][INFO ][o.e.e.NodeEnvironment    ] [VhQno38] using [1] data paths, mounts [[/ (/dev/disk1s1)]], net usable_space [353.6gb], net total_space [465.6gb], types [apfs]
[2018-10-17T10:04:22,101][INFO ][o.e.e.NodeEnvironment    ] [VhQno38] heap size [990.7mb], compressed ordinary object pointers [true]
[2018-10-17T10:04:22,104][INFO ][o.e.n.Node               ] [VhQno38] node name derived from node ID [VhQno38hQfO03l3Sxt6Bnw]; set [node.name] to override
[2018-10-17T10:04:22,104][INFO ][o.e.n.Node               ] [VhQno38] version[6.4.2], pid[12313], build[default/tar/04711c2/2018-09-26T13:34:09.098244Z], OS[Mac OS X/10.13.6/x86_64], JVM[Oracle Corporation/Java HotSpot(TM) 64-Bit Server VM/1.8.0_181/25.181-b13]
[2018-10-17T10:04:22,104][INFO ][o.e.n.Node               ] [VhQno38] JVM arguments [-Xms1g, -Xmx1g, -XX:+UseConcMarkSweepGC, -XX:CMSInitiatingOccupancyFraction=75, -XX:+UseCMSInitiatingOccupancyOnly, -XX:+AlwaysPreTouch, -Xss1m, -Djava.awt.headless=true, -Dfile.encoding=UTF-8, -Djna.nosys=true, -XX:-OmitStackTraceInFastThrow, -Dio.netty.noUnsafe=true, -Dio.netty.noKeySetOptimization=true, -Dio.netty.recycler.maxCapacityPerThread=0, -Dlog4j.shutdownHookEnabled=false, -Dlog4j2.disable.jmx=true, -Djava.io.tmpdir=/var/folders/2h/4vmz9_k14pb4x5338vs98nmw0000gn/T/elasticsearch.98Xlp9JQ, -XX:+HeapDumpOnOutOfMemoryError, -XX:HeapDumpPath=data, -XX:ErrorFile=logs/hs_err_pid%p.log, -XX:+PrintGCDetails, -XX:+PrintGCDateStamps, -XX:+PrintTenuringDistribution, -XX:+PrintGCApplicationStoppedTime, -Xloggc:logs/gc.log, -XX:+UseGCLogFileRotation, -XX:NumberOfGCLogFiles=32, -XX:GCLogFileSize=64m, -Des.path.home=/usr/local/elasticsearch/elasticsearch-6.4.2, -Des.path.conf=/usr/local/elasticsearch/elasticsearch-6.4.2/config, -Des.distribution.flavor=default, -Des.distribution.type=tar]
[2018-10-17T10:04:24,127][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [aggs-matrix-stats]
[2018-10-17T10:04:24,127][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [analysis-common]
[2018-10-17T10:04:24,127][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [ingest-common]
[2018-10-17T10:04:24,127][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [lang-expression]
[2018-10-17T10:04:24,127][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [lang-mustache]
[2018-10-17T10:04:24,128][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [lang-painless]
[2018-10-17T10:04:24,128][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [mapper-extras]
[2018-10-17T10:04:24,128][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [parent-join]
[2018-10-17T10:04:24,128][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [percolator]
[2018-10-17T10:04:24,128][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [rank-eval]
[2018-10-17T10:04:24,128][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [reindex]
[2018-10-17T10:04:24,128][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [repository-url]
[2018-10-17T10:04:24,129][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [transport-netty4]
[2018-10-17T10:04:24,129][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [tribe]
[2018-10-17T10:04:24,129][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [x-pack-core]
[2018-10-17T10:04:24,130][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [x-pack-deprecation]
[2018-10-17T10:04:24,130][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [x-pack-graph]
[2018-10-17T10:04:24,130][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [x-pack-logstash]
[2018-10-17T10:04:24,131][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [x-pack-ml]
[2018-10-17T10:04:24,131][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [x-pack-monitoring]
[2018-10-17T10:04:24,131][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [x-pack-rollup]
[2018-10-17T10:04:24,131][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [x-pack-security]
[2018-10-17T10:04:24,131][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [x-pack-sql]
[2018-10-17T10:04:24,131][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [x-pack-upgrade]
[2018-10-17T10:04:24,131][INFO ][o.e.p.PluginsService     ] [VhQno38] loaded module [x-pack-watcher]
[2018-10-17T10:04:24,132][INFO ][o.e.p.PluginsService     ] [VhQno38] no plugins loaded
[2018-10-17T10:04:27,786][INFO ][o.e.x.s.a.s.FileRolesStore] [VhQno38] parsed [0] roles from file [/usr/local/elasticsearch/elasticsearch-6.4.2/config/roles.yml]
[2018-10-17T10:04:28,270][INFO ][o.e.x.m.j.p.l.CppLogMessageHandler] [controller/12332] [Main.cc@109] controller (64 bit): Version 6.4.2 (Build 660eefe6f2ea55) Copyright (c) 2018 Elasticsearch BV
[2018-10-17T10:04:28,685][DEBUG][o.e.a.ActionModule       ] Using REST wrapper from plugin org.elasticsearch.xpack.security.Security
[2018-10-17T10:04:28,911][INFO ][o.e.d.DiscoveryModule    ] [VhQno38] using discovery type [zen]
[2018-10-17T10:04:30,161][INFO ][o.e.n.Node               ] [VhQno38] initialized
[2018-10-17T10:04:30,161][INFO ][o.e.n.Node               ] [VhQno38] starting ...
[2018-10-17T10:04:30,380][INFO ][o.e.t.TransportService   ] [VhQno38] publish_address {127.0.0.1:9300}, bound_addresses {[::1]:9300}, {127.0.0.1:9300}
[2018-10-17T10:04:33,445][INFO ][o.e.c.s.MasterService    ] [VhQno38] zen-disco-elected-as-master ([0] nodes joined)[, ], reason: new_master {VhQno38}{VhQno38hQfO03l3Sxt6Bnw}{jy1_1dpHToOxN2CCtH5ztw}{127.0.0.1}{127.0.0.1:9300}{ml.machine_memory=8589934592, xpack.installed=true, ml.max_open_jobs=20, ml.enabled=true}
[2018-10-17T10:04:33,450][INFO ][o.e.c.s.ClusterApplierService] [VhQno38] new_master {VhQno38}{VhQno38hQfO03l3Sxt6Bnw}{jy1_1dpHToOxN2CCtH5ztw}{127.0.0.1}{127.0.0.1:9300}{ml.machine_memory=8589934592, xpack.installed=true, ml.max_open_jobs=20, ml.enabled=true}, reason: apply cluster state (from master [master {VhQno38}{VhQno38hQfO03l3Sxt6Bnw}{jy1_1dpHToOxN2CCtH5ztw}{127.0.0.1}{127.0.0.1:9300}{ml.machine_memory=8589934592, xpack.installed=true, ml.max_open_jobs=20, ml.enabled=true} committed version [1] source [zen-disco-elected-as-master ([0] nodes joined)[, ]]])
[2018-10-17T10:04:33,473][INFO ][o.e.x.s.t.n.SecurityNetty4HttpServerTransport] [VhQno38] publish_address {127.0.0.1:9200}, bound_addresses {[::1]:9200}, {127.0.0.1:9200}
[2018-10-17T10:04:33,474][INFO ][o.e.n.Node               ] [VhQno38] started
[2018-10-17T10:04:33,482][WARN ][o.e.x.s.a.s.m.NativeRoleMappingStore] [VhQno38] Failed to clear cache for realms [[]]
[2018-10-17T10:04:33,536][INFO ][o.e.g.GatewayService     ] [VhQno38] recovered [0] indices into cluster_state
[2018-10-17T10:04:33,699][INFO ][o.e.c.m.MetaDataIndexTemplateService] [VhQno38] adding template [.triggered_watches] for index patterns [.triggered_watches*]
[2018-10-17T10:04:33,721][INFO ][o.e.c.m.MetaDataIndexTemplateService] [VhQno38] adding template [.watches] for index patterns [.watches*]
[2018-10-17T10:04:33,774][INFO ][o.e.c.m.MetaDataIndexTemplateService] [VhQno38] adding template [.watch-history-9] for index patterns [.watcher-history-9*]
[2018-10-17T10:04:33,807][INFO ][o.e.c.m.MetaDataIndexTemplateService] [VhQno38] adding template [.monitoring-logstash] for index patterns [.monitoring-logstash-6-*]
[2018-10-17T10:04:33,870][INFO ][o.e.c.m.MetaDataIndexTemplateService] [VhQno38] adding template [.monitoring-es] for index patterns [.monitoring-es-6-*]
[2018-10-17T10:04:33,893][INFO ][o.e.c.m.MetaDataIndexTemplateService] [VhQno38] adding template [.monitoring-beats] for index patterns [.monitoring-beats-6-*]
[2018-10-17T10:04:33,912][INFO ][o.e.c.m.MetaDataIndexTemplateService] [VhQno38] adding template [.monitoring-alerts] for index patterns [.monitoring-alerts-6]
[2018-10-17T10:04:33,932][INFO ][o.e.c.m.MetaDataIndexTemplateService] [VhQno38] adding template [.monitoring-kibana] for index patterns [.monitoring-kibana-6-*]
[2018-10-17T10:04:34,025][INFO ][o.e.l.LicenseService     ] [VhQno38] license [b8e36ec7-af24-44d2-8e35-2a8c543c8aaf] mode [basic] - valid

当看到组件 started 后，代表elasticsearch已经成功启动。

1
[2018-10-17T10:04:33,474][INFO ][o.e.n.Node               ] [VhQno38] started

发表于 更新于 分类于 ， 阅读次数： 评论数：

mysql在非正常关闭后，再次启动时，会报错。客户端也连接不上。

连接时的错误信息：

1
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

尝试重启mysql服务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# systemctl status mysqld.service
● mysqld.service - SYSV: MySQL database server.
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: failed (Result: exit-code) since 二 2018-09-25 10:48:39 CST; 1min 28s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 3351 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=1/FAILURE)

9月 25 10:48:38 deploy systemd[1]: Starting SYSV: MySQL database server....
9月 25 10:48:39 deploy mysqld[3351]: MySQL Daemon failed to start.
9月 25 10:48:39 deploy mysqld[3351]: Starting mysqld:  [FAILED]
9月 25 10:48:39 deploy systemd[1]: mysqld.service: control process exited, code=exited status=1
9月 25 10:48:39 deploy systemd[1]: Failed to start SYSV: MySQL database server..
9月 25 10:48:39 deploy systemd[1]: Unit mysqld.service entered failed state.
9月 25 10:48:39 deploy systemd[1]: mysqld.service failed.

查看错误日志

1
2
3
4
5
6
7
8
9
10
# journalctl -xe
9月 25 10:45:47 deploy systemd[1]: mysqld.service: control process exited, code=exited status=1
9月 25 10:45:47 deploy systemd[1]: Failed to start SYSV: MySQL database server..
-- Subject: Unit mysqld.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysqld.service has failed.
--
-- The result is failed.

解决方案

在mysql非正常关闭时，/var/run/mysqld会消失掉。我们查看/var/run/mysqld文件夹是否不存在，如果是，那重新建立此文件夹/var/run/mysqld，并设置权限
chown mysql.mysql /var/run/mysqld

再次重启，正常启动