[MySQL] Too Many Connections: Locked Queries

Upto 150 MySQL Queries A Second

Apr 7, 2008

I'm currently hosting all of my stuff with my own Wild West Reselling Account, i.e., InnoDomains.com.

Problem is I'm running multiple Wordpress Installations and a few websites that have some pretty intense MySQL work going on in the background, and I use about 250GB per month in bandwidth.

My Account gets the 503 downtime errors because according to Customer Service I cannot run more than 50 MySQL queries in one second. Well, that's not much of a challenge because a lengthy wordpress page runs about 90 queries and loads in 2-3 seconds. Time this by three including the other websites and a couple of shopping stores, and I'm easily doing more than 100-150 queries a second more than several times a day, thereby seeing that nasty, irritating error.

My other option is to split up all the websites over basic $ 3.95 per month accounts. I don't want to switch to a dedicated server yet because I'm not really making a whole lot of money (about $ 30 per month). Another thing I could do is move my MySQL testing to a local machine, and that may actually reduce the load on the damn server.

Are there any hosts out there that will allow me to do more than 100 MySQL queries per second? If so, are there any that are any good?

View 13 Replies View Related

Limiting MySQL Queries

Jun 30, 2007

Is there any way to set max MySQL queries per hour to all MySQL users at once?

(lets say 20000 queries for each user?)

(cPanel/SuSE 10.0/MySQL 5.0.X).

View 2 Replies View Related

Mysql Slow Queries

Feb 25, 2007

I added the following to my.cnf and restarted mysql
log_slow_queries=/var/log/mysql/mysql_slow-queries.log

I saw at leat 53 slow queries, however, there is no such file in that folder.

View 9 Replies View Related

No Clue: Automatic MySQL Queries

Apr 9, 2008

I've been pondering something for a long time here, but never really knew the right question to ask, or how to word it... so I never even know what to search for. So I honestly have no idea what I'm asking or if this is even under the right topic. What I'm looking for is answers, links, tutorials, or just some words I can search for to help me do the following:

I have a MySQL database filled with accounts. People have to pay to get these accounts. All the accounts are set to expire on different days. I need to send an e-mail out to accounts, for example, to let them know they're expiring, say, a month before this will occur. Right now the only way I know to do this is to have MySQL queries on a PHP page that, every time the page is loaded, runs through all the accounts, checks their expirations, and sends emails to those that need it. I know there has to be a way to have this happen automatically without having to load a PHP page every day, for example.

View 2 Replies View Related

MySQL Anyway To Log Queries Of Specific User

Jun 5, 2007

I know about the general log but want to just log queries of a specific user. Our database does 400 queries/s on a slow day, so I think it would be a pain to grep though a huge log like that. And, im not sure if it would cause any preformance issues.

View 0 Replies View Related

MySQL Process List - State = Locked

Aug 18, 2008

When i login to the root for the server and go to mysql process list i see a lot of processes that are connected to photo handling and the sate is Locked with over 500 seconds of each one.

I see there is one process that the state says: Copying to tmp table

I read online that if this is taking long it might lock up other queries causing your server to perform slow and only solution is to restart mysql servers.

Right now i have over 100 queries that say Locked and one that says Copying to tmp table....each time i have to restart the mysql services but this happens every 10 minutes as i have enough traffic to cause this problem.

View 9 Replies View Related

Optimizing These Particular MySQL Slow Queries In WordPress

Jun 14, 2008

What I did here was create /var/log/mysqld.slow.log for queries that are taking one second or longer. After around 36 hours, here is what I came up with. Since the file size of /var/log/mysqld.slow.log was 219KB, posting it in its entirety it not practical. Therefore, I used a program, the perl script known as mysql_slow_log_parser found on www.retards.org, to parse the mysqld.slow.log to make it easier to interpret and read. The result was that the original filesize of 219KB was converted to the smaller size of 14KB....

View 7 Replies View Related

MySQL Replication Setup With 150 Queries/sec -> Major Lag

Jun 8, 2008

I have fairly a large web site that has a forum and a torrent tracker.

Currently MySQL server is handling about 150 queries an avarage per second.
Here is the server spec:

