How to grow from 1 million to 7 million records, PHP/MySQL site

big_loser

I'm running a big site with PHP/MySQL on a Linux server with 4gb RAM and (4) 250mb HDs and (4) 3 ghz Intel CPUs. PHP is 4.4.4 and MySQL is 4.1.22 (a cPanel setup). Linux is 2.4.21-47.0.1.ELsmp.

I have a database with just under 1 million records, 1.5gb. The indexes add another 1.5gb.

The existing database is doing fairly well, but runs out of memory and swap about once per day so the server has to be rebooted. Otherwise the searches are relatively fast.

There are NO writes into the database, other than to add records by the admins about once a month. It is basically a read-only site, something like a search engine. Users search the database by keyword phrases, or click ready-made searches or find us online via search engines who have already given them their search paths as URLs to click on.

The full database needs to be searched and results ranked by relevancy, then paged out into pages for ease of use. This has been working well so far, for about a year. Here is the code we use to access the records:

SELECT *,
MATCH(Title, Contents)
AGAINST ('$trimmedkeywords')
AS score
FROM reports
WHERE MATCH(Title, Contents)
AGAINST ('$trimmedkeywords' in boolean mode)
GROUP BY Link
ORDER BY score DESC";

PROBLEM:

We have another 7 million or so records we want to add to the database, which should bring the total to around 8 million. The final database will likely be about 12 gb, with indexes adding another 12 gb, for a total size of around 25 gb or so, roughly.

POSSIBLE SOLUTIONS:

We suspect that we need more RAM, no matter what we do. We are also considering other things, including:

1) Increasing RAM to 8, 16 or even 32 gb to help accommodate the large 25gb db.

2) changing the structure of our database to have more than one table,

3) changing the nature of the indexes

4) moving to another database instead of MySQL (has to be free and should run under cPanel/PHP/Linux)

5) Fine tuning our MySQL setup config so that it can more efficiently handle this larger db.

6) possibly running several machines in parallel to serve more traffic and more pages

Any ideas on how to do this, anyone?

Thanks!

alimadzi

A few thoughts...

1) good idea
3) with a read-only database, you should have a lot of indexes
4) have you looked at postgresql?

sheephat

6) possibly running several machines in parallel to serve more traffic and more pages

I am thrown to this one out of them all. Best get more feed back though as I would like to know if i am wrong for thinking this and to get the pros opinions.

alimadzi

sheephat wrote:
6) possibly running several machines in parallel to serve more traffic and more pages

I am thrown to this one out of them all. Best get more feed back though as I would like to know if i am wrong for thinking this and to get the pros opinions.

I think it's best to do everything possible to maximize your current hardware before spending more money. Database normalization, optimization, performance tuning, etc. should all be exhausted before you whip out the ol' corporate credit card. Just my $0.02...

sheephat

of course, i meant that would be the action i would take if i had money 🙂

big_loser

alimadzi wrote:
A few thoughts...

1) good idea
3) with a read-only database, you should have a lot of indexes
4) have you looked at postgresql?

Thanks for the feedback. We will buy some more RAM soon, even if to help with the current million pages. Money is tight, so we want to optimize code and queries, etc. Improve table and index structure, etc. as needed.

Appreciate idea from folks who are running similar setups; large dbs mainly read oriented, with php/mysql/linux. Any tuning ideas, table structure pointers, etc. MUCH appreciated.

big_loser

alimadzi wrote:
I think it's best to do everything possible to maximize your current hardware before spending more money. Database normalization, optimization, performance tuning, etc. should all be exhausted before you whip out the ol' corporate credit card. Just my $0.02...

We are thinking the same. Possibly improving the code if possible in the queries, then tweaking the table structure or and indexes, and so on.

alimadzi

So you only have one table? Could you post the table definition with a list of indexes for us to review?

big_loser

alimadzi wrote:
So you only have one table? Could you post the table definition with a list of indexes for us to review?

Thanks. The table description is:

Full Texts
Field Type Null Key Default Extra
rptId int(11) PRI NULL auto_increment
rptCategory varchar(255) MUL
rptTitle text MUL
rptContents text
rptAuthor varchar(255)
rptInst text
rptCit varchar(255)
rptLink varchar(255)

And, the indexes are:

Indexes: Documentation Keyname Type Cardinality Action Field
PRIMARY PRIMARY 965614 Edit Drop rptId

rptCategory FULLTEXT 3 Edit Drop rptCategory, rptTitle, rptContents, rptAuthor, rptInst, rptCit, rptLink

rptTitle FULLTEXT 3 Edit Drop rptTitle, rptContents

Hope that is what you need. Looks badlys spaced, but I think it's readable. I got it from phpmyadmin.

Thanks a bunch for looking into this. I am running the default setup in MySQL. I think I should consider changing to the my-huge thingy, AFTER we've finished doing some tweaking in the code itself. Just a thought. It's a dedicated server running only Apache and usual sendmail, named, etc. Gets about 10,000 page requests or queries per day from about 5,000 visitors.

PS: The actual query is below (I had edited the other one):

