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 RepliesMy 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 ?
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?
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?
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
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?
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.
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 RelatedRecently 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.
how i can limit http and mysql connection limit on per domain basis.
View 2 Replies View RelatedI've been reading, and it seems that I have to use ACL. But, how do I setup a proper ACL to keep my server from being an open relay?
View 2 Replies View RelatedHow to close any communication between the server and some hosts/domains?
So there won't be any in/out connection between the server and those domains?
can APF firewall or mod_security do this?
I'm tranferring files from one server to another, and it is rather large. When I dissonect from SSH it seems like the command stops.
How do I make it continue whether or not the ssh session is still active?
When i am trying to connect to ftp, browser shows me error
Error 421 Service not available, closing control connection
this problem comes at home but not in office. I am able to access my ftp a/c with same user name and password but in home it i got the error.
Received this gem last night:
Quote:
-----Original Message-----
From: AnchorVPS Support
Sent: Fri, August 3, 2007 1:14 am
To: xxxxxx@talanovmedia.com
Subject: IMPORTANT - BACKUP YOUR DATA
AnchorVPS System Message:
08/03/2007 12:14 AM
Hello,
AnchorVPS is now unable to provide any service to any of
our clients. We ask that you backup your data within 48 hours and find
another provider.
If you have any questions or queries then
please contact us at support@anchorvps.com
Thank
You,
AnchorVPS Administration
Never thought a company would do this to their own clients. 48 hour notice?
Thought the headers in the email were suspect so I decided to open up a ticket asking if this is a legitimate message.
Quote:
Jason Min
Posted On: 03 Aug 2007 09:22 AM
Yes it is.
Regards,
Jason
Your thoughts?
Edit:
TOS
Quote:
Termination
This Agreement may be terminated: (i) by you or AnchorVPS during any Renewal Term, without cause, by giving the other party no less than seven (7) days prior written notice.
Since moving servers I've been plagued by constant disconnects whilst using FTP:
421 No transfer timeout (300 seconds): closing control connection
I've gone through Proftpd's forum and documentation numerous times to try and find a solution to this but have been unsuccessful so far.
Within proftpd's config file it's set at:
TimeoutLogin 120
TimeoutIdle 3600
TimeoutNoTransfer 3600
TimeoutStalled 3600
So I am unsure where it is getting the "300 seconds" from.
Even though the error states 300 seconds, this problem happens way before 300 seconds every time and has happened during the transfer of files (when the connection has been active and in use).
I've tried 3 different FTP clients and used the "Keep Alive" option in each and it has absolutely no effect.
I am unsure if APF is causing the problem, I can't see any problems in any of the server logs in relation to ftp.
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..
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 : ...
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.
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 RelatedHow 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 RelatedWARNING: 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.
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,
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?
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?
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)?
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?
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