MySQL Max Connections
Jan 16, 2008
My PHP application is starting to reach max mysql server user connections limit (currently set to 60). I listed mysql process list in phpmyadmin and found there lot of queries with status "LOCKED" these hang there for a long time(not always just sometimes - twice a day) and then connection limit is reached. It causes load average about 40 for as long as 10 - 20 minutes
I think it may be bacause of query structure. There are some queries with many inner joins...
Here is typical situation from phpmyadmin's process list:
1. select ... from table_1
inner join table_2
inner join table_3
inner join table_4
inner join table_5
This show status : "Copying to tmp table" in phpmyadmin
2. update table_2 set ....
This shows status: Locked
3. select ... from table_2
This shows status: Locked
Seems then when temp table is being created the table_2 is locked and it cannot make update to table_2. or maybe it's locked because of just that update on table_2.
I want to avoid of creating temp tables... Can it help if I'll make separate selects without large table joins ?
View 3 Replies
ADVERTISEMENT
Jan 7, 2007
I'm currently using MySQL 4.1.12 for Windows 2003, and I need to know how much possible connections can MySQL achieve? I'm currently around 650. I'm having 4 new servers online, and that will bring my total concurrent connections to 1000+.
how high it can go?
View 3 Replies
View Related
May 24, 2007
I notice that most hosting have limit of mySQL connections.
If the max mySQL connections is 50 and I run a forum on this hosting,about how many people can visit my website at one time?
View 3 Replies
View Related
Feb 18, 2007
from what I view, wait_timeout is used for disconnecting connections. However, it applies to tcp/ip connection only. So, in my database connection, if I am using mysql server as localhost, that variable is not applied? In that case, how can I close connections automatically after xxx seconds?
View 8 Replies
View Related
May 13, 2009
i have a small cluster (one web and one db) setup and i host a rather popular group of 4-5 sites that allow users to dynamicly create their own mobile chat communitys automaticly. each site gets its own mysql db created and populated automaticly.
this is all fine,
but in the last 24hours weird things have begun happening, previously i had the sql max_connections set to 500 and this was perfectly adquate for the demand but now even when i set the connection to 4000+ they are all maxxed out within 5-10 minutes, and mysql processlist shows thousands of unauthenticated user connections sitting at login status,
i have gone through the sites and all their mysql configs are fine so i cant see what the issue is.
server specs below
db server:
dual amd opteron 246
8GB ram
120gb hd(64gb free)
33gb swap (rarly used but their for emergencys)
centos 5 64bit.
direct 100mbit lan to web serv
only mysql,ssh and webmin running, no other apps installed
web server:
amd athlon 64 3800+
plesk 9.2.1
4gb ram
2x120gb hds
apache status onthe web server only shows 120ish http connections but the sql keeps climbing
View 8 Replies
View Related
Oct 21, 2009
unfortunately my server crashes a lot recently. What happens is that some application creates a MySQL connection which hangs and then MySQL gets overloaded and takes the whole server down.
My question:
1. How do I configure MySQL to time out even on active connections ? I.e. after 30 seconds kill the process no matter if it is still active or not. Is that possible ?
2. How do I exclude the root user from that?
View 1 Replies
View Related
Dec 17, 2008
This has happened twice now. I have my wait_timeout variable set to 60 seconds, but occasionally my database gets overloaded with "locked" queries. Here's an example of my processlist:
Code:
| 14773848 | db_name | localhost | db_name | Query | 35483 | statistics | SELECT COUNT... |
| 14776290 | db_name | localhost | db_name | Query | 35144 | statistics | SELECT COUNT... |
| 14778877 | db_name | localhost | db_name | Query | 34841 | statistics | SELECT COUNT... |
| 14790744 | db_name | localhost | db_name | Query | 33056 | Locked | UPDATE... |
| 14791275 | db_name | localhost | db_name | Query | 32977 | Locked | SELECT... |
| 14791395 | db_name | localhost | db_name | Query | 32960 | Locked | SELECT... |
| 14791556 | db_name | localhost | db_name | Query | 32929 | Locked | SELECT... |
| 14791603 | db_name | localhost | db_name | Query | 32924 | Locked | SELECT... |
| 14791757 | db_name | localhost | db_name | Query | 32900 | Locked | SELECT... |
| 14792209 | db_name | localhost | db_name | Query | 32815 | Locked | SELECT... |
| 14792355 | db_name | localhost | db_name | Query | 32786 | Locked | SELECT... |
This continues on with locked queries all the way up to my limit of 450 connections, then the whole server goes down. Note the time spent processing these queries, 35483 seconds and counting! Surely the wait_timeout is supposed to prevent this from happening?!
Also, this appears to start with queries in the State "statistics". Then the State for the following queries becomes "locked". Does anyone know what these mean? Can I get them to timeout normally? Queries which never timeout are guaranteed to bring your server down eventually, surely this is not the desired behaviour.
View 4 Replies
View Related
Aug 12, 2008
for a linux web host that has extremely fast mysql connections, 100GB of bandwidth, and the amount of space has to be at least 1GB. Price doesn't really matter.
View 8 Replies
View Related
Apr 3, 2008
Recently I've upgraded my MySQL server. I connect to it via a conection from NIC to NIC (Internal IP addresses). Since the upgrade/format, I've managed to get everything back to the way it was. Everything works fine for 15-20 minutes. Then the PHP server can no longer access the MySQL server.
I tried logging in from the PHP server, I get access denied. I then tried logging in locally, on the MySQL server and I get in fine...
It's not like it's a firewall issue though, it's strange...
I tried updating PHP/MySQL to a few releases, even the RC's to no avail...
Something to note is that when I try restarting MySQL it can take 4-5 minutes, sometimes not even complete.
View 2 Replies
View Related
Jun 22, 2007
how i can limit http and mysql connection limit on per domain basis.
View 2 Replies
View Related
May 8, 2009
Since my /var partition is full, so I moved /var/lib/mysql to /backup/mysql/.
Seems all the files are copied and I changed my.cnf reboot mysql, but all the web sites using db is not working anymore..
View 4 Replies
View Related
Dec 22, 2008
Sometimes my server surcharge load average increase at 60 , and all my configuration are OK
when i type :
netstat -plan|grep :80|awk {'print $5'}|cut -d: -f 1|sort|uniq -c|sort -nk 1
i have : ...
View 8 Replies
View Related
Dec 21, 2008
I tried to update a plugin at my blog its a wordpress blog, as soon as the update was started that site on the server stopped working, (later on i closed the upgradation window), after few minutes website start working automatically, Now in my opinion I think that update process is still running in background thats why connections are creating continuously to that website IP.
[root@server ~]# netstat -alpn | grep :80 | awk '{print $4}' | cut -d: -f1 |sort |uniq -c
1001 serverIPhere
its even touching 1500, I tried to contact my server support but unfortunately they can investigate the issue, instead they told me to check with the following command.
netstat -plan |grep :80 | awk '{print $5}' | cut -d: -f1 | sort | uniq -c
which is not an answer to my question. Can anybody please tell me why those connections are making to that website's IP? I don't think its a Ddos attack, because it was just started when i updated the plugin.
View 10 Replies
View Related
May 19, 2008
Could someone comment on the kind of load a VPS service can handle? If I were to run an HTTP server how many connection/sec would be realistic.
View 3 Replies
View Related
Mar 6, 2007
How many simulteanous connections to the site do alot of webhosting company usually allow with shared hosting packages. I was wondering because4 some companies say pay $$ a month get 300gb of bandwith a month. Can they limit the bandwith by limiting your simulteanous connections? I am asking because I just found out my host only allows 50 per hosting package that is on a shared server. To me that seems to be very little.
View 1 Replies
View Related
Apr 19, 2007
WARNING: One or more of your DNS servers does not accept TCP connections. Although rarely used, TCP connections are occasionally used instead of UDP connections. When firewalls block the TCP DNS connections, it can cause hard-to-diagnose problems. The problem servers are:
Error [No response to TCP packets].
APF is installed on the server, how do I allow TCP DNS connections? I already added port 53 to ingress/egress for TCP and UDP.
View 7 Replies
View Related
Dec 20, 2007
I run this a few times a day:
netstat -ntu | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n
and get outputs like (just the bottom here, IPs removed):
8 IP #1
8 IP #2
8 . . .etc
8
9
9
9
9
9
10
10
11
12
13
14
15
18
19
25
26
32
32
91
The server runs gallery2, how many connections does an IP need just to browse the gallery? I'd like to block wget etc, but don't want to hurt search engine optimization (SEO). Is there a compromise, like limiting IPs to 5 connections, so the site still gets crawled, just slower?
DDoS deflate is installed: [url]
which permabans IPs with 150+ connections
Also what are the commands to block and unblock these IPs,
View 2 Replies
View Related
Jan 21, 2007
I installed the MySQL binary packages in /usr/local/mysql/ after removing the MySQL RPM package. MySQL is functioning when I executed /usr/local/mysql/bin/safe_mysqld. I reinstalled MySQL before I installed PHP. When I used a PHP script to access a MySQL database, it outputs an error:
Code:
Warning: mysqli::mysqli() [function.mysqli-mysqli]: (HY000/2002): can't connect to local mysql server through socket /var/lib/mysql/mysql.sock in index.php on line 2
However, I installed MySQL in /usr/local/mysql, not in /var/lib/mysql. How do I fix MySQL?
View 1 Replies
View Related
Oct 29, 2008
Firewall TCP Out Connections
My server started lagging up and I processed my configserver firewall logs and founds tons of TCP out connections. How can I track down which user was making these connections, if possible?
View 12 Replies
View Related
Apr 21, 2009
i have a vps, and im current use lighttpd, but i want move to litespeed Standard. And i see they limit Max Concurrent Connections =150 on standard version.
but what is Max Concurrent Connections? where can i find it.
and is it the number connection via port 80 (netstat -nt | grep :80 | wc -l)?
View 3 Replies
View Related
May 6, 2009
Has anyone worked with the cable companies on internet connections for hosting? Eg. Comcast, TW.
I worked with a sales rep for Comcast a few years ago on a solution for our offices. He worked out a line that would give us 3+mbits up speed for less then the price of a T1.
It also included a dedicated line to our offices. Would using a cable line be a bad idea for a hosting connection?
View 4 Replies
View Related
Mar 16, 2008
Most of them are from Google and Yahoo...
Server is being heavily loaded beause of this.
I guess blocking crawlers is not the most brilliant
View 6 Replies
View Related
May 2, 2008
I signed up for a hosted account with gator and I don't understand something. They tell me it's a policy change for security reasons but the simultaneous SSH connections has been limited to 2. That's just nuts. Is there a real reason why someone would limit this? i need two for editors, one for shell and one for mysql. Minimum of 4. What security concern could cause them to pick 2 as the number?
I just don't get it.
Here's what they said to me.
info: Please wait for a HostGator operator to respond.
Channel Sanderson: Hi. We're working on our website and have run into a small snag. It seems we can only have two open SSH connections at a time this week. We were able to open more a couple weeks ago. Is this something that you can change?
Kella J.: Ok, the issue is.. You are only alllowed 2, no matter what..
Channel Sanderson: I believe we are not understanding each other. We're not trying to connect 10 times in a minute. We just need more connections. 2 is insufficient. We need a minimum of 4 simultaneous connections to our server.
Kella J.: I am sorry, I checked with my admin.. he said there is only a limit of 2, period..
Channel Sanderson: This is an unnecessary limitation in my view and badly limits my ability to do what I need to do.
View 13 Replies
View Related
Sep 15, 2008
Just logged in my cPanel, and Apache Server Status shows
Parent Server Generation: 7
Server uptime: 2 hours 52 minutes 5 seconds
Total accesses: 701666 - Total Traffic: 63.7 GB
CPU Usage: u1610.22 s255.4 cu0 cs0 - 18.1% CPU load
68 requests/sec - 6.3 MB/second - 95.2 kB/request
400 requests currently being processed, 0 idle workers
I told customer service and said my website (a big forum) have 4000 people now, I felt very slow, could the slowness caused by this max apache connection setting?
I got reply: "400 seems to be as high as Apache can go. Your httpd.conf settings currently show 500 max connections enabled. If Apache is stopping at 400 then this is it's hard limit for maximum connections. Also If it was able to go even higher you would eventually run into memory issues on the server that would cause the server to crash."
Can anyone tells me if "400 requests currently being processed, 0 idle workers " is a problem or could it be the cause of the slowness. I imagin if more people request connection, and apache can't deal with that much, it has to let those request wait in the queue, therefore caused slowness or time-out.
The seem server could deal with 8000 people online before, no any problem at all and speed was quite fast. I don't know what i should do now.
View 14 Replies
View Related
Jun 20, 2008
I've had a problem a couple of times where there is a bad ftp connection to a host. A trace reveals that there is a node timing out. What is a good way to work around this. Web based ftp client or other solution?
View 0 Replies
View Related
May 15, 2008
How can I Limit connections per IP in IIS6?
For example 10 connection per IP is allowed in a minute.
View 0 Replies
View Related
Apr 2, 2008
my server always have problem about the mysql connection:
Discuz! info: Can not connect to MySQL server
Time: 2004-5-14 8:55am
Script: /index.php
Error: Too many connections
Errno.: 1040
Similar error report has beed dispatched to administrator before.
i find the solution:
add "set-variable = max_connections=1000" in my.cnf file
but didnt find the file my.cnf,my control panel is directadmin,
View 6 Replies
View Related
Jan 7, 2007
My site is hosted on Dreamhost and gets over 1 million hits a day. The site is highly optimized, so it can handle the load easily without slowing the server down. Most pages have a loading time of under 0.2 seconds.
However, Dreamhost is telling me now that I'm using up too many "connections" and have limited my connections to 150 every 3 seconds (or so they say). Now 503 errors are coming up left and right, and its highly annoying to me and my users. Oh, and Dreamhost has mentioned several times that I'm oh such a very good candidate to upgrade to $400/mo dedicated hosting (from $8/mo currently).
So my question is, is this connection restriction really a valid concern of Dreamhost or are they just trying to milk me for money because my site is popular?
View 22 Replies
View Related
May 31, 2008
on setting up some sort of firewall who only allows 10 connections from the same ip to avoid spamming, abuse on the server.
How should i do this?
View 3 Replies
View Related