sql connection limit reached: 150-可以可尝试禁用SEF

i am running a small community site: http://www.schlachtumeuropa.de

when many ppl login to the same time the mysql connection limit, which is set to 150, is every time reached and the site goes "offline". are there any hints how i could set up the joomla 1.0.3 page so that this will not happen anymore ?

i have sitestats disabled, sitecache enabled

mysql> status
--------------
mysql  Ver 14.7 Distrib 4.1.11, for pc-linux-gnu (i386)
Server version:        4.1.11-Debian_4-log

I am going to bet that you have SEF enabled.

If so you may want to check out this thread:
http://forum.joomla.org/index.php/topic,11139.0.html

Dean Marshall

SEF ?

i have joomla 1.03 installed and the htaccess file from the post ^ is included in this release. that did not solve my problem.

Perhaps it is a search engine crawler then.
Is your robots.txt file up to scratch?
Do you have access to logfiles or a hosting control panel that will show you 'latest visitors'?
If so is there any discernable pattern in the usage.

If you do use the Search Engine Friendly (SEF) Urls option and the .htaccess file then I should also add that some servers don't use the specific environment variable used in the specimen .htaccess file, I am with 1and1.co.uk linux hosting and have to ammend the first line or it has no effect.
RewriteCond %{REQUEST_URI} !\.(jpg|jpeg|gif|png|css|js|pl|txt|ico)$

Try the Web page optimiser in the other thread and look for page elements very close in size to the page HTML.

Dean.

i have root access

SEF is off, htaccess is correct

So what about..

DeanMarshall wrote: Perhaps it is a search engine crawler then.
Is your robots.txt file up to scratch?
Do you have access to logfiles or a hosting control panel that will show you 'latest visitors'?
If so is there any discernable pattern in the usage.

Or perhaps you have got that many users? If you do have 150 concurrent users then you need to edit MySQL's ini file to increase the number of allowable connections. If your hosting package doesn't allow this then you may need to upgrade your hosting package or move to a larger host.

Dean

well, i don´t think that there are really 150 concurrent users ... but what i saw is that each connection (process) is open ~40000 ... i think that value is a bit to long. is this the session live time ?

Okay, Me again,

Could this be anything to do with your frames based redirect?

Does the livesite variable in your site reflect the true url rather than the schlachtumeuropa.de address?
This may be an issue. I don't know if I just happened to try while you were making changes but I got a blank screen everytime.

Your host seems to be having DNS problems. I get long delays and a domain not found when trying the 'real' address - the one in the frameset src parameter.

Dean Marshall

i know that there are for the first time dns problems ... i will talk to my hoster if they persists.

i don´t think that the "frames" are causing much troubles with high amount of connections.

is there any way of building a connection pool with mysql and joomla ? or shared connections ? i think it is not really neccessary to have one connection per user or ?

Beyond my level of 'inexpertise' I am afraid. You need a server guy.

a question: most users are using the log in with cookie feature, so every time when they come on the page a connection(process) is generated ?

i really need this solved because under heavy load / many requests my page is after a short time "offline" ... i would be really happy if anyone has some more suggestions for me


would be postgre or sqlrelay a solution ? anyone familiar with this ?

Your site is *very* image heavy.

Could it be that impatience on the part of users leads them to 'reload' a partially loaded page forcing a scaling of server load.
You have nearly 200KB of images:

I used this service to analyse your site.
http://www.websiteoptimization.com/services/analyze/

Estimated Page load times:
14.4K  208.82 seconds
28.8K 104.61 seconds
33.6K 89.72 seconds
56K 53.99 seconds

1  50307  CSS IMG  http://v2.sue.vs7509.vserver4free.de/te ... header.jpg
1 32049 HTML http://v2.sue.vs7509.vserver4free.de/
1 Not found CSS IMG http://v2.sue.vs7509.vserver4free.de/te ... in/top.gif
1 Not found CSS IMG http://v2.sue.vs7509.vserver4free.de/te ... /right.gif
1 Not found CSS IMG http://v2.sue.vs7509.vserver4free.de/te ... n/left.gif
1 Not found CSS IMG http://v2.sue.vs7509.vserver4free.de/te ... bottom.gif
1 77744 IMG http://v2.sue.vs7509.vserver4free.de/im ... 0kopie.jpg
2 37153 SCRIPT http://v2.sue.vs7509.vserver4free.de/in ... ib_mini.js
1 29471 IMG http://v2.sue.vs7509.vserver4free.de/im ... rs/sue.jpg
1 16769 CSS http://v2.sue.vs7509.vserver4free.de/te ... te_css.css
2 13966 CSS http://v2.sue.vs7509.vserver4free.de/co ... ations.css

I still think it could be badly behaving robots or possible a badly coded module.

A CMS like Joomla makes multiple queries per page load. If each query was a concurrent connection then it wouldn't take too much to hit 150.
>> mysql connection limit, which is set to 150

Dean.

thx for your tips. i had a closer look into css and the used modules. the page is now alot faster with a few users but still stuck with many users. my last idea is to use the unix odbc driver with connection pooling, any experience how to configure joomla to use this odbc thing ?

http://www.unixodbc.org/

Hi,

1  77744  IMG  http://v2.sue.vs7509.vserver4free.de/im ... 0kopie.jpg
1 50307 CSS IMG http://v2.sue.vs7509.vserver4free.de/te ... header.jpg
1 31666 CSS http://v2.sue.vs7509.vserver4free.de/co ... ations.css
1 29471 IMG http://v2.sue.vs7509.vserver4free.de/im ... rs/sue.jpg
1 28904 HTML http://v2.sue.vs7509.vserver4free.de/
1 Not found CSS IMG http://localhost/ak/images/camping.jpg
2 Not found CSS IMG http://localhost/ak/images/bg-left.gif
2 37153 SCRIPT http://v2.sue.vs7509.vserver4free.de/in ... ib_mini.js

