Webmaster Forum

Go Back   Webmaster Forum > Web Development > Web Design Lobby

Web Design Lobby Forum for general web design issues not specific to scripting or graphics.


Reply
 
Thread Tools Display Modes
Share |
  #1  
Old 12-28-2008, 03:57 PM
2939195631902 2939195631902 is offline
Contributing Member
 
Join Date: 12-14-08
Posts: 140
iTrader: 0 / 0%
Lightbulb Open multiple mysql connection!! bad practice?

I have a large number of tables that i would like to link to almost every one of my web pages. So i decided to organize the tables in different databases. Each database acts like a folder to hold relevant tables.

each of my page will be linked to "connection.php", which opens 6 connections to 6 different databases under 6 different variables; $connection_members, $connection_main, $connection_navigations ...etc.

So i just wanted to know if this will work out or not. If it does it a bad practice?

Will it slow down the connection speed? Will is get messy?
 
Reply With Quote

Advertisement

Advertisement

  #2  
Old 12-30-2008, 03:31 PM
kos's Avatar
kos kos is offline
Coding Tiger
 
Join Date: 04-13-07
Location: .ro
Posts: 4,029
iTrader: 3 / 100%
this is a good question, but the answer is [not that] simple...

opening 6 concurrent connections can (or cannot) be considered too much, it depends on:
  1. your server
  2. how many visitors your website has
the latter is the most important because for each visitor will open these 6 connections. A mysql server can handle thousands of concurrent connections but...sometimes it crashes...you know?

From another point of view: why did you split the tables in multiple databases? aren't those tables related? If they aren't than I understand your situation, but if they are then you should put them into one database, because this is the point of a db, to handle big amounts of data.

Anyway, you shouldn't be worried too much about opening 6 connections (your server can handle that) but the way you manage them

 
Reply With Quote
  #3  
Old 12-30-2008, 06:55 PM
2939195631902 2939195631902 is offline
Contributing Member
 
Join Date: 12-14-08
Posts: 140
iTrader: 0 / 0%
Exclamation Here is the projected stats.

Quote:
Originally Posted by Costin Trifan View Post
this is a good question, but the answer is [not that] simple...

opening 6 concurrent connections can (or cannot) be considered too much, it depends on:
  1. your server
  2. how many visitors your website has
the latter is the most important because for each visitor will open these 6 connections. A mysql server can handle thousands of concurrent connections but...sometimes it crashes...you know?

From another point of view: why did you split the tables in multiple databases? aren't those tables related? If they aren't than I understand your situation, but if they are then you should put them into one database, because this is the point of a db, to handle big amounts of data.

Anyway, you shouldn't be worried too much about opening 6 connections (your server can handle that) but the way you manage them

traffic: 10,000 visits/day
10MB of traffic per visit.

My server currently is an $100/month dedicated server.

So what do you think?
Can the server handle the amouont of connections?
If not, what about 5000 visits/day?
 
Reply With Quote
  #4  
Old 01-01-2009, 05:52 AM
murrayd77 murrayd77 is offline
Junior Member
 
Join Date: 12-29-08
Posts: 2
iTrader: 0 / 0%
You can open multiple MySQL databases but there will be some slump in the speed. You can open 1 connection with a couple of databases at a time and that wont give you any trouble. Using 6 connections will slowdown the database speed but still it will work.

-- Dave.
 
Reply With Quote
  #5  
Old 01-01-2009, 08:09 AM
upupup upupup is offline
Contributing Member
 
Join Date: 10-05-08
Posts: 56
iTrader: 0 / 0%
I recommend you to only open 6 persistent connections at maximum. This means that once a user comes to your site it won't open 6 connections for him, instead it will use the already opened persistent connections to query the sql server.

This should be an improvement from where you stand right now as to many connections open at once because of high traffic could lead to unwanted results.
 
Reply With Quote
  #6  
Old 01-01-2009, 08:08 PM
2939195631902 2939195631902 is offline
Contributing Member
 
Join Date: 12-14-08
Posts: 140
iTrader: 0 / 0%
I can open multiple databases with a single connection?

Quote:
Originally Posted by murrayd77 View Post
You can open multiple MySQL databases but there will be some slump in the speed. You can open 1 connection with a couple of databases at a time and that wont give you any trouble. Using 6 connections will slowdown the database speed but still it will work.

-- Dave.
So i can open multiple databases (not tables) with a single connection?
That is what i'm really trying to do.

Could you provide me with an example statement that connects to multiple databases.
 
Reply With Quote
  #7  
Old 01-07-2009, 04:13 PM
2939195631902 2939195631902 is offline
Contributing Member
 
Join Date: 12-14-08
Posts: 140
iTrader: 0 / 0%
If i have 30 tables in one DB for my site. Would it be easier to navigation through phpmyadmin, if i were to categorize them some how? Dividing them into different databases is one way? is there any other?

If I divide the 30 tables into 6 databases, can i call on all of them using 6 mysql_select_db functions on my connection.php page?

How would i call a specific table from a specific database on my index page then? (assuming the connection.php is linked to the index using include function)

Last edited by 2939195631902; 01-07-2009 at 04:23 PM.
 
