0

I have to decide whether I should change my web host as my current host does not support mysqli. Currently I do my data manipulation by saving data files and using fopen, fwrite, fread. I was thinking of saving data in mysql using connection pool. Apart from security(which frankly is not of much concern), data searches are so much more easier in mysql. However the users will be accessing the mysql DB every few seconds, thus connection pool is a must, and so is processing time.

I have already calculated that without connection pool, the processing time in mysql is many times slower as compared to file operations. I wanted to know the latency difference between connection pool and file manipulation, and if connection pool is significantly faster than file manipulation through php.

Also what would be the difference in processing load on the host servers if I chose connection pool.

Thanks in advance.

user1517108
  • 2,395
  • 6
  • 32
  • 43

3 Answers3

2

Querying & manipulating data in a large file will probably slower and more tedious than mysql without connection pool. And there's file locking, concurrency issue you've to deal with.

Anyway, try not to do any premature optimization. Use a proper database and then collect performance statistic from there. Only do optimization when necessary.

viclim
  • 959
  • 8
  • 16
0

PHP scripts are executed every time a user requests the specific resource from the web server and then terminated. Therefore scripts can not share resources and thus there is no connection pooling in PHP; apache will release all resources once the script reaches EOF.

Edit, as for your actual question, it is pretty much impossible to give a general answer to which is faster, it depends on too many parameters such as type of disk, network connection, hops between the server and the database, etc. You would have to benchmark the different methods and compare the results against each other to get a definitive answer for your specific setup.

Searching for similar topics I found this, which provides information about connection-pooling in PHP (Connection pooling in PHP).

Community
  • 1
  • 1
Jakob Pogulis
  • 1,150
  • 2
  • 9
  • 19
  • But I thought that was the reason why mysqli was introduced, to replace pconnect... so that the connections do not die and they can be reused across the scripts. – user1517108 Apr 01 '13 at 07:46
  • But there is an implicit assumption that all parameters are same in both the cases... network connections... hops... disk type... If every parameter is similar then which is faster. – user1517108 Apr 01 '13 at 07:48
  • 1
    It is really impossible to say which one is faster when **writing** a new record. When it comes to searching through the data MySQL should be faster *assuming* there is no specific structure in your data that allows you to implement specific optimizations for reading the data from file. – Jakob Pogulis Apr 01 '13 at 07:53
  • As a matter of fact, there is a connection pooling in PHP – Your Common Sense Apr 01 '13 at 08:30
  • @YourCommonSense unless you're referring to something other than mysql_pconnection, PDO::ATTR_PERSISTENT, or mysqli with p: host prefix, there isn't. There is persistent connections which is not the same thing as connection-pooling, you will encounter a whole different set of problems working with persistent connections than you would working with connection pooling. – Jakob Pogulis Apr 01 '13 at 08:37
0

I am afraid that your assumptions are all wrong.

First, one on comparing files to DB. There is no such question ever. All web-programmers in the world already using various databases, with connection pooling or without. Starting from datasize of several MB, database is the only solution.

Second, on the connection pooling. At least 90% of sites are happily working without any connection pooling, sustaining even dozens requests per second.

I don't know where did you get your numbers for calculations, but I'm afraid that source is quite unreliable. Better start your site with DB and without connection pooling. Solve all the numerous problems you will face, grow your site to a hundred online users and then start thinking of connection pooling.

By the way, if your current host does support PDO, you don't even need to move. As PDO seems the only reliable mysql driver for PHP nowadays. But don't even start for mysqli - it's totally unusable.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345