• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

MySQL retrieval speed for many connection attempts at the same time

Hello,
I am not so experienced with databases and wonder about their speed when 100-1.000 people try to connect and pull info from the database at the same time?
What happens? Does it explode?

There are 500 unique ID and each row has 10 variables that need to be pulled.
If 1.000 people try to connect to the database at the same time and try to pull those 10variables. Will the fetching be really slow and when does it crash?

I have not a clue about the speed of retrieving from a db. I have read about storage of million of records that goes really fast for a low amount of users. Then I have also read something about max 500 connections to a db at a given time.

I guess the primary question is how many users can connect to such a database at a given time with the db being able to send back the info in less than a second?
Then how long time does it take to search 500 random unique ID?

Please explain this speedissue to me.
Thanks!

0
Jennie2004
Asked:
Jennie2004
  • 3
  • 2
  • 2
3 Solutions
 
AlanJDMCommented:
Unfortunately, this answer is going to be largely subjective due to the fact that a lot of what effects the speed will be hardware and or network related.

I can tell you that I work for a company with 22,000 users of a system written using many tables with 100,000+ records and we rarely see speed issues. However, we have a very large network with a gigabit backbone and top of the line sun/solaris servers.

I doubt you will see any issue at all with the environment you describe even if on average hardware/network.


Alan
0
 
AlanJDMCommented:
This may help as well if you are refering to MySQL...

http://dev.mysql.com/doc/mysql/en/too-many-connections.html


Alan
0
 
_Marcel_Commented:
AlanJDM is right. It depends on many 'external' factors like hardware. But also software may have an impact. It could be that your webserver doesn't run 100-1.000 requests at the same time, and queues the requests. This would automatically mean that there are not that many connections to the database. A middleware layer could also influence the connections.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Jennie2004Author Commented:
I asked my webhost and was told that 500 connections is maximum at any time.
How does that work?
A connection takes only a second and then it is closed?
So 500 doesn't mean total users but rather this second 500new the next second 500more etc ?
0
 
_Marcel_Commented:
This depends on your connection strategy. When you use a 'normal' connection, the connection is opened when you connect to mysql, and closed when you close your connection to mysql, or when the script finsihes. Within a script yaou can even open and close the connection to the database more than  once (although that would often only create more overhead, but may spread the load more). When using a persistent connection, the connection is opened when you request the (first) persistent connect, and after this it will stay open. These connections will be 'recycled' though. So, when you are finished using it, the next script requesting a persistent connection can use it again, if not, it will open another one. See also http://www.php.net/manual/en/features.persistent-connections.php
So the 500 connections that your webhost is refering to are 500 connections at the same time.
0
 
AlanJDMCommented:
Reffering to my example again, we have 22,000 users and only 1000 connections defined and have never maxed out. The odds of 1000 users all executing a script that runs in 5 seconds or less within a 5 second window are astronomical. Now, if you have any heavily used, intensive scripts that run for a long time then the odds of a prblem increase. However, 1000 users sharing 500 connections will never be an issue. Unless, of course you use persistant connections, which you would never do.


Alan
0
 
Jennie2004Author Commented:
Ah, that makes me feel a lot better :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now