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
Last edited by And_One on Fri Oct 14, 2005 6:52 pm, edited 1 time in total.
Top
 
User avatar
DeanMarshall
Joomla! Hero
Joomla! Hero
Posts: 2352
Joined: Fri Aug 19, 2005 2:26 am
Location: Lancaster, Lancashire, United Kingdom
Contact:

Re: sql connection limit reached: 150

Post by DeanMarshall » Fri Oct 14, 2005 7:05 pm

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
Dean Marshall Consultancy - six Joomla experts - http://www.deanmarshall.co.uk/

Joomla Experts - Joomla Support http://www.deanmarshall.co.uk/joomla-se ... pport.html
Top
 
And_One
Joomla! Apprentice
Joomla! Apprentice
Posts: 13
Joined: Tue Oct 04, 2005 7:56 pm

Re: sql connection limit reached: 150

Post by And_One » Fri Oct 14, 2005 7:45 pm

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.
Top
User avatar
DeanMarshall
Joomla! Hero
Joomla! Hero
Posts: 2352
Joined: Fri Aug 19, 2005 2:26 am
Location: Lancaster, Lancashire, United Kingdom
Contact:

Re: sql connection limit reached: 150

Post by DeanMarshall » Fri Oct 14, 2005 8:59 pm

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.
Dean Marshall Consultancy - six Joomla experts - http://www.deanmarshall.co.uk/

Joomla Experts - Joomla Support http://www.deanmarshall.co.uk/joomla-se ... pport.html
Top
 
And_One
Joomla! Apprentice
Joomla! Apprentice
Posts: 13
Joined: Tue Oct 04, 2005 7:56 pm

Re: sql connection limit reached: 150

Post by And_One » Fri Oct 14, 2005 11:40 pm

i have root access

SEF is off, htaccess is correct
Top
User avatar
DeanMarshall
Joomla! Hero
Joomla! Hero
Posts: 2352
Joined: Fri Aug 19, 2005 2:26 am
Location: Lancaster, Lancashire, United Kingdom
Contact:

Re: sql connection limit reached: 150

Post by DeanMarshall » Sat Oct 15, 2005 12:08 am

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
Dean Marshall Consultancy - six Joomla experts - http://www.deanmarshall.co.uk/

Joomla Experts - Joomla Support http://www.deanmarshall.co.uk/joomla-se ... pport.html
Top
 
And_One
Joomla! Apprentice
Joomla! Apprentice
Posts: 13
Joined: Tue Oct 04, 2005 7:56 pm

Re: sql connection limit reached: 150

Post by And_One » Sat Oct 15, 2005 12:30 am

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 ?
Top
User avatar
DeanMarshall
Joomla! Hero
Joomla! Hero
Posts: 2352
Joined: Fri Aug 19, 2005 2:26 am
Location: Lancaster, Lancashire, United Kingdom
Contact:

Re: sql connection limit reached: 150

Post by DeanMarshall » Sat Oct 15, 2005 1:04 am

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
Dean Marshall Consultancy - six Joomla experts - http://www.deanmarshall.co.uk/

Joomla Experts - Joomla Support http://www.deanmarshall.co.uk/joomla-se ... pport.html
Top
 
And_One
Joomla! Apprentice
Joomla! Apprentice
Posts: 13
Joined: Tue Oct 04, 2005 7:56 pm

Re: sql connection limit reached: 150

Post by And_One » Sat Oct 15, 2005 1:10 am

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 ?
Top
User avatar
DeanMarshall
Joomla! Hero
Joomla! Hero
Posts: 2352
Joined: Fri Aug 19, 2005 2:26 am
Location: Lancaster, Lancashire, United Kingdom
Contact:

Re: sql connection limit reached: 150

Post by DeanMarshall » Sat Oct 15, 2005 1:15 am

Beyond my level of 'inexpertise' I am afraid. You need a server guy.
Dean Marshall Consultancy - six Joomla experts - http://www.deanmarshall.co.uk/

Joomla Experts - Joomla Support http://www.deanmarshall.co.uk/joomla-se ... pport.html
Top
 
And_One
Joomla! Apprentice
Joomla! Apprentice
Posts: 13
Joined: Tue Oct 04, 2005 7:56 pm

