Persistant mysql?

[Azu]

Sorry I just thought that it was weird that the mysql_(p)connect takes longer then all of the queries and all of the time taken by PHP to process the data put to together..

[Azu]

Hmm I tried switching to mysqli but that made the connect take TWICE as long.

 

On some pages, 90% of the load is from the connect!

 

Please tell me how to fix it, I'm sure it shouldn't be taking longer then everything else put together!

[Azu]

Doesn't ANYONE know why mysqli_connect and mysql_connect are taking longer to run then everything else on my site put together? How do I fix it?

[dj-kenpo]

dude 8ms isn't a problem, do you know how fast 8ms is?

 

if you NEED things to be faster than a few ms, for soem crazy reason, then load everything into ram. the sql table, apache, php etc.

 

8ms sounds like the harddrive seek time.

computers don't do things instantly, it's simply NOT possible.

[Azu]

Okay I'll try to explain this again.

 

When it takes around 1MS for EVERYTHING on the page except the _connect, which takes between 8 and 20 MS, if I can shave those off, that means that it will be around 8 to 20 times (800 to 2000% speed increase) fast without having to buy any new hardware. Meaning load can increase by 8 to 20 times on the same hardware.

 

This would be a HUGE increase.

 

So I would be very grateful if somebody could tell me how to fix it.

 

I am certain that mysql(i)_connect is not supposed to take longer then everything else put together.

 

And I'm pretty sure it's not disk seek. Wouldn't something as basic as the stuff needed to connect to the database be cached in memory?

[Azu]

ANYWAYS, can somebody please just answer me instead of trying to find excuses not to? That would be very nice!

[premiso]

Obviously no one has an answer.

 

The issue seems to lie within your server/server hardware. Simple as that, upgrade the server the problem is solved.

[hitman6003]

You aren't listening to what Thorpe, among others, has told you.  That 8ms overhead is necessary for your webserver to connect to the db server via the network.  It has to send the username/password combo then receive confirmation of successful login.  It will always be there.

 

mysql_pconnect is only available if you are using a module version of php.

 

http://us.php.net/manual/en/features.persistent-connections.php

 

I'm guessing that you aren't running php as a module, which means mysql_connect and mysql_pconnect act exactly the same.

 

If you want to reduce that 8ms, which most likely isn't possible, improve your network speed...if your using 100Mbps, go to 1Gb, if your using 1Gb go to 10Gb.

[rameshfaj]

I think the persistent connection to the mysql server makes the processing a bit slow and less effective.Its no problem to create the connections frequently.

The database server generates the error:

Connection refused when too many clients are accessing the connection.

So think about this also.

[Azu]

It's nothing to do with the network though. The MySQL server is on the exact same computer as the PHP. And yes, PHP is accessing MySQL through a module.

I'm sorry if I sounded rude but I just really would like to remove this overheard since it is (relatively) large..

 

I'm not really sure what I should update either. I have the latest versions of PHP and MySQL that I can find, and 4 gigabytes of RAM, 3 15,000 RPM sata hardrives in RAID 0, and a Core 2 Duo CPU.. which one is the weakest link? =S

 

Oh and I haven't been getting any errors. It's just that the connect is taking longer then everything else put together that concerns me. And it doesn't seem to matter whether I use _connect or _pconnect; I can't find any differance.  This confuses me.

[premiso]

Alright, this is my last try at helping you, then I am at a stand still.

 

It seems as though there is something with your MySQL, Apache (if you are using apache) or PHP software. One of the 3 is throwing stuff off (if you are using IIS I would suggest trying apache instead as that is probably the problem). You have probably clean slated many times, but  I would suggest getting WAMP or XAMP and trying to install those and run the script. If either of those works better it usually means there is  an issue with how one (or all) of those three were installed.

 

My suggestion, try one of  those and see if it works better, if you are running those I would suggest installing mysql, apache and php on your own. Make sure with MySQL you get the ODBC driver too.

 

Oh and btw, the Intel Core 2 Duo is the weakest link. Go AMD!

[hitman6003]

What user controls do you have on your database?

 

If you have permissions all the way down to the column level, or even the table level, for any user, it forces mysql to check those permissions for all users.

 

That can significantly slow down user authentication.

 

Additionally, depending on how you are specifying your connection, it can affect your connection speed.  For example, the default connection method for Windows will be TCP/IP, even if you specify "localhost".  If you are using shared memory as the connector on windows, it will be much slower than TCP/IP.  If you have *nix, then [assuming default settings] the default connection, so long as you put "localhost" as the host name for the db connection, will be named pipes...which is faster than the network...even the loopback interface.

[Azu]

Thanks for the help guys. Just one more question.

 

