View Full Version : MySQL Limits
Ok, I'm totally new to the wonderful world of MySQL so I was wondering, is there an actual limit of how many things you can run off of one database? I currently have about 5 on one. Do I have to worry about it getting overloading and losing everything?
Just wondering~
:)
pete3005
09-23-2002, 09:32 AM
I wasn't entirely clear what you mean't, but I assume you mean how many tables and MySQL connections you can have.
The limit is usually 45 simultaneous connections to MySQL at anyone time, when you use mysql_connect, that will allow alot of visitors per page as the connection is closed when the script has finished.
Its hard to overload MySQL by running a normal data driven site, I believe the only intense processes that will hammer MySQL are large database inserts and backups, as well as deletion and insertion of a large number of rows.
If a server has lets say 30 people running a cron job to backup a database at midnight then you will usually see server load rise by 2 or 3.
Steven and Co will no doubt be able to give you a more accurate answer. As for losing your data, backup every day, run a cron job or manually do a dump through PHPMyAdmin. As of yet I have never had any problems with MySQL on any host, even poor hosts.
I also made a function where my site automatically emails me if there is a MySQL error, it redirects uses to a friendly error page and emails me the MySQL error, page that generated the error and the error number. That way you know if MySQL or your code is playing up. Below is the function:
function serror(){
$errpage = $_SERVER['PHP_SELF']."?".$_SERVER['QUERY_STRING'];
$dberr = mysql_error();
$mysqlerrno = mysql_errno();
$to = "Your name <you@yourdomain.com>" . ", " ; $to .= "Database Error <sales@yourdomain.co.uk>";
$headers .= "From: Your Site <sales@yourdomain.co.uk>\n";
$headers .= "X-Sender: <sales@yourdomain.co.uk>\n";
$headers .= "X-Mailer: PHP\n"; // mailer
$headers .= "X-Priority: 1\n"; // Urgent message!
$headers .= "Return-Path: <sales@yourdomain.co.uk>\n"; $headers .= "Content-Type: text/html; charset=iso-8859-1\n";
$subject = "Database Error on Your Domain";
$message .='<html>
<head>
<title>Your Domain.co.uk</title>
<style type="text/css">
<!--
body,p { font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; color: #000000 }
-->
</style>
</head>
<body bgcolor="#FFFFFF" text="#000000">
';
$thetime = time()+28800;
$message .= "An error ocurred on silvertime at ".date("F j, Y, g:i a",$thetime)."<br /><br />";
$message .= "Page where error occurred: ". $errpage."\n\n";
$message .= "<br /><br />MySQL Error: ".$dberr."\n\n";
$message .= "<br /><br />MySQL Error Number: ".$mysqlerrno."\n\n";
mail($to, $subject, $message, $headers);
header("Location:error.html");
}
include that and then call the function on your query like this:
$result = mysql_query($sql) or die(serror());
That way you know of any problems and your visitors are not left with a page full of MySQL error warnings without you knowing about it.
HTH
Pete
Steven
09-23-2002, 12:10 PM
Originally posted by Sara
Ok, I'm totally new to the wonderful world of MySQL so I was wondering, is there an actual limit of how many things you can run off of one database? I currently have about 5 on one. Do I have to worry about it getting overloading and losing everything?
Just wondering~
:)
Hi Sara,
not sure what you mean by limit on things, can you clarify? The limits that we have in place as Pete has stated is that we limit the amount of mysql connections that your site has to our mysql server. Also when we say MySQL Connections=30 that means that you are allowed up to 30 concurrent connections hitting the mysql server. That is a good amount of connections to the mysql server for the vast majority of sites, so in your case I wouldn't worry about it too much. Also the most important thing that you can do is backup a mysql database. While we keep backups of our clients site(s) it is always a great idea for you to do so as well.
As Pete stated, you can do this via a cronjob. The syntax to do so is the following:
1) do a dump of your database
mysqldump -uusername -ppassword databasename > file.sql
So if my
database name is forums
username is steven
password is test
then Here is what to do.
mysqldump -usteven -ptest steven_forums > forums.sql
Let me know how it goes.
Hope this helps.
-Steven
pete3005
09-23-2002, 12:16 PM
Steven,
Can you just clarify something for me. If 100 people were at my site at anyone time, the probability of 30 MySQL connections at anyone time would be pretty low right? Am I correct in assuming a connection is open and closed in less than 1 second (unless its a huge page)?... when using mysql_connect.
Thanks
Pete :)
Steven
09-23-2002, 12:25 PM
Originally posted by pete3005
Steven,
Can you just clarify something for me. If 100 people were at my site at anyone time, the probability of 30 MySQL connections at anyone time would be pretty low right? Am I correct in assuming a connection is open and closed in less than 1 second (unless its a huge page)?... when using mysql_connect.
Thanks
Pete :)
Pete,
Yes the probability would be low of that happening. We have clients who have forums with 50 people online, but they aren't all connecting to the mysql server at the same time. Depending on how the script is written a connection is usually closed within a second or two. It is also advisable that our clients turn off persistant connections to the database in their various scripts unless there is a need for the connections to stay open.
-Steven
harmonic
09-23-2002, 02:42 PM
Persistant connections are closed at the scripts end. So it shouldn't be much of a problem, especially smaller scripts.
pete3005
09-23-2002, 03:47 PM
Harmonic,
That is not correct, my PHP Manual states:
"Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect()). "......
"Warning:
Using persistent connections can require a bit of tuning of your Apache and MySQL configurations to ensure that you do not exceed the number of connections allowed by MySQL. "
I believe the only advantage to using pconnect is speed increase due to no need to open a connection, however that is minimal difference to the end user. mysql_pconnect really should not be used on a shared server enviroment like we have at IH or any other host.
Pete
harmonic
09-23-2002, 05:55 PM
Yes, thats the exception...
I was generally refering to mysql_connect(), which opens up a 'persistent' resource. These resource variables, (such as fopen() wrappers) are all detected at the end of the script and cut off. Most people don't know about pconnect(), and it's rarely used.
pete3005
09-23-2002, 11:24 PM
Since DW MX released its native server model, by default that uses mysql_pconnect and I know at least one person here who uses DW for PHP generation. You will find that using mysql_pconnect is not rare as many people will not realise it is not the best option on a shared server enviroment.
The word 'persistent' should only be referred to when discussing mysql_pconnect and not mysql_connect.
Pete :)
Getting back to my original question, I was wondering how many things I can use in one database. Program wise. Is there a limit? I am running b2 on Blog Land on 10 different blogs, using one database. Does that make the question clearer?
:) Thanks,
Sara
harmonic
10-12-2002, 11:41 AM
There isn't a limit on number of tables in a database, and if there is, it is really high and you would not need to worry about it.
People tend to want to use multiple DB's as to reduce clutter, and allow for multiple users - cause you may not want a user accessing a certain db.
pete3005
10-12-2002, 11:47 AM
As clear as mud...lol... just j/king.
MySQL is a very robust and stable database, as quoted from the MySQL manual:
Scalability and Limits
Handles large databases. We are using MySQL Server with some databases that contain 50,000,000 records and we know of users that uses MySQL Server with 60,000 tables and about 5,000,000,000 rows.
Up to 32 indexes per table are allowed. Each index may consist of 1 to 16 columns or parts of columns. The maximum index width is 500 bytes (this may be changed when compiling MySQL Server). An index may use a prefix of a CHAR or VARCHAR field.
So I think you can start worrying when you reach 60,000 tables, if you had one table for each blog then that is 60,000 Blogs...
If you are going to get into PHP and MySQL, I would suggest you download the free manuals for each. Although it may not answer all your questions it should help you along, I find them invaluable.
Pete :D
vBulletin® v3.8.4, Copyright ©2000-2012, Jelsoft Enterprises Ltd.