$query = "SELECT *,
MATCH(rptTitle, rptContents)
AGAINST ('$trimmed')
AS score
FROM reports
WHERE MATCH(rptTitle, rptContents)
AGAINST ('$trimmed' in boolean mode)
GROUP BY rptLink
ORDER BY score DESC";

Thanks again! 🙂

alimadzi

OK, so I would definitely look into tuning your MySQL installation. The default settings are definitely not going to cut it for your situation.

And looking at the schema, do you have authors that write multiple documents? If so, you should create a separate Author table for their info and replace the rptAuthor column in your original table with a foreign key to the new Author table. That should save you at least some disk space and would make it easier to do things like list your most prolific authors in order.

alimadzi

Oh, you also need a Category table for the same reason you need an Author table. The fulltext index of categories can be moved to your new Category table. That should really give you a good performance boost and make category searching must faster.

big_loser

alimadzi wrote:
OK, so I would definitely look into tuning your MySQL installation. The default settings are definitely not going to cut it for your situation.

And looking at the schema, do you have authors that write multiple documents? If so, you should create a separate Author table for their info and replace the rptAuthor column in your original table with a foreign key to the new Author table. That should save you at least some disk space and would make it easier to do things like list your most prolific authors in order.

The database is largely searched like a search engine would, primarily for 'content'... meaning that the users are looking for information on a particular topic of interest. They enter keywords about that topic (almost never authors) and then we want to search the rptTitle and rptContent columns for that content. The category column is not used at all; more about that later. The rptAuthor and rptInst columns are merely citations of the articles source, of interest primarily to the author. Also, the rptLink column is to let us link out directly to the original source of the article, elsewhere on the web.

The 'rptCategory' column could be removed to save space. We don't need it, since the articles are all non-categorized. They are so finely targeted as to topics that they don't fit into broad categories well, at least nothing that anyone would ever search for. For that reason, we have actually just filled that column with a base phrase which is the same for all articles. Silly waste of space. Don't know how to remove it now, with a million records.

So removing that useless rptCategory column might be one job to consider for the near future. :queasy:

However, I think I will look into trying out the 'my_huge.cnf' file to tune up the MySQL a bit. I'm not sure where to go on the server to look for the my.cnf file. I'm looking into that now. I also need to FIND a copy of the my_huge.cnf for version 4.1.22 of MySQL. I'm researching that. Worth a try.

QUESTIONS:

1) Do I have the indexes setup right to optimize searches of rptTitle and rptContents by keywords?

2) Also, I could probably index just the first 256 characters of rptTitle, and the first 1,000 characters of rptContents, if that would save room. But, how would I do that?

3) How can I remove just the rptCategory column, making sure NOT TO CHANGE the 'rptID' column on the resulting new table's records. Since this is a live site with hundreds of thousands of links from Google and other SE's into the db by this field, we can't change this ID number for any of the records. We would only just want to remove the category column.

Appreciate your ideas. 🙂

alimadzi

1) I'm having trouble seeing what your indexes are. The spacing (or lack of it) is getting in the way.

2) Here's an example of a partial index (you'll want to drop the one you have first)...

CREATE INDEX title_index ON rpt (rptTitle(256));

And here's the MySQL manual page on CREATE INDEX...

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

3) Here's how to remove the rptCategory column (not sure if the corresponding index will also be dropped automatically)...

ALTER TABLE rpt DROP rptCategory;

I'm only guessing that the name of your table is 'rpt'. I honestly don't know from the info you've provided. Hope this helps.

MarkR

big_loser wrote:
The existing database is doing fairly well, but runs out of memory and swap about once per day so the server has to be rebooted. Otherwise the searches are relatively fast.

That is a major problem. Nothing should do that.

What processes are exhausting the RAM? Is it just MySQL that you boot or the whole box?

Are you on 32 or 64-bit? If you're on 32-bit, you could almost certainly benefit from moving to 64-bit, particularly if you have > 2G of physical ram.

We suspect that we need more RAM, no matter what we do. We are also considering other things, including:

Adding RAM is generally good, provided you are on a 64-bit box. You can read a lot about tuning MySQL on their web site.

The critical things are the index buffer size and the sort buffer (per connection) size.

If you have too many connections the sort buffers become a problem. You need to optimise the number of concurrent connections, no matter what.

Consider (OBVIOUSLY TEST THIS ON YOUR STRESS TEST SETUP!) putting the temporary tables area on a ram filesystem. DO NOT DO THIS WITHOUT THOROUGH TESTING.

1) Increasing RAM to 8, 16 or even 32 gb to help accommodate the large 25gb db.

Will ONLY be beneficial if you're on a 64-bit box.

2) changing the structure of our database to have more than one table,

Might be helpful. It may be that your multiple CPUs are not being utilised fully. If there are queries coming in during updates there might be a lot of contention.

Consider getting some proper test hardware. You should spend money on test hardware, not production hardware. RAM is cheap.

4) moving to another database instead of MySQL (has to be free and should run under cPanel/PHP/Linux)

cPanel is a red herring and has no benefit - you can uninstall it and configure everything manually. In your case, your application is large enough that you should have the in-house skills to configure everything manually.

