1

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
11
  • 1
    Please provide the query, explain, and analyze for it. – J.D. Dec 15 '20 at 12:22
  • 1
    i can not run explain analyze query it gives syntax error :( – Diziburda Kordexa Dec 15 '20 at 15:15
  • 1
    Your query has a syntax error? Can you post the query in your question please? – J.D. Dec 15 '20 at 15:56
  • 1
    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 system generates query like : SELECT count(id) FROM gr_msgs WHERE gid = '1-573' AND id > '5989' gr_msgs has 20k line data... And Wilson – Diziburda Kordexa Dec 16 '20 at 9:22
  • 1
    @WilsonHauck I am in Turkey. My main problem is my script uses ajax so it makes request high select query. Queries very basic and result is too. but it creates high request traffic in mysql server. – Diziburda Kordexa Dec 17 '20 at 12:06
0

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

0

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.

3
  • Thank you this config better than mine. Can i contact with you via email ? Thank you again :) – Diziburda Kordexa Dec 18 '20 at 14:48
  • @DiziburdaKordexa My email address is wlhauck at aol.com and I look forward to hearing from you. Thanks for your positive comment about the config changes. – Wilson Hauck Dec 19 '20 at 10:50
  • @DiziburdaKordexa Happy New Year. My email still functions, drop me a message, please. – Wilson Hauck Jan 1 at 11:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.