mysql、postgresql、oceanbase调优

一、mysql

1、my.cnf

[mysqld_safe]
log-error=/data/mysql/log/mysql.log
pid-file=/data/mysql/run/mysqld.pid[client]
socket=/data/mysql/run/mysql.sock
default-character-set=utf8[mysqld]
basedir=/usr/local/mysql
tmpdir=/data/mysql/tmp
datadir=/data/mysql/data
socket=/data/mysql/run/mysql.sock
port=3306
user=root
default_authentication_plugin=mysql_native_password
ssl=0 #关闭ssl
max_connections=2000  #设置最大连接数
back_log=2048  #设置会话请求缓存个数
performance_schema=OFF #关闭性能模式
max_prepared_stmt_count=128000#file
innodb_file_per_table=on #设置每个表一个文件
innodb_log_file_size=1500M #设置logfile大小
innodb_log_files_in_group=32 #设置logfile组个数
innodb_open_files=4000 #设置最大打开表个数#buffers
innodb_buffer_pool_size=230G #设置buffer pool size,一般为服务器内存60%
innodb_buffer_pool_instances=16 #设置buffer pool instance个数，提高并发能力
innodb_log_buffer_size=64M #设置log buffer size大小#tune
sync_binlog=1 #设置每次sync_binlog事务提交刷盘
innodb_flush_log_at_trx_commit=1 #每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去
innodb_use_native_aio=1 #开启异步IO
innodb_spin_wait_delay=180 #设置spin_wait_delay 参数，防止进入系统自旋
innodb_sync_spin_loops=25  #设置spin_loops 循环次数，防止进入系统自旋
innodb_spin_wait_pause_multiplier=25 #设置spin lock循环随机数
innodb_flush_method=O_DIRECT #设置innodb数据文件及redo log的打开、刷写模式
innodb_io_capacity=20000 # 设置innodb后台线程每秒最大iops上限
innodb_io_capacity_max=40000 #设置压力下innodb后台线程每秒最大iops上限
innodb_lru_scan_depth=9000 #设置page cleaner线程每次刷脏页的数量
innodb_page_cleaners=16  #设置将脏数据写入到磁盘的线程数
table_open_cache_instances=32 #设置打开句柄分区数
table_open_cache=30000 #设置打开表的数量#perf special
innodb_flush_neighbors=0 #检测该页所在区(extent)的所有页，如果是脏页，那么一起进行刷新，SSD关闭该功能
innodb_write_io_threads=16 #设置写线程数
innodb_read_io_threads=16 #设置读线程数
innodb_purge_threads=32  #设置回收已经使用并分配的undo页线程数
innodb_adaptive_hash_index=0sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES

2、调优参数

innodb_thread_concurrency	该参数用于控制InnoDB存储引擎在执行查询时使用的并发线程数。	高并发场景，可调整该参数，减少InnoDB层的资源争用。 建议取默认值为0，它表示默认情况下不限制线程并发执行的数量。
innodb_read_io_threads	该参数用于控制InnoDB存储引擎在执行读操作时使用的IO线程数。	可以根据CPU核数及读写比例设置该参数来提高性能。
innodb_write_io_threads	该参数用于控制InnoDB存储引擎在执行写操作时使用的IO线程数。	可以根据CPU核数及读写比例设置该参数来提高性能。
innodb_buffer_pool_instances	该参数用于控制InnoDB缓冲池的实例数量。	增加缓冲池实例数可以减少内存热点页面的争用，从而提高内存读取的并发性。 建议设置为8～32。
innodb_open_files	在innodb_file_per_table模式下，该参数用于指定InnoDB存储引擎打开的最大文件数。	建议此值调大一些，尤其是表特别多的情况。
innodb_buffer_pool_size	该参数指定了InnoDB缓冲池的大小。InnoDB缓冲池是一个内存区域，用于存储InnoDB表的数据和索引。	增加该参数，可使更多数据Cache在内存，减少IO操作。 建议设置为物理内存的70%左右。
innodb_log_buffer_size	该参数用于控制InnoDB事务日志缓冲池的大小。	如果该参数设置得太小，InnoDB事务日志缓冲池可能会频繁地刷新到磁盘，导致性能下降。 如果该参数设置得太大，会占用过多的内存资源，导致系统的稳定性受到影响。 合理增加日志缓冲池大小，可提升事务提交效率，尤其在大事务和高并发场景，减少日志刷盘的次数。通过查看innodb_log_wait值，来调整innodb_log_buffer_size值。以下两种情况可以考虑调大innodb_log_buffer_size参数的值： 如果innodb_log_wait值不等于0并且持续增长时，表明InnoDB存储引擎的日志缓冲池（innodb_log_buffer）空间不足，导致innodb_log_buffer需要等待。 如果innodb_log_wait值较高，说明InnoDB存储引擎需要更多的日志缓冲池空间。 默认值是64MB。
innodb_io_capacity	该参数定义了InnoDB后台线程每秒可用的IO操作数，即IOPS。	建议设置为磁盘IOPS总能力的75%（磁盘IOPS是指innodb_page_size大小的数据块时的IOPS）。
innodb_log_files_in_group	该参数用于指定InnoDB日志文件组中的日志文件数量。	-
innodb_log_file_size	该参数用于设置InnoDB日志文件的大小。	如果存在大量写操作，建议增加日志文件大小，但日志文件过大，会影响数据恢复时间。 如果是非生产环境，测试极限性能时，尽量调大日志文件。 如果是商用场景，需要考虑数据恢复时间，综合折中后设置日志文件大小。