Optimization suggestion MariaDB

I have a server that is very loaded because of MySQL, I am not very knowledgeable in this area of optimization, can anyone give me any suggestions for configuration?

Following data:

Versions:

[root@server ~]# mysql --version mysql  Ver 15.1 Distrib 10.4.8-MariaDB, for Linux (x86_64) using readline 5.1  [root@server ~]# cat /etc/redhat-release CentOS Linux release 7.7.1908 (Core) 

Memory:

[root@server ~]# free -m               total        used        free      shared  buff/cache   available Mem:           8192        3263         643           4        4284        4923 Swap:           512         467          44 

Disk:

[root@server ~]# df -h Filesystem         Size  Used Avail Use% Mounted on /dev/ploop31033p1  289G  244G   33G  89% / none               4.0G     0  4.0G   0% /sys/fs/cgroup none               4.0G     0  4.0G   0% /dev tmpfs              4.0G     0  4.0G   0% /dev/shm tmpfs              4.0G  4.7M  4.0G   1% /run tmpfs              820M     0  820M   0% /run/user/0 tmpfs              820M     0  820M   0% /run/user/1019 tmpfs              820M     0  820M   0% /run/user/1088 tmpfs              820M     0  820M   0% /run/user/1006 

unlimit -a:

[root@server ~]# ulimit -a core file size          (blocks, -c) 0 data seg size           (kbytes, -d) unlimited scheduling priority             (-e) 0 file size               (blocks, -f) unlimited pending signals                 (-i) 127488 max locked memory       (kbytes, -l) 64 max memory size         (kbytes, -m) unlimited open files                      (-n) 1024 pipe size            (512 bytes, -p) 8 POSIX message queues     (bytes, -q) 819200 real-time priority              (-r) 0 stack size              (kbytes, -s) 8192 cpu time               (seconds, -t) unlimited max user processes              (-u) 62987 virtual memory          (kbytes, -v) unlimited file locks                      (-x) unlimited 

Mysqltunner:

