List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 2 2000 2:56pm
Subject:MYSQL 3.23.8
View as plain text  
>>>>> "Peter" == Peter Zaitsev <pz@stripped> writes:

Peter> I Reported some BUGS and unusual behavior of MYSQL 3.23.7. I was told some
Peter> bugs where fixed in new release so I'm reposting some bugs which still
Peter> unsolved in my enviroment with this release.
Peter> MYSQL Compiled with charset win1251 on Sun Sparc Ultra-2 Solaris 2.6  GCC
Peter> 2.95.2

What options did you give to gcc?   (Or, please always use mysqlbug
when posting a bug report);  As fork3_test.pl works perfectly here on
my Sun sparc, solaris 2.7, I can for now only assume this is a
compiler bug!


Peter> 1)  Troubles with loosing connection to MYSQL server - I have a large number
Peter> of these records in a log file and I can reproduce connection loss error by
Peter> running one of included scripts.  This test passed on the same mashine then
Peter> MYSLQ 3.22.27 was up.
Peter> <(root@spylog)/soft/mysql/mysql-3.23.8/tests># perl fork3_test.pl
Peter> Testing 3 multiple connections to a server with 1 insert, 1 delete
Peter> and 1 select connections.
Peter> Creating table bench_f1 in database test
Peter> Use of uninitialized value at fork3_test.pl line 94.
Peter> Got error on insert:
Peter> Database handle destroyed without explicit disconnect.
Peter> thread 'insert' finnished with exit code 255
Peter> Test_delete: Deleted all rows 11 times
Peter> thread 'delete' finnished with exit code 0
Peter> Test_select: ok
Peter> thread 'select1' finnished with exit code 0
Peter> Test failed
Peter> Total time: 260 wallclock secs ( 0.04 usr +  0.01 sys =  0.05 CPU)
Peter> <(root@spylog)/soft/mysql/mysql-3.23.8/tests>#

Peter> 2) Troubles with MYISAM format: Then table rating is in ISAM format query
Peter> return correct result but converting it to MYISAM makes result really
Peter> unusual (the table is small so I can upload it to you)

Peter> ISAM: mysql> select count(*) from rating where
Peter> unix_timestamp(now())-unix_timestamp(rating.created)<84600;
Peter> +----------+
Peter> | count(*) |
Peter> +----------+
Peter> |       44 |
Peter> +----------+
Peter> 1 row in set (0.07 sec)

mysql> alter table rating TYPE=MYISAM;
Peter> Query OK, 2592 rows affected (16.95 sec)
Peter> Records: 2592  Duplicates: 0  Warnings: 2593

mysql> select count(*) from rating where
Peter> unix_timestamp(now())-unix_timestamp(rating.created)<84600;
Peter> +----------+
Peter> | count(*) |
Peter> +----------+
Peter> |      734 |
Peter> +----------+
Peter> 1 row in set (0.09 sec)

