Hello geeks,
I've been having this in mind but dnt know how to go about it.
if someone is to setup database for a site with large number of users or visitors, how can he structure the database connection cuz, i've been learning php & mysql for morethan a year now and i got to know that the more the connections, the slower the site gets to load. So please
Cc dhtml18,
DanielTheGeek,
javanian
and other profs or veteran programmers should please help, i know many newbiz have little or no knowledge.

@seun, you and your team have done well for this site, its fast with less data consumtion, please if you dont mind can you share some of your DBM techniques so that we might learn more?.. Thanks

I've never gotten to do heavy sites, but I think structuring your site well helps.
Example, If have a million users querying your dB from the whole stack will take time. But if have initially structured or re-structured your dB to have sub-dbs by first letters of usernames, you could just place an if statement first on a query so that you narrow down your search pool.
So good dB planning helps.
My two cents

I wish my co-developer (Backend Tools and Services) Shedrach was here, he would have been in the best position to answer this question..

I will provide you with some links instead of "talking too much"..

https://confluence.atlassian.com/jira/tuning-database-connections-288657160.html
http://kb.tableau.com/articles/knowledgebase/database-query-performance
...

Well, let me just talk small sha,

Using a single connection for multiple statements reduces the overhead of establishing multiple connections while giving way for multiple statements to access the database, using this method of connection management means the app probably has to wait to run a statement until the single connection is available making it your app load very slow depending on the amount of users you have.. this is okay for a website with a small amount of users.

The solution you are searching for is how to design connections that support thousands of concurrent database users and promoting high performance.

For example, let's say we have;
1. An environment including a middle tier that must support 20 to 100 concurrent database users, and performance is key. CPU and memory are plentiful on both the middle tier and database server.
2. an Oracle, Microsoft SQL Server, Sybase, or DB2 database
3. an application uses ODBC, JDBC, or ADO.NET APIs
4. 25 licenses for connections to the database server.

There are three possible solutions that my brain can produce;

Solution 1: Use a single connection with 5 to 25 statements.
Solution 2: Use a connection pool with a maximum of 5 connections, each with 5 statements.
Solution 3: Use a connection pool with a maximum of 20 connections, each with a single statement.

The most important information in this example is the ample CPU and memory on both the middle tier and database server and the ample number of licenses to the database server. The other information is really irrelevant to the design of the database connections.

Since we need high performance, Solution 3 is the best solution because it supports the processing of one statement per connection (all the statements can access the database at the same time)

Solution 1 and 2 won't solve our problem (Performance) because it supports a single connection for multiple statements, think of 400,000 people passing through a little door... these methods will slower results for users (Bad UX)

There are other important things to look into, like Transaction Management..e.t.c.

I Really Like To Know More About This Pls

DanielTheGeek:
I wish my co-developer (Backend Tools and Services) Shedrach was here, he would have been in the best position to answer this question..

I will provide you with some links instead of "talking too much"..

https://confluence.atlassian.com/jira/tuning-database-connections-288657160.html
http://kb.tableau.com/articles/knowledgebase/database-query-performance
...

Well, let me just talk small sha,

Using a single connection for multiple statements reduces the overhead of establishing multiple connections while giving way for multiple statements to access the database, using this method of connection management means the app probably has to wait to run a statement until the single connection is available making it your app load very slow depending on the amount of users you have.. this is okay for a website with a small amount of users.

The solution you are searching for is how to design connections that support thousands of concurrent database users and promoting high perormance.

For example, let's say we have;
1. An environment including a middle tier that must support 20 to 100 concurrent database users, and performance is key. CPU and memory are plentiful on both the middle tier and database server.
2. an Oracle, Microsoft SQL Server, Sybase, or DB2 database
3. an application uses ODBC, JDBC, or ADO.NET APIs
4. 25 licenses for connections to the database server.

There are three possible solutions that my brain can produce;

Solution 1: Use a single connection with 5 to 25 statements.
Solution 2: Use a connection pool with a maximum of 5 connections, each with 5 statements.
Solution 3: Use a connection pool with a maximum of 20 connections, each with a single statement.

The most important information in this example is the ample CPU and memory on both the middle tier and database server and the ample number of licenses to the database server. The other information is really irrelevant to the design of the database connections.

Since we need high performance, Solution 3 is the best solution because it supports the processing of one statement per connection (all the statements can access the database at the same time)

Solution 1 and 2 won't solve our problem (Performance) because it supports a single connection for multiple statements, think of 400,000 people passing through a little door... these methods will slower results for users (Bad UX)

