LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Server (https://www.linuxquestions.org/questions/linux-server-73/)
-   -   Server high load and slow (https://www.linuxquestions.org/questions/linux-server-73/server-high-load-and-slow-561152/)

lavinya 06-12-2007 07:00 AM

Server high load and slow
 
Hello all.

My server very slow and the is very loaded. How can I fix this problem? My server specialities and loaded softwares which are below. And sometimes there is "mysql connect failed", "Lost connection to MySQL server during query" errors. Thanks for kinds...

---
Speed:
100Mbs

LOAD:
Load avarage : 5-30 +
Cpu usage: %30-95 +
Ram usage: %70-95 +

SYSTEM INFO:
2gb ram, P4 3200 cpu, 2x250 GB SATAII hdd, Plesk 8.1.1, centos 4.5 os, apache 2.0.59, php 4.4.7, mysql 4.1.21(client),
mysql 5.0.41(server), zend optimizer, kernel 2.6.19.1.SM #5 SMP, mod_evasive, plesk firewall, qscanq+clamav,
spamassain and system up-to-date.

SOFTWARE:
phpbb 2.0.22
4images 1.7.4
joomla 1.0.12

ONLINE USER:
100-500 user online
on 2 website

my.cnf:

[mysqld]
set-variable=local-infile=0
connect_timeout=8
interactive_timeout=10
join_buffer_size=1M
key_buffer=32M
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=256
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
---

How to optimization?

Please help me..

fukawi2 06-12-2007 10:10 PM

I'm thinking there's a bottleneck with hard drive O/I since the RAM and CPU aren't 100%, so it's not too much for the CPU, and it's not having to swap out.

How are the hard drive's configured? RAID? If so, Hardware or Software? What level?

I've found SATA to be very bad in servers, although I haven't tried SATAII. Are you getting any "random" lockups / freezes etc?

lavinya 06-13-2007 12:25 PM

Quote:

Originally Posted by fukawi2
I'm thinking there's a bottleneck with hard drive O/I since the RAM and CPU aren't 100%, so it's not too much for the CPU, and it's not having to swap out.

How are the hard drive's configured? RAID? If so, Hardware or Software? What level?

I've found SATA to be very bad in servers, although I haven't tried SATAII. Are you getting any "random" lockups / freezes etc?

Hi. Thanks for your reply. There was no freezes. My server didn't locked. Only when it have been %100 (cpu or ram) mysql is get problem and giving the problem which is my said above... I don't know hardware much How can I get info about your asked question with One or sevaral commands ? Hardware infos, SATA, RAID configirations? (on shell) thanks.

p_s_shah 06-14-2007 12:29 AM

For troubleshooting Mysql Problem :
1. Add following line to /etc/my.cnf for generating slow query log.
[mysqld]
...
log-slow-queries=/var/log/mysql-slow.log
...
2. Download script for parsing slow query log.
3. Check time in executing queries and optimize slow queries.

############################################################

You can also try following script for tuning Mysql server[ On your risk ].
http://genomewiki.ucsc.edu/index.php/Tuning-primer.sh

############################################################

For Getting Hardware info :
1. Through Plesk 8.1.1 Web interface, Go to Server in left panel.
Click on System statistics under System Section.
2. For Getting Hardware info and system info, you can try following commands.
top, sar, vmstat, uptime, hdparam etc. Check manual of each command for understanding its output.

Update us with your findings.

lavinya 06-14-2007 03:51 AM

Quote:

Originally Posted by p_s_shah
For troubleshooting Mysql Problem :
1. Add following line to /etc/my.cnf for generating slow query log.
[mysqld]
...
log-slow-queries=/var/log/mysql-slow.log
...
2. Download script for parsing slow query log.
3. Check time in executing queries and optimize slow queries.

############################################################

You can also try following script for tuning Mysql server[ On your risk ].
http://genomewiki.ucsc.edu/index.php/Tuning-primer.sh

############################################################

For Getting Hardware info :
1. Through Plesk 8.1.1 Web interface, Go to Server in left panel.
Click on System statistics under System Section.
2. For Getting Hardware info and system info, you can try following commands.
top, sar, vmstat, uptime, hdparam etc. Check manual of each command for understanding its output.

Update us with your findings.

Million thanks for reply.
ok. I added this line in my.cnf
log-slow-queries=/var/log/mysql-slow.log

after
cd /usr/bin
tuning-primer.sh

output:
PHP Code:

[root@lavinya bin]# tuning-primer.sh

        
-- MYSQL PERFORMANCE TUNING PRIMER --
             - 
ByMatthew Montgomery -

MySQL Version 5.0.41 i686

Uptime 
2 days 18 hrs 46 min 55 sec
Avg
qps 92
Total Questions 
22295630
Threads Connected 
2

Server has been running 
for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit
:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

SLOW QUERIES
Current long_query_time 
10 sec.
You have 59 out of 22295676 that take longer than 10 secto complete
The slow query log is NOT enabled
.
Your long_query_time may be too highI typically set this under 5 sec.

WORKER THREADS
Current thread_cache_size 
8
Current threads_cached 
5
Current threads_per_sec 
0
Historic threads_per_sec 
0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections 
500
Current threads_connected 
2
Historic max_used_connections 
182
The number of used connections is 36
of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated 
1 G
Configured Max Per
-thread Buffers 3 G
Configured Max 
Global Buffers 46 M
Configured Max Memory Limit 
3 G
Total System Memory 
3.96 G

Max memory limit exceeds 85
of total system memory

KEY BUFFER
Current MyISAM index space 
34 M
Current key_buffer_size 
32 M
Key cache miss rate is 1 
48684
Key buffer fill ratio 
26.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size 
4 M
Current query_cache_used 
679 K
Current query_cach_limit 
1 M
Current Query cache fill ratio 
16.59 %
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL wont cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size 
2 M
Current record
/read_rnd_buffer_size 1 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size 
1.00 M
You have had 12415 queries where a join could not 
use an index properly
You should enable 
"log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If 
you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass
.

NoteThis script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found
.

OPEN FILES LIMIT
Current open_files_limit 
2500 files
The open_files_limit should typically be set to at least 2x
-3x
that of table_cache 
if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value 
256 tables
You have a total of 636 tables
You have 256 open tables
.
Current table_cache hit rate is 0%, while 100of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size 
16 M
Current tmp_table_size 
32 M
Of 1934081 temp tables
4were created on disk
Effective in
-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size 
1 M
Current table scan ratio 
404 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio 
65
You may benefit from selective 
use of InnoDB.
If 
you have long running SELECTs against MyISAM tables and perform
frequent updates consider setting 
'low_priority_updates=1'
If you have a high concurrentcy of inserts on Dynamic row-length tables
consider setting 
'concurrent_insert=2'.

[
root@lavinya bin]# /etc/init.d/psa stopall
/usr/local/psa/admin/bin/httpsdctl stophttpd stopped
Stopping Plesk
:                                            [  OK  ]
Stopping named:                                            [  OK  ]
Stopping MySQL:                                            [  OK  ]
Stopping Stopping Courier-IMAP server:
   
Stopping imap                                           [  OK  ]
   
Stopping imap-ssl                                       [  OK  ]
   
Stopping pop3                                           [  OK  ]
   
Stopping pop3-ssl                                       [  OK  ]

Shutting down psa-spamassassin service:                    [  OK  ]
Stopping httpd:                                            [  OK  ]
[
root@lavinya bin]# /etc/init.d/psa start
Starting named:                                            [  OK  ]
Starting MySQL:                                            [  OK  ]
Starting qmail:                                            [  OK  ]
Starting Courier-IMAP server:
   
Starting imapd                                          [  OK  ]
   
Starting imap-ssl                                       [  OK  ]
   
Starting pop3                                           [  OK  ]
   
Starting pop3-ssl                                       [  OK  ]

Starting psa-spamassassin service:                         [  OK  ]
Processing config directory: /usr/local/psa/admin/conf/httpsd.*.include
/
usr/local/psa/admin/bin/httpsdctl starthttpd started
Starting Plesk
:                                            [  OK  ]
[
root@lavinya bin]

[root@lavinya bin]# vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
3 0 128 301880 124720 865768 0 0 4 123 5 6 40 7 54 0
[root@lavinya bin]#

details:
http://www.lavinya.net/tuningprimer.html

Display true? or how to optimization? thanks.

p_s_shah 06-15-2007 03:39 AM

Hi again,

I asked you to download script for parsing slow query log, which is different from tuning-primer.sh. It will parse your mysql slow log file and provide you stats like which query, table, user etc. took how much time to execute. So, try to optimize those slow queries.

#######################################################################
Check for all parameters which are printed in red on http://www.lavinya.net/tuningprimer.html like
Code:

MAX CONNECTIONS
Current max_connections = 500
Current threads_connected = 1
Historic max_used_connections = 9
The number of used connections is 1% of the configured maximum.

You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

Reduce Current max_connections = 500 to Current max_connections = 300

Optimize them according to your need.
And run tuning-primer.sh again to check if everything is working fine or not.

lavinya 06-17-2007 11:51 AM

hello.
big thanks for reply.:)

but I canít understand this code:
I donít know. How to set values, example?
NOTE: Red value


Code:

[root@lavinya bin]# tuning-primer.sh

        -- MYSQL PERFORMANCE TUNING PRIMER --
            - By: Matthew Montgomery -

MySQL Version 5.0.41-log i686

Uptime = 3 days 4 hrs 34 min 17 sec
Avg. qps = 92
Total Questions = 25406284
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

SLOW QUERIES
Current long_query_time = 5 sec.
You have 115 out of 25406302 that take longer than 5 sec. to complete
The slow query log is enabled.
Your long_query_time seems to be fine

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 7
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 500
Current threads_connected = 2
Historic max_used_connections = 110
The number of used connections is 22% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 835 M
Configured Max Per-thread Buffers : 3 G
Configured Max Global Buffers : 46 M
Configured Max Memory Limit : 3 G
Total System Memory : 3.96 G

Max memory limit exceeds 85% of total system memory

KEY BUFFER
Current MyISAM index space = 35 M
Current key_buffer_size = 32 M
Key cache miss rate is 1 : 73658
Key buffer fill ratio = 34.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere


QUERY CACHE
Query cache is enabled
Current query_cache_size = 4 M
Current query_cache_used = 1 M
Current query_cach_limit = 1 M
Current Query cache fill ratio = 29.83 %
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 1 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 14127 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 2500 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 256 tables
You have a total of 636 tables
You have 256 open tables.
Current table_cache hit rate is 1%, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 2271787 temp tables, 3% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 1 M
Current table scan ratio = 28 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 112
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrentcy of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

[root@lavinya bin]#


p_s_shah 06-17-2007 10:52 PM

Visit http://dev.mysql.com/doc/refman/5.0/...variables.html for better understanding of variables.

You can modify these variables by adding them to /etc/my.cnf.
Code:

[mysqld]
key_buffer_size=24M  [Reduce it from 32M to 24M]
table_cache=512 [Increase it from 256 tables to 512 tables]
concurrent_insert=2 [Please read its description before setting it]
...

After that run tuning-primer.sh again. If everything looks good, monitor your server load for a time period. Also check if I/O is taking too much time. Run the script again after a week before restarting server, so that you have better statistics.

lavinya 06-18-2007 03:11 AM

http://www.lavinya.net/galeri/data/t...9/J0284916.jpg

thanks p_s_shah. Ok I changed to
Code:

[mysqld]
key_buffer_size=24M  [Reduce it from 32M to 24M]
table_cache=512  [Increase it from 256 tables to 512 tables] 
concurrent_insert=2  [Please read its description before setting it]
...

after run tuning-primer.sh.
same problem.
red value:

Code:

KEY BUFFER
Current MyISAM index space = 35 M
Current key_buffer_size = 24 M
Key cache miss rate is 1 : 6176
Key buffer fill ratio = 17.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere


TABLE CACHE
Current table_cache value = 512 tables
You have a total of 636 tables
You have 512 open tables.
Current table_cache hit rate is 26%, while 100% of your table cache is in use
You should probably increase your table_cache

TABLE LOCKING
Current Lock Wait ratio = 1 : 225
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'

Run again after 48 hours...
re thanks.

p_s_shah 06-18-2007 04:52 AM

Follow the instructions provided in red.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'

Reduce key_buffer_size and increase table_cache.
Code:

[mysqld]
key_buffer_size=16M  [Reduce it from 24M to 16M]
table_cache=800  [Increase it from 512 tables to 800 tables] 
concurrent_insert=2  [Please read its description before setting it]
...

Tell us if you find any improvement in server performance or not.

lavinya 07-03-2007 04:20 AM

p_s_shah million thanks. :)

