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

How do you change vbulletin's home page to the forums instead of the default (CMS or Activity Stream)? -

-
i have created website using vbulletin have installed cms , blog modules in in admin default setting forums when enter homepage url redirecting me http://www.demo.com/content/ content cms page please help for vbulletin versions < 4.2. need modify forum index page located in top level of vbulletin directory. based on url in question it's file: http://www.demo.com/index.php here contents of file, can choose whether cms or forum default home page. /** * can choose default script here. uncomment appropriate line * set default script. note: uncomment 1 of these, must * add // comment out script(s) not want use * default script. * * can choose default script if not plan move * file root of website. */ /** * use cms default script: */ require('content.php'); /** * use forum default script: */ // require('forum.php'); for vbulletin versions >= 4.2. there's new "navigation manager". you'll find control settin...
Read more

c++ - Troubles when compiling phash program -

-
here (probably) simple problem: i'm trying use perceptual hashing library phash ubuntu 11.10. had ffmpeg installed, way i've done: sudo apt-get install libphash0 sudo apt-get install libphash0-dev then tried compile program: #include <iostream> #include <phash.h> using namespace std; int main() { ulong64 myhash=0; ph_dct_imagehash("test.jpg", myhash); cout<<myhash<<endl; } when compiling, prints out: undefined reference `ph_dct_imagehash' any suggestion? should do? in advance! matteo monti you forgot link phash library, linker cannot find function. try adding -l phash gcc command line (or update makefile). if doesn't work, maybe you'll need specify library path (the location of *.a file) using -l "/usr/lib/"
Read more