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

[big_loser]

Sxooter wrote:

Note however, that PostgreSQL's full text search engine is a bit more complex to implement than MySQL's, but at the same time it's more customizable as well. Just don't expect it to consist of "create full-text-index in table a (fieldb)" "select match('abc' on (field2) from tablea...." if you try it.

I'm thinking that due to this basic memory limitation, and the dependency of MySQL on memory for full test searches, I will end up moving into a full 64-bit system, no matter how much optimizing of code and restructuring of the table(s) that I do now.

So, it seems the first priority would be to look into developing a completely 64-bit 'clean' system, from hardware, OS, application code, etc.

Then, once that is up and running, put as much RAM as possible in it (maybe at least 16gb or so if it can be used) and have plenty of fast (probably RAIDed) storage available. At that point, I should probably install the latest stable versions of PostgreSQL and any needed plugins, drivers, etc.

Do you have any recommendations for tutorials about how to get this setup, web-sites or even books (books seem always out of date though)?

Remember that the database we operate is large but very simple, really. Just a few million records with about 6 or so fields, averaging about 1.5kb /record. It is basically a read-only db, with a full-text index on just 2 fields; the TITLE and CONTENT fields which will be searched for matching keyword phrases (all or any words), then the result sets are sorted by relevancy and paginated out to the user. We also let the user click a link to get the exact record itself on its own page.

We get heavy indexing from the SE bots, about 120,000 page requests per day or so. Googlebots are about 2/3's of that total. Our ACTUAL human user page-requests are less than 10% of that, but that number is growing fast. For this heavy traffic, we need a powerful server, and that will probably get worse as the site db grows. (Naturally, we want the SE bots, since they bring us our 'real' traffic.) We have blocked obvious leeches and bad-actor bots, several hundred of the majors.

Other than daily backups and weekly updates, that's about it. It's big, but much simpler than sites with a balance of writes/updates/reads, like busy blogs, and so on.

I'm ready to jump into the community if you can point me at a site or two where people have some outlines about to-dos for us MySQLers.

Appreciate your migration tips and URL suggestions.

• Tom

[Sxooter]

For getting started, take a look here:

Tsearch2 Tutorial

And if you have any questions join the pgsql-general list here:

http://www.postgresql.org/community/lists/

to ask a question. They're a helpful bunch. But honestly, unless part of the tutorial just doesn't work for you, you should be fine.

I'd strongly suggest running the latest or near latest version of pgsql (8.1.9 or 8.2.4 right now) for performance and reliability reasons

[MarkR]

big_loser wrote:

Our production systems are located in a major ISP hub in Atlanta. They support them at the admin level, but of course do not do application development.

Whoever is supporting the system is doing a lousy job.

If as you say, they're requiring daily reboots because of running out of memory, something is SEVERELY BADLY tuned.

Whoever is responsible for monitoring and tuning them is not doing their job properly.

Nothing should actually leak memory, so it will only run out if it's been tuned sufficiently badly that it uses too much to start with (or during normal running).

---

Who manages your development systems? Do they suffer from the same symptoms?

Mark

[Sxooter]

I absolutely agree with MarkR on this. What part of your stack is running out of memory? It could be a dodgy install of linux with a memory leak. there was a problem with early 2.6 kernels where they could leak memory away until the machine crashed.

[big_loser]

Atlanta hasn't done any tuning at all. Just gave us the plain vanilla cPanel PHP/MySQL install, even though we have 4gb RAM and a TeraByte of HD storage space in 4 250mb drives. I've pleaded for more RAM and SWAP allocations, since the system was eating up RAM ans Swap a couple times a day. They're reluctant to tweak.

Yesterday I tweaked some myself. No MySQL tuning yet with my.cnf or my_huge.cnf. Just rewrote some of the code tightened down the select to look just at the two main fields, and removed an unused "group by". Wow. what an improvement... already. Since the change about 20 hours ago, the system hasn't gone above 17% of Memory, and not even 0.01% of Swap. And, we've had almost record traffic from users (haven't checked the overall traffic which would include the 90% from bots, but that normally stays within certain limits about the same every day). May not have to reboot now for many days, or even weeks. Time will tell.

Shocking what a bit of tightening here and there will do to tidy up RAM overflows. Jeesh (head-slap).

However, I'm still looking into the my.cnf and other tweaks.

And, I'm going to the URLs you recommended, Sxooter.

MarkR, I'll drop a note again to the admins and see what they say about some tuning for my real traffic and usage patterns, vs. the default setup the have given me.

BTW, anyone know where to find a copy of 'my_huge.cnf' for MySQL 4.1.22? I poked around in the server and can't find it anywhere. Found the 'my.cnf' at /etc/ but looked
up and down the tree... no soap. Can this be downloaded somewhere. Doesn't seem to show up in Google as a d/l-able file.

Thanks.

-- Tom

[MarkR]

big_loser wrote:

I've pleaded for more RAM and SWAP allocations, since the system was eating up RAM ans Swap a couple times a day. They're reluctant to tweak.

Without performance data telling you exactly what is eating up the RAM, there is no point in going silly.

Can you reproduce the fault outside of a running production system? Do you have an identically (or similarly) configured test server?

I have a theory about what is happening: You have some exceptionally long running mysql queries which are trashing the system and/or too many Apache child processes.

MySQL isn't very good at stopping what it's doing if the process which made the query goes away - it will generally continue until it finishes the query regardless of whether the original requesting process is long gone.

You should DEFINITELY enable slow query logging. You should be running "SHOW PROCESSLIST" from time to time.

Another possibility is that your web server is configured to allow too many threads to concurrently use the database.

In a 32-bit machine, the maximum number of database connections is limited by address space, typically to less than 1,000.

However, if you have 1000 Apache processes running with PHP5 using "prefork", the server will definitely run out of ram before then.

The trick is not to tune anything too high in terms of maximum processes, threads etc. The mysql key_buffer can be made as big as you like within address space limitations (More than 1G is generally bad in 32-bit).

Personally I'd advise getting a production-like test box as soon as possible.

I've no idea what configuration you have on your development box(es), but I assume it's not very similar to production.

Mark

[big_loser]

MarkR,

We simply can't afford a test box right now. The production box costs us $300/mo. and that eats up about half what the site brings in. I'm making all changes to LIVE system. Ahem...

Current definitions are:

rptId int(11) auto_increment
rptCategory varchar(255)
rptTitle text
rptContents text
rptAuthor varchar(255)
rptInst text
rptCit varchar(255)
rptLink varchar(255)

All columns are set to Null "No".

I have 3 indexes:

1) PRIMARY, type is primary, Cardinality is 1265665, Field is rptId.

