Hi.
I've just set up my prototype so I can benchmark comparatively tthe profmance
between PostgreSQL and MySQL.
There's good news, and there's bad news.
The good news is - MySQL is faster by about 2 times.
The bad news is - MySQL is ONLY faster by about 2 times.
Now, I DO understand that this is a LOT, but I'd really like to make this go
faster.
Here is the setup I have used:
Number of Master records: 49,508
Number of FTI entries (PostgreSQL only): 1,574,136
Optimize/Vacuum ran just before the test:
PostgreSQL: VACUUM VERBOSE ANALYZE;
MySQL: OPTIMIZE TABLE Master
The data set used on the prototype benchmark is a random set of old job
adverts that I had lying around from a previous project.
On PostgreSQL a custom subrouting was written to handle the FTI text
splitting.
Query used on PostgreSQL:
SELECT DISTINCT Jobs.*
FROM Jobs,
JobsFTI
WHERE Retrieved > '08-Nov-2000' AND
Retrieved < '10-Nov-2000 AND
Jobs.ID = JobsFTI.ID AND
(
JobsFTI.String = 'perl' OR
JobsFTI.String = 'linux' OR
JobsFTI.String = 'apache' OR
JobsFTI.String = 'html' OR
JobsFTI.String = 'cgi' OR
JobsFTI.String = 'mod_perl' OR
JobsFTI.String = 'modperl' OR
JobsFTI.String = 'postgres' OR
JobsFTI.String = 'nt' OR
JobsFTI.String = 'postgresql'
);
Query used on MySQL:
SELECT Jobs.*
FROM Jobs
WHERE Retrieved > '20001108' AND
Retrieved < '20001110 AND
MATCH (Title, Description)
AGAINST ('perl linux apache html cgi mod_perl modperl postgres postgresql
nt');
The test machine is a dual P3/1GHz with 1 GB of RAM. In the test, a perl
program using DBI executed the above queries 100 times. The test was ran
twice on each database, to avoid any influence of disk access on the
performance (i.e. it was all running from cache). The second value is
reported here.
Both PostgreSQL and MySQL were set to use 128 MB of buffer cache (key_buffer
in MySQL, shared_buffers in PostgreSQL), with a similar amount of sort memory
allowed.
Results:
PostgreSQL: 119 seconds. (1.19 seconds/query)
MySQL: 58 seconds. (0.58 seconds/query)
I must say I am impressed with the speed increase. In all honesty I expected
less of a difference.
I just tried reducing the query string to just 'perl linux', and now the time
taken is 9 seconds for the test (100 runs), compared to 15 seconds for
PostgreSQL. MySQL sits happily at about 16 MB memory consumption, while
PostgreSQL consumes about 130 MB. The reason for this is, apparently, because
PostgreSQL handles it's own caching (in addition to the OS).
The figure of 0.09 seconds/query is very nice for a reasonable sounding case
of two search parameters. With 35 search parameters MySQL reports 1.94
seconds search time, which is still impressive for such a large query.
Now for the punch-line question - how do I speed this up further? I need to
at least maintain this sort of performance on a signifficantly smaller server
than this benchmark test-bed. Are there any tuning parameters I should be
aware of? I have already gone through the documentation and applied
reasonable sounding values to variables. Is there some sort of query
"profiling" mechanism to try to see where things are taking longest?
I'm using MySQL v4.0-alpha BTW.
I will try recompiling with pgcc at some point with in-line functions, -O9,
and without frame pointers etc, but it is likely that this system will
eventually run on an AMD Athlon processor, so it may not work with pgcc. It
is also almost certain that the database server will be separate from the
application server, which is likely to slow things down further.
I have just done "strip" on the mysqld. No percievable difference in my
custom test.
I compiled it all without debug options.
I haven't compiled the server with -static.
I haven't tried starting the deamon with --skip-safemalloc. Is this likely to
make much difference/cause problems?
How much difference is compiling with pgcc with -O9, in-line functions, and
without frame pointers likely to make?
Are any of those tweaks deprecated in v4?
Or am I simply pushing against the limits of what is reasonably possible here?
Thanks for any input you can provide.
Regards.
Gordan
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