Can you check that the rating.created wasn't modified in the ALTER TABLE?
(It shouldn't).  If this isn't the case, please upload the table to
ftp://www.mysql.com/pub/mysql/secret and I will take a lot on this at
once!

<cut>

Peter> 3) Query which returned incorrect result in 3.23.7 now works (that's cool)
Peter> but now it runs VERY slow - unusual for query having all indexes it needs:

mysql> select
Peter> id,ts,flag,session_id,visitor_id,ip,page_group,referer_page_id,history,onlin
Peter> e_users from st00.g00hits where id>466902 and ts>'19991228203447' and
Peter> counter_id=102 limit 10;
Peter> +-------------+------+----------------------------------------------+-------
Peter> -----+---------+-------+-------+------------+
Peter> | table       | type | possible_keys                                | key
Peter> | key_len | ref   | rows  | Extra      |
Peter> +-------------+------+----------------------------------------------+-------
Peter> -----+---------+-------+-------+------------+
Peter> | g00hitstest | ref  | PRIMARY,counter_id,counter_id_2,counter_id_3 |
Peter> counter_id |       4 | const | 21274 | where used |
Peter> +-------------+------+----------------------------------------------+-------
Peter> -----+---------+-------+-------+------------+
Peter> 1 row in set (0.01 sec

Peter> this query runs 32 Seconds !!! (I bet MYSQL is doint the full table scan
Peter> here !)

No;  according to the above information it's not doing a table scan;
Something else must be the problem.  I would however also need a copy
of this table to be able to say anything else.


mysql> select
Peter> id,ts,flag,session_id,visitor_id,ip,page_group,referer_page_id,history,onlin
Peter> e_users from st00.g00hits where id>466902  and counter_id=102 limit 10;

Peter> +---------+-------+----------------------------------------------+---------+
Peter> ---------+------+-------+------------+
Peter> | table   | type  | possible_keys                                | key     |
Peter> key_len | ref  | rows  | Extra      |
Peter> +---------+-------+----------------------------------------------+---------+
Peter> ---------+------+-------+------------+
Peter> | g00hits | range | PRIMARY,counter_id,counter_id_2,counter_id_3 | PRIMARY |
Peter> NULL | NULL | 42500 | where used |
Peter> +---------+-------+----------------------------------------------+---------+
Peter> ---------+------+-------+------------+
Peter> 1 row in set (0.00 sec)

Peter> this query runs 0.01 Second !!!

Peter> The thing is both queries return the same results and according to
Peter> optimization the first query should be at least the same speed.

On the other hand, the queries are resolved with different indexes,
which explains why they take different times.  Did you run an
'myisamchk -e' on the tables?

Peter> I can also upload this table contant to you - it's about 10Mb in size.

Please do!

Peter> 4) Other optimization problem - MYSQL does not use index there it should:

Peter> select sum(layers.stat_vishosts.hits) as val,st00.g00domip.domip as
Peter> dom,st00.g00domip.ip as ip from
Peter> layers.layers_stat_vishosts,layers.stat_vishosts,st00.g00domip where
Peter> layers.layers_stat_vishosts.type=2 and
Peter> layers.layers_stat_vishosts.page_group=255 and
Peter> layers.layers_stat_vishosts.counter=101 and
Peter> layers.layers_stat_vishosts.begin >= '1999-12-01' and
Peter> layers.layers_stat_vishosts.begin <= '1999-12-26' and
Peter> layers.layers_stat_vishosts.id = layers.stat_vishosts.layer_id and
Peter> layers.stat_vishosts.id = st00.g00domip.ip group by
Peter> layers.stat_vishosts.id
Peter> ORDER BY val desc limit 10;

Peter> +----------------------+--------+----------------+---------+---------+------
Peter> -------------------------------------------+-------+------------+
Peter> | table                | type   | possible_keys  | key     | key_len | ref
Peter> | rows  | Extra      |
Peter> +----------------------+--------+----------------+---------+---------+------
Peter> -------------------------------------------+-------+------------+
Peter> | layers_stat_vishosts | range  | search,PRIMARY | search  |    NULL | NULL
Peter> |    24 | where used |
Peter> | g00domip             | ALL    | PRIMARY        | NULL    |    NULL | NULL
Peter> | 58794 |            |
Peter> | stat_vishosts        | eq_ref | PRIMARY        | PRIMARY |       8 |
Peter> layers.layers_stat_vishosts.id,st00.g00domip.ip |     1 |            |
Peter> +----------------------+--------+----------------+---------+---------+------
Peter> -------------------------------------------+-------+------------+

Peter> The query DOES NOT use index on g00domip table and prefers to do the full
Peter> table scan :(   So it runs really slow (more then a minute)
Peter> Then replacing table st00.g00domip with exactly the same table having about
Peter> 20 times more rows query starts to run MUCH faster and explain reports it
Peter> starts to use indexes.


Peter> +----------------------+--------+----------------+---------+---------+------
Peter> --------------------------+--------+------------+
Peter> | table                | type   | possible_keys  | key     | key_len | ref
Peter> | rows   | Extra      |
Peter> +----------------------+--------+----------------+---------+---------+------
Peter> --------------------------+--------+------------+
Peter> | layers_stat_vishosts | range  | search,PRIMARY | search  |    NULL | NULL
Peter> |     24 | where used |
Peter> | stat_vishosts        | ref    | PRIMARY        | PRIMARY |       4 |
Peter> layers.layers_stat_vishosts.id | 110243 |            |
Peter> | domip                | eq_ref | PRIMARY        | PRIMARY |       4 |
Peter> layers.stat_vishosts.id        |      1 |            |
Peter> +----------------------+--------+----------------+---------+---------+------
Peter> --------------------------+--------+------------+

I am sorry to say, but I would need a copy of the original tables (the
smaller one) to be able to say whats wrong.

Have you tried doing 'myisamchk -a */*.ISM ; mysqladmin flush-tables'
and tried again?

<cut>

Peter> I can upload tables content to you as well if indeed it will take about 20MB
Peter> I think.

This is ok.

Peter> Hope you help me to solve these problem and I help you to find some bugs :)

Me too;  The biggest question is why fork3_test.pl didn't work for you
when it works for me :)

Did you recompile msql-mysql-modules with the new client libraries?

Peter> Best Regards
Peter> Peter Zaitsev

Regards,
Monty
Thread
MYSQL 3.23.8Peter Zaitsev2 Jan
  • MYSQL 3.23.8Michael Widenius2 Jan
  • MYSQL 3.23.8Michael Widenius4 Jan