Mysql Limitations

[Guest GuyWithMySqlQuestion]

I've run in to this problem before and was looking for a new host; my question is, what would Total Choice consider excessive MySql usage? I have a database of rougly 500 MB worth of text and it is continuing to grow, and the front-end website of course searches through all this text.

 

Thanks!

[TCH-Andy]

Hi,

 

It would depend on the number of hits you are getting, but 500MB is getting towards needing a dedicated server if frequently searched.

[TCH-RobertM]

Hello,

 

We monitor all our servers and have thresholds set to alert us if loads are high.

 

We review what is causing the issue, if it happens to be caused because a sites MYSQL requests are cauing the loads then we have to take appropriate actions to prevent one site from impacting the others on a shared server.

 

Now you state that you have run into this problem in the past. My question to you would be did you optimize your SQL statements ?

 

Have you looked at breaking your requests up into smaller faster queries ?

[Guest Guest]

Hello,

 

We monitor all our servers and have thresholds set to alert us if loads are high.

 

We review what is causing the issue, if it happens to be caused because a sites MYSQL requests are cauing the loads then we have to take appropriate actions to prevent one site from impacting the others on a shared server.

 

Now you state that you have run into this problem in the past. My question to you would be did you optimize your SQL statements ?

 

Have you looked at breaking your requests up into smaller faster queries ?

 

Well I'm honestly not sure what I could do to make it faster; I think the problem may be that all of the text is loaded in to just one table for the search. Since I didn't want to index 300 MB worth of text on my own, I used the mysql fulltext index, which is what brings it up to 500 MB.

 

Something I had considered was splitting the table into 2, since the older text gets searched less frequently than the more recent text.

 

The host I had problems with previously was actually a free hosting service, so it had lots and lots of users.

[Guest Guest]

Well I'm honestly not sure what I could do to make it faster; I think the problem may be that all of the text is loaded in to just one table for the search. Since I didn't want to index 300 MB worth of text on my own, I used the mysql fulltext index, which is what brings it up to 500 MB.

 

Something I had considered was splitting the table into 2, since the older text gets searched less frequently than the more recent text.

 

The host I had problems with previously was actually a free hosting service, so it had lots and lots of users.

 

I would have thought you have gone the correct way about this creating a full index, that would make queries run much faster than your original post would suggest, which makes it should like you need to do a linear search of 500MB of text. Why not see how it goes, or if you really want, set this situation up on your own pc, and keep hitting it, see how it goes.

 

You just have to remember, in a shared hosting environment, you should keep an eye to avoid disruption to your fellow users.

[Guest GuyWithMySqlQuestion]

I would have thought you have gone the correct way about this creating a full index, that would make queries run much faster than your original post would suggest, which makes it should like you need to do a linear search of 500MB of text. Why not see how it goes, or if you really want, set this situation up on your own pc, and keep hitting it, see how it goes.

 

You just have to remember, in a shared hosting environment, you should keep an eye to avoid disruption to your fellow users.

 

I'm not quite sure what you mean; I assume you are referring to a full text index, which is indeed what I used (as I stated previously). I already have everything set up on my personal computer, and the performance is fine. However, my computer is not a shared hosting environment, hence the reason for me posting my question regarding the limitations Total Choice has for their plans.

 

Anyways, as far as query optimization goes each page opens a connection only if a connection is needed, and that connection is used for the life of the request, at which point it is closed. I also use query caching to cache the data returned by the query to a temporary file, which I set to expire after about an hour.

 

For a search string of "potatoes"

 

First, a count is retrieved for paging:

SELECT COUNT(*) FROM `posts` WHERE `forum_id`='20' AND `is_hidden` != '1' AND MATCH(`post_title`, `post_contents`) AGAINST ('potatoes') ORDER BY `forum_id`, `category_id`, `topic_id`, `post_id`;

 

Then, the posts are retrieved:

SELECT *, MATCH(`post_title`, `post_contents`) AGAINST ('potatoes') AS `score` FROM `posts` WHERE `forum_id`='20' AND `is_hidden` != '1' AND MATCH(`post_title`, `post_contents`) AGAINST ('potatoes') ORDER BY `score` DESC LIMIT 0, 25;

 

Page 2:

 

First, a count is retrieved for paging, and since it was cached, this particular query is not executed again.

 

Then, the posts are retrieved:

SELECT *, MATCH(`post_title`, `post_contents`) AGAINST ('potatoes') AS `score` FROM `posts` WHERE `forum_id`='20' AND `is_hidden` != '1' AND MATCH(`post_title`, `post_contents`) AGAINST ('potatoes') ORDER BY `score` DESC LIMIT 25, 25;

 

Also, here are some statistics:

 

Indexes:

 

Primary:

Cardinality: 342758

Index on: (forum_id, category_id, topic_id, post_id)

 

Fulltext:

Index on: (post_title, post_contents)

 

Space usage:

 

Type Usage

------------------

Data 250,847 KB

Index 121,680 KB

Total 372,527 KB

 

I am very interested in using Total Choice, and would also welcome any further comments about possibly splitting tables, creating my own index of the text, or anything else someone can think of.

 

Unfortunately I am just maintaining this site for a hobby so I would not be able to afford a dedicated hosting solution.

[Guest Guest]

Splitting the data into more tables would only increase your queries, and a binary chop kind of search would suffer from being split.

 

Without knowing the internal workings of MySQL, the following suggestions would not harm your query:

 

>SELECT COUNT(*)
FROM `posts` 
WHERE `forum_id`='20' 
AND `is_hidden` != '1' 
AND MATCH(`post_title`, `post_contents`) AGAINST ('potatoes') 
  ORDER BY `forum_id`, `category_id`, `topic_id`, `post_id`;

 

You are only bringing back a count, why are you ordering the results? It makes no sense.

Is is_hidden only 0 and 1? If so, is_hidden = 0 is perhaps more efficient than != 1

 

And if you are going to recreate your database from a old host, and your recration consists of lines and lines of inserts, create your index after the inserts!!!

 

Give TCH a try maybe, pay 1 month, see how it goes, keep a regular backup so if they suspend you, you have all your data, be prepared to change host? You're not really going to know in advance if its a problem, and if they suddenly suspend you, you may lose presence, its going to be hard.

[iconv]

In addition to the previous proposal, use EXPLAIN to check that MySQL is actually using the fulltext index; use ANALYZE TABLE beforehand to ensure that the optimizer has up-to-date information to decide on the index to use (MySQL only ever uses one index). Assuming that the fulltext index is selective enough on your average query, make sure it is used even when the optimizer does not get it by using FORCE INDEX in the query.