Reputation: 143

MariaDB Performance

i'm working with centos 7 and 10.1.8-MariaDB. Some times i'm feeling than the system is slow. Today checking slow querys I've seen update queries really thin or usually fast than take in some stages more than 3 or 4 seconds.

The database is runing on a dedicated server, only installed mariadb. It has 4GB Ram.

Here is memory:

 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

This is 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

The slow query 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());

The tables definition:

   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;

This is explain

 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

This is SHOW TABLE STATUS LIKE

 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

Can anyone help me with this slow querys ?

Thanks a lot !

UPDATED:

This is slow query form today

 # 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

and this is mariadb.log from yesterday (nothing today):

 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)

Upvotes: 1

MariaDB Performance

Answers (2)

Related Questions