Mysql Windows Performance Problem

Question

My system:

2X E5 2680 V3 Cpu
32 GB Ram
Windows 7 Pro

Apache 2.4.41
Mysql 8.0.17

I have a script and this for Customer Online Chat Script. I have 10 clients for customer support. It uses php json and ajax. It generates high select query.

What can I do? Is there any my.cnf example for this usage?

[client]
no-beep

# pipe
# socket=0.0
port=3306

[mysql]

default-character-set = UTF8MB4

[mysqld]
port=3306
basedir="C:\AppServ/MySQL"
datadir="C:\AppServ/MySQL/data/"
character-set-server = UTF8MB4

sql-mode=""
log-output=FILE
general-log=0
general_log_file="mysql.log"
slow-query-log=1
slow_query_log_file="mysql-slow.log"
long_query_time=3
log-error="mysql-error.log"
server-id=1


secure-file-priv="datadir="C:\AppServ/MySQL/Uploads"

max_connections=500

table_open_cache=4000

tmp_table_size=1024M
max_heap_table_size=1024M

thread_cache_size=200
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=975M
key_buffer_size=512M

read_buffer_size=256K
read_rnd_buffer_size=256K


innodb_flush_log_at_trx_commit=1


innodb_log_buffer_size=1M


innodb_buffer_pool_size=20000M


innodb_log_file_size=250M


innodb_thread_concurrency=0


innodb_autoextend_increment=6
innodb_buffer_pool_instances=16


innodb_concurrency_tickets=5000


innodb_old_blocks_time=1000


innodb_open_files=600


innodb_stats_on_metadata=0


innodb_file_per_table=1


innodb_checksum_algorithm=0

back_log=80


flush_time=0


join_buffer_size=256K


max_allowed_packet=4M


max_connect_errors=100


open_files_limit=5161


sort_buffer_size=256K


table_definition_cache=1400


binlog_row_event_max_size=8K


sync_master_info=10000


sync_relay_log=10000

sync_relay_log_info=10000

default_authentication_plugin=mysql_native_password

Answer 1

I will be crazy. Only 20k line data and 10 online client how can be slow like fax :S

My system uses SSD disk. SHOW GLOBAL STATUS pastebin.com/gLvTkZ6s SHOW GLOBAL VARIABLES pastebin.com/UcJkxRKb SHOW FULL PROCESSLIST pastebin.com/AaWGFurJ SHOW ENGINE INNODB STATUS pastebin.com/aQYLW1FH SELECT name, count FROM information_schema.innodb_metrics ORDER BY name pastebin.com/Wdz1WhU6My My system generates query like : SELECT count(id) FROM gr_msgs WHERE gid = '1-573' AND id > '5989' gr_msgs

Answer 2

Dizi, from root login, at MySQL Command Prompt,

use (db_name);
CREATE INDEX gr_msgs_idx_gid_id ON gr_msgs (gid,id);

to expedite query completion for the query posted.

Rate Per Second = RPS

Suggestions to consider for your my.ini [mysqld] section

innodb_open_files=10000  # from 600 to reduce opened_tables RPhr of 28
read_rnd_buffer_size=128K  # from 256K to reduce handler_read_rnd_next RPS of 22,160
innodb_io_capacity=1900  # from 200 to use more of your SSD IOPS capacity

This is just the beginning of your workload tuning opportunities. Visit my profile, Network profile for contact info and free downloadable Utility Scripts to assist with performance tuning. Welcome to StackExchange.