Differences between 2 MySQL instances

------=_NextPart_000_004B_01CB12B0.572D6D10
Content-Type: text/plain;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

Hello,

I have the following table under MySQL 5.1.43-community under Windows, =
and under MySQL 5.0.82sp1 Source distribution under Linux):

CREATE TABLE `table_name` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`tip_ticker` tinyint(1) NOT NULL,
`symbol` varchar(20) NOT NULL,
`market` varchar(20) NOT NULL,
`date` date DEFAULT NULL,
`time` time DEFAULT NULL,
`price` decimal(20,4) unsigned DEFAULT NULL,
`price_adjusted` double DEFAULT NULL,
`volume` bigint(20) unsigned DEFAULT NULL,
`volume_adjusted` double(255,0) unsigned DEFAULT NULL,
`bid` decimal(20,4) unsigned DEFAULT NULL,
`ask` decimal(20,4) unsigned DEFAULT NULL,
`bid_volume` bigint(20) unsigned DEFAULT NULL,
`ask_volume` bigint(20) unsigned DEFAULT NULL,
`trades` int(10) unsigned DEFAULT NULL,
`change_percent` decimal(20,4) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `i1` (`date`,`time`,`id`),
KEY `i2` (`symbol`,`date`,`time`,`id`),
KEY `i3` (`tip_ticker`,`date`,`time`,`id`),
KEY `i4` (`symbol`,`market`,`date`,`time`),
KEY `i5` (`trades`,`date`,`time`,`symbol`,`market`),
KEY `i6` (`change_percent`,`date`,`time`,`symbol`,`market`),
KEY `i7` (`date`,`time`,`symbol`,`market`)
) ENGINE=3DInnoDB AUTO_INCREMENT=3D1154030054 DEFAULT CHARSET=3Dlatin1=20

I have tried the following query under both MySQL servers:

explain select * from table_name
where
symbol=3D'etc'
and market=3D'etc2'
and date>=3D'2010-01-01'
and tip_ticker=3D1
order by trades, date, time, symbol, market
limit 20\G

The result under Windows is:

id: 1
select_type: SIMPLE
table: table_name
type: index
possible_keys: i1,i2,i3,i4,i7
key: i5
key_len: 57
ref: NULL
rows: 4058
Extra: Using where=20

But the result under Linux is:

id: 1
select_type: SIMPLE
table: table_name
type: range
possible_keys: i1,i2,i3,i4,i7
key: i4
key_len: 48
ref: NULL
rows: 96000
Extra: Using where; Using filesort=20

This query obviously takes a much longer time than the one under =
Windows.
I have also tried to force index(i5) under Linux in order to force using =
the same index as under Windows:

explain select * from table_name
force index(i5)
where
symbol=3D'etc'
and market=3D'etc2'
and date>=3D'2010-01-01'
and tip_ticker=3D1
order by trades, date, time, symbol, market
limit 20\G

But the result is:

id: 1
select_type: SIMPLE
table: table_name
type: index
possible_keys: NULL
key: i5
key_len: 57
ref: NULL
rows: 11020086
Extra: Using where=20

Even though this query uses the same index as the one under Windows, the =
number of estimated rows is approximately the total number of rows in =
the table and it also takes a very long time to complete.

Do you have any idea why this works differently under Linux? Is it =
because under Linux I have MySQL 5.0 and under Windows MySQL 5.1 and I =
definitely need to upgrade?

There are some differences between the global variables that start with =
innodb_ under Windows and Linux, but I don't know if those differences =
make InnoDB to choose another index.

Thank you.

--
Octavian



__________ Information from ESET NOD32 Antivirus, version of virus signatur=
e database 5220 (20100623) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com


------=_NextPart_000_004B_01CB12B0.572D6D10--

Re: Differences between 2 MySQL instances

--0016e6d7e4b2770e270489b20de7
Content-Type: text/plain; charset=ISO-8859-1

Have you tried running 'OPTIMIZE TABLE' on the tables in question to make
sure statistics are up to date.

I would expect the vast majority of queries to run faster on MySQL 5.1 (with
identical settings, hardware and operating system).



2010/6/23 Octavian Rasnita

