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
9 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
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
2. Solaris
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
3. Shell Programming and Scripting
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
4. OS X (Apple)
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
5. UNIX for Dummies Questions & Answers
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. UNIX and Linux Applications
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
7. Solaris
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
8. Shell Programming and Scripting
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
9. Red Hat
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
LEARN ABOUT DEBIAN
mysql2dlf
MYSQL2DLF.IN(1) LogReport's Lire Documentation MYSQL2DLF.IN(1)
NAME
mysql2dlf - convert mysql logfiles to dlf format
SYNOPSIS
mysql2dlf
DESCRIPTION
mysql2dlf converts a MySQL query log file to DLF format. Information on MySQL can be found on http://www.mysql.org/. The query log file is
defined by the --log[=file] option of mysqld, or the log=file setting in MySQL configuration file.
Such a log file looks like e.g.
/usr/sbin/mysqld, Version: 3.23.43-debug-log, started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
011218 15:02:58 1 Connect root@localhost on
1 Query SHOW VARIABLES LIKE 'pid_file'
1 Shutdown
011226 21:35:59 6 Connect joe@localhost on
6 Init DB nmrshiftdb
6 Query SHOW VARIABLES
or
42401 Query SELECT
bannerID,
banner,
format,
width,
height,
alt,
url
FROM
banners
WHERE
act = 'true' AND (keyw = 'fullsize' OR keyw = 'global')
The generic database dlf format is described in database.xml.
EXAMPLES
To process a log as produced by MySQL:
$ mysql2dlf < mysql-log
mysql2dlf will be rarely used on its own, but is more likely called by lr_log2report:
$ lr_log2report mysql < /var/log/mysql-log
NOTES
The parser has been tested for query log files for MySQL 3.23.46 only.
BUGS
This converter stores all log files information in one big nested array in memory, before writing. Therefore, memory consumption is linear
with the size of the log.
SEE ALSO
mysql(1), mysqld(1)
VERSION
$Id: mysql2dlf.in,v 1.12 2010/12/05 20:36:45 wraay Exp $
COPYRIGHT
Copyright (C) 2001, 2002 Stichting LogReport Foundation LogReport@LogReport.org
This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program (see COPYING); if not, check with
http://www.gnu.org/copyleft/gpl.html.
AUTHOR
Egon Willighagen <egonw@logreport.org>
Lire 2.1.1 2011-12-31 MYSQL2DLF.IN(1)