[edit] Last updated: Mon, 01 Nov 2010

B�l�m 23. Kal�c� Veritaban� Ba�lant�lar�

Kal�c� ba�lant�lar, beti�iniz sonland��nda kapat�lmayan SQL ba�lant�lar�na denir. Kal�c� bir ba�lant� istendi�inde, PHP daha �nceden tamamen ayn� �zelliklerde ba�ka bir kal�c� ba�lant� a��l�p a��lmad��na bakar (daha �nceden kalan) - ve e�er varsa, onu kullan�r. E�er yoksa, yeni ba�lant� kurar. 'Tamamen ayn�' ba�lant�lar, ayn� sunucuya, ayn� kullan�c� ismi ve (gerekliyse) �ifreyle a��lm�� ba�lant�lard�r.

HTTP sunucular�n�n �al��ma mant��na a�ina olmayanlar, kal�c� ba�lant�lar� ger�ekte olmad�klar� �eyler sanabilirler. �zellikle, ayn� SQL ba�lant�s� �zerinde 'kullan�c� oturumlar�' a�mazlar, transaction'lar� daha etkili bir �ekilde yapman�z� sa�lamazlar ve ba�ka pek �ok �eyi ger�ekle�tirmezler. Konu hakk�nda daha a��k olmak gerekirse, kal�c� ba�lant�lar size karde�leri kal�c� olmayan ba�lant�lar�n sunmad�� hi� bir ek �zellik sunmazlar.

Neden?

Bu daha �ok HTTP sunucular�n�n �al��ma mant��yla alakal�. Sunucunun PHP'yi kullanarak web sayfalar� olu�turmas�n�n �� y�ntemi var.

�lk y�ntem PHP'yi CGI "sarg�s�" olarak kullanmak. Bu �ekilde �al��t��nda, sunucuya gelen her sayfa iste�i i�in ayr� bir PHP yorumlay�c�s� yarat�l�r ve yokedilir (PHP sayfalar� i�in). ��lemler sonunda yokedildikleri i�in, yaratt�klar� her kaynak da (SQL sunucusuna ba�lant�lar gibi) onlarla birlikte yokolur. Bu durumda, kal�c� ba�lant�lar kurmay� denemekle elinize hi�bir�ey ge�mez -- basit�e, kal�c� olamazlar.

�kinci, ve en pop�ler, y�ntem PHP'yi �ok i�lemli (multiprocess) bir sunucuda mod�l olarak kullanmak, �u an sadece Apache'de oldu�u gibi. �ok i�lemli bir sunucu genellikle bir ana i�lemden (parent) ve onun kontrol�ndeki ger�ekte i�lemleri yapan �ocuk i�lemlerden (children) olu�ur. Ne zaman bir al�c�dan istek gelse, istek o anda ba�ka bir iste�e cevap vermeyen bir �ocuk i�leme devredilir. Bu demektir ki, ayn� al�c� ikinci bir istek yollad��nda, ba�ka bir �ocuk i�lem taraf�ndan kar��lanabilir. Kal�c� ba�lant�lar�n sizin i�in burada yapt�� i�, her �ocuk i�lemin sadece ilk seferde SQL sunucusuna ba�lanmas�d�r. Ba�ka bir sayfan�n ayn� ba�lant�ya ihtiya� duymas� durumunda �nceden kurulmu� ba�lant� kullan�l�r.

Son y�ntem PHP'yi �ok i� par�ac�kl� (multithreaded) sunucularda eklenti olarak kullanmak. �u anda PHP 4'�n Netscape FastTrack (iPlanet), Microsoft Internet Information Server (IIS), ve O'Reilly Website Pro gibi �ok i� par�ac�kl� sunucularda �al��mas�n� sa�layan ISAPI, WSAIP, ve NSAPI (Windows'ta) i�in deste�i var. Bu �al��ma �ekli az �nce anlat�lan �ok i�lemli modelle ayn�. SAPI deste�inin PHP 3'te olmad��na dikkat edin.

E�er kal�c� ba�lant�lar�n ek �zellikleri yoksa, neye yararlar?