> Hello,
>
> I have the following table under MySQL 5.1.43-community under Windows, and
> under MySQL 5.0.82sp1 Source distribution under Linux):
>
> CREATE TABLE `table_name` (
> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
> `tip_ticker` tinyint(1) NOT NULL,
> `symbol` varchar(20) NOT NULL,
> `market` varchar(20) NOT NULL,
> `date` date DEFAULT NULL,
> `time` time DEFAULT NULL,
> `price` decimal(20,4) unsigned DEFAULT NULL,
> `price_adjusted` double DEFAULT NULL,
> `volume` bigint(20) unsigned DEFAULT NULL,
> `volume_adjusted` double(255,0) unsigned DEFAULT NULL,
> `bid` decimal(20,4) unsigned DEFAULT NULL,
> `ask` decimal(20,4) unsigned DEFAULT NULL,
> `bid_volume` bigint(20) unsigned DEFAULT NULL,
> `ask_volume` bigint(20) unsigned DEFAULT NULL,
> `trades` int(10) unsigned DEFAULT NULL,
> `change_percent` decimal(20,4) DEFAULT NULL,
> PRIMARY KEY (`id`),
> KEY `i1` (`date`,`time`,`id`),
> KEY `i2` (`symbol`,`date`,`time`,`id`),
> KEY `i3` (`tip_ticker`,`date`,`time`,`id`),
> KEY `i4` (`symbol`,`market`,`date`,`time`),
> KEY `i5` (`trades`,`date`,`time`,`symbol`,`market`),
> KEY `i6` (`change_percent`,`date`,`time`,`symbol`,`market`),
> KEY `i7` (`date`,`time`,`symbol`,`market`)
> ) ENGINE=InnoDB AUTO_INCREMENT=1154030054 DEFAULT CHARSET=latin1
>
> I have tried the following query under both MySQL servers:
>
> explain select * from table_name
> where
> symbol='etc'
> and market='etc2'
> and date>='2010-01-01'
> and tip_ticker=1
> order by trades, date, time, symbol, market
> limit 20\G
>
> The result under Windows is:
>
> id: 1
> select_type: SIMPLE
> table: table_name
> type: index
> possible_keys: i1,i2,i3,i4,i7
> key: i5
> key_len: 57
> ref: NULL
> rows: 4058
> Extra: Using where
>
> But the result under Linux is:
>
> id: 1
> select_type: SIMPLE
> table: table_name
> type: range
> possible_keys: i1,i2,i3,i4,i7
> key: i4
> key_len: 48
> ref: NULL
> rows: 96000
> Extra: Using where; Using filesort
>
> This query obviously takes a much longer time than the one under Windows.
> I have also tried to force index(i5) under Linux in order to force using
> the same index as under Windows:
>
> explain select * from table_name
> force index(i5)
> where
> symbol='etc'
> and market='etc2'
> and date>='2010-01-01'
> and tip_ticker=1
> order by trades, date, time, symbol, market
> limit 20\G
>
> But the result is:
>
> id: 1
> select_type: SIMPLE
> table: table_name
> type: index
> possible_keys: NULL
> key: i5
> key_len: 57
> ref: NULL
> rows: 11020086
> Extra: Using where
>
> Even though this query uses the same index as the one under Windows, the
> number of estimated rows is approximately the total number of rows in the
> table and it also takes a very long time to complete.
>
> Do you have any idea why this works differently under Linux? Is it because
> under Linux I have MySQL 5.0 and under Windows MySQL 5.1 and I definitely
> need to upgrade?
>
> There are some differences between the global variables that start with
> innodb_ under Windows and Linux, but I don't know if those differences make
> InnoDB to choose another index.
>
> Thank you.
>
> --
> Octavian
>
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 5220 (20100623) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>


--
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Microsoft SQL Server 2005/2008 Database Administrator
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.daisley@butterflysystems.co.uk

--0016e6d7e4b2770e270489b20de7--

Re: Differences between 2 MySQL instances

------=_NextPart_000_003A_01CB1378.FEFCE160
Content-Type: text/plain;
charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable

I have tried, but with no difference.

I have changed some indexes and made the queries run faster, but I still =
found a problem:

I use a module that does paging and it makes a select(*) and this query =
takes a very long time.

I have also tried to do select(id) where the id column is the primary =
key, but it runs very slow also.
I have also seen that select(*) with various where conditions is still =
very slow.

