MySQL Performance Tuning mit Tuning-Primer.sh Script

[Huschi]

Zitat von diastasi

Can you Please give me any suggestion?

Tell us, what's your problem is about?
You haven't ask any question.

My only advice is to look at your used memory. Try to use only 384MB.

huschi.

[diastasi]

It seemed that the response of the server is really slow. The physical connection is fast but the sql responses too late. e.g. a phpbb forum with 40queries sometimes needs 49sec!!!!
Also many times stops working.
Im seeing the /var/log/apache2/error_log

[Thu Nov 26 15:54:12 2009] [error] (12)Cannot allocate memory: fork: Unable to fork new process
[Thu Nov 26 15:54:12 2009] [error] (12)Cannot allocate memory: fork: Unable to fork new process
[Thu Nov 26 15:54:13 2009] [error] (12)Cannot allocate memory: fork: Unable to fork new process
[Thu Nov 26 15:54:13 2009] [error] (12)Cannot allocate memory: fork: Unable to fork new process
[Thu Nov 26 15:54:13 2009] [error] (12)Cannot allocate memory: fork: Unable to fork new process

I run a script to see what is the memory status and it says:

VPS Memory:
 Total: 384 mb Used: 189 mb (49%) Free: 195 mb (51%)

a) Where exactly can I see how much memory apache and sql consumes?

b) What changes can I make to make system more stable?

c) When I stop/start mysql, it works for some hours fast but later again the same

[Huschi]

Zitat von diastasi

a) Where exactly can I see how much memory apache and sql consumes?

It's very difficult. Because there are many different data to sum. tuning-primer tells you that for mysql.

b) What changes can I make to make system more stable?

Change to a dedicated Server with min 1GB Ram.

c) When I stop/start mysql, it works for some hours fast but later again the same

Same anser...

huschi.

[diastasi]

thank you for the answers And Im going soon to move to another package.
But I have just a other question:
I run "top" and I got e.g. this:

top - 13:21:58 up 5 days, 19:10,  1 user,  load average: 0.06, 0.11, 0.09
Tasks:  40 total,   1 running,  39 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0% us,  0.1% sy,  0.0% ni, 99.9% id,  0.0% wa,  0.0% hi,  0.0% si
Mem:    786432k total,   185540k used,   600892k free,        0k buffers
Swap:        0k total,        0k used,        0k free,        0k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 3818 mysql     16   0 38452  16m 5260 S    0  2.2   0:54.60 mysqld
26304 root      16   0  129m  15m 8136 S    0  2.0   0:02.40 apache2
18166 root      16   0  129m 8808 1084 S    0  1.1   0:00.19 apache2
26581 root      16   0  129m 8716  992 S    0  1.1   0:00.03 apache2
 9955 root      15   0  129m 8716  992 S    0  1.1   0:00.02 apache2
 7549 root      16   0  129m 8716  992 S    0  1.1   0:00.01 apache2
 3394 root      16   0  129m 8716  992 S    0  1.1   0:00.01 apache2
15749 root      16   0  129m 8716  992 S    0  1.1   0:00.01 apache2
16014 root      15   0  129m 8716  992 S    0  1.1   0:00.00 apache2
16016 root      16   0  129m 8716  992 S    0  1.1   0:00.01 apache2
11470 root      16   0  129m 8716  992 S    0  1.1   0:00.00 apache2
30356 root      16   0  129m 8716  992 S    0  1.1   0:00.00 apache2
27738 root      16   0 94740 5272 1532 S    0  0.7   4:17.07 python2.4
26324 www-data  25   0 91488 5088  828 S    0  0.6   0:00.00 apache2
26328 www-data  16   0 91828 4960  712 S    0  0.6   0:00.16 apache2
17414 bind      16   0 53124 3884 2120 S    0  0.5   0:01.86 named
30415 root      16   0 44844 3320 2532 S    0  0.4   0:00.15 sshd
17854 sw-cp-se  16   0 25888 3064 1428 S    0  0.4   0:02.76 sw-cp-serverd
32602 root      16   0 11220 2860 1372 S    0  0.4   0:00.19 bash
 3703 root      18   0  5716 1332 1056 S    0  0.2   0:00.01 mysqld_safe