5) Fine tuning our MySQL setup config so that it can more efficiently handle this larger db.

Of course there are many things you can tune - see the MySQL documentation and/or get paid consultancy.

6) possibly running several machines in parallel to serve more traffic and more pages

Definitely a good idea. If you have web servers currently sharing the box with the database, they will compete with it for RAM. Consider running the web server(s) on separate boxes - but be wary of reaching the connection limit and/or using too much RAM in the per-client sort buffers.

Any ideas on how to do this, anyone?

In your case the most important thing is testing. You need to get an equivalent sized data set into your test servers and get some load testing done. You need to think about how to simulate your production levels of traffic.

Mark

MarkR

big_loser wrote:
4) moving to another database instead of MySQL (has to be free and should run under cPanel/PHP/Linux)

With any software change the biggest cost is validating that the new solution will work and perform. Changing to a different database is VERY difficult to achieve safely. You have a large code base which must be validated against the new platform. Moreover, your load testing must show SIGNIFICANT MEASURABLE IMPROVEMENT for it even to be worth considering.

You have to consider the re-training costs and cost of migrating much existing code BEFORE you can even MEASURE whether the new solution will be any better than the old one - it's a big step and shouldn't be taken lightly.

I'd consider it, but only after you've tried everything else.

6) possibly running several machines in parallel to serve more traffic and more pages

If the database is truly readonly, you could create several replicas of it on separate boxes and share the load. This would definitely scale almost linearly (provided the inserts/updates are minor).

You could use MySQL replication to replicate the updates to several copies of the database.

You could even keep two databases per machine and use them on alternate days if you don't mind your data being up to 1 day out of date - have one updated while the other is live, then swap over (separate databases for read & write = zero contention).

If you have some data which are collected, you could write the reporting / stats info into a separate database on another server to reduce load on the query box(es).

Mark

big_loser

I have (4) Intel(R) Xeon(TM) CPU 3.06GHz chips. Are they 64 bit?

I checked the phpinfo.php and System Info and don't see a reference to 32 or 64 bit in there. How can I tell?

Thanks.

big_loser

alimadzi wrote:
1) I'm having trouble seeing what your indexes are. The spacing (or lack of it) is getting in the way.

2) Here's an example of a partial index (you'll want to drop the one you have first)...
CREATE INDEX title_index ON rpt (rptTitle(256));
And here's the MySQL manual page on CREATE INDEX...

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

3) Here's how to remove the rptCategory column (not sure if the corresponding index will also be dropped automatically)...
ALTER TABLE rpt DROP rptCategory;
I'm only guessing that the name of your table is 'rpt'. I honestly don't know from the info you've provided. Hope this helps.

Thanks! Wow, that's a great place to start here. I'm off...

big_loser

Mark,

Thanks VERY much for your thoughtful reply. I'm going to do some more homework and think this through. Looks like a few days of work at least, to be sure.

-- Tom

--
"Big Loser" because I lost a lot of weight lately... 85 pounds.

MarkR

big_loser wrote:
I have (4) Intel(R) Xeon(TM) CPU 3.06GHz chips. Are they 64 bit?

Depends if you're running a 32-bit or 64-bit operating system.

Modern AMD and Intel processors use the x86_64 (as it's called in Linux) architecture. This is capable of running either 32 or 64-bit OS, and if you run a 64-bit OS, you can run 32 or 64 bit apps.

At the very least you should performance test your application with a 64-bit database on a 64-bit OS, if you're currently on 32-bit.

In most tests, a 64-bit OS is shown to be better.

In SOME tests, 32-bit apps on a 64-bit OS is best. Your mileage may vary.

You MUST get yourself some decent performance testing hardware.

Mark

big_loser

MarkR wrote:
Depends if you're running a 32-bit or 64-bit operating system.

Modern AMD and Intel processors use the x86_64 (as it's called in Linux) architecture. This is capable of running either 32 or 64-bit OS, and if you run a 64-bit OS, you can run 32 or 64 bit apps.

We're running Linux 2.4.21-47.0.1.ELsmp for the OS. Is this 64bit? I'm googling that.

MarkR wrote:
At the very least you should performance test your application with a 64-bit database on a 64-bit OS, if you're currently on 32-bit.

In most tests, a 64-bit OS is shown to be better.

I wrote my own application, which is merely a php script that queries the db, matches, sorts and paginates the results. How would I know if this is using 32 or 64 bit code? Mystery to me.

MarkR wrote:
In SOME tests, 32-bit apps on a 64-bit OS is best. Your mileage may vary.

Once I can find out which is which, it'll be easier to research this. I'm digging as fast as I can around the forums.

MarkR wrote:
You MUST get yourself some decent performance testing hardware.
Mark

Okay, I'll research the 'performance testing hardware' for this setup. Guess I'll just start googling that too. Any recommendations about where to start?

Sounds like you've been around the block a few times on this stuff. Even though I wrote a pretty basic script for this sort of thing that is working on this big site, I'm sure it needs some major tweaking.

Thanks, Mark. 🙂