Server high load and slow

p_s_shah · 07-15-2007, 10:32 PM

Hello Again,

Link for mysql slow log parser :
http://www.retards.org/projects/mysql/
http://www.mysqlperformanceblog.com/...low_log_parser
Usage : mysql_slow_log_parser <logfile>

Also check the description on first link for basic info.

Check if any particular query is taking too much time. If yes, try to resolve the issue like size of table or poor indexing.

lavinya · 07-17-2007, 04:16 AM

Quote:

Originally Posted by p_s_shah

Hello Again,

Link for mysql slow log parser :
http://www.retards.org/projects/mysql/
http://www.mysqlperformanceblog.com/...low_log_parser
Usage : mysql_slow_log_parser <logfile>

Also check the description on first link for basic info.

Check if any particular query is taking too much time. If yes, try to resolve the issue like size of table or poor indexing.

thanks thanks for reply.

It must be like this?

Code:

[mysqld]
set-variable=local-infile=0
key_buffer_size=20M
connect_timeout=8
interactive_timeout=10
join_buffer_size=2.00M
max_allowed_packet=3M
max_connections=500
max_connect_errors=10
myisam_sort_buffer_size=4M
read_buffer_size=2M
read_rnd_buffer_size=2M
sort_buffer_size=2M
table_cache=2000
concurrent_insert=2
thread_cache_size=8
thread_concurrency=4
wait_timeout=8
query_cache_size=4M
query_cache_limit=1M
query_cache_type=1
default-character-set=latin1
log-slow-queries=/var/log/mysql-slow.log
mysql_slow_log_parser=/var/log/mysql-slow.log
long_query_time = 5

how can we use http://www.retards.org/projects/mysq...low_log_parser
if you answer I will be happy.

p_s_shah · 07-17-2007, 11:07 PM

Remove following line from /etc/my.cnf :
mysql_slow_log_parser=/var/log/mysql-slow.log

Either download the script from given links or simply copy/paste complete script to a text file and save it to mysql_slow_log_parser.
mysql_slow_log_parser is used to parse output of your /var/log/mysql-slow.log [ Or say file specified by log-slow-queries directive. ]

Run it from command prompt by :
mysql_slow_log_parser /var/log/mysql-slow.log

Your query time should not be higher. If the query time is higher try to optimize query by :
1. Deleting old records after taking proper backup to reduce table size.
2. Indexing.

File /var/log/mysql-slow.log should look something like :

Code:

use <dbname>;
UPDATE <tablename> SET time_average='433' WHERE page='332';
# Time: 061120 13:12:21
# User@Host: <user>[<user>] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 0  Rows_examined: 0

lavinya · 11-30-2007, 09:38 AM

I installed latest version tuning-primer.sh
After 48 hours run:

#tuning-primer.sh

RESULT:

All values "...seems to be fine." Green colour(No problem)

But this pictures RED, ORANGE colour (warning)
(JOINS TAB, KEY BUFFER TAB, QUERY CACHE TAB)

http://img529.imageshack.us/img529/1565/50222905yb1.png
http://img57.imageshack.us/img57/2536/20300450yc0.png

How to optimizing values of RED, ORANGE lines?

I have;
P 6600 Core2Duo FS
4096 MB DDR2
2x250 GB SATAII
Plesk 8.2.1 Linux
Centos 5.0
system full up to date
And i have 10 websites, (AVARAGE today unique visitors: 25.000-35.000 and 2.000.000 - 3.000.000 hits)
APF, Plesk firewall, mod_evasive...

Please help me. Thanks.

tanveer · 12-01-2007, 11:40 AM

Hi,
I used to get a similiar sort of problem a month back, the same mysql error. My CPU Usage was constantly showing 99.9%. I did a lot of changing in my.cnf parameters but nothing worked. Finally the problem find out was on the developer side. They did a mistake in their coding. After correction, application is working just fine till today.

lavinya · 12-01-2007, 03:04 PM

Thanks for reply tanveer.
But how to solved my problem?

Quote:

Originally Posted by lavinya

I installed latest version tuning-primer.sh
After 48 hours run:

#tuning-primer.sh

RESULT:

All values "...seems to be fine." Green colour(No problem)

But this pictures RED, ORANGE colour (warning)
(JOINS TAB, KEY BUFFER TAB, QUERY CACHE TAB)

http://img529.imageshack.us/img529/1565/50222905yb1.png
http://img57.imageshack.us/img57/2536/20300450yc0.png

How to optimizing values of RED, ORANGE lines?

I have;
P 6600 Core2Duo FS
4096 MB DDR2
2x250 GB SATAII
Plesk 8.2.1 Linux
Centos 5.0
system full up to date
And i have 10 websites, (AVARAGE today unique visitors: 25.000-35.000 and 2.000.000 - 3.000.000 hits)
APF, Plesk firewall, mod_evasive...

Please help me. Thanks.