Is there any trick to make the select(*) queries run faster within an =
InnoDB table?

Thanks.

--
Octavian

----- Original Message -----=20
From: John Daisley=20
To: Octavian Rasnita=20
Cc: mysql@lists.mysql.com=20
Sent: Wednesday, June 23, 2010 3:55 PM
Subject: Re: Differences between 2 MySQL instances


Have you tried running 'OPTIMIZE TABLE' on the tables in question to =
make sure statistics are up to date.

I would expect the vast majority of queries to run faster on MySQL 5.1 =
(with identical settings, hardware and operating system).




2010/6/23 Octavian Rasnita

Hello,

I have the following table under MySQL 5.1.43-community under =
Windows, and under MySQL 5.0.82sp1 Source distribution under Linux):

CREATE TABLE `table_name` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`tip_ticker` tinyint(1) NOT NULL,
`symbol` varchar(20) NOT NULL,
`market` varchar(20) NOT NULL,
`date` date DEFAULT NULL,
`time` time DEFAULT NULL,
`price` decimal(20,4) unsigned DEFAULT NULL,
`price_adjusted` double DEFAULT NULL,
`volume` bigint(20) unsigned DEFAULT NULL,
`volume_adjusted` double(255,0) unsigned DEFAULT NULL,
`bid` decimal(20,4) unsigned DEFAULT NULL,
`ask` decimal(20,4) unsigned DEFAULT NULL,
`bid_volume` bigint(20) unsigned DEFAULT NULL,
`ask_volume` bigint(20) unsigned DEFAULT NULL,
`trades` int(10) unsigned DEFAULT NULL,
`change_percent` decimal(20,4) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `i1` (`date`,`time`,`id`),
KEY `i2` (`symbol`,`date`,`time`,`id`),
KEY `i3` (`tip_ticker`,`date`,`time`,`id`),
KEY `i4` (`symbol`,`market`,`date`,`time`),
KEY `i5` (`trades`,`date`,`time`,`symbol`,`market`),
KEY `i6` (`change_percent`,`date`,`time`,`symbol`,`market`),
KEY `i7` (`date`,`time`,`symbol`,`market`)
) ENGINE=3DInnoDB AUTO_INCREMENT=3D1154030054 DEFAULT =
CHARSET=3Dlatin1

I have tried the following query under both MySQL servers:

explain select * from table_name
where
symbol=3D'etc'
and market=3D'etc2'
and date>=3D'2010-01-01'
and tip_ticker=3D1
order by trades, date, time, symbol, market
limit 20\G

The result under Windows is:

id: 1
select_type: SIMPLE
table: table_name
type: index
possible_keys: i1,i2,i3,i4,i7
key: i5
key_len: 57
ref: NULL
rows: 4058
Extra: Using where

But the result under Linux is:

id: 1
select_type: SIMPLE
table: table_name
type: range
possible_keys: i1,i2,i3,i4,i7
key: i4
key_len: 48
ref: NULL
rows: 96000
Extra: Using where; Using filesort

This query obviously takes a much longer time than the one under =
Windows.
I have also tried to force index(i5) under Linux in order to force =
using the same index as under Windows:

explain select * from table_name
force index(i5)
where
symbol=3D'etc'
and market=3D'etc2'
and date>=3D'2010-01-01'
and tip_ticker=3D1
order by trades, date, time, symbol, market
limit 20\G

But the result is:

id: 1
select_type: SIMPLE
table: table_name
type: index
possible_keys: NULL
key: i5
key_len: 57
ref: NULL
rows: 11020086
Extra: Using where

Even though this query uses the same index as the one under Windows, =
the number of estimated rows is approximately the total number of rows =
in the table and it also takes a very long time to complete.

Do you have any idea why this works differently under Linux? Is it =
because under Linux I have MySQL 5.0 and under Windows MySQL 5.1 and I =
definitely need to upgrade?

There are some differences between the global variables that start =
with innodb_ under Windows and Linux, but I don't know if those =
differences make InnoDB to choose another index.

Thank you.

--
Octavian



__________ Information from ESET NOD32 Antivirus, version of virus =
signature database 5220 (20100623) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com





--=20
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Microsoft SQL Server 2005/2008 Database Administrator
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.daisley@butterflysystems.co.uk



