bioplanet's questions

我正在Centos7和Mysql 5.6.46. 我需要将默认 datadir 位置移动到加密分区，并相应地更改了我的默认 /etc/my.cnf 文件：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/NEW_FOLDER/mysql
socket=/NEW_FOLDER/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
sql_mode=""

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
port=3306
socket=/NEW_FOLDER/mysql/mysql.sock

重新启动后，我验证我可以通过编写连接到 Mysql: mysql -u <USERNAME> -p，没有问题。当我的基于 CGI 的服务器尝试访问数据库时，我的问题与此有关。我收到错误：Error connecting to database: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)这对我来说没有多大意义，因为默认套接字目录已更改。

我查看了 Apache 错误日志，我看到了这个：

DBI connect('testuser:localhost','testdb',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /srv/www/TestSite/perl-lib/Database.pm line 31.

我转到第 31 行，我看到了这个：

my $dsn = "DBI:mysql:testdb:localhost";
    $dbh = DBI->connect($dsn, 'testuser', 'testpasswd')
      or throw Portal::DatabaseError("Error connecting to database: $DBI::errstr");

在我看来，Apache 正在尝试访问 Mysql，但它不能mysql.socket从新的默认mysql datadir位置使用。有什么想法该怎么做？

我有以下两个表：

表格1：

+-----------------------------+--------------------------------------+------+-----+---------+-------+
| Field                       | Type                                 | Null | Key | Default | Extra |
+-----------------------------+--------------------------------------+------+-----+---------+-------+
| patient_id                  | bigint(20)                           | NO   | PRI | NULL    |       |
| patient_wpid                | int(11)                              | NO   | PRI | NULL    |       |
| age_at_visit                | int(11)                              | YES  |     | NULL    |       |
| sex                         | enum('male','female')                | YES  |     | NULL    |       

表 2

+-------------------------+----------------------------+------+-----+---------+----------------+
| Field                   | Type                       | Null | Key | Default | Extra          |
+-------------------------+----------------------------+------+-----+---------+----------------+
| patient_id              | bigint(20)                 | NO   | PRI | NULL    |                |
| patient_wpid            | int(11)                    | NO   | PRI | NULL    |                |
| weight                  | text                       | YES  |     | NULL    |                |
| creatinine              | text                       | YES  |     | NULL    |                |
| eGFR                    | varchar(100)               | YES  |     | N/A     |                |
+-------------------------+----------------------------+------+-----+---------+----------------+

我需要做的是，在表 2 上插入后，执行以下操作：

1. 从表 1 中选择age_at_visit和值，对于给定的和（这些字段在表 1 和表 2 上具有相同的值，并且表 1 已在表 2 之前填写）sexpatient_idpatient_wpid

2. 使用表 2 中的 和 的插入值weight并creatinine计算公式，以便为eGFR表 2 的列生成并连续存储一个值。公式如下：

   eGFR = 175 × (肌酐)^(-1.154) × (年龄)^(-0.203) × (0.742，如果女性)

所以，我写的（并且产生了很多错误，因为这是我第一次尝试使用触发器）如下：

delimiter //
CREATE TRIGGER calcEGFR AFTER INSERT ON Table2
FOR EACH ROW
BEGIN
    SELECT age_at_visit, sex FROM Table1 WHERE (Table1.patient_wpid = Table2.patient_wpid AND Table1.patient_id = Table2.patient_id)
    SET @creatinine_power := SELECT POWER(creatinine,-1.154);
    SET @age_power := SELECT POWER(Table1.age_at_visit,-0.203);
    IF Table1.sex = 'female' THEN
        SET @sex_addition := 0.742
    END IF;
    IF Table1.sex = 'male' THEN
        SET @sex_addition := 1
    END IF; 

    SET @eGFR_value := (175 * @creatinine_power * @age_power * @sex_addition)
    UPDATE Table2 SET eGFR = @eGFR_value;
END;//
delimiter ;

你能帮我纠正这个吗？谢谢！

我在 MariaDB 数据库中有一个大约 700,000,000 行的表。我已经索引了它的一些列。

我的问题是我的数据库是否表现“正常”——因为我以前没有处理过如此大量数据的经验。

看看这些查询，如果您认为这是正常的响应时间，请告诉我：

SELECT count( DISTINCT(QualityLevel) ) FROM patient_records;
+---------------------------------+
| count( DISTINCT(QualityLevel) ) |
+---------------------------------+
|                          265595 |
+---------------------------------+
1 row in set (1.248 sec)

我认为这是很好的响应时间。但是，请看下面：

select count(QualityLevel) from patient_records where QualityLevel>10.14;
+---------------------+
| count(QualityLevel) |
+---------------------+
|           700756562 |
+---------------------+
1 row in set (3 min 10.324 sec)

不太好（我假设？）。和更多：

select count(recordID) from patient_records where QualityLevel>10.14 and snpID='.';
+-----------------+
| count(recordID) |
+-----------------+
|        56627747 |
+-----------------+
1 row in set (23 min 53.028 sec)

尽管两列 (QualityLevel和snpID) 都已编入索引，但这一列相当慢。

我需要为这些查询构建一个 Web 界面，但它不可能需要 25 分钟才能执行。我在这里想念什么？也许数据库分区会有所帮助（对此一无所知，因此任何建议都将受到高度赞赏）。

我的my.cnf文件：

[mysqld]
datadir=/home/ssd/mysql_datadir
tmpdir=/home/ssd/mysql_tmdir
socket=/home/ssd/mysql_datadir/mysql.sock
innodb_buffer_pool_size=4GB
query_cache_type=1
query_cache_size = 10M
query_cache_limit=256k

[client]
port=3306
socket=/home/ssd/mysql_datadir/mysql.sock

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

我的 SHOW CREATE TABLE 是：

patient_records | CREATE TABLE `patient_records` (
  `recordID` int(11) NOT NULL AUTO_INCREMENT,
  `MRD_sample_FORMAT_id` int(11) NOT NULL,
  `DataType` char(1) NOT NULL,
  `SequencingOrigin` char(1) NOT NULL,
  `ChrNo` varchar(2) NOT NULL,
  `ChrPos` int(10) unsigned NOT NULL,
  `snpID` varchar(20) NOT NULL,
  `NuclREF` varchar(500) NOT NULL,
  `NuclALT` varchar(3000) NOT NULL,
  `QualityLevel` float NOT NULL,
  `FilterString` char(1) NOT NULL,
  `InfoString` varchar(1000) NOT NULL,
  `GT` varchar(20) DEFAULT NULL,
  `AD` varchar(20) DEFAULT NULL,
  `DP` varchar(20) DEFAULT NULL,
  `GQ` varchar(20) DEFAULT NULL,
  `PL` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`recordID`),
  KEY `fk_patients_idx` (`MRD_sample_FORMAT_id`),
  KEY `ChrNo` (`ChrNo`) USING BTREE,
  KEY `snpID` (`snpID`),
  KEY `idx_patient_records_ChrNo` (`ChrNo`),
  KEY `position_chrom` (`ChrPos`),
  KEY `quality` (`QualityLevel`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=700756618 DEFAULT CHARSET=latin1 |

我正在运行 5.5.60-MariaDB，我需要在服务器上上传一个数据库。.sql 文件位于 SSD 驱动器（本地）上，它包含约 7 亿行，索引 4 列。一切都包含在转储文件中。

我的问题是，它已经花费了 24 小时而数据库仍未启动，这是否正常？服务器不忙于做其他事情，有 16 个内核和 125 GB 的 RAM。

我正在使用的命令是：

mysql -u root myDB < database_dump.sql

我的配置文件如下：

[mysqld]
datadir=/home/ssd/mysql_datadir
tmpdir=/home/ssd/mysql_tmdir
socket=/var/lib/mysql/mysql.sock
innodb_buffer_pool_size=4GB

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

# include all files from the config directory
!includedir /etc/my.cnf.d

tmp 和 data 目录都在同一个 SSD 磁盘上，sql 转储也是如此。我不知道这是否是一个正常的预期时间，或者我应该更改我的设置中的某些内容，这就是我伸出援手的原因。要上传的数据库引擎是 InnoDB。

谢谢！