There are other important things to look into, like Transaction Management..e.t.c.

My 'twins brother' has spoken!

superneutral:
I Really Like To Know More About This Pls

What aspect specifically, Database Management is wide.

Also note; I'm more of a front-end guy, so my answers may not be really in depth.

DanielTheGeek:


What aspect specifically, Database Management is wide.

Also note; I'm more of a front-end guy, so my answers may not be really in depth.

thanks sir,
am grateful for your response

DanielTheGeek:


What aspect specifically, Database Management is wide.

Also note; I'm more of a front-end guy, so my answers may not be really in depth.

That's not a prob.. I like to design a forum just like nairaland but still want to include something's to make it different.

Microservices!

Urine:
Microservices!

Meaning?

Learn database table normalization. Learn how to write efficient SQL queries. Learn how to add appropriate indexes. I work with databases that have a hundred billion rows and that's how I do it. Learn the database you use, and how to performance tune and how appropriate parameters can effect the performance. Just these will get you far. The day you have a site with a hundred million users, hit me up and we can talk about sharding and SQL alternatives.

CALEB65:
Hello geeks,
I've been having this in mind but dnt know how to go about it.
if someone is to setup database for a site with large number of users or visitors, how can he structure the database connection cuz, i've been learning php & mysql for morethan a year now and i got to know that the more the connections, the slower the site gets to load. So please
Cc dhtml18,
DanielTheGeek,
javanian
and other profs or veteran programmers should please help, i know many newbiz have little or no knowledge.

Bros the truth is that you don't yet acquire the experience not skills.

There is a tool i use to design my database call mysql workbench very good tool for any DBA.
First of all understand how your tables are related.
Don't get scared but every large number of data started from small.
So as the developer of the project you understand better where you code is not very efficient.
Always ensure you can scale your database.

guru01:

Bros the truth is that you don't yet acquire the experience not skills.

There is a tool i use to design my database call mysql workbench very good tool for any DBA.
First of all understand how your tables are related.
Don't get scared but every large number of data started from small.
So as the developer of the project you understand better where you code is not very efficient.
Always ensure you can scale your database.

i'm still developing offline, i just want to be mind of all that it takes to be a better developer so as nt to have holes i'll be refered to later in the future. Thanks, ur response is very helpful

DonSegmond:
Learn database table normalization. Learn how to write efficient SQL queries. Learn how to add appropriate indexes. I work with databases that have a hundred billion rows and that's how I do it. Learn the database you use, and how to performance tune and how appropriate parameters can effect the performance. Just these will get you far. The day you have a site with a hundred million users, hit me up and we can talk about sharding and SQL alternatives.

thank you sir

@DanielTheGeek, how can i get in touch with your shedrach?

DanielTheGeek:
There are three possible solutions that my brain can produce;

Solution 1: Use a single connection with 5 to 25 statements.
Solution 2: Use a connection pool with a maximum of 5 connections, each with 5 statements.
Solution 3: Use a connection pool with a maximum of 20 connections, each with a single statement.

The most important information in this example is the ample CPU and memory on both the middle tier and database server and the ample number of licenses to the database server. The other information is really irrelevant to the design of the database connections.

Since we need high performance, Solution 3 is the best solution because it supports the processing of one statement per connection (all the statements can access the database at the same time)

Solution 1 and 2 won't solve our problem (Performance) because it supports a single connection for multiple statements, think of 400,000 people passing through a little door... these methods will slower results for users (Bad UX)

There are other important things to look into, like Transaction Management..e.t.c.

Solution 2 is good where your processes perform a set of dependent queries, i.e data returned from the first feeds into the key data of the second etc.

In this case, having freer access to the second statement does not improve performance, so you may as well bundle it under the same connection with the first.

For simpler processes requiring single queries, you could have a separate pool for Solution 3.

So a combination of Solution 2 and Solution 3 can be useful.

superneutral:
That's not a prob.. I like to design a forum just like nairaland but still want to include something's to make it different.

Just try to be unique and make sure you use a concrete design at least that's what Nairaland lacks.

CALEB65:
@DanielTheGeek, how can i get in touch with your shedrach?

I'd chat him up, ask him to contribute... If you're okay with dropping your contact do it or send your question to shedrach@rangostudios.com and wait for his reply.

DanielTheGeek:


I'd chat him up, ask him to contribute... If you're okay with dropping your contact do it or send your question to shedrach@rangostudios.com and wait for his reply.

thank u sir. I'll mail him