0

i have a windows 2012 server with 16 core cpu and 64 gig of ram and 3*146gig sas 15k that have been used as a database server and just have mysql installed as a part of xampp.

the mysql version is 5.7.x

i tried some different configs but when some parameters changing mysql dont start anyway!

1)when i set innodb_dedicated_server mysql dont start

innodb_dedicated_server = true

2)when i set innodb_buffer_pool_size , innodb_buffer_pool_instances , innodb_buffer_pool_chunk_size together mysql dont work too

innodb_buffer_pool_size = 40G
innodb_buffer_pool_instances = 40
innodb_buffer_pool_chunk_size = 1G

but when i set these configures mysql start working correctly (innodb_buffer_pool_instances , innodb_buffer_pool_chunk_size are commented)

port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_connection=5000
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

max_allowed_packet=1048576


innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64


#innodb_buffer_pool_size=40G
#innodb_log_file_size=2G

#innodb_dedicated_server = true

innodb_buffer_pool_size = 20G
#innodb_buffer_pool_instances = 20
#innodb_buffer_pool_chunk_size = 1G

how can i get a better performance?!

updates:

my.cnf

SHOW GLOBAL STATUS;

SHOW GLOBAL VARIABLES;

SHOW FULL PROCESSLIST;

SHOW ENGINE INNODB STATUS;

MySQLTuner.pl

Improve this question
2
  • Additional information request. Post on pastebin.com and share the links. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report F) SHOW ENGINE INNODB STATUS; for server workload tuning analysis. MySQLTuner.pl (perl) for Windows is available at github.com/pmachapman
    – Wilson Hauck
    Jun 13, 2019 at 18:40
  • 1
    @WilsonHauck the requested data added to post update.thank you.
    – peiman F.
    Jun 15, 2019 at 6:47

2 Answers 2

Reset to default
0

Rate Per Second = RPS - Suggestions to consider for your my.ini [mysqld] section

max_connections=1200  # from 5000 because max_used_connections = 930
thread_cache_size=256  # from 12 to reduce threads_created of 746 in 3 days
read_rnd_buffer_size=256K  # from 8M to reduce handler_read_rnd_next RPS of 4,628
read_buffer_size=256K  # from 20M to reduce about 98% of read effort by CPU, media

MySQLTuner.pl indicates you have 32G rather than 64G indicated in your question per their line Maximum possible memory usage: 169.7G (530% of installed RAM)

The posted results of MySQLTuner.pl appears to just be part of their reporting.

What is going on with all the KILL activities reported by SHOW FULL PROCESSLIST?

Your version is 10.1.39 and MariaDB rather than 5.7.x as listed in the Question, please correct the Question with Edit.

Disclaimer: I am the content author of website mentioned in my profile, Network profile. Additional helpful suggestions could be provided.

Improve this answer
8
  • 1
    thank you , i put your suggested setting in my my.cnf except max_connection (because when server go live i need more connections ).now i have better performance and server answer faster.the new mysqltunner is here https://pastebin.com/4c4ifN42.is there any thing else can changed for better result ?! also i dont understand why server ram usage is low , its about 6gig of 64 gigs!
    – peiman F.
    Jun 16, 2019 at 12:18
  • @peimanF. Thanks for the positive news on better performance and the newer MySQLTuner.pl report. The goal of MySQL is to use memory WHEN needed to keep consumption (expense) low. There are more suggestions available, see our website and use the SHOP Analysis Services, please.
    – Wilson Hauck
    Jun 16, 2019 at 20:09
  • @peimanF. Thanks for the Accepted. At our website we have FREE Utility Scripts available for download to improve performance.
    – Wilson Hauck
    Jun 22, 2019 at 13:15
  • 1
    thank you for your asking.Server is live and every thing work well until 300 mil of records.but more then 300 mil record and about 30 gig of innodb data it start to goes laggy and slow response!! is this normal ?
    – peiman F.
    Jul 30, 2019 at 20:10
  • 1
    yes,ram usage is going up to 45 gig but when it reach the 45gig the response time is going up :(
    – peiman F.
    Jul 30, 2019 at 20:11
0

Check the logs. There should be some clue of what caused the crash. I don't think the settings that you highlighted would cause a crash, but let's change them some:

innodb_buffer_pool_size = 40G        -- good
innodb_buffer_pool_instances = 16    -- 16 is "good enough"; more is "diminishing returns"
innodb_buffer_pool_chunk_size = 128M -- only needed for dynamically changing bp
max_connection = 1000

('instances' has only a small impact on performance.) If my advice is not good, read the Windows-specific recommendations in https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances

If you have HDD (not SSD), then lower some settings:

innodb_io_capacity = 400
innodb_read_io_threads = 16
innodb_thread_concurrency = 0
innodb_write_io_threads = 16

If you are concerned about performance, turn on the slowlog with long_query_time=1 so you can catch the naughtier queries.

innodb_dedicated_server does not exist until 8.0.3.

Improve this answer
4
  • 1
    is instances * chunk_size must be equal to buffer_pool_size? then 16*128 = 2G. also my hard is sas 15k i can use 64 for i/o thread?
    – peiman F.
    Jun 11, 2019 at 4:04
  • @peimanF. - Chunk_size is only for dynamically resizing the buffer pool. That's something you probably won't ever do. SO, ignore chunk_size.
    – Rick James
    Jun 11, 2019 at 5:40
  • @peimanF. - SAS is spinning drive; you are not likely to be able to use many threads with that old, slower technology. (SSDs are faster.)
    – Rick James
    Jun 11, 2019 at 5:42
  • @peimanF. - You asked about crashing and about performance; I cannot explain the crashes, but improving the tuning should at least make things 'better'.
    – Rick James
    Jun 11, 2019 at 5:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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