[root@server ~]# mysqltuner  >>  MySQLTuner 1.7.18 - Major Hayden <major@mhtx.net>  >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/  >>  Run with '--help' for additional options and output filtering  [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 10.4.8-MariaDB [OK] Operating on 64-bit architecture  -------- Log file Recommendations ------------------------------------------------------------------ [OK] Log file /var/lib/mysql/hostname.domain.com.err exists [--] Log file: /var/lib/mysql/hostname.domain.com.err(463K) [OK] Log file /var/lib/mysql/hostname.domain.com.err is readable. [OK] Log file /var/lib/mysql/hostname.domain.com.err is not empty [OK] Log file /var/lib/mysql/hostname.domain.com.err is smaller than 32 Mb [!!] /var/lib/mysql/hostname.domain.com.err contains 2169 warning(s). [!!] /var/lib/mysql/hostname.domain.com.err contains 1537 error(s). [--] 22 start(s) detected in /var/lib/mysql/hostname.domain.com.err [--] 1) 2019-10-03 19:15:13 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 2) 2019-10-03 19:08:03 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 3) 2019-10-03 18:46:20 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 4) 2019-10-03 18:43:06 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 5) 2019-10-03 18:37:40 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 6) 2019-10-03 18:20:11 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 7) 2019-10-03 18:19:55 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 8) 2019-10-03 18:12:47 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 9) 2019-10-03 17:27:18 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 10) 2019-10-03 16:26:17 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 10 shutdown(s) detected in /var/lib/mysql/hostname.domain.com.err [--] 1) 2019-10-03 19:15:12 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 2) 2019-10-03 19:08:02 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 3) 2019-10-03 18:46:06 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 4) 2019-10-03 18:43:05 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 5) 2019-10-03 18:37:39 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 6) 2019-10-03 18:20:10 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 7) 2019-10-03 18:19:55 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 8) 2019-10-03 18:12:46 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 9) 2019-10-03 15:40:03 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 10) 2019-10-02 16:52:47 0 [Note] /usr/sbin/mysqld: Shutdown complete  -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [--] Data in MyISAM tables: 2.8G (Tables: 4134) [--] Data in InnoDB tables: 2.0G (Tables: 5109) [--] Data in MEMORY tables: 1.3M (Tables: 51) [OK] Total fragmented tables: 0  -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA.  -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [!!] User 'confident@10.100.2.194' has user name as password. [!!] User 'confident2@%' does not specify hostname restrictions. [!!] There is no basic password file list!  -------- CVE Security Recommendations -------------------------------------------------------------- [--] Skipped due to --cvefile option undefined  -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 10m 51s (238K q [366.876 qps], 1K conn, TX: 858M, RX: 74M) [--] Reads / Writes: 90% / 10% [--] Binary logging is disabled [--] Physical Memory     : 8.0G [--] Max MySQL memory    : 3.4G [--] Other process memory: 0B [--] Total buffers: 2.3G global + 3.7M per thread (300 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 2.3G (29.10% of installed RAM) [OK] Maximum possible memory usage: 3.4G (41.92% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (1/238K) [OK] Highest usage of available connections: 4% (13/300) [OK] Aborted connections: 0.00%  (0/1182) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 35K sorts) [!!] Joins performed without indexes: 6500 [OK] Temporary tables created on disk: 11% (3K on disk / 29K total) [OK] Thread cache hit rate: 98% (13 created / 1K connections) [!!] Table cache hit rate: 14% (842 open / 5K opened) [OK] Open file limit used: 5% (916/16K) [OK] Table locks acquired immediately: 99% (77K immediate / 77K locks)  -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. [--] Memory used by P_S: 0B [--] Sys schema isn't installed.  -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 2 thread(s). [--] Using default value is good enough for your version (10.4.8-MariaDB)  -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 22.0% (29M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/93.8M [OK] Read Key buffer hit rate: 100.0% (11M cached / 4K reads) [OK] Write Key buffer hit rate: 99.9% (14K cached / 14K writes)  -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 2.0G/2.0G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (12.5 %): 128.0M * 2/2.0G should be equal to 25% [!!] InnoDB buffer pool instances: 1 [--] Number of InnoDB Buffer Pool Chunk : 16 for 1 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 99.91% (33389412 hits/ 33421049 total) [!!] InnoDB Write Log efficiency: 56.21% (3048 hits/ 5423 total) [OK] InnoDB log waits: 0.00% (0 waits / 2375 writes)  -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/496.0K [OK] Aria pagecache hit rate: 99.0% (307K cached / 3K reads)  -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled.  -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled.  -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled.  -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] Binlog format: MIXED [--] XA support enabled: ON [--] Semi synchronous replication Master: OFF [--] Semi synchronous replication Slave: OFF [--] This is a standalone server  -------- Recommendations --------------------------------------------------------------------------- General recommendations:     Control warning line(s) into /var/lib/mysql/hostname.domain.com.err file     Control error line(s) into /var/lib/mysql/hostname.domain.com.err file     Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )     Restrict Host for user@% to user@SpecificDNSorIp     MySQL was started within the last 24 hours - recommendations may be inaccurate     Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1     Adjust your join queries to always utilize indexes     Increase table_open_cache gradually to avoid file descriptor limits     Read this before increasing table_open_cache over 64: https://bit.ly/1mi7c4C     Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/     This is MyISAM only table_cache scalability problem, InnoDB not affected.     See more details here: https://bugs.mysql.com/bug.php?id=49177     This bug already fixed in MySQL 5.7.9 and newer MySQL versions.     Beware that open_files_limit (16364) variable     should be greater than table_open_cache (3000)     Performance schema should be activated for better diagnostics     Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL     Consider installing Sys schema from https://github.com/good-dba/mariadb-sys for MariaDB     Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU Variables to adjust:     join_buffer_size (> 1.0M, or always use indexes with JOINs)     table_open_cache (> 3000)     performance_schema = ON enable PFS     innodb_log_file_size should be (=256M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.     innodb_buffer_pool_instances(=2) 

My conf file:

[root@server ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server]  # # include all files from the config directory # !includedir /etc/my.cnf.d  [mysqld] log-error=/var/lib/mysql/hostname.domain.com.err #performance-schema=ON default-storage-engine=MyISAM   # limits max_connections=300 max_allowed_packet=128M  # other configs join_buffer_size=1M innodb_file_per_table=1 innodb_buffer_pool_size=2G innodb_buffer_pool_instances=1 innodb_log_file_size=128M open_files_limit=32775 table_open_cache=3000 query_cache_size=0 query_cache_type=0 

Command top:

top - 19:45:23 up 6 days,  6:54,  1 user,  load average: 2.97, 3.31, 3.61 Tasks: 181 total,   2 running, 179 sleeping,   0 stopped,   0 zombie %Cpu(s): 82.3 us,  5.8 sy,  0.0 ni, 11.9 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st KiB Mem :  8388608 total,   290356 free,  3884688 used,  4213564 buff/cache KiB Swap:   524288 total,    46620 free,   477668 used.  4498892 avail Mem    PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND  4698 mysql     20   0 4149072   1.2g  16296 S  86.0 15.3  27:32.86 mysqld 

