Tối ưu hiệu suất khi xử lý nhiều dữ liệu trong MariaDB

duysexy · February 16, 2024, 4:52pm

chào mọi người. web của em đang sử dụng php8, mariadb 10.6.
hiện tại em có vài bảng với số dòng khoảng vài triệu, khi truy vấn tìm 1 row theo id thì mấy giây mới tìm được, và mỗi page em lấy ra 50 row thì cũng load khá lâu. không biết cách mà các site khác dùng để xử lý là gì nhỉ. đổi qua csdl khác, hay chia dữ liệu ra nhiều bảng khác nhau chẳng hạn, hoặc do em chưa tối ưu cấu hình cho mariadb cũng như cấu hình vps của em thấp?, em đang sử dụng nginx + php + mariadb trên almax linux 8. vps 8 core, 8gb ram.
em cảm ơn.

Duy_Hoang98 · February 16, 2024, 4:07pm

nội dung file /etc/my.cnf

[client]
socket=/var/lib/mysql/mysql.sock

[mysql]
max_allowed_packet = 1024M

[mysqld]
local-infile=0
ignore-db-dir=lost+found
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#bind-address=127.0.0.1
# optimized my.cnf for MariaDB 5.5.x
# by eva2000
# vbtechsupport.com

tmpdir=/var/lib/mariadbtmp

innodb=ON
#skip-federated
#skip-pbxt
#skip-pbxt_statistics
#skip-archive
#skip-name-resolve
#old_passwords
back_log = 1024
max_connections = 1000
key_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 2048M
join_buffer_size = 12M
read_buffer_size = 12M
sort_buffer_size = 12M
table_definition_cache = 8192
table_open_cache = 4096
thread_cache_size = 256
wait_timeout = 180
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 1024M
#max_seeks_for_key = 4294967295
#group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 12M
bulk_insert_buffer_size = 128M
# query_cache boost for MariaDB >10.1.2+
# https://community.centminmod.com/posts/30811/
query_cache_limit = 1024K
query_cache_size = 512M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default_storage_engine = InnoDB

log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/home/dlemp.demo/logs/mysql-slow.log
log-error=/home/dlemp.demo/logs/mysql.log

# innodb settings
#innodb_large_prefix=1
innodb_purge_threads=1
#innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 4G

## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
#innodb_buffer_pool_instances=4

innodb_log_files_in_group = 2
innodb_log_file_size = 1G
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 0
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=90
innodb_flush_method = O_DIRECT
#innodb_support_xa=1

# 200 * # DISKS
innodb_io_capacity = 300
innodb_io_capacity_max = 2000
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_flush_neighbors = 1

# mariadb settings
[mariadb]
thread_handling = pool-of-threads
thread_pool_size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 64M
aria_log_purge_type = immediate 
aria_pagecache_buffer_size = 64M
aria_sort_buffer_size = 64M

[mariadb-5.5]
#innodb_file_format = Barracuda
innodb_file_per_table = 1

#ignore_db_dirs=
query_cache_strip_comments=0

innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe] 
socket=/var/lib/mysql/mysql.sock
log-error=/home/dlemp.demo/logs/mysql.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 1024M

[myisamchk] 
tmpdir=/var/lib/mariadbtmp
key_buffer = 256M 
sort_buffer = 32M
read_buffer = 32M
write_buffer = 32M

[mysqlhotcopy]
interactive-timeout

[mariadb-10.0]
#innodb_file_format = Barracuda
innodb_file_per_table = 1

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=4M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

csdl · February 16, 2024, 2:16pm

ID là Primary key của table?
Có 3 lý do có thể làm cho câu lệnh của bạn chậm: Lưu ý, đây là giải pháp chung cho tất cả các RDBMS chứ không riêng gì MySQL/MariaDB

Chưa tạo Primary Key trên cột ID ( Unique index sẽ tự tạo trên cột ID khi bạn set nó là PrimaryKey).
=> Solution: tạo PK
Table statistics không bị out-of-date, vd như statistics ghi nhận table chỉ có và trăm rows, nhưng thật sự nó có tới vài triệt rows.
=> Solution: update table statistics
Cậu lệnh của bạn (điều kiện where) không phù hợp để engine quyết định xài index.
=> Solution: chạy explain plain xem thực sự index có được dùng trong query plan ko. Nếu không phải sửa lại câu query cho phù hợp.
Vd: nếu điều kiện (1) và (2) thỏa, nhưng điều kiện query bạn dùng WHERE any-function(ID)=123 thì index trên ID sẽ không được dùng.

duysexy · February 16, 2024, 3:10pm

hi anh. e vừa kiểm tra.

order_id của em ko phải là PK nhưng em đã set UNIQUE cho nó ( PK là cột id khác)
trong phpmyadmin em số lượng row hiển thị đúng là có khác với khi em select count (số lượng chính xác ) thật. em chạy lệnh ANALYZE TABLE thì số lượng có thay đổi, nhưng vẫn thiếu so với số lượng thực. có cách nào để nó update đúng ko anh.
chạy explain query, cái này làm như thế anh nhỉ.

library · February 16, 2024, 4:18pm

Cậu có thể show cho bọn tớ schema của các bảng liên quan tới câu query, và câu query của cậu được không?
Nếu được, cậu có thể chạy explain query và show kết quả ở đây.
Không có thêm thông tin thì không có giải pháp đâu cậu

Cậu dùng VPS nảy chạy tất cả những thứ này á?

Cái này tớ không nghĩ là vấn đề đâu. InnoDB (tớ đoán cậu dùng DB engine mặc định, cho tớ biết nếu không phải nhé) không bao giờ lưu chính xác số dòng như MyISAM đâu.

csdl · February 16, 2024, 4:11pm

(3) EXPLAIN PLAN, bạn có thể seach chủ đề này trên google, vd như đây là một bài hướng dẫn dùng EXPLAIN PLAN:

Joe · February 16, 2024, 5:38pm

…I am not a MariaDB expert, but I found this LINK about “How to tune MariaDB Database for High Performance.”