Core2Duo 2.66Ghz
4Gb RAM
320GB SATA 7200RPM (Server provider does not have 1.5K RPM nor 1.0k RPM)
100Mbit Connection (servers on the same switch and the switch does not have 1Gbit port)
MySQL Version: 5.0.51a

I had Master-Master Replication setup with forum running on one and the tracker running on the other.
Although this has been working for about few days, we started seeing lags in the replication process.
After a week, there is a major lag and the changes made on one of the servers takes about 5 hours to appear on the another.
So, this doesn't work.

What would be the other ways of splitting MySQL queries concerning the same database?

While I was researching, I read about MySQL Cluster with database storage engine being NDB.

But, let's say that there is a power failure on both the nodes at the same time, then I would lose the whole database as the database is stored on the memory correct?
I would not like to take that chance either, but if this is faster then replication method then maybe I will concider.

I thought about editing the forum coding to make all queries that concerns the tracker to go in to, say server B (with forum's primary MySQL server being Server A), and make the tracker use server B as MySQL backend, but it seemed like a heavy work so that will be the last choice.

View 6 Replies View Related

VPS Incredibly Slow, Huge Numbers Of Slow MySQL Queries For Plesk/Watchdog

Nov 11, 2008

I've got 25 domains on a Virtuozzo/Plesk8.6/CentOS5 VPS. Each domain has one up-to-date install of WordPress, most have very little traffic (average 200mb per month), maybe 2 domains get 5-7gb traffic per month.

I monitor port 80 connections and rarely see more than 10 at a time.
That should in my opinion be no problem at all for a VPS with 768mb guaranteed ram and 2.4ghz cpu. I've got 30gb hard drive spare too.

But.... about 8 or 10 times a day it grinds to a complete halt: server load at 500-1000%, sites timing out, plesk takes 3mins to load, often I can't even connect with SSH, and the plesk web server, apache

INSERT INTO module_watchdog_sys_stat (time, type, value, service_id) VALUES(FROM_UNIXTIME(1226404705), 'MAINMEM_USAGE', 17472, 11);

80 seconds sounds like a huge amount of time for a MySQL insert to me! Does anyone know if this is likely to be the cause of my trouble? Some problem with Plesk and the database? Or could it be something else?

View 3 Replies View Related

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 View Related

MySQL Max- Connections

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

MySQL Connections

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

Closing Mysql Connections

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

Mysql Connections Topping Out Within Seconds

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

MySQL Timeout On Active Connections

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

Best Host For Fast MySQL Connections

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

MySQL Stops Allowing External Connections

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

How To Limit Http/mysql Connections Per Domain

Jun 22, 2007

how i can limit http and mysql connection limit on per domain basis.

View 2 Replies View Related

VPS Locked

May 16, 2009

We get this for our HyperVM/Lxadmin vps when we try to restart:

vzctl restart <vid>

Container already locked

Also all sites are down.

View 4 Replies View Related

The Vps Is Locked By Another User

May 5, 2009

when I tried to reboot my VPS via Hypervm Enterprise I got this error:

===========
Alert: The vps myvps.vm is locked by another user. This would also be because you had interrupted an earlier activity that would take long time to complete. HyperVM has waited 15 seconds for the other process to finish. Please try after sometime.
===========

and memory usage going crazy it's going from 400MB "natural usage level" to 1024MB then the vps and all sites going down.

View 4 Replies View Related

Locked Out Of SSH .. Firewall

May 25, 2008

Today i got home from work to find none of my websites responding. When i ping the server i get a response as normal so i know its online. It seems the firewall iptables is blocking all incoming access to the server. When trying to connect to ssh nothing happens (just a blank black screen) heres a screenshot: [url]

I rang my datacenter but no one is there as its sunday evening.

Is there anyway i can get into the server to turn off the firewall?

View 9 Replies View Related

Locked Out Of Server

Apr 15, 2008

Have a new client who's locked themselves out of their web server. Basically they've forgotten their password. Sadly there's no backup admin account.

It's a Dell PE1950 running Win2K3 R2 (SP2). Standalone server (non-domain). OS is in RAID-1 on a PERC 5i card.

I've attempted to use BartPE and UBCD with various instruction sets I've found online regarding PERC controllers, but I can't actually get either of them to boot (just hangs at the 'loading PE' screen with the bar).

I've tried using

I'm thinking I'm screwing up the driver part somehow, as the discs otherwise boot on standard, non-RAID IDE systems. I'm using Win2K3 R2 media for my base package.

View 5 Replies View Related

Locked Inbox

Feb 14, 2007

When account is accessed via Outlook an error is generated saying "mailbox is temp unavaliable because another email message is being delivered or another application is accessing it. The server responded: -ERR Timeout while obtaining a lock on /home/user/mail/domain.com/emailuser/inbox"

When attempt to login using HORDE I just cycle through logging in, going from login and trying to get to the In box and timing out as content fails to complete

I tried searching the forums for this, nothing. Anyone know what to do about this?

View 1 Replies View Related

Domain Locked Into Websitepros For Fee

Apr 24, 2008

I have a customer that currently is paying a nice chunk of money every month for WebsitePros.com (one of those do it yourself sites).

Domain is registered by them and they are claiming if we want to cancel and get access to the domain name we have to pay a $100 fee(to gain access to the domain name).

View 7 Replies View Related

DNS Queries From Our Server

May 10, 2007

We host our DNS with DNSmadeeasy.com. We noticed that the number of DNS requests is too high. So we decided to contact their support and ask them for log file, so we can determine

what's going on.

Reply we got from them is the following:

We researched your queries and we found that many of them were coming from the SoftLayer resolving name servers. We phone their admins to report the abuse and they said that the queries were infact coming from your own server with them.

We will not charge for any overage on the queries this month so you can research what on your server is causing these queries. But we will provide you with logs of about 5% of our your traffic.

here is the example of the line from log file sent by them:

May 10 17:15:48 t145 named[29945]: client 66.228.118.72#52529: query: host-196.218.57.145.tedata.net.mydomain.com IN AAAA -E
May 10 17:15:48 t145 named[29945]: client 66.228.118.72#52529: query: afontenayssb-151-1-40-153.w83-114.abo.wanadoo.fr.mydomain.com IN AAAA -E

I changed the real domain name to "mydomain.com"

I checked the apache access log and found these hosts there in exact order as they are in log file sent by dnsmadeeasy.

What is strange is that why is my server making back these requests ? Also these requests are bad because these are not valid domain names:

e.g. should be "host-196.218.57.145.tedata.net" instead of "host-196.218.57.145.tedata.net.mydomain.com"

Seems that it includes ".mydomain.com" to the end of every request.

Is there a way to discover what is making those DNS queries?

View 11 Replies View Related

Dnsstuff Queries

Jan 19, 2007

how can i run queries in dnstuff.com

for examlpe we can run following query for DNS Lookup MX record

Code:
nslookup -q=MX www.webhostingtalk.com
ISP Cached DNS Lookup =?
E-mail Test = ?
ISP Cached DNS Lookup =?

View 3 Replies View Related

Redirect NS Queries

Jun 13, 2007

I have 2000 domains set to

NS1.anydomain.com NS2 etc

Is it possible, through some script or otherwise, to send the traffic to certain/chosen domains to a completely different set of nameservers.? Preferably without having too much server load?

I already use a domain catchall, so no NS records are actually registered on the server.

View 2 Replies View Related

Trying To Figure Out Why A System Locked Up And Hung

Mar 2, 2008

I had to bump a server we have over at the planet, but I cannot seem to determine the cause of its unresponsiveness (everything down to even simple ping). I checked APF logs and it didn't show anything except its normal periodic refresh of rules and such. I also see an selinux warning about proftpd trying to get to proftpd.conf and I saw something about selinux blocking mysql from using mysql.sock as well - I temporarily disabled SELINUX all together (it was in targeted mode). In the log files, it did log the fact though that it received SIGTERM and was going to runlevel 0, so it wasn't completely dead. I've gone through quite a few other logs and have come up empty handed. Is there anything else I can do? I want to get to the bottom of this.

View 0 Replies View Related