Hello again. After 13 days running script.
How to optimization? Thanks.

Code:

[root@lavinya bin]# tuning-primer.sh

        -- MYSQL PERFORMANCE TUNING PRIMER --
            - By: Matthew Montgomery -

MySQL Version 5.0.41-log i686

Uptime = 13 days 1 hrs 42 min 11 sec
Avg. qps = 95
Total Questions = 108336450
Threads Connected = 2

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

SLOW QUERIES
Current long_query_time = 5 sec.
You have 2877 out of 108336722 that take longer than 5 sec. to complete
The slow query log is enabled.
Your long_query_time seems to be fine

WORKER THREADS
ERROR 2013 (HY000): Lost connection to MySQL server at 'sending authentication information', system error: 32
Current thread_cache_size = 8
Current threads_cached = 4
Current threads_per_sec = -242613
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 500
Current threads_connected = 3
Historic max_used_connections = 241
The number of used connections is 48% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 1 G
Configured Max Per-thread Buffers : 3 G
Configured Max Global Buffers : 30 M
Configured Max Memory Limit : 3 G
Total System Memory : 3.96 G

Max memory limit exceeds 85% of total system memory


KEY BUFFER
Current MyISAM index space = 38 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 817117
Key buffer fill ratio = 100.00 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.

QUERY CACHE
Query cache is enabled
Current query_cache_size = 4 M
Current query_cache_used = 1 M
Current query_cach_limit = 1 M
Current Query cache fill ratio = 45.23 %
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 1 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 60292 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.


