Datenbank überlastet :-(

**MelloPie** · 21.08.2002, 19:56

Kommt denn eine Fehlermeldung wie
too many connections?
Schau mal ob Dein Bannerrotationsscript die connections wieder schliesst.

**homer_j** · 21.08.2002, 22:25

Original geschrieben von MelloPie
Kommt denn eine Fehlermeldung wie
too many connections?
Schau mal ob Dein Bannerrotationsscript die connections wieder schliesst.

Fehlermeldungen kommen verschiedene, glaub ich. Werd mal schaun, welche es sind morgen...

mysql_close($link); sollte reichen um die connections zu schließen, oder?
das komische an der sache ist ja, dass es tagsüber (wenn was los ist) so ist, dass der server 5 minuten gut läuft und dann 5 minuten total überlastet ist usw.
abends gehts eigentlich immer recht gut. im moment sieht's so aus:

10:23pm up 1 day, 2 min, 1 user, load average: 0.65, 0.57, 6.60
135 processes: 132 sleeping, 2 running, 1 zombie, 0 stopped
CPU states: 12.2% user, 3.3% system, 0.0% nice, 84.3% idle
Mem: 248028K av, 178540K used, 69488K free, 0K shrd, 1440K buff
Swap: 787176K av, 11048K used, 776128K free 101128K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
11110 root 11 0 1164 1164 740 R 0.9 0.4 3:55 top
8673 wwwrun 9 0 13084 5232 4776 S 0.5 2.1 0:00 httpd
10195 wwwrun 9 0 13140 5300 4788 S 0.3 2.1 0:00 httpd
10247 wwwrun 9 0 0 0 0 Z 0.3 0.0 0:00 httpd <defunct>
1517 wwwrun 9 0 13236 5440 4808 S 0.1 2.1 0:18 httpd
22028 root 9 0 1820 1816 1352 R 0.1 0.7 0:04 sshd
26730 wwwrun 9 0 13200 5360 4792 S 0.1 2.1 0:08 httpd
4800 wwwrun 9 0 13120 5280 4792 S 0.1 2.1 0:00 httpd
9582 wwwrun 9 0 13076 5224 4768 S 0.1 2.1 0:00 httpd
10209 wwwrun 10 0 13044 5188 4728 S 0.1 2.0 0:00 httpd
10219 wwwrun 9 0 13044 5192 4736 S 0.1 2.0 0:00 httpd
10221 wwwrun 9 0 13016 5156 4700 S 0.1 2.0 0:00 httpd
1 root 9 0 76 64 44 S 0.0 0.0 0:07 init
2 root 9 0 0 0 0 SW 0.0 0.0 0:00 keventd
3 root 19 19 0 0 0 SWN 0.0 0.0 0:00 ksoftirqd_CPU0
4 root 9 0 0 0 0 SW 0.0 0.0 0:00 kswapd
5 root 9 0 0 0 0 SW 0.0 0.0 0:00 bdflush
6 root 9 0 0 0 0 SW 0.0 0.0 0:04 kupdated
138 root 9 0 0 0 0 SW 0.0 0.0 0:00 eth0
167 root 9 0 560 516 496 S 0.0 0.2 0:00 dhcpcd
201 root 9 0 836 816 704 S 0.0 0.3 0:00 sshd

**hand** · 21.08.2002, 22:36

Was steht denn da so in der my.cnf?

**homer_j** · 21.08.2002, 22:48

Original geschrieben von hand
Was steht denn da so in der my.cnf?

btw: gibts eigentlich ausser my.cnf und php.ini noch dateien, in denen man mysql configurieren kann??

hier die my.cnf:

# Example mysql config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# a important part and systems up to 128M very MySQL is used together with
# other programs (like a web server)
#
# You can copy this file to
# /etc/mf.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.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --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
#bind-address = 127.0.0.1
#skip-networking
set-variable = key_buffer=16M
set-variable = max_allowed_packet=1M
set-variable = table_cache=400
set-variable = sort_buffer=512K
set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=4M
set-variable = max_connections=200
log-bin
server-id = 1

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

# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=4M
#set-variable = bdb_max_lock=10000

# Uncomment the following if you are using Innobase tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:25M;ibdata2:37M;ibdata3:100M;ibdata4:300M
#set-variable = innodb_mirrored_log_groups=1
#set-variable = innodb_log_files_in_group=3
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50

[mysqldump]
quick
set-variable = max_allowed_packet=16M

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

[isamchk]
set-variable = key_buffer=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout

**hand** · 21.08.2002, 23:20

Es gibt nur die my.cnf

Leistungsdaten des Host? Eigentlich nur CPU MHz / RAM eventuell Platten / Controller

sowie:
shell> mysqld --help

http://www.mysql.com/doc/en/Server_parameters.html

**homer_j** · 22.08.2002, 00:13

Original geschrieben von hand
Es gibt nur die my.cnf

Leistungsdaten des Host? Eigentlich nur CPU MHz / RAM eventuell Platten / Controller

sowie:
shell> mysqld --help

http://www.mysql.com/doc/en/Server_parameters.html

Der Server ist ein Rootserver L von Puretec, d.h. 1200 Mhz Celeron, 256 MB Ram, 40 GB HD. Wie kann ich rausfinden, was für ne Platte und für ein Controller in dem Server steckt?

**hand** · 22.08.2002, 09:02

Controller is nit so wichtig, wollt eigentlich nur wissen, ob scsi, aber macht nix.
Wenn Du die Info hast über
shell> mysqld --help
können wir weiterschauen.

Noch was: Connectet die Application die Du da hast über
mysql_pconnect()
oder
mysql_connect()
?

**homer_j** · 22.08.2002, 19:53

Das Script connectet mit mysql_connect()

Ich denke nicht, dass es scsi ist... wäre wohl zu teuer für die billigen pretec-server.

mysqld --help:

mysqld Ver 3.23.37 for suse-linux on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB, by Monty and others
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Starts the MySQL server

Usage: mysqld [OPTIONS]

--ansi Use ANSI SQL syntax instead of MySQL syntax
-b, --basedir=path Path to installation directory. All paths are
usually resolved relative to this
--big-tables Allow big result sets by saving all temporary sets
on file (Solves most 'table full' errors)
--bind-address=IP Ip address to bind to
--bootstrap Used by mysql installation scripts
--character-sets-dir=...
Directory where character sets are
--chroot=path Chroot mysqld daemon during startup
--core-file Write core on errors
-h, --datadir=path Path to the database root
--default-character-set=charset
Set the default character set
--default-table-type=type
Set the default table type for tables
--delay-key-write-for-all-tables
Don't flush key buffers between writes for any MyISAM
table
--enable-locking Enable system locking
-T, --exit-info Used for debugging; Use at your own risk!
--flush Flush tables to disk between SQL commands
-?, --help Display this help and exit
--init-file=file Read SQL commands from this file at startup
-L, --language=... Client error messages in given language. May be
given as a full path
-l, --log[=file] Log connections and queries to file
--log-bin[=file] Log queries in new binary format (for replication)
--log-bin-index=file File that holds the names for last binary log files
--log-update[=file] Log updates to file.# where # is a unique number
if not given.
--log-isam[=file] Log all MyISAM changes to file
--log-long-format Log some extra information to update log
--low-priority-updates INSERT/DELETE/UPDATE has lower priority than selects
--log-slow-queries=[file]
Log slow queries to this log file. Defaults logging
to hostname-slow.log
--pid-file=path Pid file used by safe_mysqld
--myisam-recover[=option[,option...]] where options is one of DEAULT,
BACKUP or FORCE.
--memlock Lock mysqld in memory
-n, --new Use very new possible 'unsafe' functions
-o, --old-protocol Use the old (3.20) protocol
-P, --port=... Port number to use for connection

-O, --set-variable var=option
Give a variable an value. --help lists variables
-Sg, --skip-grant-tables
Start without grant tables. This gives all users
FULL ACCESS to all tables!
--safe-mode Skip some optimize stages (for testing)
--skip-concurrent-insert
Don't use concurrent insert with MyISAM
--skip-delay-key-write
Ignore the delay_key_write option for all tables
--skip-locking Don't use system locking. To use isamchk one has
to shut down the server.
--skip-name-resolve Don't resolve hostnames.
All hostnames are IP's or 'localhost'
--skip-networking Don't allow connection with TCP/IP.
--skip-new Don't use new, possible wrong routines.
--skip-host-cache Don't cache host names

--skip-show-database Don't allow 'SHOW DATABASE' commands
--skip-thread-priority
Don't give threads different priorities.
--socket=... Socket file to use for connection
-t, --tmpdir=path Path for temporary files
--transaction-isolation
Default transaction isolation level
--temp-pool Use a pool of temporary files
-u, --user=user_name Run mysqld daemon as user
-V, --version output version information and exit

Default options are read from the following files in the given order:
/etc/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqld server
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit
--no-defaults Don't read default options from any options file
--defaults-file=# Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read

To see what values a running MySQL server is using, type
'mysqladmin variables' instead of 'mysqld --help'.
The default values (after parsing the command line arguments) are:

basedir: /usr/
datadir: /var/lib/mysql/
tmpdir: /tmp/
language: /usr/share/mysql/english/
pid file: /var/lib/mysql/p15097545.pid
binary log:
binary log index:
TCP port: 3306
Unix socket: /var/lib/mysql/mysql.sock

system locking is not in use

Possible variables for option --set-variable (-O) are:
back_log current value: 50
binlog_cache_size current value: 32768
connect_timeout current value: 5
delayed_insert_timeout current value: 300
delayed_insert_limit current value: 100
delayed_queue_size current value: 1000
flush_time current value: 0
interactive_timeout current value: 28800
join_buffer_size current value: 131072
key_buffer_size current value: 16773120
long_query_time current value: 10
lower_case_table_names current value: 0
max_allowed_packet current value: 1047552
max_binlog_cache_size current value: 4294967295
max_binlog_size current value: 1073741824
max_connections current value: 200
max_connect_errors current value: 10
max_delayed_threads current value: 20
max_heap_table_size current value: 16777216
max_join_size current value: 4294967295
max_sort_length current value: 1024
max_tmp_tables current value: 32
max_user_connections current value: 0
max_write_lock_count current value: 4294967295
myisam_sort_buffer_size current value: 4194304
myisam_max_extra_sort_file_size current value: 256
myisam_max_sort_file_size current value: 2047
net_buffer_length current value: 7168
net_retry_count current value: 10
net_read_timeout current value: 30
net_write_timeout current value: 60
open_files_limit current value: 0
query_buffer_size current value: 0
record_buffer current value: 131072
slow_launch_time current value: 2
sort_buffer current value: 524280
table_cache current value: 400
thread_concurrency current value: 10
thread_cache_size current value: 0
tmp_table_size current value: 1048576
thread_stack current value: 65536
wait_timeout current value: 28800

**hand** · 22.08.2002, 20:14

Hab alles ausgedruckt und schau mir das alles in Ruhe an. Wird sicher etwas länger dauern ....

**hand** · 22.08.2002, 21:19

Ich kenne die MySQL Applikationen nicht, die auf diesem Host laufen, weiß nicht wieviele Tabellen es gibt, ob hauptsächlich abgefragt wird.

Das ganze ist ein herantasten und beobachten ...

Was mich wundert ist, daß die jüngsten MySQL-Manuals kein Kapitel mehr haben: "Getting maximum performance from MySQL" und auch auf der mysql.org nix gescheites zu fuínden ist.

376 processes: 201 sleeping, 175 running, 0 zombie, 0 stopped
CPU states: 98.6% user, 1.3% system, 0.0% nice, 83673.5% idle
Mem: 248028K av, 191480K used, 56548K free, 0K shrd, 2272K buff
Swap: 787176K av, 0K used, 787176K free 52972K cached

o Also 376 Prozesse stehen an, davon werden 175 durchgeführt, 201 warten
o Von in etwa 240MB RAM werden dafür 190MB verwendet, 55 MB sind noch verfügbar
o Memorydaten müssen nicht auf Platte geswapped werden

Ich schlage ohne Gewähr folgende Optionen vor:

Code:

set-variable=key_buffer_size=32M
# key_buffer_size alter Wert: 16M 

set-variable=record_buffer=256K
# record_buffer alter Wert: 128K

set-variable=sort_buffer=1M
# sort_buffer alter Wert: 512K

set-variable=wait_timeout=1800 
# wait_timeout alter Wert: 28800 = 8 Stunden

set-variable=interactive_timeout=1800 
# interactive_timeout alter Wert: 28800 = 8 Stunden

key_buffer_size
Index blocks are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks.

Increase is get better index handling, for all reads and multiple writes. If you make this too big the system will starte to page and go REAL slow (Anm.: 55MB sind ungenutzt)

record_buffer
Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value

sort_buffer
Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUB BY operations.

wait_timeout
The number of seconds the server waits for activity on a connection before closing it

wait_timeout
The number of seconds the server waits for activity on a interactive (mysql_real_connect()) connection before closing it

Vielleicht beginnst Du mit den timeout Optionen. Einfach die Optionen in die my.cnf eintragen und MySQL durchstarten.

Good Luck

**homer_j** · 22.08.2002, 23:09

Hallo,

also das Script ist ein Banner-Exchange mit ca. 150.000 Views / Tag. Insgesammt sind es 6 Tabellen. Drei davon werden aber relativ selten aufgerufen. Für jeden ausgelieferten Banner werden drei Tabellen benötigt; in zwei davon wird etwas geschrieben.

Deine Einstellungen werde ich morgen mal testen; jetzt um diese Zeit läuft der Server sowieso gut, da kann ich nix testen.
Ist es normal, das einige der Variablen noch gar nicht in der my.cnf stehen? (z.B. interactive_timeout)
Ich schreib die dann einfach bei [mysqld] dazu, oder?

MfG
Matthias

**homer_j** · 23.08.2002, 18:22

Code:

set-variable=key_buffer_size=32M
# key_buffer_size alter Wert: 16M 

set-variable=record_buffer=256K
# record_buffer alter Wert: 128K

set-variable=sort_buffer=1M
# sort_buffer alter Wert: 512K

set-variable=wait_timeout=1800 
# wait_timeout alter Wert: 28800 = 8 Stunden

set-variable=interactive_timeout=1800 
# interactive_timeout alter Wert: 28800 = 8 Stunden

Die Einstellungen haben leider nichts geholfen. Der Server ist immer noch voll zu :-(
Vorschläge, was ich noch ändern kann? thx
Es kann ja wohl nicht sein, dass der Server dem Script nicht gewachsen ist, oder? Wozu hat man den nen eigenen Server? :-(

MfG
Matthias

**admin** · 23.08.2002, 18:45

wo hast du denn deinen Server stehen?

**homer_j** · 23.08.2002, 19:15

Original geschrieben von Berni
wo hast du denn deinen Server stehen?

soweit ich weiß stehen alle puretec-server in karlsruhe

Datenbank überlastet :-(