24449 root      16   0 10532 1264  960 R    0  0.2   0:00.74 top
28072 root      15   0 25768 1252  804 S    0  0.2   0:00.69 sshd
19677 root      15   0  8540 1168  904 S    0  0.1   0:00.00 courierlogger
19721 root      16   0  8540 1168  904 S    0  0.1   0:00.11 courierlogger
19704 root      20   0  8408 1020  768 S    0  0.1   0:00.00 courierlogger
19757 root      18   0  8408 1020  768 S    0  0.1   0:00.00 courierlogger
19965 root      16   0 10864  936  736 S    0  0.1   0:00.07 xinetd
28203 root      16   0 11436  936  716 S    0  0.1   0:00.31 cron
19674 root      15   0 10688  920  640 S    0  0.1   0:00.00 couriertcpd
19716 root      15   0 10684  916  640 S    0  0.1   0:00.11 couriertcpd
19694 root      18   0 10684  908  632 S    0  0.1   0:00.00 couriertcpd
19753 root      21   0 10684  908  632 S    0  0.1   0:00.00 couriertcpd
    1 root      15   0  6044  672  556 S    0  0.1   0:01.29 init
20222 qmailr    16   0  2744  644  296 S    0  0.1   0:00.39 qmail-rspawn
15811 root      15   0  3660  604  456 S    0  0.1   0:05.12 syslogd
 3819 root      16   0  2560  532  452 S    0  0.1   0:00.00 logger
20217 qmails    16   0  2624  500  396 S    0  0.1   0:04.66 qmail-send
20219 qmaill    16   0  2572  488  408 S    0  0.1   0:01.20 splogger
20221 root      15   0  2612  412  300 S    0  0.1   0:00.06 qmail-lspawn
20223 qmailq    15   0  2568  380  300 S    0  0.0   0:00.37 qmail-clean

Is it normal that apache runs as root so many times???
And what this 129m means? 129MB only for apache????
At the specific time I had noone visiting my websites! Not even bots

[Huschi]

Zitat von diastasi

Is it normal that apache runs as root so many times???

Yes if it is configured to do so.

And what this 129m means? 129MB only for apache????

All Apache-Processes use the same 129MB of Code-Space. The number on the right side tell you how many Data-Memory each Process has allocated.

At the specific time I had noone visiting my websites! Not even bots

Sometimes the preforked Apache-Childs die slowly.
You have configured "MaxClients 10" plus one Master => 11 Apache-Processes.

huschi.

[diastasi]

Zitat von Huschi

Yes if it is configured to do so.

Is there something I can do to help?

Zitat von Huschi

Sometimes the preforked Apache-Childs die slowly.
You have configured "MaxClients 10" plus one Master => 11 Apache-Processes.

I tried the following. It goes a bit better (I think ) But maybe I made things worst? (I see the same No of apache:root proccesses)

<IfModule mpm_prefork_module>
    StartServers       2
    MinSpareServers    2
    MaxSpareServers    5
    MaxClients        50
    MaxRequestsPerChild   300
</IfModule>

<IfModule mpm_worker_module>
    StartServers       2
    MaxClients        50
    MinSpareThreads    2
    MaxSpareThreads    5
    ThreadsPerChild     10
    MaxRequestsPerChild   300
</IfModule>

Do you think I can keep this? Or you have any better solution?

regards
Anthony

[Huschi]

You don't need more Apache-Clients. I think 10 are enough.
More PHP-Scripts will use more MySQL-Connections at the same time. This will slow the database down.

A Vserver is very different in his resources as a dedicated. The Hardware is separated in many little "Serverlets". You cannot trust all messages in your box.

huschi.

[diastasi]

Zitat von Huschi

A Vserver is very different in his resources as a dedicated. The Hardware is separated in many little "Serverlets". You cannot trust all messages in your box.

huschi.

I see your point. Sometimes Im thinking if it would be better to have a good hosting instead of a small Vserver.

