MySQL Forums
Forum List  »  Performance

MySQL tuning
Posted by: Rob G
Date: June 23, 2013 07:58PM

Hi ,

I've a mysql installation on my server (2G RAM/ 2CPU) where Im running 3 databases;

I'm seeing performance issues with these DB's where a select query is taking 10 secs to execute for an output of 6500 rows.

Here is my my.cnf config. can anyone suggest me how can improve the performnace of the select query ?Let me know if I've missed something

[mysqld]
innodb_file_per_table=1
default-storage-engine=MyISAM
#####################################
max_user_connections=75
max_connections=75
###
innodb_buffer_pool_size=512M

wait_timeout = 60
key_buffer = 1024M
sort_buffer = 8M
join_buffer = 8M
myisam_sort_buffer_size = 64M
max_allowed_packet = 32M
max_heap_table_size = 64M
thread_stack = 128K
table_cache = 1800
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 768K
myisam_sort_buffer_size = 48M
thread_cache_size = 512
query_cache_type = 1
query_cache_limit = 8M
query_cache_size = 256M
tmp_table_size = 64M
log_slow_queries = ON
log-slow-queries=/var/log/mysql/slow-query.log
query_prealloc_size = 65536
query_alloc_block_size = 131072
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4

max_write_lock_count = 1 #To force MySQL to temporarily elevate the priority of all SELECT statements that are waiting for a table after a specific number of inserts to the table occur. This allows READ locks after a certain number of WRITE locks.
low_priority_updates = 1

open_files_limit=3118
[isamchk]
key_buffer = 512M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[myisamchk]
key_buffer = 512M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL tuning
1894
June 23, 2013 07:58PM
855
June 23, 2013 08:42PM
873
June 23, 2013 09:06PM
942
June 24, 2013 08:07PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.