Hi!
>>>>> "Peter" == Peter Zaitsev <[EMAIL PROTECTED]> writes:
Peter> Hello Michael,
Peter> Saturday, April 07, 2001, 3:14:42 AM, you wrote:
MW> Hi!
>>>>>>> "Peter" == Peter Zaitsev <[EMAIL PROTECTED]> writes:
Peter> Hello mysql,
Peter> I made one more test of mysql perfomance.
Peter> PIII-700/768Mb/Linux 2.4.2 glbc 2.1.3
Peter> I had the followings table with 12.000.000 rows (well I checked
Peter> this with 10000 rows as well but the speed does not differs much)
Peter> in it and I checked how fast I can select data by hash key "select *
Peter> from g00pages where hash=1" - the query was constant and only one
Peter> row matched. The speed was about queries/sec 1800.
MW> Is hash an unique key?
Peter> No it's not but it has just a couple of collisions per 100.000
Peter> records.
The problem with a not unique key is that MySQL will do an extra check
if it should use the key during the optimization stage. If the key
would be unique, MySQL can skip this stage.
Peter> So I decided to check what about selecting many pages at the time.
Peter> like "select * from g00pages there hash in (1,2,3...)" - the best
Peter> result I got is then selecting about 100 pages/query this gave me
Peter> about 2 times more perfomance then selecting pages one by one. This
Peter> locked even more upsetting.
MW> Why is this upsetting? Because you only got 3600 pages per second or
MW> because it was 2 times faster than single queries?
Peter> Both :) I really expected about 10000 of selects per second one by
Peter> one, and much more scaling like I got with heap table, then I got up
Peter> to 70000 pages per second.
A thing I forgot to add:
- As a lot of times goes to sending/receiving data between
client/server I expect that you will get more than 3600 queries /
second if you are using more than one client. Have you tried this?
MW> The reason singly queries are slower are of course that the
MW> initialization phase (reading the data from a socket, parsing,
MW> locking, checking which keys to use) is about half of the query time.
Peter> Yes, the problem is I thought this takes much more then 50% on such
Peter> very simple query, then all data is in memory. The only thing indeed
Peter> to call the os is data as myisam does not caches data, therefore I
Peter> tested my reiserfs and got 25000 of file open+read+close per second on
Peter> directory with 1500000 files of 10 bytes each :)
Peter> I've tried the same thing with heap table - the result's are
Peter> strange. The perfomance is starting from about 3600 pages/sec then
Peter> I've increased The number of pages/query I've got up to 70000
Peter> pages/sec which is quite good :)
This shows that we need to do some more benchmarks to understand
exactly where the time is spent for queries like this.
Peter> The strange thing is why result differ so much then ALL data fits it
Peter> memory without any problem
Peter> Well. Of couse I'll soon try glibc 2.2.x with your patches but I
Peter> don't se how it can improve things then I have obly one thread
Peter> running and one cpu and the query is running relatively big.
glibc 2.2 will only help when you have many threads doing queries at
the same time.
Peter> Heikki made tests which also shows some strange things - for
Peter> example why INSERT is FASTER then SELECT.
MW> I haven't seen the test but I can imagine this is true in some
MW> context. The reason for this is that a SELECT has to go through many
MW> optimization stages to find out what indexes to use and what queries
MW> to do. This is one basic fault with SQL; The optimizer has to do a
MW> lot of work...
Peter> Well. But then I select with multiple pages selected optimizer takes
Peter> it's work ony once, and this is well showed with heap tables, but with
Peter> myisam this somehow is not thrue.
With MyISAM, there is a lot of more system calls involved than with
HEAP tables ; It could be these that slows downs things.
Peter> Other interesting thing is - if I'll tell mysql explictly to use the
Peter> index will it reduce optimization cost ?
Yes.
Peter> It's a pity what mysql misses something like PROFILE call for the
Peter> query so I could check how much time is spent for different phases of
Peter> query execution.
Any suggestions for the output for this?
MW> Sergei is actually working on something interesting for people that
MW> need all the speed they can get: We are going to provide a interface
MW> directly to the storage handler, without any optimizations.
MW> This will of course not be SQL, but we expect this to be VERY fast as
MW> long as you are searching on a specific key...
Peter> Yes it's nice. The other possible speedup is prepeared statements, or
Peter> execution plan cache (classical solutions) :)
We will add prepared statements in MySQL 4; The question is will these
really help your basic setup?
Peter> Even looking at the context swithces does not explain the thing much
Peter> - my result show that this system can do about 300.000 of context
Peter> swithces per second - so 5.000-10.000 of context swithces per second
Peter> does not eat much from cpu.
MW> To be able to comment, I would need to make a gprof of this.
MW> (This has to wait at least until the end of next week...)
Peter> OK. This is not really pain - just comments :)
Peter> My current pains are problems with repair tables and.... keycache :)
No comments (yet).
Regards,
Monty
---------------------------------------------------------------------
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