Anyway I will try Max clients 10 too. I dont have anything to loose

I tried to deinstall itk-mpm and voila.... It removed:
-Apache2, Php, Plesk, etc.
Hehehe, everything in Vserver (Hopefully I have backup)

How can someone removes mpm-itk from a Debian/Plesk installation?
The answer from Server4You was that maybe all these became from mpm-itk

[Huschi]

Oha, you have the mpm-itk installed? Not mpm-prefork?
There must be another part of configuration for that. (Or it runs on default values.)

To deinstall you must install another mpm first or at the same time. Try to install e.g. mpm-prefork and hope it is going to deinstall the mpm-itk.

huschi.

[amaier161]

Ich wollte eben mal den Tuning-Primer starten und habe diese Fehlermeldung erhalten:

s15293006:/usr/bin # tuning-primer.sh
mysql: unknown variable 'mysql.allow_persistent=Off'
mysql: unknown variable 'mysql.allow_persistent=Off'

mysql: unknown variable 'mysql.allow_persistent=Off'
mysql: unknown variable 'mysql.allow_persistent=Off'
mysql: unknown variable 'mysql.allow_persistent=Off'
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

mysql: unknown variable 'mysql.allow_persistent=Off'
mysql: unknown variable 'mysql.allow_persistent=Off'
MySQL Version

mysql: unknown variable 'mysql.allow_persistent=Off'
mysql: unknown variable 'mysql.allow_persistent=Off'
/usr/bin/tuning-primer.sh: line 534: /: syntax error: operand expected (error token is "/")

Ideen?

Danke, Alex

[Huschi]

mysql: unknown variable 'mysql.allow_persistent=Off'

Der Fehler ist in der my.cf oder ~/my.cf zu finden. Dort steht in der [mysql] irgendwo diese Config.

/usr/bin/tuning-primer.sh: line 534: /: syntax error: operand expected (error token is "/")

Was steht bei Dir in Zeile 534?
Oder: Welche Versions-Nummer von tuning-primer verwendest Du?

huschi.

[amaier161]

Hallo Huschi,

mysql: unknown variable 'mysql.allow_persistent=Off'

Der Fehler ist in der my.cf oder ~/my.cf zu finden. Dort steht in der [mysql] irgendwo diese Config.

Das steht bei mir in der my.cnf (ist aber wohl das selbe. Oder?). Was muss ich nun damit machen, die Zeile löschen/kommentieren?

/usr/bin/tuning-primer.sh: line 534: /: syntax error: operand expected (error token is "/")

Was steht bei Dir in Zeile 534?
Oder: Welche Versions-Nummer von tuning-primer verwendest Du?

In Zeile 534 steht:

PHP-Code:

queries_per_sec=$(($questions/$uptime)) 


und Version ist: 1.5-r5 / Released: 2009-11-22

Vielen Dank, Alex

[Huschi]

Zitat von amaier161

Was muss ich nun damit machen, die Zeile löschen/kommentieren?

Du kannst auch die ganze Datei löschen.
Damit hast Du 3 Möglichkeiten die alle zum Ziel führen. Darfst Dir selber aussuchen, was Du davon umsetzt.

Version ist: 1.5-r5 / Released: 2009-11-22

Ok, Du hast die neueste Version. Aber der Fehler könnte ein Folgefehler von oben sein. Mach also erstmal das Eine...

huschi.

[amaier161]

Jau, jetzt läufts. Muss nun eben nur noch warten bis der Server 48 Stunden gelaufen ist.

Eine letzte Frage hätte ich noch: Was macht bzw. was macht 'mysql.allow_persistent' nicht?

Danke, Alex

[Huschi]

Zitat von amaier161

Muss nun eben nur noch warten bis der Server 48 Stunden gelaufen ist.

Da die Änderung nur in der [mysql]-Sektion stattfand war ein Server-Restart unnötig. Denn diese Einstellungen gelten nur für den Client.

Was macht bzw. was macht 'mysql.allow_persistent' nicht?

Soll ich das für Dich googlen? Oder kannst Du das vielleicht doch alleine?

huschi.