Spring Data JPA 手动释放当前数据库会话

公司一次项目事故,项目在正常测试时没有问题,在实际生产并发场景下发生了线程锁死的状况。

后来分析原因,项目中在数据库查询时使用了多线程的写法。项目使用Spring Data JPA, 配置了

spring.jpa.open-in-view: True

该配置是为了关联模型查询方便,但是导致查询结束,当前访问会话未结算的情况下,数据库不释放连接。
后续子线程会尝试继续申请数据库连接,进而导致这种写法情况下,应用会为每次访问分步骤至少请求2个数据库连接资源。

该模型场景在并发量小的情况下,数据库连接数量充裕的时候并不会发生问题。
但是遇到高并发场景,大量前端会话在同时占用了主线程数据库连接而又未在子线程申请到连接的时候,就会发生进程锁死,进而导致整个应用假死的情况。

后来发现这个问题之后,解决思路是尝试获取到主线程jpa的当前会话,在主线程查询之后关闭会话,经过试验貌似可以解决问题。

Session session = (Session) jpaContext.getEntityManagerByManagedType(PriceTicket.class).getDelegate();
session.close();

下面是该文件完整代码

/*
 * Created by Wanghw 2018/3/29.
 */

package com.example.demo;

import com.example.demo.jpa.PriceTicket;
import com.example.demo.jpa.PriceTicketRepository;
import org.hibernate.Session;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaContext;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Callable;
import java.util.concurrent.Future;

@RestController
@RequestMapping("/mulThread")
public class TestMulThread {

    @Autowired
    private PriceTicketRepository priceTicketRepository;

    @Autowired
    private BeanFactory beanFactory;

    @Resource(name="myExecutor")
    private ThreadPoolTaskExecutor executor;

    @Autowired
    private JpaContext jpaContext;

