MySQL Performance Tuning mit Tuning-Primer.sh Script

Huschi · Sep 24, 2009

conym18 said:
Worin besteht der Unterschied zw. "sort_buffer_size" und "sort_buffer"?

Leichte Frage: "sort_buffer" gibt es nicht.
Dafür aber sort_buffer_size.

Hab grad nach BC gesucht. Wo bekomme ich das her?

Aus Deiner Paket-Verwaltung. Das Paket heißt bei den meisten Distris tatsächlich und voll unerwartet "bc".

huschi.

noto · Oct 19, 2009

@conym18

probier mal diese Konfiguration:

Apache httpd.conf

Code:

Timeout 30
KeepAlive On
MaxKeepAliveRequests 150
KeepAliveTimeout 2


<IfModule prefork.c>
StartServers       1
MinSpareServers    1
MaxSpareServers    2
ServerLimit       20
MaxClients        20
MaxRequestsPerChild  8000
</IfModule>

MySQL my.cnf

Code:

[mysqld]

key_buffer = 2M
net_buffer_length = 1K

sort_buffer_size = 32K
myisam_sort_buffer_size = 32K

read_buffer_size = 32K
read_rnd_buffer_size = 32K
join_buffer_size = 2M

query_cache_size = 2M
thread_cache = 8
table_cache = 512
max_allowed_packet = 256K

max_connections = 20
low_priority_updates = 1
long_query_time = 2

danach startest du Apache und MySQL neu. Der Server kann dann 20 Besucher gleichzeitig bedienen. Alle anderen müssen eben warten bis ein Apache Prozess frei wird. Bei einem KeepAliveTimeout von 2 Sekunden geht das sehr rasch.

cmssys · Oct 24, 2009

Hi

Danke für den Beitrag,
Ich wollte auch mal Fragen wo ich was Einstellen kann,Vielleicht kann mir jemand tip geben.Das sieht ziemlich wuest aus nehme ich.Na ja wenn mann sich nicht so auskennt.Es wird für VB Forum benutzt.Vielleicht kann ich ein oder andere Module Abschalten unter Apache.
Es sind 4-5 Foren Foren drauf.

Danke
MfG

Server von Keyweb Dedicated
Typ: KM9200
CPU: Core 2Duo 2x1,8 GHz
RAM: 2 GB
HD: 2 x 320 GB
BS-Version: REDHAT

my.cnf

Code:

# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password	= your_password
port		= 3306
socket		= /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port		= 3306
socket		= /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
join_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache_size = 128
query_cache_size = 32M
table_cache = 3048
net_buffer_length = 2M
max_connections = 50
low_priority_updates = 1
long_query_time = 2
log_slow_queries=/var/log/mysqld.slow.log
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id	= 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Point the following paths to different dedicated disks
#tmpdir		= /tmp/		
#log-update 	= /path-to-dedicated-directory/hostname

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

httpd.conf

Code:

#--- Header start from /etc/headers/httpd.conf ----
#
###################################################
##                                               ##
## DO NOT CHANGE THIS FILE. CHANGES WILL BE LOST ##
## ==============================================##
##     CHANGE /etc/headers/httpd.conf insteed    ##
##                                               ##
###################################################
#
#
# This is the main Apache server configuration file.  It contains the
# configuration directives that give the server its instructions.
# See <URL:http://httpd.apache.org/docs/2.2/> for detailed information.
# In particular, see
# <URL:http://httpd.apache.org/docs/2.2/mod/directives.html>
# for a discussion of each configuration directive.
#
#
# Do NOT simply read the instructions in here without understanding
# what they do.  They're here only as hints or reminders.  If you are unsure
# consult the online docs. You have been warned.  
#
# The configuration directives are grouped into three basic sections:
#  1. Directives that control the operation of the Apache server process as a
#     whole (the 'global environment').
#  2. Directives that define the parameters of the 'main' or 'default' server,
#     which responds to requests that aren't handled by a virtual host.
#     These directives also provide default values for the settings
#     of all virtual hosts.
#  3. Settings for virtual hosts, which allow Web requests to be sent to
#     different IP addresses or hostnames and have them handled by the
#     same Apache server process.
#
# Configuration and logfile names: If the filenames you specify for many
# of the server's control files begin with "/" (or "drive:/" for Win32), the
# server will use that explicit path.  If the filenames do *not* begin
# with "/", the value of ServerRoot is prepended -- so "logs/foo.log"
# with ServerRoot set to "/etc/httpd" will be interpreted by the
# server as "/etc/httpd/logs/foo.log".
#

### Section 1: Global Environment
#
# The directives in this section affect the overall operation of Apache,
# such as the number of concurrent requests it can handle or where it
# can find its configuration files.
#

#
# Don't give away too much information about all the subcomponents
# we are running.  Comment out this line if you don't mind remote sites
# finding out what major optional modules you are running
ServerTokens OS

