mysql time out veringern

MySQL, PostgreSQL, SQLite
memo1003
Posts: 24
Joined: 2005-07-08 23:34

mysql time out veringern

Post by memo1003 » 2005-09-10 12:31

Hallo,
Mein Problem ist dies, ich habe einen phpbb hosting laufen. Auf dem Server läuft nur dies.
Habe my.cnf entsprechend der angaben vom script Ersteller geändert. Läuft alles auch gut. Nur seit dem über 3500 Foren drauf laufen gibt es Probleme. 3500 ist so zu verstehen: 1 phpbb Forum, für alle anderen Foren werden immer nur neue Datenbank Tabellen erstellt mit anderen Präfix zb. phpbb_forum1, phpbb_forum2 usw. Alle Tabellen sind im gleichen Datenbank. Mittags wo die meisten User online sind auf den Foren, wird der Server so langsam das manchmal "Seite kann nicht gefunden werden" angezeigt wird. Und wenn doch dann in 5 Minuten.

Ich gehe davon aus das es von time out von mysql kommt. Meines Wissens macht phpbb für jede Verbindung einen Prozess auf der dann 18 stunden lang geht.

Wie kann ich es machen das diese 18 stunden auf z.b 30 Minuten verringert werden?

Muss ich was an phpbb ändern? wenn ja was? oder reicht da eine Ã?nderung im my.cnf

Server ist von s4y Llinux 9.2 p4 3,4 GHZ 2GBRAM

Bin für alle Antworten dankbar

User avatar
Joe User
Project Manager
Project Manager
Posts: 11598
Joined: 2003-02-27 01:00
Location: Hamburg

Re: mysql time out veringern

Post by Joe User » 2005-09-10 12:58

Deaktiviere die persistenten DB-Verbindungen in der php.ini und/oder führe folgende Ã?nderungen am phpBB-Source durch:

Code: Select all

