Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- [root@galera-dbnode-03 ~]# cat /etc/my.cnf
- # Template my.cnf for PXC
- # Edit to your requirements.
- [client]
- ssl-ca=/etc/ssl/mysql/ca.pem
- ssl-cert=/etc/ssl/mysql/client-cert.pem
- ssl-key=/etc/ssl/mysql/client-key.pem
- socket=/var/lib/mysql/mysql.sock
- [mysqld]
- server-id=11
- datadir=/var/lib/mysql
- socket=/var/lib/mysql/mysql.sock
- log-error=/var/log/mysqld.log
- pid-file=/var/run/mysqld/mysqld.pid
- # Binary log expiration period is 604800 seconds, which equals 7 days
- binlog_expire_logs_seconds=604800
- ######## wsrep ###############
- # Path to Galera library
- wsrep_provider=/usr/lib64/galera4/libgalera_smm.so
- # Cluster connection URL contains IPs of nodes
- #If no IP is found, this implies that a new cluster needs to be created,
- #in order to do that you need to bootstrap this node
- wsrep_cluster_address=gcomm://172.16.75.21,172.16.75.22,172.16.75.23
- # In order for Galera to work correctly binlog format should be ROW
- binlog_format=ROW
- # Slave thread to use
- wsrep_slave_threads=8
- wsrep_log_conflicts
- # This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
- innodb_autoinc_lock_mode=2
- # Node IP address
- #wsrep_node_address=192.168.70.63
- # Cluster name
- wsrep_cluster_name=core-mysql-cluster
- #If wsrep_node_name is not specified, then system hostname will be used
- wsrep_node_name=galera-dbnode-03
- wsrep_node_address=172.16.75.23
- #pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
- tls_version=TLSv1.2
- pxc_strict_mode=PERMISSIVE
- pxc_encrypt_cluster_traffic=ON
- # SST method
- wsrep_sst_method=xtrabackup-v2
- # Increase IST cache to allow Incremental recovery.
- wsrep_provider_options=gcache.size=1G;gcache.recover=yes
- #sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
- sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
- innodb_autoinc_lock_mode=2
- innodb_buffer_pool_size = 8192M
- innodb_flush_method = O_DIRECT
- max_allowed_packet=500M
- wait_timeout=7200
- max_connections = 1000
- innodb_monitor_enable=all
- default_storage_engine=InnoDB
- # Deprecated settings from MySQL 5.7
- #innodb_log_file_size=2048M
- #log_slave_updates
- #expire_logs_days=7
- #symbolic-links=0
- ssl-ca=/etc/ssl/mysql/ca.pem
- ssl-cert=/etc/ssl/mysql/server-cert.pem
- ssl-key=/etc/ssl/mysql/server-key.pem
- [root@galera-dbnode-03 ~]# pt-mysql-summary
- -bash: pt-mysql-summary: command not found
- [snip.....]
- Installed:
- percona-toolkit.x86_64 0:3.5.7-1.el7
- Dependency Installed:
- perl-IO-Socket-IP.noarch 0:0.21-5.el7 perl-IO-Socket-SSL.noarch 0:1.94-7.el7 perl-Mozilla-CA.noarch 0:20130114-5.el7
- perl-Net-LibIDN.x86_64 0:0.12-15.el7 perl-Net-SSLeay.x86_64 0:1.55-6.el7 perl-TermReadKey.x86_64 0:2.30-20.el7
- Complete!
- [root@galera-dbnode-03 ~]# pt-mysql-summary
- # Percona Toolkit MySQL Summary Report #######################
- System time | 2024-05-06 08:12:32 UTC (local TZ: SAST +0200)
- # Instances ##################################################
- Port Data Directory Nice OOM Socket
- ===== ========================== ==== === ======
- 0 0
- # MySQL Executable ###########################################
- Path to executable | /usr/sbin/mysqld
- Has symbols | No
- # Slave Hosts ################################################
- No slaves found
- # Report On Port 3306 ########################################
- User | root@localhost
- Time | 2024-05-06 10:12:32 (SAST)
- Hostname | galera-dbnode-03.nymbis.tech
- Version | 8.0.36-28.1 Percona XtraDB Cluster (GPL), Release rel28, Revision bfb687f, WSREP version 26.1.4.3
- Built On | Linux x86_64
- Started | 2024-05-05 11:21 (up 0+22:51:05)
- Databases | 0
- Datadir | /var/lib/mysql/
- Processes | 1 connected, 2 running
- Replication | Is a slave, has 0 slaves connected
- Pidfile | /var/run/mysqld/mysqld.pid (exists)
- # Processlist ################################################
- Command COUNT(*) Working SUM(Time) MAX(Time)
- ------------------------------ -------- ------- --------- ---------
- Connect 1 1 80000 80000
- Daemon 1 1 80000 80000
- Query 6 6 250 100
- Sleep 9 0 700000 80000
- User COUNT(*) Working SUM(Time) MAX(Time)
- ------------------------------ -------- ------- --------- ---------
- event_scheduler 1 1 80000 80000
- root 1 1 0 0
- system user 15 6 80000 80000
- Host COUNT(*) Working SUM(Time) MAX(Time)
- ------------------------------ -------- ------- --------- ---------
- 15 5 250 100
- connecting host 1 1 80000 80000
- localhost 2 2 80000 80000
- db COUNT(*) Working SUM(Time) MAX(Time)
- ------------------------------ -------- ------- --------- ---------
- NULL 15 8 175000 80000
- State COUNT(*) Working SUM(Time) MAX(Time)
- ------------------------------ -------- ------- --------- ---------
- init 1 1 0 0
- innobase_commit_low (-1) 1 0 0 0
- Replica has read all relay log 1 1 0 0
- Waiting for an event from Coor 4 4 250 100
- Waiting for source to send eve 1 1 80000 80000
- Waiting on empty queue 1 1 80000 80000
- wsrep 8 0 0 0
- # Status Counters (Wait 10 Seconds) ##########################
- Variable Per day Per second 15 secs
- Binlog_snapshot_position 500000000 6000 50000
- Binlog_cache_disk_use 22500
- Binlog_cache_use 4500000 50 9
- Bytes_received 3500000000 40000 50000
- Bytes_sent 1250000 15 3500
- Com_admin_commands 3000
- Com_alter_table 4
- Com_begin 4500000 50 9
- Com_change_db 2
- Com_commit 4500000 50 9
- Com_create_db 1
- Com_create_table 40
- Com_delete 125000 1
- Com_flush 1
- Com_insert 7000000 80 1
- Com_select 3000 1
- Com_set_option 3000
- Com_show_replica_status 5
- Com_show_slave_status 5
- Com_show_status 1750
- Com_show_variables 1
- Com_update 3000000 30 6
- Connections 3000 1
- Created_tmp_files 15
- Created_tmp_tables 1750
- Error_log_buffered_bytes 6000000 60
- Error_log_buffered_events 25000
- Error_log_expired_events 3000 1
- Error_log_latest_write 1750000000000000 20000000000 1000000
- Flush_commands 3
- Handler_commit 40000000 450 50
- Handler_delete 400000 4 1
- Handler_external_lock 40000000 450 60
- Handler_prepare 30000000 350 35
- Handler_read_first 60
- Handler_read_key 12500000 150 25
- Handler_read_next 17500 4
- Handler_read_rnd 3000000 35 7
- Handler_read_rnd_next 1000000 15 100
- Handler_rollback 1
- Handler_update 7000000 90 20
- Handler_write 7000000 80 50
- Innodb_buffer_pool_bytes_data 9000000000 100000 -25122
- Innodb_buffer_pool_bytes_dirty 150000000 1750 -1520435
- Innodb_buffer_pool_pages_flushed 25000000 300 125
- Innodb_buffer_pool_pages_made_not_young 7000000 80 8
- Innodb_buffer_pool_pages_made_young 600000 6
- Innodb_buffer_pool_pages_old 200000 2 -1
- Innodb_buffer_pool_read_requests 300000000 3500 600
- Innodb_buffer_pool_reads 4500000 50 1
- Innodb_buffer_pool_write_requests 150000000 1750 250
- Innodb_checkpoint_age 22500000 250 -314245
- Innodb_checkpoint_max_age 90000000 1000
- Innodb_data_fsyncs 20000000 250 90
- Innodb_data_read 90000000000 1000000 175000
- Innodb_data_reads 5000000 60 10
- Innodb_data_writes 45000000 500 200
- Innodb_data_written 400000000000 4500000 2000000
- Innodb_dblwr_pages_written 20000000 225 125
- Innodb_dblwr_writes 5000000 60 35
- Innodb_ibuf_free_list 200000 2
- Innodb_ibuf_segment_size 200000 2
- Innodb_redo_log_uuid 1000000000 12500
- Innodb_redo_log_checkpoint_lsn 700000000000 9000000 350000
- Innodb_redo_log_current_lsn 700000000000 9000000 50000
- Innodb_redo_log_flushed_to_disk_lsn 700000000000 9000000 50000
- Innodb_redo_log_logical_size 22500000 250 -314265
- Innodb_redo_log_physical_size 100000000 1250
- Innodb_redo_log_capacity_resized 100000000 1250
- Innodb_log_waits 175
- Innodb_log_write_requests 80000000 900 125
- Innodb_log_writes 20000000 250 60
- Innodb_lsn_current 700000000000 9000000 50000
- Innodb_lsn_flushed 700000000000 9000000 50000
- Innodb_lsn_last_checkpoint 700000000000 9000000 350000
- Innodb_master_thread_active_loops 80000 1
- Innodb_master_thread_idle_loops 5000
- Innodb_max_trx_id 10000000 125 25
- Innodb_os_log_fsyncs 12500000 150 40
- Innodb_os_log_written 40000000000 450000 70000
- Innodb_pages_created 1250000 15
- Innodb_pages_read 5000000 60 10
- Innodb_pages0_read 60
- Innodb_pages_written 20000000 225 125
- Innodb_purge_trx_id 10000000 125 25
- Innodb_rows_deleted 400000 4 1
- Innodb_rows_inserted 7000000 80 7
- Innodb_rows_read 3000000 35 15
- Innodb_rows_updated 3000000 30 6
- Innodb_system_rows_deleted 90
- Innodb_system_rows_inserted 90
- Innodb_system_rows_read 4500000 50 15
- Innodb_system_rows_updated 4500000 50 10
- Innodb_num_open_files 70
- Innodb_undo_tablespaces_total 2
- Innodb_undo_tablespaces_implicit 2
- Innodb_undo_tablespaces_active 2
- Innodb_secondary_index_triggered_cluster_reads 30000 2
- Key_read_requests 15
- Key_reads 8
- Mysqlx_port 35000
- Mysqlx_ssl_ctx_verify_mode 5
- Mysqlx_worker_threads 2
- Net_buffer_length 17500
- Open_table_definitions 90
- Opened_files 30
- Opened_table_definitions 175
- Opened_tables 300 1
- Performance_schema_session_connect_attrs_longest_seen 125
- Queries 15000000 175 20
- Questions 10000 3
- Select_scan 3500
- Ssl_accepts 8 1
- Ssl_finished_accepts 8 1
- Ssl_session_cache_timeout 300
- Table_locks_immediate 1750
- Table_open_cache_hits 20000000 225 25
- Table_open_cache_misses 300 1
- Threads_created 2
- Uptime 90000 1 1
- wsrep_protocol_version 10
- wsrep_last_applied 5000000 60 9
- wsrep_last_committed 5000000 60 9
- wsrep_replicated 4500000 50 9
- wsrep_replicated_bytes 20000000000 250000 50000
- wsrep_repl_keys 25000000 300 40
- wsrep_repl_keys_bytes 300000000 3500 500
- wsrep_repl_data_bytes 17500000000 200000 50000
- wsrep_received 35000
- wsrep_received_bytes 300000 3
- wsrep_local_commits 4500000 50 9
- wsrep_local_send_queue_max 2
- wsrep_local_recv_queue_max 2
- wsrep_local_cached_downto 5000000 60 8
- wsrep_flow_control_interval_low 100
- wsrep_flow_control_interval_high 100
- wsrep_commit_window 1
- wsrep_local_state 4
- wsrep_cert_index_size 25
- wsrep_cert_bucket_count 8000
- wsrep_gcache_pool_size 1250000000 12500
- wsrep_cluster_weight 1
- wsrep_cluster_conf_id 1
- wsrep_cluster_size 1
- wsrep_thread_count 9
- # Table cache ################################################
- Size | 4000
- Usage | 5%
- # Key Percona Server features ################################
- Table & Index Stats | Disabled
- Multiple I/O Threads | Enabled
- Corruption Resilient | Enabled
- Durable Replication | Not Supported
- Import InnoDB Tables | Not Supported
- Fast Server Restarts | Not Supported
- Enhanced Logging | Disabled
- Replica Perf Logging | Disabled
- Response Time Hist. | Not Supported
- Smooth Flushing | Not Supported
- HandlerSocket NoSQL | Not Supported
- Fast Hash UDFs | Unknown
- # Percona XtraDB Cluster #####################################
- # Plugins ####################################################
- InnoDB compression | ACTIVE
- # Schema #####################################################
- Specify --databases or --all-databases to dump and summarize schemas
- # Noteworthy Technologies ####################################
- SSL | Yes
- Explicit LOCK TABLES | No
- Delayed Insert | No
- XA Transactions | No
- NDB Cluster | No
- Prepared Statements | No
- Prepared statement count | 0
- # InnoDB #####################################################
- Version | 8.0.36-28
- Buffer Pool Size | 8.0G
- Buffer Pool Fill | 100%
- Buffer Pool Dirty | 1%
- File Per Table | ON
- Page Size | 16k
- Log File Size | 2 * 48.0M = 96.0M
- Log Buffer Size | 16M
- Flush Method | O_DIRECT
- Flush Log At Commit | 1
- XA Support |
- Checksums |
- Doublewrite | ON
- R/W I/O Threads | 4 4
- I/O Capacity | 200
- Thread Concurrency | 0
- Concurrency Tickets | 5000
- Commit Concurrency | 0
- Txn Isolation Level |
- Adaptive Flushing | ON
- Adaptive Checkpoint |
- Checkpoint Age | 20M
- InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
- Oldest Transaction | 0 Seconds
- History List Len | 33
- Read Views | 0
- Undo Log Entries | 0 transactions, 0 total undo, 0 max undo
- Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads
- Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites
- Pending I/O Flushes | 0 buf pool, 0 log
- Transaction States | 10xnot started
- # MyISAM #####################################################
- Key Cache | 8.0M
- Pct Used | 20%
- Unflushed | 0%
- # Security ###################################################
- Users | 54 users, 0 anon, 0 w/o pw, 0 old pw
- Old Passwords |
- # Encryption #################################################
- No keyring plugins found
- # Binary Logging #############################################
- Binlogs | 163
- Zero-Sized | 0
- Total Size | 159.9G
- binlog_format | ROW
- expire_logs_days | 0
- sync_binlog | 1
- server_id | 11
- binlog_do_db |
- binlog_ignore_db |
- # Noteworthy Variables #######################################
- Auto-Inc Incr/Offset | 1/1
- default_storage_engine | InnoDB
- flush_time | 0
- init_connect |
- init_file |
- sql_mode | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
- join_buffer_size | 256k
- sort_buffer_size | 256k
- read_buffer_size | 128k
- read_rnd_buffer_size | 256k
- bulk_insert_buffer | 0.00
- max_heap_table_size | 16M
- tmp_table_size | 16M
- max_allowed_packet | 500M
- thread_stack | 1M
- log |
- log_error | /var/log/mysqld.log
- log_warnings |
- log_slow_queries |
- log_queries_not_using_indexes | OFF
- log_slave_updates | ON
- # Configuration File #########################################
- Config File | /etc/my.cnf
- [client]
- ssl-ca = /etc/ssl/mysql/ca.pem
- ssl-cert = /etc/ssl/mysql/client-cert.pem
- ssl-key = /etc/ssl/mysql/client-key.pem
- socket = /var/lib/mysql/mysql.sock
- [mysqld]
- server-id = 11
- datadir = /var/lib/mysql
- socket = /var/lib/mysql/mysql.sock
- log-error = /var/log/mysqld.log
- pid-file = /var/run/mysqld/mysqld.pid
- binlog_expire_logs_seconds = 604800
- wsrep_provider = /usr/lib64/galera4/libgalera_smm.so
- wsrep_cluster_address = gcomm://172.16.75.21,172.16.75.22,172.16.75.23
- binlog_format = ROW
- wsrep_slave_threads = 8
- wsrep_log_conflicts
- innodb_autoinc_lock_mode = 2
- wsrep_cluster_name = core-mysql-cluster
- wsrep_node_name = galera-dbnode-03
- wsrep_node_address = 172.16.75.23
- tls_version = TLSv1.2
- pxc_strict_mode = PERMISSIVE
- pxc_encrypt_cluster_traffic = ON
- wsrep_sst_method = xtrabackup-v2
- wsrep_provider_options = gcache.size=1G;gcache.recover=yes
- sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
- innodb_autoinc_lock_mode = 2
- innodb_buffer_pool_size = 8192M
- innodb_flush_method = O_DIRECT
- max_allowed_packet = 500M
- wait_timeout = 7200
- max_connections = 1000
- innodb_monitor_enable = all
- default_storage_engine = InnoDB
- ssl-ca = /etc/ssl/mysql/ca.pem
- ssl-cert = /etc/ssl/mysql/server-cert.pem
- ssl-key = /etc/ssl/mysql/server-key.pem
- # Memory management library ##################################
- jemalloc is not enabled in mysql config for process with id 15972
- # The End ####################################################
- [root@galera-dbnode-03 ~]#
Advertisement
Add Comment
Please, Sign In to add comment