#
# ServerRoot: The top of the directory tree under which the server's
# configuration, error, and log files are kept.
#
# NOTE!  If you intend to place this on an NFS (or otherwise network)
# mounted filesystem then please read the LockFile documentation
# (available at <URL:http://httpd.apache.org/docs/2.2/mod/mpm_common.html#lockfile>);
# you will save yourself a lot of trouble.
#
# Do NOT add a slash at the end of the directory path.
#
ServerRoot "/etc/httpd"

#
# PidFile: The file in which the server should record its process
# identification number when it starts.
#
PidFile run/httpd.pid

#
# Timeout: The number of seconds before receives and sends time out.
#
Timeout 30

#
# KeepAlive: Whether or not to allow persistent connections (more than
# one request per connection). Set to "Off" to deactivate.
#
KeepAlive ON

#
# MaxKeepAliveRequests: The maximum number of requests to allow
# during a persistent connection. Set to 0 to allow an unlimited amount.
# We recommend you leave this number high, for maximum performance.
#
MaxKeepAliveRequests 150

#
# KeepAliveTimeout: Number of seconds to wait for the next request from the
# same client on the same connection.
#
KeepAliveTimeout 1
HostnameLookups off

##
## Server-Pool Size Regulation (MPM specific)
## 

# prefork MPM
# StartServers: number of server processes to start
# MinSpareServers: minimum number of server processes which are kept spare
# MaxSpareServers: maximum number of server processes which are kept spare
# ServerLimit: maximum value for MaxClients for the lifetime of the server
# MaxClients: maximum number of server processes allowed to start
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule prefork.c>
StartServers      8
MinSpareServers    8
MaxSpareServers   16
ServerLimit      100
MaxClients       100
MaxRequestsPerChild  4000
</IfModule>

# worker MPM
# StartServers: initial number of server processes to start
# MaxClients: maximum number of simultaneous client connections
# MinSpareThreads: minimum number of worker threads which are kept spare
# MaxSpareThreads: maximum number of worker threads which are kept spare
# ThreadsPerChild: constant number of worker threads in each server process
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule worker.c>
StartServers         2
MaxClients         150
MinSpareThreads     25
MaxSpareThreads     75 
ThreadsPerChild     25
MaxRequestsPerChild  0
</IfModule>

#
# Listen: Allows you to bind Apache to specific IP addresses and/or
# ports, in addition to the default. See also the <VirtualHost>
# directive.
#
# Change this to Listen on specific IP addresses as shown below to 
# prevent Apache from glomming onto all bound IP addresses (0.0.0.0)
#
#Listen 12.34.56.78:80
Listen 80

#
# Dynamic Shared Object (DSO) Support
#
# To be able to use the functionality of a module which was built as a DSO you
# have to place corresponding `LoadModule' lines at this location so the
# directives contained in it are actually available _before_ they are used.
# Statically compiled modules (those listed by `httpd -l') do not need
# to be loaded here.
#
# Example:
# LoadModule foo_module modules/mod_foo.so
#
LoadModule auth_basic_module modules/mod_auth_basic.so
LoadModule auth_digest_module modules/mod_auth_digest.so
LoadModule authn_file_module modules/mod_authn_file.so
LoadModule authn_alias_module modules/mod_authn_alias.so
LoadModule authn_anon_module modules/mod_authn_anon.so
LoadModule authn_dbm_module modules/mod_authn_dbm.so
LoadModule authn_default_module modules/mod_authn_default.so
LoadModule authz_host_module modules/mod_authz_host.so
LoadModule authz_user_module modules/mod_authz_user.so
LoadModule authz_owner_module modules/mod_authz_owner.so
LoadModule authz_groupfile_module modules/mod_authz_groupfile.so
LoadModule authz_dbm_module modules/mod_authz_dbm.so
LoadModule authz_default_module modules/mod_authz_default.so
LoadModule ldap_module modules/mod_ldap.so
LoadModule authnz_ldap_module modules/mod_authnz_ldap.so
LoadModule include_module modules/mod_include.so
LoadModule log_config_module modules/mod_log_config.so
LoadModule logio_module modules/mod_logio.so
LoadModule env_module modules/mod_env.so
LoadModule ext_filter_module modules/mod_ext_filter.so
LoadModule mime_magic_module modules/mod_mime_magic.so
LoadModule expires_module modules/mod_expires.so
LoadModule deflate_module modules/mod_deflate.so
LoadModule headers_module modules/mod_headers.so
LoadModule usertrack_module modules/mod_usertrack.so
LoadModule setenvif_module modules/mod_setenvif.so
LoadModule mime_module modules/mod_mime.so
LoadModule dav_module modules/mod_dav.so
LoadModule status_module modules/mod_status.so
LoadModule autoindex_module modules/mod_autoindex.so
LoadModule info_module modules/mod_info.so
LoadModule dav_fs_module modules/mod_dav_fs.so
LoadModule vhost_alias_module modules/mod_vhost_alias.so
LoadModule negotiation_module modules/mod_negotiation.so
LoadModule dir_module modules/mod_dir.so
LoadModule actions_module modules/mod_actions.so
LoadModule speling_module modules/mod_speling.so
LoadModule userdir_module modules/mod_userdir.so
LoadModule alias_module modules/mod_alias.so
LoadModule rewrite_module modules/mod_rewrite.so
LoadModule proxy_module modules/mod_proxy.so
LoadModule proxy_balancer_module modules/mod_proxy_balancer.so
LoadModule proxy_ftp_module modules/mod_proxy_ftp.so
LoadModule proxy_http_module modules/mod_proxy_http.so
LoadModule proxy_connect_module modules/mod_proxy_connect.so
LoadModule cache_module modules/mod_cache.so
LoadModule suexec_module modules/mod_suexec.so
LoadModule disk_cache_module modules/mod_disk_cache.so
LoadModule file_cache_module modules/mod_file_cache.so
LoadModule mem_cache_module modules/mod_mem_cache.so
LoadModule cgi_module modules/mod_cgi.so