Re: sql connection limit reached: 150

Post by And_One » Sat Oct 15, 2005 1:17 am

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 ?
Top
 
And_One
Joomla! Apprentice
Joomla! Apprentice
Posts: 13
Joined: Tue Oct 04, 2005 7:56 pm

Re: sql connection limit reached: 150

Post by And_One » Thu Oct 27, 2005 10:06 am

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 ?
Top
User avatar
DeanMarshall
Joomla! Hero
Joomla! Hero
Posts: 2352
Joined: Fri Aug 19, 2005 2:26 am
Location: Lancaster, Lancashire, United Kingdom
Contact:

Re: sql connection limit reached: 150

Post by DeanMarshall » Thu Oct 27, 2005 10:29 am

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.
Dean Marshall Consultancy - six Joomla experts - http://www.deanmarshall.co.uk/

Joomla Experts - Joomla Support http://www.deanmarshall.co.uk/joomla-se ... pport.html
Top
 
And_One
Joomla! Apprentice
Joomla! Apprentice
Posts: 13
Joined: Tue Oct 04, 2005 7:56 pm

Re: sql connection limit reached: 150

Post by And_One » Sat Oct 29, 2005 2:28 pm

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/
Last edited by And_One on Sat Oct 29, 2005 3:52 pm, edited 1 time in total.
Top
User avatar
DeanMarshall
Joomla! Hero
Joomla! Hero
Posts: 2352
Joined: Fri Aug 19, 2005 2:26 am
Location: Lancaster, Lancashire, United Kingdom
Contact:

Re: sql connection limit reached: 150

Post by DeanMarshall » Sat Oct 29, 2005 4:28 pm

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.
Dean Marshall Consultancy - six Joomla experts - http://www.deanmarshall.co.uk/

Joomla Experts - Joomla Support http://www.deanmarshall.co.uk/joomla-se ... pport.html
Top
 
And_One
Joomla! Apprentice
Joomla! Apprentice
Posts: 13
Joined: Tue Oct 04, 2005 7:56 pm

Re: sql connection limit reached: 150

Post by And_One » Sat Oct 29, 2005 5:57 pm

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 ?
Top
User avatar
DeanMarshall
Joomla! Hero
Joomla! Hero
Posts: 2352
Joined: Fri Aug 19, 2005 2:26 am
Location: Lancaster, Lancashire, United Kingdom
Contact:

Re: sql connection limit reached: 150

Post by DeanMarshall » Sat Oct 29, 2005 6:13 pm

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.
Dean Marshall Consultancy - six Joomla experts - http://www.deanmarshall.co.uk/

Joomla Experts - Joomla Support http://www.deanmarshall.co.uk/joomla-se ... pport.html
Top
 
And_One
Joomla! Apprentice
Joomla! Apprentice
Posts: 13
Joined: Tue Oct 04, 2005 7:56 pm

Re: sql connection limit reached: 150

Post by And_One » Sat Oct 29, 2005 7:17 pm

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
Last edited by And_One on Sat Oct 29, 2005 8:07 pm, edited 1 time in total.
Top
User avatar
kenmcd
Joomla! Champion
Joomla! Champion
Posts: 5672
Joined: Thu Aug 18, 2005 2:09 am
Location: California
Contact:

Re: sql connection limit reached: 150

Post by kenmcd » Sat Oct 29, 2005 11:44 pm

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.
██ LibreTraining
Top
 
And_One
Joomla! Apprentice
Joomla! Apprentice
Posts: 13
Joined: Tue Oct 04, 2005 7:56 pm

Re: sql connection limit reached: 150

Post by And_One » Sat Oct 29, 2005 11:50 pm

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
Top
User avatar
kenmcd
Joomla! Champion
Joomla! Champion
Posts: 5672
Joined: Thu Aug 18, 2005 2:09 am
Location: California
Contact:

Re: sql connection limit reached: 150

Post by kenmcd » Sun Oct 30, 2005 12:46 am

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.
██ LibreTraining
Top
 
And_One
Joomla! Apprentice
Joomla! Apprentice
Posts: 13
Joined: Tue Oct 04, 2005 7:56 pm

Re: sql connection limit reached: 150

Post by And_One » Sun Oct 30, 2005 12:55 am

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
Top