Mysql standalone server uses all cpu's..!

gurupal_s

Hi,

I have one mysql on standalone server on ec2 with 16 cores and 32GB ram. CPU goes full 100% (1600% on 16cores) tested with 100 concurrent connections (100 users at a time). Anyone help me out to get rid of this. My traffic will go upto 1k concurrent users.

Need some configuration with the above settings.

my.cnf file :-

max_allowed_packet = 120M
thread_stack = 192K
log_queries_not_using_indexes = OFF

myisam-recover-options = BACKUP
max_connections = 5000
query_cache_type = 1
query_cache_limit = 4M
query_cache_size = 256M
thread_cache_size = 16K

innodb_buffer_pool_size = 8G
innodb_flush_method = O_DIRECT
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_file_format = Antelope

myisam_sort_buffer_size = 16MB
key_buffer_size = 20G
max_heap_table_size = 128M
tmp_table_size = 128M
join_buffer_size = 32M
read_buffer_size = 32M
read_rnd_buffer_size = 1M
sort_buffer_size = 32M
open_files_limit = 65535

skip-name-resolve

doghouch

What exactly are you querying?

You’re using InnoDB already, so it might be something with your application.

wiunix

you can use " https://github.com/major/MySQLTuner-perl "

wiunix

@doghouch said:
What exactly are you querying?

You’re using InnoDB already, so it might be something with your application.

yes, maybe he/she need to look the running queries using mytop or something like this

rick2610

Enable the slow query log and analyze those queries with explain to see if indexes are used.

inklight

doghouch said: What exactly are you querying?

>

+1 this may up to app you are using , I'm assuming it's real-time game or some interactive app (maybe chat etc ) , 100 concurrent connections are not that much specially for such server single queries on much lower server don n light speed ,

doghouch

@inklight said:

doghouch said: What exactly are you querying?

>

+1 this may up to app you are using , I'm assuming it's real-time game or some interactive app (maybe chat etc ) , 100 concurrent connections are not that much specially for such server single queries on much lower server don n light speed ,

100% on 16 cores is a bit ridiculous, so yeah... rip

gurupal_s

@inklight said:

doghouch said: What exactly are you querying?

>

+1 this may up to app you are using , I'm assuming it's real-time game or some interactive app (maybe chat etc ) , 100 concurrent connections are not that much specially for such server single queries on much lower server don n light speed ,

It's study material website build on opencart. Traffic is always between 200-900 concurrent users.

Hxxx

Hard to say without taking a look at the queries, joins, etc.

inklight

gurupal_s said: It's study material website build on opencart

opencart are not well optimized script , you should find away to cache some of it page , e.g either try with Nginx FastCGI cache or any software related solution .

Maxsam4

@gurupal_s said:
It's study material website build on opencart. Traffic is always between 200-900 concurrent users.

Share link? Study material fetching 900 concurrent users must be worth checking out.

For your original question, try MariaDB and some object caching.

Janevski

@wiunix said:

@doghouch said:
What exactly are you querying?

You’re using InnoDB already, so it might be something with your application.

yes, maybe he/she need to look the running queries using mytop or something like this

She? There are no women on the internet. They are busy doing more important things in life rather than screwing around with networks and servers and vpses and hostings and domains and other meaningless crap.