Code:

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

MySQL Version 5.1.22-rc-community-log i686

Uptime = 0 days 0 hrs 17 min 20 sec
Avg. qps = 20
Total Questions = 21001
Threads Connected = 17

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
[url]http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html[/url]
Visit [url]http://www.mysql.com/products/enterprise/advisors.html[/url]
for info about MySQL's Enterprise Monitoring and Advisory Service

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

BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
Setting expire_logs_days will allow you to remove old binary logs automatically
See [url]http://dev.mysql.com/doc/refman/5.1/en/purge-master-logs.html[/url]
Binlog sync is not enabled, you could loose binlog records during a server crash

WORKER THREADS
Current thread_cache_size = 128
Current threads_cached = 9
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 50
Current threads_connected = 16
Historic max_used_connections = 25
The number of used connections is 50% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 781 M
Configured Max Per-thread Buffers : 710 M
Configured Max Global Buffers : 426 M
Configured Max Memory Limit : 1.10 G
Physical Memory : 1.94 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 682 M
Current key_buffer_size = 384 M
Key cache miss rate is 1 : 77
Key buffer free ratio = 85 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 12 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 38.64 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 7 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 2.00 M
You have had 6 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 = 6156 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_open_cache = 3048 tables
Current table_definition_cache = 128 tables
You have a total of 1501 tables
You have 1530 open tables.
The table_cache value seems to be fine
You should probably increase your table_definition_cache value.

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 1023 temp tables, 9% were created on disk
Created disk tmp tables ratio seems fine

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

TABLE LOCKING
Current Lock Wait ratio = 1 : 351
You may benefit from selective use of InnoDB.
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

sly · Nov 10, 2009

Hallo zusammen,

ich bin momentan auch dabei mein vServer zu optimieren, da der Server überfordet wird und laut error logs zu wenig RAM verfügbar ist. Vielleicht könnt Ihr mir ein bisschen auf der weg dorthin helfen. Die MySQL Konfiguration hab ich soweit fertig.

Momentan hab ich ca. 1800 visits am Tag, Tendenz steigend. Reicht der vServer überhaupt dafür aus ohne die Besucher "lange warten" zu lassen oder sollte ich eher auf einen Dedizierten Server umsteigen?

Server Daten:
vServer PREMIUM X3 @ s4u
6.000 MHz Prozessor-Power
50.000 MB Webspace (RAID5)
1.024 MB DDR-RAM
2.048 MB FlexRAM

top

Code:

Tasks:  50 total,   1 running,  49 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.6% us,  0.3% sy,  0.0% ni, 99.1% id,  0.0% wa,  0.0% hi,  0.0% si
Mem:   2097152k total,   189472k used,  1907680k free,        0k buffers
Swap:        0k total,        0k used,        0k free,        0k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 8151 root      16   0 16624 1192  912 R    0  0.1   0:00.03 top
    1 root      15   0   732  296  244 S    0  0.0   0:00.89 init
 1453 root      11  -4 12260  772  444 S    0  0.0   0:00.00 udevd
 3173 messageb  16   0 14552  832  636 S    0  0.0   0:00.11 dbus-daemon
 3847 root      16   0 23920 1588 1328 S    0  0.1   0:00.04 console-kit-dae
 4007 root      15   0  5776  680  548 S    0  0.0   0:07.07 syslogd
 4010 root      16   0 61740 1324  704 S    0  0.1   0:06.78 sshd
 4017 root      16   0 18952  968  760 S    0  0.0   0:00.77 xinetd
 4044 root      18   0 17976  748  456 S    0  0.0   0:00.00 couriertcpd
 4048 root      18   0 13648  944  684 S    0  0.0   0:00.00 courierlogger
 4057 root      21   0 17976  748  456 S    0  0.0   0:00.00 couriertcpd
 4059 root      18   0 13648  944  684 S    0  0.0   0:00.00 courierlogger
 4066 root      16   0 17988  800  500 S    0  0.0   0:00.29 couriertcpd
 4068 root      16   0 13780 1116  836 S    0  0.1   0:00.22 courierlogger
 4077 root      19   0 17976  748  456 S    0  0.0   0:00.00 couriertcpd
 4081 root      18   0 13648  944  684 S    0  0.0   0:00.00 courierlogger
 5133 sw-cp-se  16   0 46664 3348 1508 S    0  0.2   0:05.97 sw-cp-serverd
 5165 root      16   0 20440  812  624 S    0  0.0   0:00.09 cron
 5211 named     16   0 77940  12m 1936 S    0  0.6   0:00.42 named
