Database Optimization Questions

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Skiddles2010
Forum Newbie
Posts: 19
Joined: Tue Jul 06, 2010 11:05 pm

Database Optimization Questions

Post 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?
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Database Optimization Questions

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Database Optimization Questions

Post 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).
Skiddles2010
Forum Newbie
Posts: 19
Joined: Tue Jul 06, 2010 11:05 pm

Re: Database Optimization Questions

Post 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.
Post Reply