Buradaki cevap olduk�a basit -- etkinlik. Kal�c� ba�lant�lar, SQL sunucunuza yeni ba�lant� a�mak performans a��s�ndan hesapl� de�ilse �ok i�e yararlar. Performans hesapl�l�� pek �ok �eye ba�l�d�r. �rne�in, ne t�r bir veritaban� oldu�u, HTTP sunucusuyla ayn� bilgisayarda olup olmay��, SQL sunucusunun �al��t�� bilgisayar�n i�lem yo�unlu�u ve benzeri. Sonu� �u ki, yeni ba�lant� performans� d��kse, kal�c� ba�lant�lar i�e yarar. Her �ocuk i�lemin her seferinde yeni ba�lant� a�mas� yerine hayat� boyunca sadece bir kere ba�lant� yapmas�n� ve s�rekli onu kullanmas�n� sa�larlar. Bu demektir ki, kal�c� ba�lant� a�an her �ocuk i�lemin kendisine ait SQL sunucusuna kal�c� bir ba�lant�s� olacakt�r. �rne�in, 20 tane kal�c� ba�lant� a�an betik �al��t�rm�� farkl� �ocuk i�lem varsa, SQL sunucusuna, her �ocuk i�lemden bir tane olmak �zere, 20 tane ba�lant� olacakt�r.

Ancak dikkat edin ki, SQL sunucusunun ba�lant� limiti kal�c� ba�lant�larla a��ld��nda bunun bir tak�m g�t�r�leri olacakt�r. E�er veritaban� sunucunuz ayni anda 16 ba�lant�ya izin veriyorsa, ve yo�un bir �al��ma an�nda 17 �ocuk i�lem kal�c� ba�lant� a�maya kalkarsa, bunlardan biri ba�lant�y� a�amaz. E�er betiklerinizde ba�lant�lar�n kapanabilmesine olanak tan�mayan hatalar varsa (k�s�r d�ng�ler gibi), 32 ba�lant�ya izin veren bir veritaban� sunucusu �ok �abuk bir �ekilde kullan�m d�� olur. Bo� duran (idle) ba�lant�larla nas�l ilgilenece�iniz konusunda veritaban�n�z�n d�k�manlar�na ba�vurun.

Uyar�

Kal�c� ba�lant� kullan�rken ak�lda tutulmas� gereken birka� ek g�t�r� daha var. �rne�in kal�c� bir ba�lant� �zerinden bir tablo kitlendiyse (LOCK), ve betik herhangi bir sebepten dolay� kilidi kald�ram�yorsa, ard�s�ra gelen ve ayn� ba�lant�y� kullanacak betikler s�resiz olarak bloke olacak, ve sizin ya HTTP sunucusunu ya da veritaban� sunucusunu ba�tan �al��t�rman�z gerekecektir. Ba�ka bir g�t�r� transaction kullan�rken olu�abilir. Bir transaction blo�u, beti�in �al��ma s�resi blo�un �mr�nden kisaysa, bir sonraki beti�e ta��nacakt�r. �ki durumda da register_shutdown_function() ile transaction'i geri alacak ya da tablo kilitlerini kald�racak bir temizlik fonksiyonu kaydedebilirsiniz. Daha iyisi, problemleri t�m�yle �nlemek i�in tablo kilitleri veya transaction kullanan betiklerde kal�c� ba�lant�lar� kullanmamakt�r (ba�ka yerlerde hala kullanabilirsiniz).

�nemli bir �zet. Kal�c� ba�lant�lar normal ba�lant�larla birebir e�lenebilecek �ekilde tasarland�. Bu demektir ki her zaman kal�c� ba�lant�lar� normal olanlarla de�i�tirebilirsiniz, ve bu betiklerin davran��lar�n� etkilemeyecektir. Betiklerin etkinliklerini belki de�i�tirebilirler, fakat davran��n� de�i�tirmezler!

G�venli Kip

Ba�lant� ��leme

[edit] Last updated: Mon, 01 Nov 2010

add a note User Contributed Notes Kal�c� Veritaban� Ba�lant�lar�

jorgeleon85 at gmail dot com 18-Feb-2011 02:07


I've been looking everywhere for a benchmark or at least comparison of the overhead used by oci_connect and oci_pconnect.

Just saying "oci_connect is slower because the overhead..." is not enough for me. For than I wrote a couple scripts to compare perfomance.

At the end I found out an average of 34% more time using a oci_connect than oci_pconnect, using a query of 50 rows and 100 columns.

Obviously this wasn't a real benchmark however it gives a simple idea of the efficiency of each function.

ambrish at php dot net 20-Sep-2010 01:37