11418 drweb     16   0 42120  37m 1640 S    0  1.8  11:47.34 drwebd.real
30669 qmails    16   0  3708  560  452 S    0  0.0   0:00.79 qmail-send
30670 qmaill    16   0  3652  532  444 S    0  0.0   0:00.19 splogger
30671 root      16   0  3692  456  340 S    0  0.0   0:00.06 qmail-lspawn
30672 qmailr    16   0  3688  472  352 S    0  0.0   0:00.06 qmail-rspawn
30674 qmailq    16   0  3648  420  336 S    0  0.0   0:00.08 qmail-clean
31958 root      15   0  259m  18m 8324 S    0  0.9   0:18.00 httpd2-prefork
 3659 wwwrun    16   0  259m  10m  480 S    0  0.5   0:00.00 httpd2-prefork
19483 root      16   0 97456 3628 2664 S    0  0.2   0:00.08 sshd
19623 root      16   0 22408 2752 1504 S    0  0.1   0:00.06 bash
24005 root      16   0 97456 3612 2664 S    0  0.2   0:00.06 sshd
24160 root      16   0 22412 2756 1504 S    0  0.1   0:00.06 bash
28409 root      17   0 13072 1496 1180 S    0  0.1   0:00.01 mysqld_safe
28472 mysql     16   0 58244 8076 4192 S    0  0.4   0:00.31 mysqld
29741 root      16   0 22976 2840 1844 S    0  0.1   0:00.05 vi
16328 root      16   0 97456 3612 2664 S    0  0.2   0:00.05 sshd
17456 root      16   0 22412 2756 1504 S    0  0.1   0:00.05 bash
 5703 root      16   0 97296 3596 2664 S    0  0.2   0:00.03 sshd
 5777 root      15   0 22408 2752 1504 S    0  0.1   0:00.05 bash
20128 root      15   0 97456 3616 2664 S    0  0.2   0:00.05 sshd
20220 root      15   0 22412 2756 1504 S    0  0.1   0:00.07 bash
25956 root      16   0 22972 2736 1848 S    0  0.1   0:00.03 vi
 5681 wwwrun    16   0  259m  13m 3144 S    0  0.6   0:00.05 httpd2-prefork
 6100 wwwrun    16   0  259m  13m 3112 S    0  0.6   0:00.06 httpd2-prefork
 7221 wwwrun    15   0  259m  13m 3072 S    0  0.6   0:00.04 httpd2-prefork
 7732 wwwrun    16   0  259m  13m 3116 S    0  0.6   0:00.03 httpd2-prefork
 7752 wwwrun    16   0  259m  12m 3060 S    0  0.6   0:00.03 httpd2-prefork
 7759 root      16   0 97456 3612 2664 S    0  0.2   0:00.03 sshd
 8045 root      15   0 22408 2752 1504 S    0  0.1   0:00.06 bash
 9564 root      16   0 63084 2736 2096 S    0  0.1   0:00.00 sshd
 9578 sshd      16   0 63084 1372  720 S    0  0.1   0:00.00 sshd

user_beancounters

Code:

Version: 2.5
       uid  resource                     held              maxheld              barrier                limit              failcnt
    147167: kmemsize                 13752939             27364793             27349653             30084618                 3289
            lockedpages                     0                    8                 1335                 1335                    0
            privvmpages                 47568               190116               524288               540672                    0
            shmpages                     4537                21177                38581                38581                    0
            dummy                           0                    0                    0                    0                    0
            numproc                        58                   77                  666                  666                    0
            physpages                   25662               157113                    0  9223372036854775807                    0
            vmguarpages                     0                    0               262144  9223372036854775807                    0
            oomguarpages                25673               157126               262144  9223372036854775807                    0
            numtcpsock                     31                  151                  666                  666                    0
            numflock                        6                   14                 1000                 1100                    0
            numpty                          6                    6                   66                   66                    0
            numsiginfo                      0                   12                 1024                 1024                    0
            tcpsndbuf                  450048              2574144              6388615              9116551                    0
            tcprcvbuf                  612616              1151504              6388615              9116551                    0
            othersockbuf                28128               329904              3194307              5922243                    0
            dgramrcvbuf                     0                16976              3194307              3194307                    0
            numothersock                   25                   49                  666                  666                    0
            dcachesize                1245753              4216324              5959083              6137856                    0
            numfile                      2885                 6732                10656                10656                    0
            dummy                           0                    0                    0                    0                    0
            dummy                           0                    0                    0                    0                    0
            dummy                           0                    0                    0                    0                    0
            numiptent                      10                   10                  192                  192                    0