__________ Information from ESET NOD32 Antivirus, version of virus signatur=
e database 5223 (20100623) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com


------=_NextPart_000_003A_01CB1378.FEFCE160--

Re: Differences between 2 MySQL instances

Hi!


Octavian Rasnita wrote:
> I have tried, but with no difference.
>=20
> I have changed some indexes and made the queries run faster, but I =
still found a problem:
>=20
> I use a module that does paging and it makes a select(*) and this q=
uery takes a very long time.
>=20
> I have also tried to do select(id) where the id column is the prima=
ry key, but it runs very slow also.
> I have also seen that select(*) with various where conditions is st=
ill very slow.
>=20
> Is there any trick to make the select(*) queries run faster within =
an InnoDB table?

You should accept the fact that 5.1 has improvements over 5.0, some o=
f
them being better optimization resulting in faster execution.

Upgrade the 5.0 installation to 5.1 to profit from the newer version.

>=20
> Thanks.
>=20
> --
> Octavian
>=20
> ----- Original Message -----=20
> From: John Daisley=20
> To: Octavian Rasnita=20
> Cc: mysql@lists.mysql.com=20
> Sent: Wednesday, June 23, 2010 3:55 PM
> Subject: Re: Differences between 2 MySQL instances
>=20
>=20
> Have you tried running 'OPTIMIZE TABLE' on the tables in question=
to make sure statistics are up to date.
>=20
> I would expect the vast majority of queries to run faster on MySQ=
L 5.1 (with identical settings, hardware and operating system).
>=20
>=20
> 2010/6/23 Octavian Rasnita
>=20
> Hello,
>=20
> I have the following table under MySQL 5.1.43-community under W=
indows, and under MySQL 5.0.82sp1 Source distribution under Linux):
>=20
> [[...]]
>=20
> Do you have any idea why this works differently under Linux? Is=
it because under Linux I have MySQL 5.0 and under Windows MySQL 5.1 =
and I definitely need to upgrade?

The MySQL code is identical on all platforms, except where platform
differences affect the code.
(Prominent example for such a difference: The Windows file system
doesn't differ between small and capital letters in a name.)
Assuming the same version on two different platforms, with the same d=
ata
and indexes, MySQL will use the same execution strategy.
If in addition the various settings are identical (especially cache
sizes), and the current usage of caches doesn't differ, you should no=
t
see any other difference than what is caused by different CPU and dis=
k
speeds, RAM size, machine load, 32 vs 64 bit differences, and such fa=
ctors.

The real differences are between versions - improvements and bug fixe=
s.

In addition to general improvements, you should remember that 5.0 is =
not
actively maintained any more, so even if significant bugs should be
found you cannot rely on getting fixes for them.
Change to a version that receives updates, currently that is 5.1
(or 5.5, but that is not yet declared "production").

>=20
> [[...]]

Jörg

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz
Amtsgericht Muenchen: HRB161028


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: [Spam][78.6%] Re: Differences between 2 MySQL instances

From: "Joerg Bruehe"

Hi!


Octavian Rasnita wrote:
> I have tried, but with no difference.
>
> I have changed some indexes and made the queries run faster, but I still
> found a problem:
>
> I use a module that does paging and it makes a select(*) and this query
> takes a very long time.
>
> I have also tried to do select(id) where the id column is the primary key,
> but it runs very slow also.
> I have also seen that select(*) with various where conditions is still
> very slow.
>
> Is there any trick to make the select(*) queries run faster within an
> InnoDB table?

You should accept the fact that 5.1 has improvements over 5.0, some of
them being better optimization resulting in faster execution.

Upgrade the 5.0 installation to 5.1 to profit from the newer version.



I have tried the select(*) with InnoDB under 5.1, but it is still very slow
(dozens of seconds) while with MyISAM is instant. I know that InnoDB works
differently and cannot do that select so fast, but... dozens of seconds is
too much.

I have succeeded to make the main select very fast, but then the select(*)
needed for paging the results is hundreads times slower.
I have also tried select SQL_CALC_FOUND_ROWS and select found_rows() but it
is much slower than a simple select(*).

Thanks.

Octavian


__________ Information from ESET NOD32 Antivirus, version of virus signature database 5224 (20100624) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org