Mysql my.cnf settings help required


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Mysql my.cnf settings help required
# 1  
Old 07-20-2010
Mysql my.cnf settings help required

Dear Friends

I run my website "www.egully.com" on a dedicated server with a config of memory 8GB and 8GB RAM
the CPU load average often shoots from 2-3 to 20-30 and the site becomes extremely slow. On investigation we found a lot of temporary mysql tables are been created which is taking up lot of CPU resource. The temp tables are like :

/var/tmp/#sql_25dd_0.MYD
/var/tmp/#sql_25dd_1.MYD
/var/lib/mysql/egully1_cpdata2009/isc_product_search.MYD
/var/lib/mysql/egully1_cpdata2009/isc_product_search.MYD
/var/lib/mysql/egully1_cpdata2009/isc_product_search.MYD
/var/lib/mysql/egully1_cpdata2009/isc_product_search.MYD
/var/lib/mysql/egully1_cpdata2009/isc_product_search.MYI

These files are in order of 0.5-2.5GB.
After restarting mysql all the temp files gets deleted and system gets back to normal. But, after some time the problem again resurfaces.

I doubt if there is some problem with my.cnf file setting. Our current settings are
[mysqld]
set-variable = max_connections=200
socket = /var/lib/mysql/mysql.sock
key_buffer =512M
table_cache = 1024
open_files_limit = 16344
join_buffer_size = 32M
read_buffer_size = 32M
sort_buffer_size = 32M
tmp_table_size=256M
read_rnd_buffer_size=16M
max_heap_table_size=256M
myisam_sort_buffer_size=256M
thread_cache_size=8
thread_cache=8192
query_cache_type=1
query_cache_limit=256M
query_cache_size=256M
thread_concurrency=8
wait_timeout = 30
connect_timeout = 30
interactive_timeout = 60
long_query_time=1
log-slow-queries = /var/log/slowqueries.log
max_allowed_packet=128M


Can some one suggest what I need to do for improving the speed and avoid creation of the temp files
 
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Red Hat

Missing the MySQL extension which is required by WordPress.

hi i started in the apache userdirectory and virtual host now iwant installtion wordpress in home one of the useres But this error is Your PHP installation appears to be missing the MySQL extension which is required by WordPress. And the configuration files and other items I was enter... (0 Replies)
Discussion started by: mnnn
0 Replies

2. Shell Programming and Scripting

Help Required - facing error when trying to connect to a mysql by ssh to another server

Hi i am new to unix shell scripting i have the below requirement connect to server B and then connect to mysql in the server B and query for a particular record and capture that value but when i perform this i am facing an error can u guys help me on this:confused::confused: ... (2 Replies)
Discussion started by: Hamdul
2 Replies

3. Solaris

Reboot required on Server, Just confirm my settings.

Hi Guys, I need to reboot one Server as the newly inserted disk is not getting detected in system , I have also confirmed with Sun Support and finally it was the reboot which was required after doing all troubleshooting stuff. So I have disassembled the mirror and kept working disk's single... (3 Replies)
Discussion started by: manalisharmabe
3 Replies

4. UNIX and Linux Applications

MySQL missing my.cnf

Hi, SLES11 : I've installed the following MySQL Package: MySQL-client-community-5.1.54-1.sles11.x86_64 MySQL-devel-community-5.1.54-1.sles11.x86_64 MySQL-server-community-5.1.54-1.sles11.x86_64 MySQL-shared-community-5.1.54-1.sles11.x86_64Problem: I don't see any my.cnf file (find / -name... (1 Reply)
Discussion started by: hiddenshadow
1 Replies

5. UNIX for Dummies Questions & Answers

Required PUTTY Settings

I am using putty to conect various unix/solais/hp boxes... The font size appear in the putty are very small and the black background and white text is very usual ... I want to change the size of the text appear to som ewhat bigger and text color to change green and cursor appearence to... (2 Replies)
Discussion started by: girija
2 Replies

6. OS X (Apple)

What settings are required for login to CVS using Terminal in Mac OS X?

Hi All, I want to login to CVS using terminal. I am executing the following command in the terminal :- export CVSROOT=: pserver:ags_rd@istcvs.corp.apple.com:/istcvs/CVSHOME cvs login But i get the following error : Afreens-iMac:buildTest Afreen$ export CVSROOT=:... (1 Reply)
Discussion started by: Afreen
1 Replies

7. Shell Programming and Scripting

What settings are required for login to CVS using Terminal in Mac OS X?

Hi All, I want to login to CVS using terminal. I am executing the following command in the terminal :- export CVSROOT=: pserver:ags_rd@istcvs.corp.apple.com:/istcvs/CVSHOME cvs login But i get the following error : Afreens-iMac:buildTest Afreen$ export CVSROOT=:... (3 Replies)
Discussion started by: Afreen
3 Replies

8. Solaris

Creating A Solaris Zone - Unable to install - Are network settings required?

Hi i have created a solaris zone but have not yet connected any network cables for this new zone, can i set the zone up without running the command 'add net' and not adding an ip or physical interface? i tried to add dummy internet settings and get this zonecfg:coddr> add net... (4 Replies)
Discussion started by: newbiesolaris10
4 Replies

9. Shell Programming and Scripting

Getting required fields from a test file in required fromat in unix

My data is something like shown below. date1 date2 aaa bbbb ccccc date3 date4 dddd eeeeeee ffffffffff ggggg hh I want the output like this date1date2 aaa eeeeee I serached in the forum but didn't find the exact matching solution. Please help. (7 Replies)
Discussion started by: rdhanek
7 Replies
Login or Register to Ask a Question