free

Code:

             total       used       free     shared    buffers     cached
Mem:       2097152     190464    1906688          0          0          0
-/+ buffers/cache:     190464    1906688
Swap:            0          0          0

server-tuning.conf

Code:

<IfModule prefork.c>
        StartServers         2
        MinSpareServers      2
        MaxSpareServers      5
        ServerLimit	    75
        MaxClients          75
        MaxRequestsPerChild  0
</IfModule>

<IfModule worker.c>
        StartServers         1
        MinSpareThreads     15
        MaxSpareThreads     50
        MaxClients          75
        ThreadsPerChild     15
        MaxRequestsPerChild  0
</IfModule>

<IfModule leader.c>
        StartServers         2
        MinSpareThreads     25
        MaxSpareThreads     75
        MaxClients         150
        ThreadsPerChild     25
        MaxRequestsPerChild  0
</IfModule>

<IfModule perchild.c>
        NumServers           5
        StartThreads         5
        MinSpareThreads      5
        MaxSpareThreads     10
        MaxThreadsPerChild  20
        MaxRequestsPerChild  0

        AcceptMutex fcntl
</IfModule>

<IfModule metuxmpm.c>
        StartThreads          5
        MinSpareThreads       5
        MaxSpareThreads      10
        MaxRequestsPerChild   0

        Multiplexer     "wwwrun"        "www"

</IfModule>

KeepAlive On
MaxKeepAliveRequests 10
KeepAliveTimeout 2

my.cnf

Code:

[mysqld]
set-variable=local-infile=0
local-infile = 0
skip-locking

key_buffer = 8M
max_allowed_packet = 1M
table_cache = 2000
tmp_table_size = 16M
thread_cache_size = 10
query_cache_size = 8M
join_buffer_size = 2M
open_files_limit = 3000
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_connections = 26
long_query_time = 2
log_slow_queries = /var/log/mysqld.slow.log

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

tuning-primer

Code:

Uptime = 3 days 18 hrs 58 min 55 sec
Avg. qps = 0
Total Questions = 252226
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
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

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

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 10
Current threads_cached = 9
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 26
Current threads_connected = 1
Historic max_used_connections = 10
The number of used connections is 38% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 60 M
Configured Max Per-thread Buffers : 90 M
Configured Max Global Buffers : 26 M
Configured Max Memory Limit : 116 M
Physical Memory : 2.00 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 1 M
Current key_buffer_size = 8 M
Key cache miss rate is 1 : 171
Key buffer free ratio = 77 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 8 M
Current query_cache_used = 2 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 32.64 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 512 K
Current read_rnd_buffer_size = 508 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 2.00 M
You have had 28 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 = 4036 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 = 2000 tables
You have a total of 761 tables
You have 935 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 14443 temp tables, 12% 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 = 252 K
Current table scan ratio = 142 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 58586
Your table locking seems to be fine

Apache2 error_log

Code:

[Mon Nov 09 22:39:43 2009] [error] (12)Cannot allocate memory: apr_socket_accept: (client socket)
[Mon Nov 09 22:42:49 2009] [error] (12)Cannot allocate memory: fork: Unable to fork new process
[Mon Nov 09 22:42:59 2009] [error] (12)Cannot allocate memory: fork: Unable to fork new process
....

Ich hoffe Ihr könnt mir ein wenig weiterhelfen.

Danke im voraus!

MfG
sLy

Huschi · Nov 10, 2009

@sly:
Bei top fehlt die erste Zeile mit "load".
Deine MaxClients sind zu hoch. 75 mal 13 MB (siehe top) macht bereits 975 MB die Apache bei voller Auslastung nur als Datenspeicher belegt.
Da die DB eine max_used_connection von 10 ausspuckt, tippe ich darauf dass Du mit nur 25-30 Apache-Clients bestens auskommst.

Und dann lass Dir nicht die Augen verwischen mit dem DDR-Ram. Du hast kein Hardware-RAM sondern nur zugesicherten Speicher und kurzfristig (wenn vorhanden) nutzbaren Buffer-Bereich (==FlexRAM).
Damit werden alle Berechnungen über 2GB zunichte gemacht, da Du in Wirklichkeit nur mit 1 GB rechnen solltest.

@ALL
"Visits" ist keine brauchbare Kennzahl zur Server-Optimierung.
Die Anzahl der Requests und das Verhältnis von dynamischen und statischen Inhalten sind sinnvolle Angaben.

huschi.

sly · Nov 10, 2009

Hallo Huschi,

danke für deine Antwort...

Huschi said:
Bei top fehlt die erste Zeile mit "load".

Der load ist niedrig und muss bei hochbetrieb nochmal überprüft werden.

Code:

top - 12:28:47 up 3 days, 23:47,  1 user,  load average: 0.07, 0.02, 0.00

Huschi said:
Deine MaxClients sind zu hoch. 75 mal 13 MB (siehe top) macht bereits 975 MB die Apache bei voller Auslastung nur als Datenspeicher belegt.
Da die DB eine max_used_connection von 10 ausspuckt, tippe ich darauf dass Du mit nur 25-30 Apache-Clients bestens auskommst.