2) rptCategory, FULLTEXT, 4, Fields are all exept rptId (rptId, rptCategory, rptTitle, rptContents, ptAuthor, rptInst, rptCit, rptLink)

3) rptTitle FULLTEXT, 4, Fields are rptTitle and rptContents.

So, I guess I need to get a list of to-dos:

1) remove unused "catagory" colum. Eats up storage and memory. I never search on it, and it is simply useless.

2) strip size of unnecessarily large columns to minimums. (is there a command to see what is the maximum size now being used by any row???)

3) add more RAM to see if that helps

Any other ideas?

[big_loser]

Sxooter wrote:

I absolutely agree with MarkR on this. What part of your stack is running out of memory? It could be a dodgy install of linux with a memory leak. there was a problem with early 2.6 kernels where they could leak memory away until the machine crashed.

I am running Linux 2.4.21-47, under WHM/cPanel. Can't easily change that myself.

The database is 4gb in size and my RAM is 4gb overall, with a 2gb Swap allocation. I think the Swap could be perhaps 8-10gb, right? Just an idea. The rule of thumbs says "at least twice RAM", correct? I have (4) 250gb HDD's, but I think 2 of them are unused completely. Looks like the system is configured for 'general use', not for one big MySQL dB like mine.

[FONT="Courier New"]

FILESYSTEM SIZE USED AVAIL USE MOUNTED ON

/dev/hda5 5.0G 198M 4.5 G 5 % /
/dev/hda1 99M 21M 74.0 M 22 % /boot
/dev/hda8 192G 2.1G 180.0 G 2 % /home
none 2.0G 0 2.0 G 0 % /dev/shm
/dev/hda3 15G 2.8G 12.0 G 20 % /usr
/dev/hda2 15G 4.7G 9.5.0 G 33 % /var
/dev/hdd1 230G 3.1G 215.0 G /backup
[/FONT]

As you can see, I still have about 500 GB free, somewhere. This is NOT the way to setup my system for a final dB size of about 25GB or so (including indexes).

Without changing the system itself, how should I setup the 4GB RAM and (4) 250gb HDDs to allow running a MySQL dB that is about 25gb?

[MarkR]

We still can't say what's wrong. Your main steps should be:
1. Acquire a test machine - it should not be in a datacentre, it could be in your office - get the EXACT SAME software stack on it
2. Load test it
3. Find out what's wrong
4. Fix it

Because we still don't have enough information to determine the cause of the problem, we can't tell you what's wrong.

If $300 a month uses up half the site's takings, what are you paying the developers / sysadmins with???!

Additionally, a large database server should not be on a 32-bit OS. Never. Not ever.

From the "df" output above, I suggest that it's been partitioned in a very haphazard way - as this machine is a database server, the databases are probably in /var - which is one of your smallest partitions. You want as much space in /var as possible. /home is probably not important as you won't have any real users on the system.

You almost certainly can't change the partitioning without reformatting, hence you'd need to migrate to a new box (which might be a good idea anyway).

I'd definitely consider migrating to 64-bit - you will need another box while you migrate (and of course a third box to do testing on on an ongoing basis).

Mark

[Piranha]

It seems like you are thinking much about the hardware and different database engines. But have you thought about other ways to solve the problems you have?

What storage engine are you using with the database? Note that if you use isam it says in the manual:

Can't handle tables larger than 4GB.

You can read more about storage engines in MySQL here. You should defenetley look into it.

Another thing you should look into is your queries, and your PHP code in general. The most important thing is of course the queries if that is where the problem is. You have already found out that removing group by have major performance benefits, there could be other things like this in your code. Another common mistake is to have several queries where it is normally enough with one, as an example have a query in a loop.

Don't get me wrong, hardware and software are important. And it is especially important to find and fix the problem with having to restart the server. But I think it is even more important to have optimized code. With two servers you can double the traffic (and it costs lots and lots of money); with opitmized code you might be able to 10-double it (without spending much money).

[Piranha]

You say that you use the following query:

$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";

Do you use all the information you get from the query? If not, then just select the rows you use.

Do you use the whole content or just the first x characters? If you only use the first x charactersit would probably be better to have a column with only the first x characters, then you don't have to return things you don't use.