    @GetMapping()
    public ResultDTO list(){

        Pageable page = new PageRequest(0,1);
        // 如果配置为 spring.jpa.open-in-view: True(默认是True)。主线程会占用一个数据库连接,直到该访问会话结束
        priceTicketRepository.findAll(page);

        Session session = (Session) jpaContext.getEntityManagerByManagedType(PriceTicket.class).getDelegate();    //尝试获取当前jpa会话,强制结束会话。
        session.close();

        List<Future<Integer>> tasks= new ArrayList<>();
        // 这里会起多个子线程进行数据库查询,会尝试获取数据库连接
        for (int  i = 0; i<4 ; i++){
            ProductPriceCalculateTask task =  beanFactory.getBean(ProductPriceCalculateTask.class);
            task.init(i);
            tasks.add(executor.submit(task));
        }

        try {
            for (Future<Integer> productPriceTask : tasks){
                if(productPriceTask!=null){
                    Integer pt = productPriceTask.get();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return ResultDTO.builder().isSuccess(true).build();
    }

    @Component
    @Scope("prototype")
    public static class ProductPriceCalculateTask implements Callable<Integer> {
        @Autowired
        private PriceTicketRepository priceTicketRepository;

        private Integer index;

        public void init(Integer index) {
            this.index=index;
        }

        @Override
        public Integer call() throws Exception {
            Pageable page = new PageRequest(0,1);
            priceTicketRepository.findAll(page);
            return index;
        }
    }
}

Linux CentOS 7 OpenVPN Server 安装配置

OpenVPN是一个开源应用程序,可以通过公共Internet创建一个专用网络。在我们开始之前,我们首先需要安装Enterprise Linux(EPEL)存储库的额外包。下面就是关于如何在CentOS 7上设置Client和OpenVPN服务器的教程。

1.安装EPEL套件库,顺道更新
yum -y install epel-release && yum -y update
2.安装 OpenVPN

首先我们需要安装OpenVPN。我们还将安装Easy RSA来生成我们的SSL密钥对,这将保护我们的VPN连接。

yum install -y openvpn easy-rsa
3.生成密钥及证书的前置条件
3.1.创建密钥与证书储存目录,并且准备好要产生密钥与证书的相关文件:
mkdir -p /etc/openvpn/easy-rsa/keys
cp -rf /usr/share/easy-rsa/3.0.3/* /etc/openvpn/easy-rsa
cp /etc/openvpn/easy-rsa/openssl-1.0.0.cnf /etc/openvpn/easy-rsa/openssl.cnf
3.2.修改凭证的默认资料,之后产生凭证时就会自动填写,不必再手动输入。
vim /etc/openvpn/easy-rsa/vars

可以在https://github.com/OpenVPN/easy-rsa 上面找到模板vars

    wget -c https://github.com/OpenVPN/easy-rsa/archive/master.zip

这里提供一个模板

set_var EASYRSA                 "$PWD"
set_var EASYRSA_PKI             "$EASYRSA/pki"
set_var EASYRSA_DN              "cn_only"
set_var EASYRSA_REQ_COUNTRY     "CN"
set_var EASYRSA_REQ_PROVINCE    "Jiangsu"
set_var EASYRSA_REQ_CITY        "Nanjing"
set_var EASYRSA_REQ_ORG         "XIAOCAICAI"
set_var EASYRSA_REQ_EMAIL       "admin@xiaocaicai.com.com"
set_var EASYRSA_REQ_OU          "XIAOCAICAI EASY CA"
set_var EASYRSA_KEY_SIZE        2048
set_var EASYRSA_ALGO            rsa
set_var EASYRSA_CA_EXPIRE       7500
set_var EASYRSA_CERT_EXPIRE     3650
set_var EASYRSA_NS_SUPPORT      "no"
set_var EASYRSA_NS_COMMENT      "XIAOCAICAI CERTIFICATE AUTHORITY"
set_var EASYRSA_EXT_DIR         "$EASYRSA/x509-types"
set_var EASYRSA_SSL_CONF        "$EASYRSA/openssl-1.0.cnf"
set_var EASYRSA_DIGEST          "sha256"
3.3.初始化PKI

首先需要把pki的目录创建好,以前的版本是会要求执行一个clean-all脚本。

$ ./easyrsa init-pki

Note: using Easy-RSA configuration from: ./vars

init-pki complete; you may now create a CA or requests.
Your newly created PKI dir is: /Users/sskaje/Work/CA/Easy/pki
3.4.创建CA

由于设置了 EASYRSA_DN 为 cn_only,所以创建CA时比较简单。
如果设置成 org 则会要求输入很多项目,或者从 vars 文件里加载。

$ ./easyrsa build-ca

Note: using Easy-RSA configuration from: ./vars
Generating a 2048 bit RSA private key
...................................................................................................................................+++
........+++
writing new private key to '/Users/sskaje/Work/CA/Easy/pki/private/ca.key.NoYqS71N95'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Common Name (eg: your user, host, or server name) [Easy-RSA CA]:SSKAJE CERTIFICATE AUTHORITY 

CA creation complete and you may now import and sign cert requests.
Your new CA certificate file for publishing is at:
3.5.创建DH参数

Diffie hellman参数使用的是 openssl dhparam 创建的,openvpn文档里提到的size只有1024和2048,所以vars文件里的 EASYRSA_KEY_SIZE 我没设成4096。
easyrsa 3 的命令是
$ ./easyrsa gen-dh

Note: using Easy-RSA configuration from: ./vars
Generating DH parameters, 2048 bit long safe prime, generator 2
This is going to take a long time
...
DH parameters of size 2048 created at /Users/sskaje/Work/CA/Easy/pki/dh.pem
3.6.生成证书

生成证书的操作步骤就两步,生成请求文件,根据请求文件签发证书。
easy-rsa 3.0签发证书时要求制定type,可选的值参考x509-types目录下的文件名,包括

server:TLS服务端,适用于https服务端和vpn服务端
client:TLS客户端,适用于web浏览器和vpn客户端
ca:签发子CA证书
gen-req, build-client-full, build-server-full 可以使用 nopass 参数生成不加密的私钥。
可以使用 build-client-full 和 build-server-full 直接完成 gen-req 和 sign-req 的过程。

$ ./easyrsa build-server-full xiaocaicai-server nopass
$ ./easyrsa build-client-full xiaocaicai-client nopass
3.7.撤销证书

使用 revoke 撤销证书,命令格式如下:
./easyrsa revoke NAME

3.8.生成CRL文件

CRL是证书撤销列表,有些软件会需要,包括OpenVPN。

$ ./easyrsa gen-crl
3.9.查看证书/查看请求文件

使用 show-cert 和 show-req 查看请求文件,参数是请求时的名字。

3.10.更新数据库
$ ./easyrsa update-db
3.11.将Server的密钥及凭证放到主目录里:
cp ./pki/dh.pem ./pki/ca.crt ./pki/issued/xiaocaicai-server.crt ./pki/private/xiaocaicai-server.key /etc/openvpn
4.配置 OpenVPN server.conf

OpenVPN安装目录默认存在 server.conf 配置文件,我们拷贝一份并修改使用:
# cp /usr/share/doc/openvpn-*/sample/sample-config-files/server.conf /etc/openvpn

我们进入 /etc/openvpn/ 目录编辑 server.conf 文件。

配置完成后的内容如下:

# egrep -v "^$|^#|^;" server.conf

local 10.10.204.62
port 1194
proto udp
dev tun
ca ca.crt
cert renwoleserver.crt
key renwoleserver.key # This file should be kept secret
dh dh2048.pem
server 10.8.0.0 255.255.255.0
ifconfig-pool-persist ipp.txt
push "redirect-gateway def1 bypass-dhcp"
push "dhcp-option DNS 223.5.5.5"
client-to-client
keepalive 10 120
tls-auth ta.key 0 # This file is secret
cipher AES-256-CBC
compress lz4-v2
push "compress lz4-v2"
user nobody
group nobody
persist-key
persist-tun
status /var/log/openvpn-status.log
log /var/log/openvpn.log
verb 3
explicit-exit-notify 1
5.启动IP转发功能
# echo "net.ipv4.ip_forward = 1" >> /etc/sysctl.conf
# sysctl -p
6.配置firewall转发规则

确保服务器可以转发数据包到服务器内外网,开启必要的通讯服务,并启用IP伪装(Masquerade)功能。

# firewall-cmd --permanent --add-service openvpn
# firewall-cmd --permanent --add-masquerade
# DEV=$(ip route get 223.5.5.5 | awk 'NR==1 {print $(NF-2)}' )
# firewall-cmd --permanent --direct --passthrough ipv4 -t nat -A POSTROUTING -s 10.8.0.0/24 -o $DEV -j MASQUERADE
# firewall-cmd --reload
# firewall-cmd --list-all
7.启动 OpenVPN

重启网络与OpenVPN服务,并且让OpenVPN开机后自动启动:

# systemctl restart network
# systemctl restart openvpn@server
# systemctl enable openvpn@server

Mysql cannot allocate memory for the buffer pool 解决方法

问题出现原因
昨晚我修改了数据库,为字段添加了”FULLTEXT”索引,结果今天早上发现数据库连不上了,也没法重启,于是查看日志文件:

cat /var/log/mysqld.log

发现如下错误日志:

130728  6:50:14 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
130728  6:50:14 InnoDB: Completed initialization of buffer pool
130728  6:50:14 InnoDB: Fatal error: cannot allocate memory for the buffer pool

简单解释就是没法为mysql的buffer pool分配128M的内存。
原因很明显了,内存不足呗,查找资料后发现,原来这个参数主要作用是缓存innodb表的索引,数据,插入数据时的缓冲。我的服务器只有1G内存,所以这个参数开始为索引分配缓存的时候把mysql弄崩溃了。

解决方法
mysql的配置都在/ect/mysql.cnf里面(Ubuntu目录有稍微的差别,用whereis my.cnf就能找到了)。

vim /etc/my.cnf

可以看到第四行:

#innodb_buffer_pool_size = 128M

这里显示默认配置是128M,是注释状态,我们只需要把注释去掉,把128改小一点(根据个人情况修改),我改为:

innodb_buffer_pool_size = 50M

就感觉差不多了。

进一步优化
一般出现这种问题的同学,内存肯定都不大,所以我们可以设置swap分区,也就是通常所说的虚拟内存。
swap的作用具体这里就不做说明了,简单点说就是可以辅助Mem内存。
直接上代码:

dd if=/dev/zero of=/swapfile bs=1M count=1024
mkswap /swapfile
swapon /swapfile

最后添加代码/swapfile swap swap defaults 0 0到/etc/fstab文件里面。

free

能看到Mem和swap信息就说明成功了。
最后重启mysql就可以了。

参考文档
http://hongjiang.info/aliyun-vps-mysql-aborting/

Starting MySQL On Low Memory Virtual Machines

Cloud computing makes it very affordable to get your own private virtual server on the Internet. Digital Ocean offers an entry level droplet for USD$5 per month, and Amazon.com has a micro instance tier on the EC2 platform that is free for the first year. These instances are rather useful if you want to test out some new technologies (multi source replication?) or put together a proof of concept. However, these servers comes with a very low amount of RAM, usually between half GB to one GB. These days, it is common to see production grade database servers with literally 100x that amount. 100MB of memory usage makes no significant difference to one of these servers, but definitely noticible to a 512MB virtual machine. I recently encountered an issue where MySQL would not start up. The interesting part was the lack of any errors from MySQL:

140521 08:26:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140521 08:26:41 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Yet if we check our kernel messages, we clearly see an out of memory event:

May 21 08:26:41 aes2 kernel: Out of memory: Kill process 24774 (mysqld) score 842 or sacrifice child
May 21 08:26:41 aes2 kernel: Killed process 24774, UID 0, (mysqld) total-vm:549180kB, anon-rss:437324kB, file-rss:44kB

Or in other cases, we see errors about InnoDB not being able to allocate buffer pool:

2014-05-21 08:33:23 25042 [Note] InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
2014-05-21 08:33:23 25042 [ERROR] InnoDB: Cannot allocate memory for the buffer pool

But we do have RAM available:

[root@aes2 ~]# free -m
             total       used       free     shared    buffers     cached
Mem:           490         86        403          0          7         32
-/+ buffers/cache:         46        443
Swap:            0          0          0
[root@aes2 ~]# 

Well, the issue here is Performance Schema, and not making it obvious. When starting up, it allocates all the RAM it needs. By default, it will use around 400MB of RAM, which isn’t noticible with a database server with 64GB of RAM, but it is quite significant for a small virtual machine. If you add in the default InnoDB buffer pool setting of 128MB, you’re well over your 512MB RAM allotment and that doesn’t include anything from the operating system. We can easily disable Performance Schema by putting this under the [mysqld] section configuration:

performance_schema = off

It is possible to customize which monitors are used in Performance Schema to reduce the memory footprint. But with limited RAM, it will probably be much better to use all of that for other things, like the buffer pool. There is a MySQL bug report that provides a pretty decent description about Performance Schema: http://bugs.mysql.com/bug.php?id=68514 And there is a feature request to print out the amount of memory used for Performance Schema, that would have made this whole problem more visable: http://bugs.mysql.com/bug.php?id=69665

Starting MySQL On Low Memory Virtual Machines

How To Install MariaDB on CentOS 7

Introduction

MariaDB is an open-source database management system, commonly installed as part of the popular LEMP (Linux, Nginx, MySQL/MariaDB, PHP/Python/Perl) stack. It uses a relational database and SQL (Structured Query Language) to manage its data. MariaDB is a fork of MySQL managed by the original MySQL developers. It’s designed as a replacement for MySQL, uses some commands that reference mysql, and is the default package on CentOS 7.

In this tutorial, we will explain how to install the latest version of MariaDB on a CentOS 7 server. If you specifically need MySQL, see the How to Install MySQL on CentOS 7 guide. If you’re wondering about MySQL vs. MariaDB, MariaDB is the preferred package and should work seamlessly in place of MySQL.

Prerequisites

To follow this tutorial, you will need:

A CentOS 7 with a non-root user with sudo privileges. You can learn more about how to set up a user with these privileges in the Initial Server Setup with CentOS 7 guide.

Step 1 — Installing MariaDB

We’ll use Yum to install the MariaDB package, pressing y when prompted to confirm that we wish to proceed:

sudo yum install mariadb-server

Once the installation is complete, we’ll start the daemon with the following command:

sudo systemctl start mariadb

systemctl doesn’t display the outcome of all service management commands, so to be sure we succeeded, we’ll use the following command:

sudo systemctl status mariadb

If MariaDB has successfully started, the output should contain “Active: active (running)` and the final line should look something like:

Dec 01 19:06:20 centos-512mb-sfo2-01 systemd[1]: Started MariaDB database server.
Next, let’s take a moment to ensure that MariaDB starts at boot, using the systemctl enable command, which will create the necessary symlinks.

sudo systemctl enable mariadb

Output

Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

Next, we’ll turn our attention to securing our installation.

Step 2 — Securing the MariaDB Server

MariaDB includes a security script to change some of the less secure default options for things like remote root logins and sample users. Use this command to run the security script:

sudo mysql_secure_installation

The script provides a detailed explanation for every step. The first prompts asks for the root password, which hasn’t been set so we’ll press ENTER as it recommends. Next, we’ll be prompted to set that root password, which we’ll do.

Then, we’ll accept all the security suggestions by pressing Y and then ENTER for the remaining prompts, which will remove anonymous users, disallow remote root login, remove the test database, and reload the privilege tables.

Finally, now that we’ve secured the installation, we’ll verify it’s working.

Step 3 — Testing the Installation

We can verify our installation and get information about it by connecting with the mysqladmin tool, a client that lets you run administrative commands. Use the following command to connect to MariaDB as root (-u root), prompt for a password (-p), and return the version.

mysqladmin -u root -p version

You should see output similar to this:

Output
mysqladmin Ver 9.0 Distrib 5.5.50-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Server version 5.5.50-MariaDB
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 4 min 4 sec

Threads: 1 Questions: 42 Slow queries: 0 Opens: 1 Flush tables: 2 Open tables: 27 Queries per second avg: 0.172
This indicates the installation has been successful.

Conclusion
In this tutorial, we’ve installed and secured MariaDB on a CentOS 7 server. To learn more about using MariaDB, this guide to learning more about MySQL commands can help. You might also consider implementing some additional security measures.