MySQL 3.23.49 - Performances on NT

Anon

Hi guys,

I am writing to check something with you concerning performances of MySQL on NT platforms.

My hardware is a PII 400Mhz (ok, it is not a rocket) 256 Mb Ram and W2k on board.

MySQL (v. 3.23.49) runs in conjucntion with a data feeding application (via ODBC) plus Apache and PhP (without Zend optimization) for user interface.

Now, that's the fact:

The database runs fine (actually, with excellent performances) until a point when the main table reaches more or less 300 Mb (that is after less of two months of data feeding!). At that point the performances of the database go sharply down (Php readings goes well over the 30 secs of timeout).

On a 1.4 Ghz Processor, with 512 Mb Ram (running WinNT) this point was reached with the main table at 500 MB.

Performances, i noticed, really drop down in a logartimic behaviour. Does any of you know why this happend or have experienced the same in similar situations?

Thank you for sharing your opinions on this matter,

Vin.

PS. MySQL was installed using Typical option from the installshield and no other adjustments have been added than the creation of a couple of users.

[deleted]

256 Mb Ram , 300mb data
512mb ram, 500mb data.

see the pattern?

You're reaching the servers RAM limit.
Database servers love ram.
Everything they can keep in RAM is accessible in 10ns, whereas if they have to read it from disk, it takes 10-20ms (that's 1000 times slower).

Obviously you can't keep expanding the server's memory, so you should try to minimize the amount of data that has to be read from the disk.

Do you have proper indexes defined on your tables? is your database setup to use as much ram as it can without taking it all (leaving non for the OS)

Anon

Firstly, Vincent, thank you for your reply.

Indeed. It seams that performances drop when the database exceeds the amount of physical RAM (I am currently busy deleting 100 Mb of data -200 Mb Total - and expecting good performances, for the sake of solving this matter once for all).

My surprise was in the way how this drop happened. It seams to be too drammatical.

The index is about 5% of the main talbe (300MbTable/15MbIndex).

Delete must be almost finished now. I'll verify the perfomences and come back.

Vin

Anon

Odd.

I just deleted 60.000 rows ( about 104Mb) from the original 165.000 rows and the delete was successful.

The size of the database, though, including keys, remains unchanged still at 300Mb+15Mbkeys, while it was expected to drop at 200Mb+10Mbkeys.

No wonder, the performances are as bad as they were before the delete (60.000 rows ago).

I am now expanding the RAM (384 Mb Physical)...

Vin.

[deleted]

Well, the difference between 10ns and 10ms is dramatic.

Try running some EXPLAIN commands to find out wether all your queries can indeed use indexes, otherwise a sequential scan will take ages...

Anon

Indeed,

well I have upgraded the RAM to 398Mb and the results are better (of course). Time-out is not an issue anymore on a table of 300Mb.

About the Index stuff, you must be right. I notice that whatever range of dates i select for my search (i store messages in the database, and dates of messages are indexed). The results takes always exactly the same time.

Even though, I wander about two things:

Why deleting data, the rows desappeared but the file size of the database remained unchanged.
Do servers really need to have as much RAM as the size of the data table? The answer here is probably no, it should be something to do with indexing.

vin.

[deleted]

MySQL's datafiles don't shrink. They dont free the space that you deleted the records from, they keep it allocated to store new records in lateron. Why they do this: heaven only knows.

You can forcibly re-claim the lost space by using OPTIMIZE

http://www.mysql.com/doc/O/P/OPTIMIZE_TABLE.html

Anon

Thanks again!

I have the first issue cleared now. I am doing some tests about the second one.

vin

PS. There will be a day when database builders will start selling RAM's, along with their software :-)

Anon

Also make sure that you're using optimized queries. You don't want to force php to buffer up a huge query just to use one of the rows or something. I'm sure you know what you're doing if you've got a 300+mb database, but I figured I'd mention it.

Anon

Thanks,

yes, queries are pretty much all optimized and requested data is limited at the very minimum. The remaining problem, that I am now examining, is about use of indexes. I noticed that in one particoular case searches where made without use of the index at all (what a waste!).

By the way, is there anyone with some experience of full-text indexing in the last 4.xx version? Given the results that i found on the hardware requirements, a real-time full-text indexing only makes sense if you really have as much room in you RAM as much the size of the database is, is this correct?

vin.

[deleted]

It is faster to add new data to a file than it is to change data within a file. When you delete records from your table, the database simply marks those records in the files as 'deleted' and leaves the file intact. This is much faster than re-writing half the file to make up for the deleted record.
Indeed, the answer is no. (what would banks do with their multi-terabyte databases?)
It's simply a matter of caching. Data that's already in memory does not have to be read from disk. The more memory you have, the more records you can keep in memory, the fewer disk-reads you have to do, the faster you can get your results.
If you can't keep all records in memory, the next-best thing is to keep the data about the records in memory; the index.

gerardg

Isn't it the same way with postgresql and vacuum?

[deleted]

That's correct, PostgreSQL uses vacuum to recover the freesapce, but vaccuum is also used to analyze the data, to re-build it's statistics for the query-optimizer. Run it regularly!