Database Optimization Questions
Posted: Mon Jul 12, 2010 11:48 pm
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:
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?
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:
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.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.
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?