In IBM_DB2 extension v1.9.0 or later performs a transaction rollback on persistent connections at the end of a request, thus ending the transaction. This prevents the transaction block from carrying over to the next request which uses that connection if script execution ends before the transaction block does.

Tom 28-Dec-2009 05:37


There's a third case for PHP: run on a fastCGI interface. In this case, PHP processes are NOT destroyed after each request, and so persistent connections do persist. Set PHP_FCGI_CHILDREN << mysql's max_connections and you'll be fine.

ynzhang from lakeheadu canada 09-Mar-2009 10:49


It seems that using pg_pconnect() will not persist the temporary views/tables. So if you are trying to create temporary views/tables with the query results and then access them with the next script of the same session, you are out of luck. Those temporary view/tables are gone after each PHP script ended. One way to get around this problem is to create real view/table with session ID as part of the name and record the name&creation time in a common table. Have a garbage collection script to drop the view/table who's session is expired.

christopher dot jones at oracle dot com 26-Jun-2007 10:46


For the oci8 extension it is not true that " [...] when using transactions, a transaction block will also carry over to the next script which uses that connection if script execution ends before the transaction block does.".  The oci8 extension does a rollback at the end scripts using persistent connections, thus ending the transaction.  The rollback also releases locks. However any ALTER SESSION command (e.g. changing the date format) on a persistent connection will be retained over to the next script.

andy at paradigm-reborn dot com 20-Feb-2007 08:13


To those using MySQL and finding a lot of leftover sleeping processes, take a look at MySQL's wait_timeout directive. By default it is set to 8 hours, but almost any decent production server will have been lowered to the 60 second range. Even on my testing server, I was having problems with too many connections from leftover persistent connections.

whatspaz at g NO dot SPAM mail dot c o m 26-Nov-2006 03:02


in response to web at nick, have you tried FLUSH PRIVILEGES. this should reload those privileges.

RQuadling at GMail dot com 06-Mar-2006 03:43


If you have multiple databases on the same server AND you are using persistent connections, you MUST prefix all the table names with the specific database name.



Changing the database using the xxx_select_db functions alters the database for the connection for all users who are sharing that connection (assuming PHP is running shared and not CGI/CLI).



If you have 2 databases (live and archive) and your script is talking to both, you cannot use 2 persistent connections and change the database for each one.



Internally, persistent connections are used even if you do not specify that you want to use persistent connections. This is why new_link was added to mysql_connect/mssql_connect (PHPV4.2.0+).

fabio 12-Jan-2006 03:54


You can in fact provide a port for the connection, take a look at http://de2.php.net/manual/en/function.mysql-pconnect.php#AEN101879



Just use "hostname:port" for the server address.

aaryal at foresightint dot com 15-Jan-2004 03:21


this one bit quite a bit of chunk out of my you-know-what. seems like if you're running multiple database servers on the same host (for eg. MySQL on a number of ports) you can't use pconnect since the port number isn't part of the key for database connections. especially if you have the same username and password to connect to all the database servers running on different ports. but then it might be php-MySQL specific. you might get a connection for an entirely different port than the one you asked for.

web at nickSPAMrabinowitz dot com 02-Dec-2003 02:41


This may only pertain to Apache/MySQL:



After several hours of wrestling with MySQL "Access denied" messages, I determined that persistent database connections don't necessarily reflect subsequent privilege changes.



I loaded a PHP script attempting a LOAD DATA statement, and got an "Access denied" error. I granted FILE privileges to the MySQL user, and was able to run LOAD DATA statements from the terminal, but still got "Access denied" from my PHP pages.  When I switched from mysql_pconnect() to mysql_connect(), the problem went away; eventually I restarted apache to kill the persistent connection and switched back to mysql_pconnect(), and now everything works fine.

jean_christian at myrealbox dot com 15-Aug-2002 03:13


If anyone ever wonders why the number of idle db process (open connections) seems to grow even though you are using persistent connections, here's why:



"You are probably using a multi-process web server such as Apache. Since

database connections cannot be shared among different processes a new

one is created if the request happen to come to a different web server

child process."

sebastian at flothow dot de 18-Apr-2000 07:28


Yes, with nonpersistent connections database connections last only while a database-related request is processed, thus reducing the load on the database server.


However, latency will be somewhat higher since a database connection must be opened before a request can be handeled.

add a note