Page load times:
14.4K  220.87 seconds
28.8K 110.64 seconds
33.6K 94.89 seconds
56K 57.09 seconds

I don't really see much difference in your total page load - it is not the .css file itself, more the images it references, they are *very* large.
In particular the first two listed above could do with optimising. And the two that reference localhost??? what is that about?

On the issue of caching database queries - I have zero knowledge or experience of such things, but I don't think this is where your problems lie. You appear to be using a free hosting provider - perhaps this is an issue?  I don't know how many users your site has, or whether search engine bots or similar could be an issue.  Also have you tried turning on the caching option within Joomla?

I mean no disrespect but seriously I wouldn't like to be visiting your site on dial-up - 1 minute for the front page to load!

Here is my advice for what it is worth:
1. Optimise your images - get their file sizes down.

2. To optimise your .css files somewhat you could try this:

place the PHP snippet above into the top of your CSS document. Then, rename your CSS file with a 'php' extension, and then refer to that file in the section of your template when linking your css file, for example:



3. You might also turn on gzip compression in your site's global configuration screen if the option is available to you.
4. Turn on the caching options in Joomla.

Good luck.

Dean.

i can do all the changes but this will not solve my main problem: sql connection limit reached 150 !!

it is my own linux root server (vserver) and i have full control over it. when alot of users connect to the site it will reach the connection limit in a short period of time. during the day the page loads "normal" but when we have an event the page reaches the connection limit to the mysql server. the only thing i can see with the myphpadmin is that every logged in user has its own process ... so the limit of 150 will be reached very soon if more users log into the page. my main question how can i build a connection pool with ~20-50 connections ?

odbc or sqlrelay would be an option or ?

In that case then you don't need any of this caching stuff - just remove the limits from the database user.

Find out which user your site is running as - you entered this when you set up Joomla.
Load up PHPMyAdmin and navigate to the users table

Server: localhost  Database: mysql  Table: user 

Click 'browser' in the top bar of the right hand frame.
Find your previously identified 'user' in the list - it should have max connections 150 in the rightermost column.
Put a tick / check mark in the left most column and click the edit icon.
Find the 'max_connection' row and put a zero in the value column - this will remove any limit.
Make sure the save radio button is selected and click go.
You may need to restart your MySQL server
For all I know you may need to restart Apache ??

That should - on a good day with a fair wind - solve your main issue.
Then you can look at the rest later.

Dean.

thanx for that tip, but all my users have it set to 0.

my.cnf

set-variable=max_connections=200

^^this limit is the problem

but when i set it to e.g 2000 than the server is a bit overloaded and after 300 the loading times increase rapidly

I have tuned a number of MySQL servers for high volume ad servers.
(phpAdsNew and Max Media Manager)

There are a few settings which may eliminate the problem rather quickly.
The MySQL defaults are really bad for any volume.

Best way to see what is happening is to use the MySQL Administrator to watch the server for awhile, and then tweak the settings.

If you are interested - PM me a username and password.
If you are concerned about the security of web access, the best thing to do is to setup a new MySQL user and restrict access to only my IP address (which I will PM).

I can login and watch what is happening and then make the changes (they are only temporary until added to the config file), and then tell you what needs to be changed in the config file.

I have been wanting to tune a Joomla site.

i did some changes some hours ago after searching the web ....

i am not sure if this will help, i will know on wednesday afternoon when our next event is planned. you can contact me on icq: 72986499 if you wanna look at the mysql host if the problem still persists after wedneday, ok ? next scheduled event is after wednesday the next saturday, so we have 2 day per week to look on the page when the page is on heavy load. thx for your offer!


my.cnf looks now:


#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "/var/lib/mysql/my.cnf" to set server-specific options or
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/serve ... ables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client] port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe] socket = /var/run/mysqld/mysqld.sock
nice = 0
open-files-limit=8192


[mysqld] #
# * Basic Settings
#
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
set-variable=max_connections=500

wait_timeout = 500
connect_timeout = 10

# For compatibility to other Debian packages that still use
# libmysqlclient10 and libmysqlclient12.
old_passwords = 1
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
#
# * Query Cache Configuration
#
query_cache_limit = 1048576
query_cache_size        = 16777216
query_cache_type        = 1
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log = /var/log/mysql.log
#log = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement
#
# Here you can see queries with especially long duration
#log-slow-queries = /var/log/mysql/mysql-slow.log
#
# The following can be used as easy to replay backup logs or for replication.
#server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
# See /etc/mysql/debian-log-rotate.conf for the number of files kept.
max_binlog_size        = 104857600
#binlog-do-db = include_database_name
#binlog-ignore-db = include_database_name
#
# * BerkeleyDB
#
# According to an MySQL employee the use of BerkeleyDB is now discouraged
# and support for it will probably cease in the next versions.
skip-bdb
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# If you want to enable SSL support (recommended) read the manual or my
# HOWTO in /usr/share/doc/mysql-server/SSL-MINI-HOWTO.txt.gz
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump] quick
quote-names
max_allowed_packet = 16M

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

[isamchk] key_buffer = 16M

Best time to watch the server is during the heavy load to see what is happening.

The MySQL config file is the bare minimum and has not been tuned.

maybe you can share a better my.cnf with us with the infos from my server?

well my root vserver is this: http://neu.star-hosting.de/c/cms/front_ ... 37&idcat=6