Finding right configuration for a dedicated mariadb server - for huge innodb tables

Question

I have been working on a separate server to configure the mariadb but still queries are taking sooo long time to return and mostly not returning.

Mariadb version: 10.1.31-1 Server has 50GB Ram and 2200 Ghz * 10 core Centos7 as OP

The target database currently has very huge innodb tables and not indexed well. there is no any other table type just innodb.

Currently i am making some nested select queries and saving results to another empty clone database with insert into or replace into.

as an example:

REPLACE INTO db1.table1
SELECT * FROM db0.table1 WHERE ART_ID IN (
    SELECT ART_ID FROM db0.table2 WHERE BRA_ID IN (
        SELECT BRA_ID FROM db0.table3 WHERE BRAND IN (
            SELECT BRAND FROM db0.table4 WHERE ID IN (... bunch of comma seperated ids)
        )
    )
);

The only connection is from local and/or just one remote connection. The only task of the server is to filter the huge data to another db, no web server etc.

I put the /etc/my.cnf file down below. I made lots of tests on this file so you may see some useless additions.

The question is: Is there any miss configurations in the cnf file; and is there any other configuration tips etc to run those bunch of queries in reasonable time.

It was working better but after i change the configurations or / and update the server everything got much slower. (yes i update the tables as well after updating db version)

# The MySQL server
[mysqld]
performance_schema=0
bind-address = 0.0.0.0
max_connections = 32
socket      = /var/lib/mysql/mysql.sock
skip-external-locking
max_allowed_packet = 1024M

connect_timeout=300
wait_timeout=1200
interactive_timeout=300

key_buffer_size = 2G
bulk_insert_buffer_size=256M
sort_buffer_size = 32M
#read_buffer_size = 8M
#read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 32M
join_buffer_size=32M
#max_heap_table_size= 8G
tmp_table_size=8G
concurrent_insert=2
#external-locking=FALSE
#open_files_limit=50000

default-storage-engine=innodb
innodb_file_per_table=1

#lower_case_table_names=1

# Try number of CPU's*2 for thread_concurrency
innodb_write_io_threads=10
innodb_read_io_threads=20
#innodb_buffer_pool_instances=8
#thread_stack = 1M
#thread_cache_size = -1

innodb_buffer_pool_size = 16G

#query_cache_limit = 1073741824
#query_cache_size = 1073741824
#query_cache_type = 1

query_cache_size=0
query_cache_type=0

#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size

innodb_fast_shutdown=0

#innodb_log_file_size = 1G
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 0
innodb_lock_wait_timeout = 1073741823
innodb_thread_concurrency=0
innodb_commit_concurrency=0
innodb_flush_method=O_DSYNC
innodb_log_compressed_pages = 0
#innodb_flush_neighbors = 0
#innodb_adaptive_hash_index_partitions = 8


log-bin=mysql-bin
server-id   = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M

[myisamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M

[mysqlhotcopy]
interactive-timeout

---

I used percona wizard and merge the advices. the current my.cnf is below.

I run the php application yesterday night (2018-03-05 22:41:30 UTC), but still mysql could not return any result.

the application is only adding limit 10000 and offset (i did not want to start from beginning, i am currently tracking the last offset and starting from that point.)

currently i am planning to delete and reinstall mariadb :( (it will be emotional reaction)

# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208

[mysql]

# CLIENT #
port                           = 9999
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
port                           = 9999
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM # - there is no myisam table
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 30
#thread-cache-size              = 16
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 5G
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 42G
innodb-buffer-pool-instances   = 42


thread_cache_size=100
innodb_lru_scan_depth=100
innodb_purge_threads=4
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_thread_concurrency=0
max_seeks_for_key=32
max_write_lock_count=16
thread_concurrency=35

innodb_fast_shutdown=0
innodb_file_per_table=1
default-storage-engine=innodb
concurrent_insert=2
join_buffer_size=32M
tmp_table_size=8G
read_buffer_size = 8M

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 0
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

Answer 1

Suggestions for your my.cnf-ini [mysqld] section using data posted 3/15/18

thread_cahce_size=40  # from 20, 8 required for overhead, room to wiggle
innodb_io_capacity=400  # from 200   new iostat -x will confirm use
# read_buffer_size=8388608  # lead with # for default of 128K - extreme at 8M
read_rnd_buffer_size=128K  # from 256K will affect handler_read_rnd_next 
sync_binlog=32  # from 1 sec frequency, unless you fear recovery required
# join_buffer_size=4M  # lead with # for default of 128K
key_buffer_size=1M  # from 32M less than 1M used - primarily Innodb tables
innodb_adapative_max_sleep_delay=15  # from 150000 (15 secs - n0 other users)
innodb_buffer_pool_instances=8  # from 42 to minimize mgmt. overhead
innodb_buffer_pool_size=40G  # from 44G allow OS a little breathing room
innodb_change_buffer_max_size=3  # from 25% - no need to set aside 10G when not changing anything significant
innodb_doublewrite=OFF  # from ON you are not a PROD effort - crash recovery NA for run of this instance
innodb_print_all_deadlocks=ON  # from OFF you always need in error log
innodb_read_ahead_threshold=8  # from 56, why wait to read NXT extent?
innodb_stats_sample_pages=32  # from 8 for more accurate index cardinality 
log_warnings=2  # from 1 to record addl connection error details
max_join_size=1000000000  # from more huge number to 1B rows max for join
sql_select_limit=1000000000  # from more huge number to 1B rows selected
query_alloc_block_size=32K  # from 16K - minimize RAM alloc AvgQrySz 20K
query_cache_type=0  # for OFF, not used
query_cache_size=0  # from 8M  not needed for one time selections
query_cache_limit=1K  # from 256K, just to keep the framework, in case QC needed
query_cache_min_res_unit=512  # from 2K for optimal QC storage when QC used
query_prealloc_size=32K  # from 24K to minimize RAM alloc for parsing 
transaction_alloc_block_size=32K  # from 8K to minimize RAM alloc
transaction_prealloc_size=32K  # from 4K to minimize RAM alloc
updatable_views_with_limit=NO  # from YES to reduce handler_external_lock count

this one could have to go back to YES - it is DYNAMIC variable

Please find your ONE com_stmt_prepare CODE and ensure it has CLOSE before leaving the session to release resources per refman PREPARE or mysql_stmt_prepare for API.

Once you are running, before running your troubled query, consider using ALTER TABLE tbl_name ENGINE=INNODB for each table to take advantage of additional pages for better cardinality, then try your long running query.

Answer 2

I guess i need to change select query style, i will try the offered query alternatives because it did not work, queries are still to slow (i assume)

But these are current server stats (49GB ram, 10 core) and mysqls virtual memory is about 47~GB which is fine, no any other app is running. I think its alright ?

tuning-primer.sh
https://pastebin.com/bcjS1PWf

mysqltuner.pl
https://pastebin.com/mXUTLPQ6

my.cnf
https://pastebin.com/9jMLzhZS

Answer 3

The performance problem is primarily in the formulation of the query, not necessarily in the tuning or indexes.

Query Formulation

In general, turn IN ( SELECT ... ) into JOIN.

Tackling the last step:

SELECT  bra_id
    FROM  t3
    JOIN  t4  ON  t3.brand = t4.brand
    WHERE  t4.id IN (...commalist...)

with indexes:

t4: INDEX(id, brand)
t3: INDEX(brand, bra_id)

This starts with t4, looking for the various ids, then mapping them to brands. Then it reaches into t3 by brand to get bra_id.

I'll let you practice by doing the next two steps to get through t2 to t1.

Tuning advice

Don't blindly increase values in my.cnf, it can get you into trouble.

innodb_buffer_pool_size = 35G  -- the most important

42G is probably OK -- if you don't have too many other apps running on the same server. 42 instances may work. Some say 16 instances should be the max.

The rest of the tunables can probably be left alone.

max_heap_table_size= 8G
tmp_table_size=8G

Those are dangerously high. With them, 8GB could be allocated once (or more than once) per connection. But you have only 50GB of RAM. It would be better to set them to no more than 1% of RAM (500M or less; 32M should be just fine).

#lower_case_table_names=1

Leave commented out. Any attempt to override what is needed for the OS is likely to get you into deep weeds.

port                           = 9999

What was wrong with the default 3306?

Other

"application grabbed 10000 items in 6 hours. for the next block of items 8 hour passed" -- Please provide SHOW CREATE TABLE for the 4 tables being read, plus the table being written to. And tell us how big the tables are. I suspect you are thrashing over index updates in the target table. There is a simple solution.

Check to see if mysqld is swapping.

Answer 4

@erce

These THREE lines NEED TO BE REMOVED (just before LOGGING area)

join_buffer_size=32M
tmp_table_size=8G
read_buffer_size = 8M

LOGGING

tmp_table_size was accurately matched earlier with max_heap_table_size. This line later takes charge and get you unbalanced which is BAD.

The join_buffer_size and read_buffer_size are killing your RAM footprint. Use mysqlcalculator.com to understand why, please.

Answer 5

I used percona wizard and merge the advices. the current my.cnf is below.

I run the php application yesterday night (2018-03-05 22:41:30 UTC), but still mysql could not return any result.

the application is only adding limit 10000 and offset (i did not want to start from beginning, i am currently tracking the last offset and starting from that point.)

[mysql]

# CLIENT #
port                           = 9999
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
port                           = 9999
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM # - there is no myisam table
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 30
#thread-cache-size              = 16
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 5G
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 42G
innodb-buffer-pool-instances   = 42


thread_cache_size=100
innodb_lru_scan_depth=100
innodb_purge_threads=4
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_thread_concurrency=0
max_seeks_for_key=32
max_write_lock_count=16
thread_concurrency=35

innodb_fast_shutdown=0
innodb_file_per_table=1
default-storage-engine=innodb
concurrent_insert=2
join_buffer_size=32M
tmp_table_size=8G
read_buffer_size = 8M

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 0
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

Answer 6

The my.cnf data in the Question does not match SHOW GLOBAL VARIABLES; values.

Please use this link
https://mariadb.com/kb/en/library/configuring-mariadb-with-mycnf/
to help get accurate matched values for assistance.

Then, Suggestions to consider for your my.cnf [mysqld] section include:

innodb_buffer_pool_size=8G  # from > 40G (95% of RAM will not work well)
innodb_lru_scan_depth=100  # from 1024  see refman every second digging too deep
log_warnings=2  # from 1 to include connection errors more detail
max_connect_errors=10  # from 1,000,000 no need to waste a million cycles
thread_cache_size=100  # from 16  CAP per V8 MySQ to be prepared for volume
have_symlink=NO  # to protect you server, unless you NEED it
innodb_purge_threads=4  # from 1  to accommodate when needed
innodb_read_io_threads=64  # from 4 see dba.stackexhange.com Q 5666 9/12/11 Rolando
innodb_write_io_threads=64  # from 4  per Rolando to use multi-core
innodb_thrad_concurrency=0  # another part of multi-core enabling
max_seeks_for_key=32  # from huge number, not found in 32, will not be found
max_write_lock_count=16  # give RD opportunity after nn locks
thread_concurrency=35  # to limit concurrent processing load

@Erce, To be applied in the correct active my.cnf, please. Withdrawn my offer to redo your mysqld section.