文章/答案/技术大牛

发布

社区首页 >问答首页 >MariaDB性能

问MariaDB性能
EN

Stack Overflow用户

提问于 2016-09-26 02:59:26

回答 2查看 1K关注 0票数 1

我正在使用centos 7和10.1.8-MariaDB。有时候我觉得系统太慢了。今天，在检查慢查询时，我发现update查询真的很瘦，或者通常比某些阶段的查询快3到4秒。

数据库在专用服务器上运行，只安装了mariadb。它有4 4GB的内存。

这是内存：

 cat /proc/meminfo
 MemTotal:        3883016 kB
 MemFree:          156380 kB
 MemAvailable:     293708 kB
 Buffers:               0 kB
 Cached:           288140 kB
 SwapCached:       171352 kB
 Active:          2546636 kB
 Inactive:        1023020 kB
 Active(anon):    2391628 kB
 Inactive(anon):   905344 kB
 Active(file):     155008 kB
 Inactive(file):   117676 kB
 Unevictable:           0 kB
 Mlocked:               0 kB
 SwapTotal:       2113532 kB
 SwapFree:        1334248 kB
 Dirty:                 0 kB
 Writeback:             0 kB
 AnonPages:       3241692 kB
 Mapped:            20172 kB
 Shmem:             15456 kB
 Slab:              87216 kB
 SReclaimable:      67176 kB
 SUnreclaim:        20040 kB
 KernelStack:        3200 kB
 PageTables:        12088 kB
 NFS_Unstable:          0 kB
 Bounce:                0 kB
 WritebackTmp:          0 kB
 CommitLimit:     4055040 kB
 Committed_AS:    4645884 kB
 VmallocTotal:   34359738367 kB
 VmallocUsed:      160244 kB
 VmallocChunk:   34359568380 kB
 HardwareCorrupted:     0 kB
 AnonHugePages:   2045952 kB
 HugePages_Total:       0
 HugePages_Free:        0
 HugePages_Rsvd:        0
 HugePages_Surp:        0
 Hugepagesize:       2048 kB
 DirectMap4k:       59328 kB
 DirectMap2M:     4134912 kB

这是my.cnf

 my.cnf
 #
 # This group is read both both by the client and the server
 # use it for options that affect everything
 #
 [mysqld]
 bind-address = 0.0.0.0

 #tmp per carregar:
 wait_timeout=108000
 max_allowed_packet=40960M

 [client-server]
 #
 # include all files from the config directory
 #
 !includedir /etc/my.cnf.d

 [mysqld]
 # 20151031
 # charset
 # init_connect=.SET collation_connection = utf8_unicode_ci.
 # init_connect=.SET NAMES utf8.
 character-set-server=utf8
 collation-server=utf8_unicode_ci

 open_files_limit = 8192
 max_connections = 200

 slow-query-log = 1
 slow-query-log-file = /var/log/mariadb/mysql-slow.log
 long_query_time = 1

 #performance
 # innodb_buffer_pool_size default: 134217728 (128MB) - 60 o 70% memoria
 # 2048MB - 70% = 6012954214 (5734MB)
 innodb_buffer_pool_size=3GB
 innodb_buffer_pool_instances=3

 thread_cache_size=200

 #query_cache_size
 query_cache_type = 1
 query_cache_limit = 1M
 query_cache_min_res_unit = 2k
 query_cache_size = 80M

 #LOGS
 #log-error
 log_warnings = 3
 log-error = /var/log/mariadb/mariadb.log

慢查询日志：

 # User@Host: swb[swb] @  [192.168.50.65]
 # Thread_id: 23002274  Schema: swb  QC_hit: No
 # Query_time: 2.000710  Lock_time: 0.000209  Rows_sent: 0  Rows_examined: 1
 # Rows_affected: 1
 SET timestamp=1474793382;
 UPDATE instalaciones
                 SET
                    fSincro = now(),
                    tHotspot='ACTIVO'
                 WHERE tRouterSerial = '558104409B1B';



 # User@Host: swb[swb] @  [192.168.50.65]
 # Thread_id: 22499243  Schema: swb  QC_hit: No
 # Query_time: 1.909591  Lock_time: 0.000041  Rows_sent: 0  Rows_examined: 0
 # Rows_affected: 1
 SET timestamp=1474636304;
 INSERT INTO redirect(tRedirect,fFecha) VALUES ('ff0005a8c8f2c409a1a5a58fab6d16b455d09258',now());