Can any expert on this help me? I can’t slow down the load on this server, it’s a new server that was recently migrated.

I thank you for your help.

MariaDB Inserts are getting slower and slower (7x tables, ~ 2.8M and 200MB)

I have auto increment on each of a table. One unique ID that is consistent of 10 numbers, and each table have ~ 6 big int columns (values are small from 1-60k), and from 0 to 4 var chars (~ up to 500 characters, on average from 5 to 50 characters).

I am fighting with this for months and can’t make it production stage :(, basically it drops from ~ 170 inserts (from app perspective) to ~ 40 just after ~ 200-500k inserts.

This is nothing as I’ve worked with DB that was holding trillions of columns and auto increment and huge varchars. (however paid solution :().

I already tweaked the config so many times but still getting to the point where server is using ~ 950% & .net core 25% (of all cores).

Machine has i9 9900k 8c/16t, 64GB RAM, 2x NVME 2TB

I can’t even run @ 5 minute API test as it won’t be able to process all data from a queue 🙁 (API can accept ~ 20k/s).

Buffers, read io, inno_db other tweaks for a commit etc. were applied, nothing seems to be working.

Looks like it cannot for some reason handle just so little data and I cannot figure out why (I never had any real experience with free databases, so I only assume that it should be able to insert 300k records within 60 seconds and sustain this for ~ 10TB).

MariaDB docker “Can’t init tc log” on start up when using mounted storage

I am initializing a new MariaDB database. Running docker with a volume to my home directory allows MariaDB to start up just fine:

docker run -it --rm --name mymaria \   -e MYSQL_RANDOM_ROOT_PASSWORD=yes \   -e MYSQL_PASSWORD=p@$  $  w0rd \   -e MYSQL_DATABASE=myapp \   -e MYSQL_USER=myapp \   -v /home/myuser/mysql:/var/lib/mysql \   mariadb:10.2 

However, running the mariadb container with a volume via a mounted directory like so:

docker run -it --rm --name mymaria \   -e MYSQL_RANDOM_ROOT_PASSWORD=yes \   -e MYSQL_PASSWORD=p@$  $  w0rd \   -e MYSQL_DATABASE=myapp \   -e MYSQL_USER=myapp \   -v /mnt/storage/mysql:/var/lib/mysql \   mariadb:10.2 

This configuration returns this from the docker logs output:

Initializing database 2019-09-23  5:12:13 139724696503616 [ERROR] Can't init tc log 2019-09-23  5:12:13 139724696503616 [ERROR] Aborting   Installation of system tables failed! ... 

Simply removing tc.log as some folks have suggested does not work. Restarting mariadb will rewrite tc.log back into the volume /var/lib/mysql.

Perhaps this is a permissions issue? I feel like I’ve tried every combination of chown with each directory. I do notice that docker appears to change the mounted volume /mnt/storage/mysql to drwxr-xr-x 2 systemd-coredump root 176 Sep 22 23:11 mysql which I find odd.

I encounter this issue only with the 10.2 tag and not the latest. However, for an orchestration I’m working on, it suggests mariadb:10.2.

Using to_base64 function and regexp_replace with backreferences in MariaDB

I am using MariaDB 10.3. I want to replace a string using REGEX and encode part of the result into base64.

For example, I want to first invert the “a” and “b” strings in this example:

MariaDB [(none)]> SELECT regexp_replace('abc', '(a)(b)', '\2\1'); +-------------------------------------------+ | regexp_replace('abc', '(a)(b)', '\2\1') | +-------------------------------------------+ | bac                                       | +-------------------------------------------+ 1 row in set (0.000 sec) 

“abc” is now “bac, that’s ok.

Now I want to base64-encode the result, so ba should become YmE=:

MariaDB [(none)]> SELECT to_base64('ba'); +-----------------+ | to_base64('ba') | +-----------------+ | YmE=            | +-----------------+ 1 row in set (0.000 sec) 

But when I apply the to_base64 function within regexp_replace, it no longer interprets my backreferences and encodes instead of the the ba string:

MariaDB [(none)]> SELECT regexp_replace('abc', '(a)(b)', to_base64('\2\1')); +------------------------------------------------------+ | regexp_replace('abc', '(a)(b)', to_base64('\2\1')) | +------------------------------------------------------+ | XDJcMQ==c                                            | +------------------------------------------------------+ 1 row in set (0.000 sec)  MariaDB [(none)]> select from_base64('XDJcMQ=='); +-------------------------+ | from_base64('XDJcMQ==') | +-------------------------+ |                     | +-------------------------+ 1 row in set (0.000 sec) 

How can we make the backreferences be interpreted BEFORE applying the to_base64 function?

Many databases combined with MariaDB Spider engine tables [on hold]

We are in the process of refactoring the architecture of our main ETL process in our company.

Basically, the process:

  1. Extracts data from WebServices, MySQL Databases, etc.
  2. The data gets standarized.
  3. The data gets saved in our database.

The data sources are other businesses integrations. We develop a new integration for every client. A integration is given by an extractor application, specifically adaptaed to the sources endpoints (mainly some WebServices). The data extracted is then sent to AWS SQS queues for standarization. The standarizer app runs every hour consuming the messages. Once they’re standarized, messages are sent to another queue (the saver queue). Every hour, the saver app runs and process the messages, saving the content in our database.

enter image description here

Since we’re growing in the number of business integrations, our database is getting some stress during the ETL process and we fear the we’ll end up in a bottleneck. Mainly because of the huge amount of write IOPS that the saver introduces in the main database.

I’m currently in charge of the refactor and I came up with the following architecture design. Given the modular design of the saver and standarizer, they could be “packed” in a app stack alogn with the extractor and a dedicated database for each business. Like so:

enter image description here

The ? labeled box is because the consumer API executes some query over the database that required that all the data is stored on a single database (some reducing, grouping, etc), so, a service of some kind should be present there to let the Consumer API app execute those queries as if a single database is still present.

Doing some research I found MariaDB’s Spider storage engine as an option. Basically, what I was thinking is to mount a EC2 instance with MariaDB installed, and define tables that are partitioned across the businesses databases. That way, the Consumer API will think that only a single database exists, and would let MariaDB take care of the SQL request distributed across each database.

My question is:

Is this a valid use case for the Spider engine?+

To take in mind:

  1. We are not in a position of implementing a ESB (small team, low cost)
  2. We must avoid refactoring the SQL queries of the Consumer API.

Thanks in advance.

ShineServers.Com – FLAT 70% Discount, SSD Storage, CLOUDLINUX, MariaDB, cPanel, Unlimited BW

Started In 2010 With An Aim To Provide A Comprehensive Range Of Secure Web Hosting Services; Shine Servers Proudly Serves Thousands Of Satisfied Customers At Present And Still Counting. Registered By Name Of "Shine Servers LLP" In New Delhi, India And Serving The Worldwide, We Have An Overall Experience Of 10 Years In The Web Hosting Industry. Quality And Customer Service Is Our Stronghold…

ShineServers.Com – FLAT 70% Discount, SSD Storage, CLOUDLINUX, MariaDB, cPanel, Unlimited BW

MariaDB Galera + Replication (GTID error)

  • Using MariaDB 10.2.25 + Galera with 3 masters
  • All Masters have binlogs active
  • Want to switch the Master_Host of an external salve (also running MariaDB 10.2.25) to a different Master.
  • Replication is using GTID (CHANGE MASTER TO master_use_gtid=slave_pos)
  • Replication works perfectly until attempting to change Master_host

Changing master:

STOP SLAVE; CHANGE MASTER TO MASTER_HOST='x.x.x.x'; START SLAVE; 

Results in:

            Last_IO_Errno: 1236             Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find GTID state requested by slave in any binlog files. Probably the slave state is too old and required binlog files have been purged.'                Last_SQL_Errno: 0                Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 107                Master_SSL_Crl: /etc/mysql/ssl/ca-cert.pem            Master_SSL_Crlpath:                    Using_Gtid: Slave_Pos                   Gtid_IO_Pos: 1-107-1746809509 

Reading the correct binlog from the Master, I see:

... #190626 19:57:36 server id 107  end_log_pos 55375314    Update_rows: table id 148 flags: STMT_END_F # at 55375314 #190626 19:57:36 server id 107  end_log_pos 55375341    Xid = 1983287875 COMMIT/*!*/; # at 55375341 #190626 19:57:36 server id 107  end_log_pos 55375379    GTID 1-107-1746809509 trans /*!100001 SET @@session.gtid_seq_no=1746809509*//*!*/; BEGIN ... 

What is going on here? The GTID exists in the Master binlogs.