Re: SPARC to x64 Transition
This is confirmed working on a small test set with MySQL 4.1.22
transitioning from SPARC to X64. Thanks everyone!
-Aaron
Here's the test set we used:
CREATE TABLE `test_innodb` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`bigint` bigint(11) DEFAULT NULL,
`float` float DEFAULT NULL,
`double` double DEFAULT NULL,
`deci` decimal(6,2) DEFAULT NULL,
`var` varchar(255) DEFAULT NULL,
`datetime` datetime DEFAULT NULL,
`timestamp` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
big_unsigned bigint unsigned,
float_unsigned float unsigned,
int_unsigned int unsigned,
PRIMARY KEY (`id`),
KEY `deci` (`deci`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
insert into `test_innodb` values
('1' ,'9223372036854775807' ,'321.123' ,'123132321.213213' ,'12.20'
,'somehcar' ,'2009-02-10 10:44:10' ,'2009-02-17 10:46:05' ,(400 *
400) ,444.888 ,123456) ,
('2' ,'-9223372036854775807' ,'-67498.7' ,'-6.84616419684968e+17' ,'-0.84'
,'somevarchar' ,'2009-02-05 10:45:12' ,'2009-02-17 10:46:12' ,(500 *
300) ,444.888 ,123456) ,
('3' ,'0' ,'0.0' ,'0.0' ,'0.0' ,NULL ,'-00-00 00:00:00' ,'2009-02-17
10:46:12' ,(0) ,0.0 ,0);
On Mon, Feb 16, 2009 at 8:26 AM, Heikki Tuuri wrote:
> Aaron,
>
> "
> I've currently got a project to migrate a LARGE (3.5TB) MySQL data set from
> a Sun SPARC machine to a Sun x86 machine, both running Solaris 10 (though
> obviously one is x86 and the other is SPARC). Is it possible to simply
> copy
> the data files from one host to the other or is a full mysqldump/import
> necessary to preserve data integrity?
>
> If a file copy doesn't work, why specificially would it fail?
>
> Thanks,
> -Aaron
> "
>
> you can simply copy the files, whether InnoDB or MyISAM. As far as I know,
> all modern processors use the same floating point format. And all integer
> and other data structures are platform independent in MyISAM and InnoDB.
>
> Best regards,
>
> Heikki
> Innobase/Oracle
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=aaronb...@gmail.com
>
>
Re: Why do quotes in an IN() clause effect performance so drastically?
Hi,
I guess the id_file_set is an INT?
The problem si most likely due to the fact you are comparing integer to
string, which forces MySQL to use type conversion. For more information
check http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html
When type conversion occurs MySQL will not be able to use the index and will
have to do a full table scan which can be seen from your explain queries. As
for the 'bogus' case most likely it has been dropped because it cannot be
converted to integer. This case is explained at
http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
Hope this helps.
Regards
Dobromr Velev
On Wednesday 18 February 2009 05:25, Daevid Vincent wrote:
> I'm really confused. First, I don't understand why quoting my IN()
> values here caused them to run significantly slower than the non-quoted
> versions... on just this simple contrived example it can be as much as
> 2.2 seconds vs. 0 seconds to return on a table that has 2.5M rows.
>
> The problem I'm facing is that the stupid PEAR::DB class is
> "smart-quoting" a list of values and giving me this:
>
> mysql> explain select * from bite_event_log where id_file_set in
> ('-1','2412948') limit 1;
> ++-++--+---+--+
>-+--+-+-+
>
> | id | select_type | table | type | possible_keys | key |
>
> key_len | ref | rows| Extra |
> ++-++--+---+--+
>-+--+-+-+
>
> | 1 | SIMPLE | bite_event_log | ALL | id_file_set | NULL | NULL
> | NULL | 1213328 | Using where |
>
> ++-++--+---+--+
>-+--+-+-+
>
> But what I really want is for it to do this:
>
> mysql> explain select * from bite_event_log where id_file_set in
> (-1,2412948) limit 1;
> ++-++---+---+-+
>-+--+--+-+
>
> | id | select_type | table | type | possible_keys | key
> | key_len | ref | rows | Extra |
>
> ++-++---+---+-+
>-+--+--+-+
>
> | 1 | SIMPLE | bite_event_log | range | id_file_set |
>
> id_file_set | 5 | NULL |2 | Using where |
> ++-++---+---+-+
>-+--+--+-+
>
> Mixing quoted and non-quoted is said to be "bad"
> http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_i
>n
>
> mysql> explain select * from bite_event_log where id_file_set in
> ('-1',2412948) limit 1;
> ++-++--+---+--+
>-+--+-+-+
>
> | id | select_type | table | type | possible_keys | key |
>
> key_len | ref | rows| Extra |
> ++-++--+---+--+
>-+--+-+-+
>
> | 1 | SIMPLE | bite_event_log | ALL | id_file_set | NULL | NULL
> | NULL | 1213328 | Using where |
>
> ++-++--+---+--+
>-+--+-+-+
>
> However, aside from the straight numerical one above (2nd down), this
> version is the second best performing!?
>
> And furthermore, using a word string like "bogus" significantly
> out-performs another string such as "-1". Huh?!? WTF?
> It's like mySQL was "smart enough" to know that "bogus" could be
> dropped, whereas it's not smart enough to know to drop "-1",
> despite the fact that the id_file_set column is an unsigned integer.
>
> mysql> explain select * from bite_event_log where id_file_set in
> ('bogus',2412948) limit 1;
> ++-++---+---+-+
>-+--+--+-+
>
> | id | select_type | table | type | possible_keys | key
> | key_len | ref | rows | Extra |
>
> ++-++---+---+-+
>-+--+--+-+
>
> | 1 | SIMPLE | bite_event_log | range | id_file_set |
>
> id_file_set | 5 | NULL |2 | Using where |
> ++-++---+---+-+
>-+--+--+-+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [OFFTOPIC] PHP and local-infile
Hi Mauricio,
On Wed, Feb 18, 2009 at 12:58 AM, Mauricio Tellez
wrote:
> Hi, sorry if this is an offtopic. I have a web site with LAMP, one module
> use a "LOAD DATA LOCAL INFILE" statement, all was running fine, but today I
> ran this module and I get an "The used command is not allowed with this
> MySQL version" message. If I use the same query from mysql command line I
> get the same message, so I put the following in my.cnf: loose-local-infile
> at [client]section. This solve the mysql command line, but when running from
> PHP I still got the same. Any clue?
I think you need to either start the server with
--local-infile[={0|1}]
If you start the server with --local-infile=0, clients cannot use
LOCAL in LOAD DATA statements. See Section 5.3.4, "Security Issues
with LOAD DATA LOCAL".
http://dev.mysql.com/doc/refman/5.0/en/privileges-options.html#option_mysqld_local-infile
Or set the variable globally in the running instance.
local_infile
Variable Name local_infile
Variable Scope Global
Dynamic VariableYes
Whether LOCAL is supported for LOAD DATA INFILE statements. See
Section 5.3.4, "Security Issues with LOAD DATA LOCAL".
Understanding the implications.
http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html
Cheers,
Ewen
>
> --
> Mauricio Tellez
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why do quotes in an IN() clause effect performance so drastically?
Hi David,
On Wed, Feb 18, 2009 at 4:25 AM, Daevid Vincent wrote:
> I'm really confused. First, I don't understand why quoting my IN()
> values here caused them to run significantly slower than the non-quoted
> versions... on just this simple contrived example it can be as much as
> 2.2 seconds vs. 0 seconds to return on a table that has 2.5M rows.
>
> The problem I'm facing is that the stupid PEAR::DB class is
> "smart-quoting" a list of values and giving me this:
>
> mysql> explain select * from bite_event_log where id_file_set in
> ('-1','2412948') limit 1;
> ++-++--+---+--+-+--+-+-+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows| Extra |
> ++-++--+---+--+-+--+-+-+
> | 1 | SIMPLE | bite_event_log | ALL | id_file_set | NULL | NULL
> | NULL | 1213328 | Using where |
> ++-++--+---+--+-+--+-+-+
>
Here the quotes are forcing MySQL to see strings where it should see
integers, so when the optimizer evaluates the available indexes it
misses id_file_set index.
> But what I really want is for it to do this:
>
> mysql> explain select * from bite_event_log where id_file_set in
> (-1,2412948) limit 1;
> ++-++---+---+-+-+--+--+-+
> | id | select_type | table | type | possible_keys | key
> | key_len | ref | rows | Extra |
> ++-++---+---+-+-+--+--+-+
> | 1 | SIMPLE | bite_event_log | range | id_file_set |
> id_file_set | 5 | NULL |2 | Using where |
> ++-++---+---+-+-+--+--+-+
>
Here the integers are evaluated as integers and the index in used.
> Mixing quoted and non-quoted is said to be "bad"
> http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in
>
> mysql> explain select * from bite_event_log where id_file_set in
> ('-1',2412948) limit 1;
> ++-++--+---+--+-+--+-+-+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows| Extra |
> ++-++--+---+--+-+--+-+-+
> | 1 | SIMPLE | bite_event_log | ALL | id_file_set | NULL | NULL
> | NULL | 1213328 | Using where |
> ++-++--+---+--+-+--+-+-+
This may differ from the original quoted version because the
statistics change or the results are cached, but the explain output is
largely the same.
>
> However, aside from the straight numerical one above (2nd down), this
> version is the second best performing!?
>
> And furthermore, using a word string like "bogus" significantly
> out-performs another string such as "-1". Huh?!? WTF?
> It's like mySQL was "smart enough" to know that "bogus" could be
> dropped, whereas it's not smart enough to know to drop "-1",
> despite the fact that the id_file_set column is an unsigned integer.
>
> mysql> explain select * from bite_event_log where id_file_set in
> ('bogus',2412948) limit 1;
> ++-++---+---+-+-+--+--+-+
> | id | select_type | table | type | possible_keys | key
> | key_len | ref | rows | Extra |
> ++-++---+---+-+-+--+--+-+
> | 1 | SIMPLE | bite_event_log | range | id_file_set |
> id_file_set | 5 | NULL |2 | Using where |
> ++-++---+---+-+-+--+--+-+
>
>
Not sure whats going on here, I am guessing that 'bogus' is cast at some point.
Ewen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
