optimization - MySQL my.cnf performance tuning recommendations -

-

i kind of hoping might able offer assistance optimizing my.cnf file extremely high volume mysql database server.

our architecture follows:  memory     : 96gb cpus       : 12 os & mysql : 64-bit disk space : 1.2 tb db engine  : myisam

our web application used 300 client simultaneously. need our my.cnf tuned give best possible performance infrastructure.

i aware indexes , optimized queries major factor in this, start system configured , follow systematically re-engineering our queries accordingly.

here our current my.cnf file content:  [mysqld] datadir=/home/mysql socket=/home/mysql/mysql.sock user=mysql  log-bin=mysql-bin server-id=1 # disabling symbolic-links recommended prevent assorted security risks symbolic-links=1  log-slow-queries = /var/log/mysqld_slow_queries.log long_query_time = 10  max_connections = 500  key_buffer_size = 32768m #max_allowed_packet = 2m #table_open_cache = 128 #sort_buffer_size = 1024k #net_buffer_length = 64k #read_buffer_size = 1024k #read_rnd_buffer_size = 1024k #myisam_sort_buffer_size = 8m query_cache_size = 128m query_cache_limit = 128m  interactive_timeout = 300 wait_timeout = 300  # added values after load testing thread_cache_size = 8 #tmp_table_size = 256m #max_heap_table_size = 256m #table_cache = 512 #join_buffer_size = 512  log-error=/var/log/mysqld.log  innodb_buffer_pool_size=128m #innodb_file_per_table #innodb_log_file_size=250m ##innodb_buffer_pool_size=64m #innodb_buffer_pool_size=1024m #innodb_log_buffer_size=4m ##log-bin=mysql-bin  [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid  #[myisamchk] #key_buffer = 64m #sort_buffer = 64m #read_buffer = 16m #write_buffer = 16m

any suggestions? folks.

edit rolandomysqldba

since data myisam, please run query , show output

select concat(round(kbs/power(1024, if(powerof1024<0,0,if(powerof1024>3,0,powerof1024)))+0.4999), substr(' kmg',if(powerof1024<0,0, if(powerof1024>3,0,powerof1024))+1,1)) recommended_key_buffer_size (select least(power(2,32),kbs1) kbs (select sum(index_length) kbs1 information_schema.tables engine='myisam' , table_schema not in ('information_schema','mysql')) aa ) a, (select 3 powerof1024) b;

@ rolando - thanks...the results of query 4g.

try starting percona wizard , comparing recommendations against current settings 1 one. don't worry there aren't many applicable settings might think.

https://tools.percona.com/wizard

everyone points key_buffer_size first have addressed. 96gb memory i'd wary of tiny default value (likely 96m!).


Comments

Post a Comment

Popular posts from this blog

django - How can I change user group without delete record -

-
on website user have 1 group. , user can change group. it's made user.groups.clear() and user.groups.add(new_group) but it's not efficient, because there 2 sql query: delete, insert. how can change group update query? user , group related each other using manytomanyfield . means intersection table exists relating both entities, , if don't specify model map (using through attribute) django creates 1 you. looking @ sources django.contrib.auth.models see that's case. fortunatly, can access intermediary model using through attribute of manager (in case, user.groups.through ). can use regular model. example: >>> alice = user.objects.create_user('alice', 'alice@example.com', 'alicepw') >>> employee = group.objects.create(name='employee') >>> manager = group.objects.create(name='manager') >>> alice.groups.add(employee) >>> alice.groups.all() [<group: employee...
Read more

java - Need to add SOAP security token -

-
i need set custom soap header attribute on jax-ws generated webservice client. case webservice calls must go through proxy server requiring specific token (recieved web request header) present in soap request header. e.g.: 1 carserviceservice service = null; 2 service = new carserviceservice(new url(url), new qname(qname); 3 carserviceendpoint port = service.getcarserviceport(); it seems in line 3 wsdl retrieved , call fails due missing security token. 1 point direction on how done? a detailed example has been mentioned here: creating , deploying jax-ws web service on tomcat 6 this article shows how create , use security token .
Read more

java - EclipseLink JPA Object is not a known entity type -

-
i have strange problem eclipselink , object want persist. have 1 object (keypointlistimpl) stores object keypointimpl in list. persisting keypointimpl objects works great if try persist keypointlistimpl object java.lang.illegalargumentexception says object keypointimpl isn't known entity type. here keypointimpl code: @entity @table(name="keypoints") public class keypointimpl implements keypoint { @id @generatedvalue(strategy = generationtype.identity) private int id; @enumerated(enumtype.string) private detectortype keypointtype; private float x; private float y; private float size; private float angle; private float response; private int octave; private int classid; ... } here keypointlistimpl code: @entity @table(name="keypointlists") public class keypointlistimpl implements keypointlist { @id @generatedvalue(strategy = generationtype.identity) private int id; @onetoone(cascade={ca...
Read more