Page 1 of 1

Database Optimization Questions

Posted: Mon Jul 12, 2010 11:48 pm
by Skiddles2010
I run a forum community that on average has about 40 people online per 15 minute window at any given time. We usually peak around 70~80 users online in the same window. I'm not sure if that information is relevant to my concern but I thought it should be mentioned.

The problem: max number of mysql connections being reached.

I've been back and forth with my host all evening and they basically said that the error is either being caused by server problems on their side and/or the fact that my database is not optimized. This hasn't been an issue until recently when I started programming a new database and admittedly my queries show little concern for database optimization. None of the tables contain more than 15 rows.. I didn't think this would be an issue. Here's what the tech had to say:
Ryan: [10:19:19 PM] This is being caused by your database meeting our 15 simultaneous connection limit.
[10:19:40 PM] Here is the information our scripting department gave:
[10:19:42 PM] There are lots of ways to do that. I usually recommend a db repair, followed by normalizing their databases which means to remove duplicate content. possibly archive data, enable caching in their

site and optimize queries in general. There are bad queries and good queries. SELECT * From table name is bad
[10:17:08 PM] SELECT coulmn1,column2 is good
[10:17:12 PM] more speciifc
[10:17:22 PM] if their mysql_slow_queries show something like
[10:17:41 PM] 2000 rows examined but 4 results returned, probably bad.
[10:17:50 PM] meaning their tables are not indexed well
[10:18:09 PM] because it had to search through 2000 rows before it found only 4 results.
This was the 4th time around talking to them. The 3rd time they said I had a 20 simultaneous connection limit. Whatever that means, I don't know that I buy it. My sites been running for over a year on whatever settings it used to be on and we've been perfectly fine.

I do have one suspected issue, and I wonder if any of you could shed some light on the problem. In MySQL I have a number of columns to hold my variables. Most are set to VARCHAR (255). I have a number of TEXT (65500). In other words, I've been setting the variable size to the nearly the max allowable. What effect does this have on the server? I just figured that if I needed the space later it would be there and that was better than if I came up short.

What can I do to optimize my database, if this is what is causing the issue?

Re: Database Optimization Questions

Posted: Tue Jul 13, 2010 12:16 pm
by Jade
Think of it this way... You have a HUGE book containing blank sheets of paper. Lets say there are over a million blank sheets of paper. The whole piece of paper is the MAX allowable data for each user. If you assign each user an entire page and the user only posts two letters on that page then the other 99% of the page is just wasted space. Now think about searching those million sheets of blank paper for the 2 letters that one person wrote down... it's not an easy task. All of that unused -- but reserved -- space means that your database will be swapping out it's cache more frequently and taking longer to search through things. The more you can limit the size of your data the better optimization your code will have.

My suggestion would be to create a new database and copy all of your tables over with more appropriate sizes. Query the data you already have... what's the longest title someone has posted? What's the longest message someone has posted on the forum. Those should become your new MAX sizes. Then you can import all of your old data into your new database and you won't have to worry about anything truncating and you've helped increase your performance.

Re: Database Optimization Questions

Posted: Tue Jul 13, 2010 4:14 pm
by Eran
To get concrete help, you need to post your database schema, frequently run queries and so forth. Please profile your application to see which queries are troublesome, run EXPLAIN on those queries and post the results here along with the schema (including indexes).

Re: Database Optimization Questions

Posted: Wed Jul 14, 2010 7:32 am
by Skiddles2010
Jade wrote:Think of it this way... You have a HUGE book containing blank sheets of paper. Lets say there are over a million blank sheets of paper. The whole piece of paper is the MAX allowable data for each user. If you assign each user an entire page and the user only posts two letters on that page then the other 99% of the page is just wasted space. Now think about searching those million sheets of blank paper for the 2 letters that one person wrote down... it's not an easy task. All of that unused -- but reserved -- space means that your database will be swapping out it's cache more frequently and taking longer to search through things. The more you can limit the size of your data the better optimization your code will have.

My suggestion would be to create a new database and copy all of your tables over with more appropriate sizes. Query the data you already have... what's the longest title someone has posted? What's the longest message someone has posted on the forum. Those should become your new MAX sizes. Then you can import all of your old data into your new database and you won't have to worry about anything truncating and you've helped increase your performance.
Thanks for the good advice. I will definitely go through today and figure out exactly how much space I need for each column.