Hi!

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.

Is hash an unique key?

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.

Why is this upsetting? Because you only got 3600 pages per second or because it was 2 times faster than single queries?

The reason singly queries are slower are of course that the initialization phase (reading the data from a socket, parsing, locking, checking which keys to use) is about half of the query time.

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 :)

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.

Peter> Heikki made tests which also shows some strange things - for Peter> example why INSERT is FASTER then SELECT.

I haven't seen the test but I can imagine this is true in some context. The reason for this is that a SELECT has to go through many optimization stages to find out what indexes to use and what queries to do. This is one basic fault with SQL; The optimizer has to do a lot of work...

Sergei is actually working on something interesting for people that need all the speed they can get: We are going to provide a interface directly to the storage handler, without any optimizations. This will of course not be SQL, but we expect this to be VERY fast as long as you are searching on a specific key...

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.

To be able to comment, I would need to make a gprof of this. (This has to wait at least until the end of next week...)

Regards, Monty