#mysql
01 January 2008
Total 13 pages. You are browsing page 1/13.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
--- Log opened Tue Jan 01 00:00:04 2008
00:09 <****> can anyone tell me why the same query on two different machines which are using the same database, same schema, same indexes, etc, take 2 seconds on one and over 4 minutes on the other? here's the details http://pastie.caboo.se/133664
00:09 <****> if both machines are using the same index, why does only one of them have "Using where; Using index; Using temporary; Using filesort" in the description of describe query, while the other one simply shows "Using filesort"
00:10 <****> same version of mysql?
00:11 <****> pretty close
00:11 <****> the slower machine is running 5.1.22-rc-community, the faster is running 5.1.12-beta-log
00:12 <****> opcode: massively different cpu's, like multi-core vs p4?
00:12 <****> hi
00:12 <****> threnody: no, not a huge difference in cpu's. the problem is that the query on the slower machine is not using the index properly
00:12 <****> opcode is 5.1.22 the fast one?
00:13 <****> ebergen: 5.1.12 is the faster one
00:13 <****> interesting
00:13 <****> when i do a query on mysql, it blocks the writes which tends to make the system unstable (very large number of writes / second) any way to fix this?
00:14 <****> so my question is why is one machine not using the index while the other one is? they both have the same schema
00:14 <****> jhiver myisam?
00:14 <****> i think so, i didn't specify db type when creating the table
00:14 <****> there you go
00:15 <****> ok so now the big question is, how do i change the table type, and which one should i use?
00:16 <****> jhiver: I wonder why innodb wasn't the default...
00:16 <****> plz how can test if a column field is empty
00:16 <****> for historical reasons pehaps, i had no idea myisam would create this kind of issues
00:16 <****> or blank
00:16 <****> threnody it's owned by oracle :)
00:16 <****> IS NULL
00:16 <****> =''
00:16 <****> thank's
00:17 <****> jhiver: you can use ALTER TABLE to switch to innodb
00:17 <****> ok
00:17 <****> i don't care what anyone says.. innodb is way faster than myisam on larger tables.. people always say use myisam on larger tables but that's horse shit :)
00:17 <****> i suppose it's best to stop the writes while i do this =)
00:17 <****> only on seqscans is myisam faster :)
00:17 <****> jhiver: if your startup script doesn't say something like skip-innodb
00:17 <****> i don't know, it's standard debian etch
00:17 <****> TapoutT it is bullshit, they are faster at different things
00:18 <****> let me find out
00:18 <****> rarely is myisam faster on larger tables, smaller sure.. but innodb kicks ass :)
00:18 <****> innodb should be default everything :)
00:18 <****> TapoutT myisam should be faster for secondary key reads on large tables
00:19 <****> especially since myisam uses less space so you're going to keep more data in ram
00:19 <****> synapseuk:/etc/init.d# cat /etc/init.d/mysql |grep inno
00:19 <****> synapseuk:/etc/init.d#
00:19 <****> i guess not
00:20 <****> ebergen, whenever I do an index read.. my drives cap out at about 2-3MB/sec .. primary key indexes.. i've tried everything to fix it, changing over to innodb the tables go 30MB/sec minimum if need to read in
00:20 <****> usually 1-2MB/sec cap with myisam with index reads
00:21 <****> which index?
00:21 <****> jhiver: do 'show variables like 'have_innodb';'
00:21 <****> primary or secondary?
00:21 <****> primary key
00:21 <****> that's why
00:21 <****> innodb is faster for primary key reads
00:21 <****> jhiver: or 'show engines;'
00:21 <****> it also tires to write row data in primary key order so you essentially go sequential reads from the disk
00:21 <****> says YES
00:22 <****> ya but innodb responds in like 2-3 minutes on these large queries.. myisam responds in like 1- 1 1/2 hours ..
00:22 <****> i love innodb
00:22 <****> myisam has it's place tho
00:22 <****> because of the reason I just explained
00:22 <****> it's why sayign "innodb is faster " or "myisam is faster" is wrong
00:22 <****> ebergen, i'll broad swipe with that statement and stand behind it.. innodb owns ;)
00:23 <****> ok so now i can stop the writes, do an alter table, then pehaps restart the db?
00:23 <****> for good measure? =)
00:24 <****> jhiver: ALTER TABLE t1 ENGINE = InnoDB;
00:24 <****> ebergen, even with innodb overhead.. all my stuff is blazing compared to myisam .. and i had 0 locking issues.. so I dunno :)
00:24 <****> as I said before it all depends
00:24 <****> ok let me stop the writes first =)
00:24 <****> innodb is better for your workload, great.
00:25 <****> that doesn't make it better for everyone else's workload
00:26 <****> true
00:26 <****> ok
00:26 <****> that should take a while
00:26 <****> this table has millions of records
00:26 <****> I'm trying to do replication.
00:26 <****> .
00:26 <****> In the master my.cnf file I have:'
00:26 <****> log-bin=cooler_canonizer_bin
00:26 <****> .
00:26 <****> I can see the log file index, and files being written to
00:26 <****> /var/lib/mysql
00:26 <****> .
00:26 <****> On the slave, in the change master command, I said:
00:26 <****> MASTER_LOG_FILE='cooler_canonizer_bin',
00:26 <****> the two common ones are "myisam is faster than innodb" and "myisam is faster for reads and innodb faster for writes"
00:26 <****> .
00:26 <****> i think i insert like 200k records / day in it for several month or something
00:26 <****> but when I try to start slave I get:
00:26 <****> neither of which is true
00:26 <****> Could not find first log file name in binary log index file
00:26 <****> .
00:26 <****> Do I need to specify an absolute path on the slave or something?
00:27 <****> .
00:27 <****> thanks
00:27 <****> Brent_Allso1 did you really feel the need to put a . every 3rd line?
00:28 <****> sorry
00:28 <****> i'm surprised mysql doesn't eat all the CPU doing this!
00:28 <****> good
00:28 <****> Hello. I have a serious problem with MySQL Replication 5.1.x and its data nodes. Seemingly at random after hours or days of operation, one of the data nodes will have all queries in a frozen state. There are 40 queries on one of the data nodes with the 'state' being in 'end' and will not budge.
00:29 <****> For example, a simple query: UPDATE table SET a = 'test' WHERE x = '2' has been in the 'end' state for 73,000 seconds+. There are many other queries (with different tables in use) in the processlist with the same 'end' state, for around the same time (and less) listed.
00:29 <****> Killing a thread does not remove it. It is marked as 'Killed', but never terminates. New queries on tables do not work (they simply dont 'return'). http://bugs.mysql.com/bug.php?id=31791 this bug appears to be what may be happening (the symptoms anyway).
00:29 <****> Anyone have any ideas? Im at a loss :\
00:30 <****> Plasma, Your way beyond me, I'm just trying to get replication to start.
Total 13 pages. You are browsing page 1/13.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last