Page 1 of 1
PHP/mySQL performance.
Posted: Fri Jan 07, 2011 6:20 pm
by spedula
I'm coding an app, in short it's a template engine/CMS, where every user will have over 200 rows in one table and several tables with similar amounts. I've come up with a solution that will create a new DB based on how many users are assigned to it. That part is functioning 100% fine.
Just for clarification purposes:
My users will be called clients
Clients users will be called customers
Each client can potentially be serving a multitude of customers. Clients site will be sending requests for mysql data for each and every customer. This means that the script has to look through 1000's of rows of data just for that 1 customer to get the info that they requested, and there can be a very large number of simultaneous customer. I've already put in place a way to reduce the amount of rows per table per client by doing the DB split.
My question is, how many rows is too many? I know it's a little vague but generally speaking. When will I start seeing significant performance issues, like 1,000's or 10,000's rows? More?
Please let me know if you need more info.
Re: PHP/mySQL performance.
Posted: Mon Jan 10, 2011 10:48 am
by social_experiment
spedula wrote:When will I start seeing significant performance issues, like 1,000's or 10,000's
You are more likely to see performance issues when you start reaching the 10000 row mark. Are you refering to performance issues on the side of the CMS or to server performance?
Re: PHP/mySQL performance.
Posted: Mon Jan 10, 2011 2:25 pm
by spedula
Well the server performance will affect the CMS performance, will it not? So I don't really see what your questions was getting at. Please explain if I'm not understanding correctly.
Re: PHP/mySQL performance.
Posted: Mon Jan 10, 2011 2:47 pm
by mikosiko
In general, any database (MYSQL Included) can scale well to millions of records assuming that it is well designed, indexed and queried properly.
Only after you check completely your indexing strategy, your query design (and performance using explain plans) and being sure that your performance issues are coming from the DB, only then worth to start looking into techniques such as sharding/partitioning/caching and in some extremes or very particular cases look into alternative database solutions. With a few 1000's rows performance is no a big deal at least something is really wrong in the design (Db, Indexes or querys)
Re: PHP/mySQL performance.
Posted: Mon Jan 10, 2011 3:07 pm
by spedula
Right now, every page load has 6 query's to different tables and then loop that has a variable amount of queries, as such:
Code: Select all
// gets all site style values and script style values
$r = count($keyid);
$sc = count($script);
$values[] = '';
$scriptValue[] = '';
for ($i = 0; $i < $r; $i++){
$query = mysql_query("SELECT elementValue FROM sitesettings WHERE storeid='".$storeid."' AND keyId='".$keyid[$i]."' AND pageid='".$pageid."'");
$rowdata = mysql_fetch_array($query);
$values[$i] = $rowdata[elementValue];
for ($s = 0; $s < $sc; $s++){
if ($script[$s] == $keyid[$i]){
$scriptValue[$s] = $rowdata[elementValue];
}
}
}
Which can be up to 25 or so loops.
How can I change that to be just one query, and then base my loop around the returned resource of that query?
Re: PHP/mySQL performance.
Posted: Mon Jan 10, 2011 3:28 pm
by social_experiment
spedula wrote:So I don't really see what your questions was getting at.
If you designed the CMS badly the performance problems will be here, if you didn't then you only have to worry about server performance (regarding your row total). mikosiko covered it pretty well in his reply.
Re: PHP/mySQL performance.
Posted: Tue Jan 11, 2011 6:38 am
by kalpesh.mahida
Code: Select all
$query = mysql_query("SELECT elementValue FROM sitesettings WHERE storeid='".$storeid."' AND keyId='".$keyid[$i]."' AND pageid='".$pageid."'");
$keyid[$i] seems to vary, you make use of in like keyId in (every keys from $keyid array)
Re: PHP/mySQL performance.
Posted: Tue Jan 11, 2011 6:58 am
by josh
Mysql can handle billions of rows. Performance issues will happen due to sub-optimal coding. For example, issuing a bunch of small queries instead of one large one, or vice versa. Or not indexing a table, or using joins instead of sub-queries. Read up on mysql profiling rather than worry about 200 vs 2,000 rows. Row count is not the sole influence of performance.. Your coding skills, and your hardware are.
Re: PHP/mySQL performance.
Posted: Wed Jan 12, 2011 8:43 pm
by spedula
$keyid[$i] seems to vary, you make use of in like keyId in (every keys from $keyid array)
I have no idea what you're trying to convey. I'm new to PHP (~4 months) and even newer to SQL.
Can you please explain that in more detail?
Mysql can handle billions of rows. Performance issues will happen due to sub-optimal coding. For example, issuing a bunch of small queries instead of one large one, or vice versa. Or not indexing a table, or using joins instead of sub-queries. Read up on mysql profiling rather than worry about 200 vs 2,000 rows. Row count is not the sole influence of performance.. Your coding skills, and your hardware are.
Thanks for the tip. I'll look into profiling when I get a chance.