[RESOLVED] Large Database expansion

swissmissflash

Hi,

I'm hoping some of you could give me a general idea on how to best expand with larger databases. I have a decent size database running on it's own dedicated server (over 10 million entries) and the performance seems to be slowing. As it stands now, when I run a search query it usually slows up the site tremendisouly. Usual MySQL error I get is: Too many connections. This is a feature I have been holding off adding to my site because of that. I know code optimization is a huge factor but it almost seems it's too large to give the power to search to my users. I was wondering if I should copy the database on a new server just for search? maybe updating it weekly or something with the new entries?

I guess i'm just looking for some advice on how to manage all this. Any input is greatly appriciated.

🙂

NogDog

Query optimization is a non-trivial matter. Some links to start with:

column indexes
explain select
analyze table

MarkR

"Too many connections" indicates a problem in the configuration of your application / database server.

The maximum number of connections that your app server (e.g. Apache) ever makes to the database should be less than its configured maximum.

If it's more, you have serious problems.

Ensure that:

You are never, ever, making more connections than necessary in the PHP code - one should be sufficient for almost every applciation.
The maximum number of concurrent threads / processes that PHP can run in (e.g. determined by Apache MaxClients) multiplied by the number of connections per page, is less than the database configured maximum.

However, I suspect that this is simply a side effect of the fact that you're doing some very slow queries.

On your development server, you may wish to synthesise some data of the same size (or bigger) than your production server, then profile the queries (e.g. in MySQL, enable query time logging - DO NOT DO THIS IN PRODUCTION!).

Once you've determined which are slow queries, you may wish to do some or all of the following:
- Rewrite the application so it doesn't do them any more
- Cache the results of the slow queries somewhere faster
- Add indexes to increase the speed of the queries
- Restructure the database so you can replace them with faster queries

etc

Mark

Sxooter

If you're searching 10M rows you should be using full text searching, or an indexable text search (something like: where field like 'abc%' should be index searchable, while where field like '%abc%' generally is not).

swissmissflash

Thanks for the very helpful replies! I've been reading before responded with more questions to bother you guys with. =P

Maybe you guys can offer some suggestions if I give you an example of what is running so sloow.

The database I am trying to search has 7,716,099 rows which are 162 B and a row length of 111. (according to phpmyadmin). The whole thing is 1.2gb.

The table is a records of uploads my users have added it has 4 rows.

id
userid
url
title

id is primary, userid and title are indexes. I chose to make title an index because I want to search that for result. It seemed to speed things up a bit... however when I do a query like the following it takes over 130 secs. 🙁

SELECT `url`, `title`
FROM `records`
WHERE `title` LIKE '%somestring'
LIMIT 0 , 20

Any suggestions for speeding this up? I'm lost....

Thank you for your time thus far. I really do appriciate every response. 🙂

MarkR

In most cases, "LIKE" matches cannot take advantage of indexes, therefore they will involve a full table scan. This will be slow on a large table.

You should consider using full-text indexing instead - see the MySQL documentation for more information. Be sure to test any proposed solution on your dev server on a synthetic data set of the same size (or larger) than your production data.

Mark

swissmissflash

Thanks for the quick replay Mark!

There we go!! Same query is 0.0063 sec! 😃

Thank all of you so much! 😃