How do I set MySQL to use named pipes, since they are faster then TCP/IP? And yes I am using localhost to connect.. would it be better to use 127.0.0.1 or my IP address? Or is localhost okay?

 

P.S. I think I have the user permissions just on the default "allow all" or whatever the default is.

[Azu]

Oh and I think I worded something wrong before.

 

See the problem is that every single time a page is loaded, PHP starts a whole new connection to MySQL.

 

This is obviously a horrible waste of resources, so I need to find out how to make it so that it just opens ONE connection, and then just keeps that single connection open forever and uses it for all future requests, instead of constantly having to open a new connection every single time a page is opened. So that there is no more connection overhead. I thought that the _pconnect would do this but like I have said, it doesn't. So how do I do this? Please tell me.. I really really need to know..

[drewbee]

Using mysql_connect on every single page doesn't create that much more overhead. I've seen sites with thousands of pages, each having thousands of users on it at any given time using mysql_connect and having no problem. If it is slowing down, it is your hardware, not your connections.

[premiso]

Oh and I think I worded something wrong before.

 

See the problem is that every single time a page is loaded, PHP starts a whole new connection to MySQL.

 

This "problem" is not just for you, everyone has that same overhead.

 

So let's think of why they designed mysql to open a new connection each time a script call is made, k? The reasoning for that if it was left open the whole time the memory would get eaten up. This would literally kill your computer's resources which would make it 5 times slower. Think of this way, if you had 1,000 people connecting at the same time and you never closed the mysql connection, each one of those would need the connection data in it's memory. This would cause a huge memory overflow and would probably crash the server. That is why a new connection is made each page, to avoid such a catastrophe.

 

I think you just need to understand that the new connection on each page is necessary, because it will save your server from losing resources and memory.

[Azu]

Oh okay thanks.. so basically there is a memory leak if the connection is left open?

Is there a way to fix it (besides closing and reopening the connection over and over)?

Or if not, is there a way to just make it do it anyways, and just have it automatically restart once in a while? I have plenty RAM..

[drewbee]

Azu,

You are completely misunderstanding what is going on. You do not want to leave this connection open. The only times I would even think of using pconnect is if I am using some type of AJAX application that requires server data pushing (rather then polling). But that is a completely different discussion. Another situation to which I may use a persistant connection is accessing a remote database from a site other then where it is hosted. For local connections though, connect is just fine.

 

There is no "fix" to the memory leak, because it is not a problem nor a bug. It does as it is suppose to do, nothing more and nothing less.

 

If you do decide to use pconnect, you better have some type of system in place as well to ensure the requestor/updater/user making query calls is who they say it is.

 

pconnect most power comes in useful with transactions. If you are not using transactions, then this is a rather pointless. feet, IMO. Even still, transactions can be a very dangerous thing to be used with pconnect

 

Warnings taken from php.net/mysql_pconnect

Do not use transactions (e.g. with InnoDB MySQL tables) with persistent connections.  If your script stops or exits for any reason, your transaction will be left open and your locks will be left on.  You have to reset MySQL to release them (so far as I can figure).  They won't ROLLBACK automatically on error, like they ought to.  When you restart the script, you'll get a new connection, so you can't rollback or commit for the previous script.  Any script with a start transaction, rollback, or commit SQL statement should use regular (not persistent) connections.  Seems like PHP ought to automatically issue a ROLLBACK on any open transactions when a script exits (error or otherwise) without a COMMIT.  ZEND's site has a brief blurb on this.  It's OK to mix/match so you use a persistent connection for the read stuff, but open a new regular connection conditionally (if you have to update, which is usually less often).

Be very careful when using persistent connections and temporary tables on MySQL: in MySQL, temporary tables are visible only to the currenct connection, but if you have a persistent connection the temporary tables will supposedly be visible to everybody sharing the same persistent connection. This can lead to major trouble. I suggest to use totally random temporary table names when using persistent connections to avoid major problems.

 

Be very careful when using persistent connections on a machine running multiple mysql servers. You must specify the correct socket path, otherwise PHP will reuse connections irregardless of what server they are connected to. That is, it will see an open connection with matching parameters and use it, even if the connection is actually for a different server.

 

Please take a look at these URLS for more information on pconnect:

 

http://www.php.net/mysql_pconnect

http://us2.php.net/manual/en/features.persistent-connections.php

[Azu]

Thanks for the articles and explanations.

I'm not using pconnect though, since it doesn't seem to make ANY differance to performance (no slower, no faster) so it's not what I am looking for.

 

I was just wandering if there is a way to make my website just use a single connection instead of reconnecting over and over. And if there would be a problem with this then I would like to find a way around it.

[Azu]

=(