Hab ich bereits eingestellt. Nun muss ich erstmal abwarten, Tee trinken und hoffen das es reicht

Huschi said:
"Visits" ist keine brauchbare Kennzahl zur Server-Optimierung.
Die Anzahl der Requests und das Verhältnis von dynamischen und statischen Inhalten sind sinnvolle Angaben.

Wie kann man dies denn am besten filtern?

Gruß
sLy

Huschi · Nov 10, 2009

Die Request ergeben sich aus der Anzahl der Zeilen von access_log.
Wenn Du dort alle PHP-Scripte rausfilters und zählst kannst Du das Verhältnis von dynamischen zu statischen Inhalten grob abschätzen.

huschi.

diastasi · Nov 21, 2009

I am sorry but I can only speak English

so I read all posts by Google translate.
I have a VPS in Server4you with 384MB guarantie RAM and 768 Flex Ram.
Space Free 14GB.

My apache2.conf is:

Code:

ServerRoot "/etc/apache2"

LockFile /var/lock/apache2/accept.lock

PidFile /var/run/apache2.pid

Timeout 300
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15

<IfModule mpm_prefork_module>
    StartServers       1
    MinSpareServers    1
    MaxSpareServers    5
    MaxClients        10
    MaxRequestsPerChild   0
</IfModule>

<IfModule mpm_worker_module>
    StartServers       1
    MaxClients        10
    MinSpareThreads    1
    MaxSpareThreads    4
    ThreadsPerChild      25
    MaxRequestsPerChild   0
</IfModule>

User www-data
Group www-data

AccessFileName .htaccess

<Files ~ "^\.ht">
    Order allow,deny
    Deny from all
</Files>

TypesConfig /etc/mime.types
DefaultType text/plain
HostnameLookups Off
ErrorLog /var/log/apache2/error.log
LogLevel warn