Reply With Quote
  #8  
Old 01-07-2009, 05:08 PM
kos's Avatar
kos kos is offline
Coding Tiger
 
Join Date: 04-13-07
Location: .ro
Posts: 4,029
iTrader: 3 / 100%
if those 30 tables are related somehow (that is, they're part of a whole) why do you split them into different dbs anyway? A database can handle and store more than 30 tables because that's the whole idea of a database, to store large amounts of data. You're not optimizing anything if you split your tables into multiple dbs, all you do is just making it harder for you...
 
Reply With Quote
  #9  
Old 01-07-2009, 05:22 PM
2939195631902 2939195631902 is offline
Contributing Member
 
Join Date: 12-14-08
Posts: 140
iTrader: 0 / 0%
All 30 databases are for a single website. However, i can divide them into the following catagories:

visitor, navigation, ...etc

visitor will contain tables: user accounts, visitor_log, staff_acounts ..etc

navigation will contain: primary, secondary, footer_navigation.

Unless this isn't necessary at all, then i wouldn't bother with it. I just thought it would be easier to find specific tables in phpmyadmin if they are catagorized like such.
 
Reply With Quote
  #10  
Old 01-08-2009, 11:06 PM
kos's Avatar
kos kos is offline
Coding Tiger
 
Join Date: 04-13-07
Location: .ro
Posts: 4,029
iTrader: 3 / 100%
Quote:
All 30 databases are for a single website
then put them into one database. Finding them in phpmyadmin would be easier and painless than opening 6 concurrent connections.
 
Reply With Quote
  #11  
Old 01-09-2009, 01:40 PM
juust juust is offline
Junior Member
 
Join Date: 01-05-09
Location: netherlands
Posts: 20
iTrader: 0 / 0%
Normally mysql can keep 2500 open connections at the same time, that doesnt mean 2500 simultaneous visitors. If you use 6 connections per visitor you can only handle 400 simultaneous visitors.

10.000 visitors is 500/hr, if you get traffic peaks you'd get in trouble. I'd opt for 1 database, and explicitly closing connections once the script is done.

Using a connection (resource) id, with mysql_close($id) when your script is done, closes a connection. Otherwise it remains open for a fixed amount of time and times out, then the resource is released (if you get 500 visitors within the time-out period your site goes down).

you can use a prefix if you have a database with lots of tables :
vis_ for visitor
nav_ for navigation
then you may have to rewrite queries, but that ain't too much work.
 
Reply With Quote
  #12  
Old 01-09-2009, 04:37 PM
2939195631902 2939195631902 is offline
Contributing Member
 
Join Date: 12-14-08
Posts: 140
iTrader: 0 / 0%
Lightbulb

Quote:
Originally Posted by juust View Post
Normally mysql can keep 2500 open connections at the same time, that doesnt mean 2500 simultaneous visitors. If you use 6 connections per visitor you can only handle 400 simultaneous visitors.

10.000 visitors is 500/hr, if you get traffic peaks you'd get in trouble. I'd opt for 1 database, and explicitly closing connections once the script is done.

Using a connection (resource) id, with mysql_close($id) when your script is done, closes a connection. Otherwise it remains open for a fixed amount of time and times out, then the resource is released (if you get 500 visitors within the time-out period your site goes down).

you can use a prefix if you have a database with lots of tables :
vis_ for visitor
nav_ for navigation
then you may have to rewrite queries, but that ain't too much work.
Thanks for everyones' comments. I'll go ahead and combine them together and categorize them with prefix.

However, you mentioned that mysql will close after a fixed amount of time if i don't go ahead and close the connection in script.

So are you suggesting its always a good practice to close the connection?

I also read somewhere that mysql automatically closes any connection aftering the a page and its linked pages are loaded. Furthermore, it said manually closing connection in scripts may actually be a bad practice.
 
Reply With Quote
  #13  
Old 01-09-2009, 05:49 PM
juust juust is offline
Junior Member
 
Join Date: 01-05-09
Location: netherlands
Posts: 20
iTrader: 0 / 0%
You're right about the connection being closed when the script ends (php.net : mysql_close).

I assume the script ends when the user closes the browser connection (or session), I am not so sure the mysql connection is closed once the page is loaded. Out of habit if I don't use a persistent link I close every connection at the end of the page script. Maybe the others know more about that ?
 
Reply With Quote
Go Back   Webmaster Forum > Web Development > Web Design Lobby

Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to open Multiple Tabs? jason98 SEO Forum 26 09-12-2008 07:02 AM
insert multiple select into mysql janim Coding Forum 1 08-05-2007 04:08 PM
[mySQL] - UPDATE - possible timeout - connection problem - HELP pythox Coding Forum 7 11-13-2006 12:17 PM
Practice SN3 Web Design Lobby 6 11-04-2003 01:36 PM


V7N Network
Get exposure! V7N I Love Photography V7N SEO Blog V7N Directory


All times are GMT -7. The time now is 08:37 AM.
Powered by vBulletin
Copyright 2000-2014 Jelsoft Enterprises Limited.
Copyright © 2003 - 2018 VIX-WomensForum LLC