High Server Load

[hqarrse]

I run monyog by using ssh tunneling in Putty, which seems to be what you've done? As far as monyog is concerned therefore it is connecting to a local mysql. This is described here:

realprogrammers.com/ho...putty.html

I then tell monyog to login as my mysql root user and off it goes.

The effect of exceeding your configured max connections from the users point of view is a database error message, but this is much better than crashing the server by allowing mysql to attempt to deal with too many connections and be forced to use swap space - as I said you then enter a vicious circle.

[hqarrse]

By the way, this overloading is happening to me again at the moment as we've got busier - will the nightmare never end??!! This time I think the problem is elsewhere, perhaps the level of traffic on the network between the apache and mysql servers. Anyway if I discover any exciting new ways to keep things going against all odds, I'll post em here

[hqarrse]

A very useful tuning guide:

www.shinguz.ch/index.h...g_key.html

[Marcus81]

Hello people,

This is me again...

I transfer my site on dedicated server
AMD Athlon 64 X2
5600+ Dual Core
4 GB DDR2 RAM
2 x 400 GB SATA II

Server and CMS versions is:

CMS Version 9.1.1
PHP Version 5.2.0-8+etch10
MySQL Version 5.0.32-Debian_7etch5 (client: 5.0.32)
Apache2


Site have about 18 000 members, about 4000 unique visitors per day. Size of datebase is 248.4 MB

Load is about 2 , and in the moments goes to 5-6-7-8-9
At the time, site have about 70-80 visitors maximum.

How can this small numbers of visitors make such high load ?

The main prolem is with MySQL.. CPU of MySQL

13665 mysql 15 0 164m 44m 5888 S 161 1.1 15666:09 mysqld

this 161 is CPU usage!
How it can be?

What I have to do to optimize my mysql server or mysql datebase ?
One friends tell me that is because Dragonfly have many JOIN in sql queries and queries is not optimized.

I have little research through phpmyadmin and find some interesting things in "Process list" and then "Status" in PhpMyAdmin

This is what I found red there..

Slow_queries 37 The number of queries that have taken more than long_query_time seconds.

Handler_read_rnd 105 M The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.

Handler_read_rnd_next 216 G The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

Created_tmp_disk_tables 30 k The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.


Select_full_join 1,777 k The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

Sort_merge_passes 1,684 The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.


Anybody have idea how to solve this problem?

Some people tells me that on such a server I can't have a load at all

Thank you for help

[NanoCaiordo]

How long ago have you analized your tables?

[DJ Maze]

After that activate slow query logging for a few hours.
Then post the slow query log here (gzip/bzip compressed please)

[SnoSheriff]

Did you guys solve your "High Server Load"? I'm having the same issues and it's bringing my site to a crawl.