0

i have a windows 7 server just have mysql installed.

i will connect to it on a local network after a while of working server response slowly and cant answer requests correctly..

my clients have up to 3k-4k request per seconds

the server have two xeon cpu with 64 gig ram and some 10 k sas hard disk

but cpu usage is lower then 10% and ram usage is about 2 gigs

my mysql configuration is like this:

[client]
port        = 3306
socket      = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_connection=5000
max_allowed_packet = 10M
table_open_cache = 512
sort_buffer_size = 20M
read_buffer_size = 20M
read_rnd_buffer_size = 80M
myisam_sort_buffer_size = 64M
thread_cache_size = 16
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

log-bin=mysql-bin

server-id   = 1

innodb_flush_method = O_DIRECT

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

how can i get a better performance this ?

asktyagi
  • 2,860
  • 2
  • 8
  • 25
peiman F.
  • 101
  • 6
  • Can you give stats when server perform slow? need I/O, CPU, memory, network and slow query details with number of active connection at that time. – asktyagi Jun 02 '19 at 11:54
  • windows work fine when mysql is slow and dont responding.how can i monitor i/o status? – peiman F. Jun 02 '19 at 11:56
  • check this https://serverfault.com/questions/188589/windows-record-i-o-stats-eqivalent-to-iostat – asktyagi Jun 02 '19 at 12:02
  • How many 10k spindles, in what type of array? Might get 100 IOPS each, not counting parity or mirrored writes. – John Mahowald Jun 02 '19 at 15:57
  • @peimanF. Because this is essentially a duplicate of dba.stackexchange.com Question 240147 would it be reasonable to ask the moderator to REMOVE this question from serverfault.com? – Wilson Hauck Jul 30 '19 at 18:42

1 Answers1

0

What Engine are you using for your tables? It should be InnoDB. In this case, you should configure

innodb_buffer_pool_size = 20G

(Or perhaps more; how much data do you have?)

Use the slowlog to find the slow queries, then let's see SHOW CREATE TABLE and EXPLAIN SELECT ....

Rick James
  • 2,463
  • 1
  • 6
  • 13