表的定义：

   CREATE TABLE `instalaciones` (
   `idInstalacion` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `idCliente` int(10) unsigned DEFAULT NULL,
   `tRouterSerial` varchar(50) DEFAULT NULL,
   `tFacebookPage` varchar(256) DEFAULT NULL,
   `tidFacebook` varchar(64) DEFAULT NULL,
   `tNombre` varchar(128) DEFAULT NULL,
   `tMensaje` varchar(128) DEFAULT NULL,
   `tWebPage` varchar(128) DEFAULT NULL,
   `tDireccion` varchar(128) DEFAULT NULL,
   `tPoblacion` varchar(128) DEFAULT NULL,
   `tProvincia` varchar(64) DEFAULT NULL,
   `tCodigoPosta` varchar(8) DEFAULT NULL,
   `tLatitud` decimal(15,12) DEFAULT NULL,
   `tLongitud` decimal(15,12) DEFAULT NULL,
   `tSSID1` varchar(40) DEFAULT NULL,
   `tSSID2` varchar(40) DEFAULT NULL,
   `tSSID2_Pass` varchar(40) DEFAULT NULL,
   `fSincro` datetime DEFAULT NULL,
   `tEstado` varchar(10) DEFAULT NULL,
   `tHotspot` varchar(10) DEFAULT NULL,
   `fAlta` datetime DEFAULT NULL,
   PRIMARY KEY (`idInstalacion`),
   UNIQUE KEY `tRouterSerial` (`tRouterSerial`),
   KEY `idInstalacion` (`idInstalacion`),
   KEY `idCliente` (`idCliente`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1611 DEFAULT CHARSET=utf8;

 CREATE TABLE `redirect` (
   `tRedirect` varchar(80) DEFAULT NULL,
   `fFecha` datetime DEFAULT NULL,
   KEY `itRedirect` (`tRedirect`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这是解释

 id select_type table   type    possible_keys   key key_len ref rows    Extra
 1  SIMPLE  instalaciones   range   tRouterSerial   tRouterSerial   153 NULL    1   "Using where"


 id select_type table   type    possible_keys   key key_len ref rows    Extra
 1  INSERT  redirect    ALL NULL    NULL    NULL    NULL    NULL    NULL

这是SHOW TABLE STATUS，如下所示

 Name,Engine,Version,Row_format,Rows,Avg_row_length,Data_length,Max_data_length,Index_length,Data_free,Auto_increment,Create_time,Update_time,Check_time,Collation,Checksum,Create_options,Comment

 instalaciones,InnoDB,10,Compact,1401,339,475136,0,131072,0,1611,"2016-06-28 22:20:45",NULL,NULL,utf8_general_ci,NULL,,

 Name   Engine  Version Row_format  Rows    Avg_row_length  Data_length Max_data_length Index_length    Data_free   Auto_increment  Create_time Update_time Check_time  Collation   Checksum    Create_options  Comment
 redirect   InnoDB  10  Compact 30766   102 3162112 0   3162112 13631488    NULL    "2016-02-22 09:57:20"   NULL    NULL    utf8_general_ci NULL

有人能帮我解决这个慢查询吗？

非常感谢！

更新：

这是今天的慢查询表单

 # User@Host: swb[swb] @  [192.168.50.65]
 # Thread_id: 23552588  Schema: swb  QC_hit: No
 # Query_time: 1.279738  Lock_time: 0.000115  Rows_sent: 0  Rows_examined: 1
 # Rows_affected: 1
 SET timestamp=1474975258;
 UPDATE instalaciones
                 SET
                    fSincro = now(),
                    tHotspot='ACTIVO'
                 WHERE tRouterSerial = '558104B41762';
 # Time: 160927 18:40:30

这是昨天的mariadb.log (今天什么都没有)：

 2016-09-26 12:53:49 140053608847104 [Warning] Aborted connection 23132533 to db: 'unconnected' user: 'SWB_BDA' host: '192.168.50.65' (Unknown error)
 2016-09-26 12:53:49 140053608249088 [Warning] Aborted connection 23132521 to db: 'swb' user: 'SWB_BDA' host: '192.168.50.65' (Unknown error)

mariadb

mysql-slow-query-log

mysql

sql

回答 2

Stack Overflow用户

发布于 2016-09-28 06:58:02

这种迟缓可能是由于掉期造成的。有几个设置可能会溢出可用RAM。无论如何..。

3 4GB的innodb_buffer_pool_size不适合4 4GB的服务器。建议更改以下内容：

max_allowed_packet=40960M       --> 50M
innodb_buffer_pool_size=3GB     --> 1500M
innodb_buffer_pool_instances=3  --> 1
thread_cache_size=200           --> 10
query_cache_size = 80M          --> 40M

票数 1

Stack Overflow用户

发布于 2016-09-27 13:45:24

你的数据库有多大?数据库服务器的4G内存听起来很小。你检查过你的错误日志了吗？您还应该看到vmstat或类似的内容，以查看当查询速度较慢时还发生了什么。如果没有其他事情发生，提供的示例不应该很慢。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/39690644

复制

相似问题

问MariaDB性能
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MariaDB性能EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MariaDB性能
EN