OPEN FILES LIMIT
Current open_files_limit = 3510 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.

Your open_files_limit value seems to be fine

TABLE CACHE

Current table_cache value = 1500 tables
You have a total of 636 tables
You have 1500 open tables.
Current table_cache hit rate is 7%, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 9541878 temp tables, 4% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 1 M
Current table scan ratio = 33 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 42
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'

[root@lavinya bin]#


lavinya 07-08-2007 05:16 AM

:( please help.

p_s_shah 07-09-2007 01:47 AM

Reduce configured Max Memory Limit from 3GB to 2GB.
Code:

[mysqld]
...
table_cache = 2000
join_buffer_size = 2.00 M
key_buffer_size = 20 M
...

Did you checked for Slow Query logs as suggested before ?
Take help of your developer friend to optimize mysql queries.

lavinya 07-11-2007 05:02 AM

Quote:

Originally Posted by p_s_shah
Reduce configured Max Memory Limit from 3GB to 2GB.
Code:

[mysqld]
...
table_cache = 2000
join_buffer_size = 2.00 M
key_buffer_size = 20 M
...

Did you checked for Slow Query logs as suggested before ?
Take help of your developer friend to optimize mysql queries.

thanks for your helps.

I changed
Code:

table_cache = 2000
join_buffer_size = 2.00 M
key_buffer_size = 20 M

I didnt understand your this clum "Reduce configured Max Memory Limit from 3GB to 2GB."
Because there isnt max memory limit value in my.cnf

My current my.cnf is :
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
long_query_time = 5

I checked my "Slow Query logs" but I dont know what should I do. And there is no my develepor friend. :confused:

lavinya 07-14-2007 03:41 AM

I didnt understand your this clum "Reduce configured Max Memory Limit from 3GB to 2GB."
Because there isnt max memory limit value in my.cnf

Please help me.


All times are GMT -5. The time now is 09:38 PM.