Re: Large databases, performance

On 3 Oct 2002 at 8:54, Charles H. Woloszynski wrote:

> I'd be curious what happens when you submit more queries than you have
> processors (you had four concurrent queries and four CPUs), if you care
> to run any additional tests. Also, I'd report the query time in
> absolute (like you did) and also in 'Time/number of concurrent queries".
> This will give you a sense of how the system is scaling as the workload
> increases. Personally I am more concerned about this aspect than the
> load time, since I am going to guess that this is where all the time is
> spent.

OK. I am back from my cave after some more tests are done. Here are the
results. I am not repeating large part of it but answering your questions..

Don't ask me how these numbers changed. I am not the person who conducts the
test neither I have access to the system. Rest(or most ) of the things remains
same..

MySQL 3.23.52 with innodb transaction support:

4 concurrent queries :- 257.36 ms
40 concurrent queries :- 35.12 ms

Postgresql 7.2.2

4 concurrent queries :- 257.43 ms
40 concurrent queries :- 41.16 ms

Though I can not report oracle numbers, suffice to say that they fall in
between these two numbers.

Oracle seems to be hell lot faster than mysql/postgresql to load raw data even
when it's installed on reiserfs. We plan to run XFS tests later in hope that
that would improve mysql/postgresql load times.

In this run postgresql has better load time than mysql/innodb ( 18270 sec v/s
17031 sec.) Index creation times are faster as well (100 sec v/s 130 sec).
Don't know what parameters are changed.

Only worry is database size. Postgresql is 111GB v/s 87 GB for mysql. All
numbers include indexes. This is really going to be a problem when things are
deployed. Any idea how can it be taken down?

WAL is out, it's not counted.

Schema optimisation is later issue. Right now all three databases are using
same schema..

Will it help in this situation if I recompile posgresql with block size say 32K
rather than 8K default? Will it saev some overhead and offer better performance
in data load etc?

Will keep you guys updated..

Regards,
Shridhar

-----------------------------------------------------------
Shridhar Daithankar
LIMS CPE Team Member, PSPL.
mailto:shridhar_daithankar(at)persistent(dot)co(dot)in
Phone:- +91-20-5678900 Extn.270
Fax :- +91-20-5678901
-----------------------------------------------------------