//// db/mysql4.php
// function sql_db($sqlserver, $sqluser, $sqlpassword, $database, $persistency = true)
// {
//   $this->persistency = $persistency;
function sql_db($sqlserver, $sqluser, $sqlpassword, $database)
{

//// db/mysql4.php
// $this->db_connect_id = ($this->persistency) ?
//                        mysql_pconnect($this->server, $this->user, $this->password) :
//                        mysql_connect($this->server, $this->user, $this->password);
$this->db_connect_id = mysql_connect($this->server, $this->user, $this->password);

//// includes/db.php
// $db = new sql_db($dbhost, $dbuser, $dbpasswd, $dbname, false);
$db = new sql_db($dbhost, $dbuser, $dbpasswd, $dbname);
HTH
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

memo1003
Posts: 24
Joined: 2005-07-08 23:34

Re: mysql time out veringern

Post by memo1003 » 2005-09-10 19:38

ich habe die dateien so abgeändert habe aber leider fehler bekommen. bei jede aktualisierung kam ein anderes, einaml string fehler, einmal "cant connect mysql" und nochmal paar andere.

mein php.ini sieht so aus:

Code: Select all

[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent = Off

; Maximum number of persistent links.  -1 means no limit.
mysql.max_persistent = -1

; Maximum number of links (persistent + non-persistent).  -1 means no limit.
mysql.max_links = -1

; Default port number for mysql_connect().  If unset, mysql_connect() will use
; the $MYSQL_TCP_PORT or the mysql-tcp entry in /etc/services or the
; compile-time value defined MYSQL_PORT (in that order).  Win32 will only look
; at MYSQL_PORT.
mysql.default_port =

; Default socket name for local MySQL connects.  If empty, uses the built-in
; MySQL defaults.
mysql.default_socket =

; Default host for mysql_connect() (doesn't apply in safe mode).
mysql.default_host =

; Default user for mysql_connect() (doesn't apply in safe mode).
mysql.default_user =

; Default password for mysql_connect() (doesn't apply in safe mode).
; Note that this is generally a *bad* idea to store passwords in this file.
; *Any* user with PHP access can run 'echo get_cfg_var("mysql.default_password")
; and reveal this password!  And of course, any users with read access to this
; file will be able to reveal the password as well.
mysql.default_password =

; Maximum time (in secondes) for connect timeout. -1 means no limimt
mysql.connect_timeout = 60

; Trace mode. When trace_mode is active (=On), warnings for table/index scans and
; SQL-Erros will be displayed.
mysql.trace_mode = Off

was genau muss ich da ändern?

und bei :

Code: Select all

//// db/mysql4.php 
// $this->db_connect_id = ($this->persistency) ? 
//                        mysql_pconnect($this->server, $this->user, $this->password) : 
//                        mysql_connect($this->server, $this->user, $this->password); 
$this->db_connect_id = mysql_connect($this->server, $this->user, $this->password);
ist bei mir das hier:

Code: Select all

$this->db_connect_id = mysql_connect($this->server, $this->user, $this->password);
nicht drinne ich habe 2.0.17 im einsatz. War das vieleicht so gemeint das ich das hinzufügen soll?


bedanke mich im voraus

User avatar
Joe User
Project Manager
Project Manager
Posts: 11598
Joined: 2003-02-27 01:00
Location: Hamburg

Re: mysql time out veringern

Post by Joe User » 2005-09-10 19:50

memo1003 wrote:mein php.ini sieht so aus:
OK.
memo1003 wrote:War das vieleicht so gemeint das ich das hinzufügen soll?
In der Datei db/mysql4.php ersetzt Du:

Code: Select all

function sql_db($sqlserver, $sqluser, $sqlpassword, $database, $persistency = true)
{
  $this->persistency = $persistency;
durch:

Code: Select all

function sql_db($sqlserver, $sqluser, $sqlpassword, $database)
{
und:

Code: Select all

$this->db_connect_id = ($this->persistency) ? mysql_pconnect($this->server, $this->user, $this->password) : mysql_connect($this->server, $this->user, $this->password);
durch:

Code: Select all

$this->db_connect_id = mysql_connect($this->server, $this->user, $this->password);
In der Datei includes/db.php ersetzt Du:

Code: Select all

$db = new sql_db($dbhost, $dbuser, $dbpasswd, $dbname, false);
durch:

Code: Select all

$db = new sql_db($dbhost, $dbuser, $dbpasswd, $dbname);
Verständlicher?
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

memo1003
Posts: 24
Joined: 2005-07-08 23:34

Re: mysql time out veringern

Post by memo1003 » 2005-09-10 20:02

danke dir vielmals
hat supper geklappt.

User avatar
Joe User
Project Manager
Project Manager
Posts: 11598
Joined: 2003-02-27 01:00
Location: Hamburg

Re: mysql time out veringern

Post by Joe User » 2005-09-10 20:17

http://www.rootforum.org/forum/viewtopic.php?t=36343 hast Du schon gelesen und entsprechend umgesetzt?
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

memo1003
Posts: 24
Joined: 2005-07-08 23:34

Re: mysql time out veringern

Post by memo1003 » 2005-09-10 20:33

ich habe es gelesen aber mich nicht ganz getraut wegen:
Diese ist für Server mit 256MB bis 1024MB RAM
mein server hat 2GB ram und bei mir müssen mehr wie 100 connections sein. Weil 100 habe ich sofort dann kommt immer max connections...

bei mir läuft nur dieses foren server.

meiner sieht so aus: (bestimmt jedemenge scheise :-D )

Code: Select all

# 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/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.
#
# 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 
skip-innodb 
query_cache_limit=5M 
query_cache_size=5M 
query_cache_type=1 
max_connections=200 
interactive_timeout=10 
wait_timeout=100 
connect_timeout=10 
thread_cache_size=128 
key_buffer=16M 
join_buffer=1M 
max_allowed_packet=16M 
table_cache=1024 
record_buffer=1M 
sort_buffer_size=2M 
read_buffer_size=2M 
max_connect_errors=10

thread_concurrency=4 
myisam_sort_buffer_size=64M 
server-id       = 1

# 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

# 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

# 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
#bdb_cache_size = 4M
#bdb_max_lock = 10000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1: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 = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

# The safe_mysqld script
[safe_mysqld]
err-log=/var/lib/mysql/mysqld.log

[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 = 256M
read_buffer = 2M
write_buffer = 2M

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

[mysqlhotcopy]
interactive-timeout