# Include module configuration:
Include /etc/apache2/mods-enabled/*.load
Include /etc/apache2/mods-enabled/*.conf

# Include all the user configurations:
Include /etc/apache2/httpd.conf

# Include ports listing
Include /etc/apache2/ports.conf

# Include generic snippets of statements
Include /etc/apache2/conf.d/

LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "%h %l %u %t \"%r\" %>s %b" common
LogFormat "%{Referer}i -> %U" referer
LogFormat "%{User-agent}i" agent

ServerTokens Full
ServerSignature On

<IfModule alias_module>
    Alias /icons/ "/usr/share/apache2/icons/"

    <Directory "/usr/share/apache2/icons">
        Options Indexes MultiViews
        AllowOverride None
        Order allow,deny
        Allow from all
    </Directory>

</IfModule>

<IfModule mod_autoindex.c>

    IndexOptions FancyIndexing VersionSort HTMLTable NameWidth=*

    AddIconByEncoding (CMP,/icons/compressed.gif) x-compress x-gzip

    AddIconByType (TXT,/icons/text.gif) text/*
    AddIconByType (IMG,/icons/image2.gif) image/*
    AddIconByType (SND,/icons/sound2.gif) audio/*
    AddIconByType (VID,/icons/movie.gif) video/*

    AddIcon /icons/binary.gif .bin .exe
    AddIcon /icons/binhex.gif .hqx
    AddIcon /icons/tar.gif .tar
    AddIcon /icons/world2.gif .wrl .wrl.gz .vrml .vrm .iv
    AddIcon /icons/compressed.gif .Z .z .tgz .gz .zip
    AddIcon /icons/a.gif .ps .ai .eps
    AddIcon /icons/layout.gif .html .shtml .htm .pdf
    AddIcon /icons/text.gif .txt
    AddIcon /icons/c.gif .c
    AddIcon /icons/p.gif .pl .py
    AddIcon /icons/f.gif .for
    AddIcon /icons/dvi.gif .dvi
    AddIcon /icons/uuencoded.gif .uu
    AddIcon /icons/script.gif .conf .sh .shar .csh .ksh .tcl
    AddIcon /icons/tex.gif .tex
    AddIcon /icons/bomb.gif core

    AddIcon /icons/back.gif ..
    AddIcon /icons/hand.right.gif README
    AddIcon /icons/folder.gif ^^DIRECTORY^^
    AddIcon /icons/blank.gif ^^BLANKICON^^

    DefaultIcon /icons/unknown.gif


    ReadmeName README.html
    HeaderName HEADER.html

    IndexIgnore .??* *~ *# RCS CVS *,v *,t 
</IfModule>

<IfModule mod_mime.c>

    AddType application/x-compress .Z
    AddType application/x-gzip .gz .tgz

    AddLanguage ca .ca
    AddLanguage cs .cz .cs
    AddLanguage da .dk
    AddLanguage de .de
    AddLanguage el .el
    AddLanguage en .en
    AddLanguage eo .eo
    AddLanguage es .es
    AddLanguage et .et
    AddLanguage fr .fr
    AddLanguage he .he
    AddLanguage hr .hr
    AddLanguage it .it
    AddLanguage ja .ja
    AddLanguage ko .ko
    AddLanguage ltz .ltz
    AddLanguage nl .nl
    AddLanguage nn .nn
    AddLanguage no .no
    AddLanguage pl .po
    AddLanguage pt .pt
    AddLanguage pt-BR .pt-br
    AddLanguage ru .ru
    AddLanguage sv .sv
    AddLanguage zh-CN .zh-cn
    AddLanguage zh-TW .zh-tw
</IfModule>

<IfModule mod_negotiation.c>
    ForceLanguagePriority Prefer Fallback

</IfModule>

<IfModule mod_mime.c>
    AddCharset us-ascii    .ascii .us-ascii
    AddCharset ISO-8859-1  .iso8859-1  .latin1
    AddCharset ISO-8859-2  .iso8859-2  .latin2 .cen
    AddCharset ISO-8859-3  .iso8859-3  .latin3
    AddCharset ISO-8859-4  .iso8859-4  .latin4
    AddCharset ISO-8859-5  .iso8859-5  .cyr .iso-ru
    AddCharset ISO-8859-6  .iso8859-6  .arb .arabic
    AddCharset ISO-8859-7  .iso8859-7  .grk .greek
    AddCharset ISO-8859-8  .iso8859-8  .heb .hebrew
    AddCharset ISO-8859-9  .iso8859-9  .latin5 .trk
    AddCharset ISO-8859-10  .iso8859-10  .latin6
    AddCharset ISO-8859-13  .iso8859-13
    AddCharset ISO-8859-14  .iso8859-14  .latin8
    AddCharset ISO-8859-15  .iso8859-15  .latin9
    AddCharset ISO-8859-16  .iso8859-16  .latin10
    AddCharset ISO-2022-JP .iso2022-jp .jis
    AddCharset ISO-2022-KR .iso2022-kr .kis
    AddCharset ISO-2022-CN .iso2022-cn .cis
    AddCharset Big5        .Big5       .big5 .b5
    AddCharset cn-Big5     .cn-big5
    # For russian, more than one charset is used (depends on client, mostly):
    AddCharset WINDOWS-1251 .cp-1251   .win-1251
    AddCharset CP866       .cp866
    AddCharset KOI8      .koi8
    AddCharset KOI8-E      .koi8-e
    AddCharset KOI8-r      .koi8-r .koi8-ru
    AddCharset KOI8-U      .koi8-u
    AddCharset KOI8-ru     .koi8-uk .ua
    AddCharset ISO-10646-UCS-2 .ucs2
    AddCharset ISO-10646-UCS-4 .ucs4
    AddCharset UTF-7       .utf7
    AddCharset UTF-8       .utf8
    AddCharset UTF-16      .utf16
    AddCharset UTF-16BE    .utf16be
    AddCharset UTF-16LE    .utf16le
    AddCharset UTF-32      .utf32
    AddCharset UTF-32BE    .utf32be
    AddCharset UTF-32LE    .utf32le
    AddCharset euc-cn      .euc-cn
    AddCharset euc-gb      .euc-gb
    AddCharset euc-jp      .euc-jp
    AddCharset euc-kr      .euc-kr
    #Not sure how euc-tw got in - IANA doesn't list it???
    AddCharset EUC-TW      .euc-tw
    AddCharset gb2312      .gb2312 .gb
    AddCharset iso-10646-ucs-2 .ucs-2 .iso-10646-ucs-2
    AddCharset iso-10646-ucs-4 .ucs-4 .iso-10646-ucs-4
    AddCharset shift_jis   .shift_jis .sjis

    AddHandler type-map var
    AddType text/html .shtml
    AddOutputFilter INCLUDES .shtml
</IfModule>

ErrorDocument 506 /error/HTTP_VARIANT_ALSO_VARIES.html.var

<IfModule mod_setenvif.c>
    BrowserMatch "Mozilla/2" nokeepalive
    BrowserMatch "MSIE 4\.0b2;" nokeepalive downgrade-1.0 force-response-1.0
    BrowserMatch "RealPlayer 4\.0" force-response-1.0
    BrowserMatch "Java/1\.0" force-response-1.0
    BrowserMatch "JDK/1\.0" force-response-1.0
    BrowserMatch "Microsoft Data Access Internet Publishing Provider" redirect-carefully
    BrowserMatch "MS FrontPage" redirect-carefully
    BrowserMatch "^WebDrive" redirect-carefully
    BrowserMatch "^WebDAVFS/1.[012]" redirect-carefully
    BrowserMatch "^gnome-vfs/1.0" redirect-carefully
    BrowserMatch "^XML Spy" redirect-carefully
    BrowserMatch "^Dreamweaver-WebDAV-SCM1" redirect-carefully
</IfModule>

# Include the virtual host configurations:
Include /etc/apache2/sites-enabled/

My.cnf

Code:

[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
language	= /usr/share/mysql/english
skip-external-locking
bind-address		= 127.0.0.1

key_buffer		= 2M
max_allowed_packet	= 4M
thread_stack		= 128K
thread_cache_size	= 8
max_connections        = 50
query_cache_limit       = 512K
query_cache_size        = 1M
log_bin			= /var/log/mysql/mysql-bin.log

expire_logs_days	= 10
max_binlog_size         = 100M

skip-bdb
skip-innodb

[mysqldump]
quick
quote-names
max_allowed_packet	= 4M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key_buffer		= 2M

!includedir /etc/mysql/conf.d/

Also in vhost.conf

Code:

<IfModule mpm_itk_module>
AssignUserId emagazi psacln
</IfModule>

and tuning-primer.sh gives

Code:

mysqld is alive

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

MySQL Version 5.0.32-Debian_7etch11-log x86_64

Uptime = 1 days 0 hrs 39 min 1 sec
Avg. qps = 2
Total Questions = 223305
Threads Connected = 1

Warning: Server has not been running for at least 48hrs.
It may not be safe to use 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
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 1 out of 223326 that take longer than 10 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.

BINARY UPDATE LOG
The binary update log is enabled

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 = 50
Current threads_connected = 1
Historic max_used_connections = 8
The number of used connections is 16% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 34 M
Configured Max Per-thread Buffers : 132 M
Configured Max Global Buffers : 13 M
Configured Max Memory Limit : 145 M
Physical Memory : 768 M
Max memory limit seem to be within acceptable norms

KEY BUFFER
1386 * 1024 / 2097152 * 100
Current MyISAM index space = 16 M
Current key_buffer_size = 2 M
Key cache miss rate is 1 : 18
Key buffer free ratio = 0 %
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 = 1 M
Current query_cache_used = 693 K
Current query_cache_limit = 512 K
Current Query cache Memory fill ratio = 67.71 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 258 queries where a join could not use an index properly
You have had 209 joins without keys that check for key usage after each row
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 = 1024 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 = 64 tables
You have a total of 484 tables
You have 64 open tables.
Current table_cache hit rate is 0%, 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 9191 temp tables, 29% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your 
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 51 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 15985
Your table locking seems to be fine

I have
-phpbb forum
-joomla
-Plesk 9.2
Not so much visitors in forum/joomla

Lots of tables (480 tables) and SQL is terribly slow

Can you Please give me any suggestion?
Thank you in advance
Can you please

diastasi · Nov 21, 2009

also in cat /proc/user_beancounters

Code:

uid  resource                  held              maxheld              barrier                limit              failcnt
186059: kmemsize          12163798             15552293             15523666             17076032                 6202
lockedpages                     0                    0                  344                  344                    0
privvmpages                 60136               108805               196608               204800                    0
shmpages                      743                11319                19567                19567                    0
dummy                           0                    0                    0                    0                    0
numproc                        70                   89                  128                  128                    0
physpages                   14049                58154                    0  9223372036854775807                    0
vmguarpages                     0                    0                98304  9223372036854775807                    0
oomguarpages                14049                58154                98304  9223372036854775807                    0
numtcpsock                     23                   46                  288                  288                    0
numflock                        6                   12                  224                  246                    0
numpty                          1                    1                   16                   16                    0
numsiginfo                      0                   16                  512                  512                    0
tcpsndbuf                  243776               560216              2449232              3598712                    0
tcprcvbuf                  287312               432384              2449232              3598712                    0
othersockbuf                21096               657752               844366              1481926                    0
dgramrcvbuf                     0                 8488               844366               844366                    0
numothersock                   20                   38                  288                  288                    0
dcachesize                 931068              1204794              2254786              2322430                    0
numfile                      2391                 2753                 4256                 4256                    0
dummy                           0                    0                    0                    0                    0
dummy                           0                    0                    0                    0                    0
dummy                           0                    0                    0                    0                    0
numiptent                      26                   28                   64                   64                    0

And because I dont use Plesk so much I can close it

Code:

/etc/init.d/psa stop

I do NOT have Spamassasin and Drweb installed.

diastasi · Nov 24, 2009

anyone please help me?

Huschi · Nov 24, 2009

diastasi said:
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 · Nov 26, 2009

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

Code:

[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:

Code:

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 · Nov 26, 2009

diastasi said:
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 · Nov 27, 2009

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:

Code:

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
[B]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[/B]
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 · Nov 27, 2009

diastasi said:
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 · Nov 27, 2009

Huschi said:
Yes if it is configured to do so.

Is there something I can do to help?

Huschi said:
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)

Code:

<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 · Nov 27, 2009

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 · Nov 28, 2009

Huschi said:
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 · Nov 29, 2009

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 · Dec 16, 2009

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

MySQL Performance Tuning mit Tuning-Primer.sh Script

Moderator

New Member

New Member

Registered User

Moderator

Registered User

Moderator

New Member

New Member

New Member

Moderator

New Member

Moderator

New Member

Moderator

New Member

Moderator

New Member

Moderator

New Member

We value your privacy