| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14
Comments:
<0> frank: that's what the MySQL CLI interface calls ITSELF when you start it.
<0> frank: why not?
<1> unless you know exactly what you're doing
<1> but go ahead
<0> aren't I screwed at the moment anyways?
<0> I just want to change host from 192.168.1.* to something more sane, as it should not be 192.168.1.x
<2> yeap
<0> since that machine does not even have a 192.168 address
<0> nor is it on a LAN
<3> MrBIOS-work: so, update mysql.user set Host='localhost' where User='root'
<0> how do I change it manually?
<4> i've got a php application, and i've got a bottleneck that's keeping me from processing more then 300 requests/second... how do i figure out where it is?
<4> (be that, in mysql, or in the php app)
<3> start by running your common queries through explain. Might also look at the slow query log
<3> !m gezick explain
<5> gezick: (EXPLAIN Syntax (Get Information About a SELECT)) : http://dev.mysql.com/doc/mysql/en/EXPLAIN.html
<3> !m gezick slow query log
<5> gezick: (The Slow Query Log) : http://dev.mysql.com/doc/mysql/en/Slow_query_log.html
<4> inviso_: i've explained all my queries
<4> and they're all good.
<4> also, no slow queries
<3> gezick: Indexes look good?
<4> yep
<3> you may be looking at some design problems, but short of that, you're probably hitting hardware limits. 300 requests per second is pretty decent if it's even moderately complicated and you're running basic hardware
<4> i think that it might be in mysql's networking, or perhaps, in the amount of memory in the server
<3> Have you tuned the mysql server's memory specs?
<4> i'm running on 3 dual opterons with 2gig each
<3> also, do you have reverse dns lookup turned on?
<4> the mysql process is only on one of the machines though
<4> reverse lookups are off.
<6> mysql isnt being strict about delimiting the results of this command line query
<4> i haven't really done much with the emeory specs (you're talking about buffers and whatnot yes?)
<3> gezick: yep, and caches
<7> ah talking about the cli.. will mysql EVER fix the "ctrl-C exits the shell" behaviour? this is so annoying and broken...
<4> inviso_: how should i go about tuning them?
<3> key buffer and query_cache probably being the first two to hit.. Also, thread_buffer would be good
<3> paq: it could be worse
<7> inviso_, hmm.. how?
<8> Hi folks. I set up mysqld on my laptop ages ago and have since completely forgotten the p***word. Is it possible to reset it?
<3> gezick: I think there's a tuning doc up. I'll give you a couple links to look at
<3> !m gezick tuning
<5> gezick: (Tuning Server Parameters) : http://dev.mysql.com/doc/mysql/en/Server_parameters.html
<3> !m gezick server system variables
<5> gezick: (Server System Variables) : http://dev.mysql.com/doc/mysql/en/Server_system_variables.html
<3> !m RiotGeek reset root
<5> RiotGeek: (How to Reset the Root P***word) : http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html
<3> paq: sqlplus
<8> Thanks.
<7> inviso_, ok.. you have a point.. but still.. doesnt anyone consider this worth fixing?
<3> paq: Might not be :) Have you checked the bug reports to see if it was addressed?
<7> inviso_, too lazy.. ;)
<4> inviso_: do those docs explain how to tell where the problems are?
<3> paq: Excellent. I'm happy to serve as your mentor. bugs.mysql.com
<7> inviso_, hehe.. there's a ticket.. since 2003
<3> gezick: nope. show status on the mysql cli will give you some indication, but it would really depend upon your usage. Bump the key_buffer regardless if it's still set low. It holds indexes in cache. Also note that mysql only uses as much ram as you allow it to.
<3> paq: yep, expected as much
<4> inviso_: also, would it be at all helpful to replicate so that mysql i srunning on all the machines?
<8> inviso_: Will setting the root pw to NULL make it p***wordless? It's an unimportant db only accessible on localhost of my laptop.
<0> inviso, I finally got it
<3> gezick: perhaps. If you're mostly read, it would help a fair amount. Do you have a hardware load balancer?
<3> MrBIOS-work: excellent. What was it?
<0> I had to do "UPDATE mysql.user SET host='%' WHERE user='root'; "
<0> the host had been set to 192.168.1.x
<0> but it wasn't working
<6> mysql isnt being strict about delimiting the results of this command line query
<4> inviso_: what should i be looking for in show_status?
<0> even though both this and that machine have IPs starting with 192.168.1.x
<3> MrBIOS-work: right. localhost didn't help?
<0> at least I'm in now jhowever
<6> i need the delimiting to be of uniform width for each column
<3> gezick: the key_buffer read/write ratio to start with
<4> inviso_: no, i'm using the balancer in fastcgi/lighttpd
<4> inviso_: i don't see that in show_status
<4> er, show status
<3> gezick: show status like "key_%"
<0> inviso, it does, and its been set to localhost
<8> invisio_: Well, seriously, if the data isn't important and someone would have to have physically stolen my laptop in order to get at it just seems like an annoyance. Is getting access to mysql likely to allow them to do any more damage than has already been done?
<4> inviso_ reads/writes are 4:1
<3> gezick: use the _request ones
<4> 3:1
<3> that's so so hot. What's unused at?
<3> *not so hot
<4> about the same as write_requests
<3> RiotGeek: I think NULL will work, but it's probably better to set the user/p*** in ~/.my.cnf
<3> gezick: that's wierd. You've got a low hit ratio but your key_blocks aren't full yet?
<4> | Key_blocks_not_flushed | 0 |
<4> | Key_blocks_unused | 6653 |
<4> | Key_blocks_used | 56 |
<4> | Key_read_requests | 17832 |
<4> | Key_reads | 195 |
<4> | Key_write_requests | 6172 |
<4> | Key_writes | 57 |
<4> sorry to flood, but i figured that would be more useful
<3> gezick: how about select status like "Questions"?
<3> gezick: yeah, throw those in a pastebin
<4> 1119068
<3> something is suspect. Throw the whole show status up in a pastebin
<4> http://pastebin.com/531467
<4> er, holdon
<4> http://pastebin.com/531470 try that now
<3> I'm at 1 million questions on one of my servers and 313K read_requests. Obviously, 17K is pretty low. I'd guess you're not hitting indexes as often as you thought
<3> otoh, I may not be reading it right :)
<3> gezick: Ok, how much total data are we talking about? Megs? Gigs?
<4> i was testing it on a simpler set of queries before, now i'm testing it with more complete stuff
<4> and o course, that's slower than 300 reqs/s
<4> http://pastebin.com/531473
<4> total data here is megs
<3> You probably don't need a huge key_buffer then. It's meant to cache indexes so size it appropriately. Do you have queries that pull the same data over and over?
<3> also, you might consider bumping thread_cache_size up a bit
<3> it's probably at 0 currently
<4> no, i mean, i'm running the testing with ab
<4> so, where should i put key_buffer, and thead_cache_size ?
<3> see the server system variables link I gave you earlier
<4> what would be good values to start with
<4> ok
<2> can my engine by InnoDB and my type by MyISAM?
<3> UncleD: huh?
<2> well, I was reading http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
<3> gezick: depends on your data. You need to keep room for the sort_buffer/read_buffer/etc as well as the query_cache
<2> and after each table, it specifies ENGINE=InnoDB
<3> UncleD: yup. InnoDB is needed for FK
<2> I want to be able to specify referential foreign keys
<2> invisio_: Ok cool.
<2> invisio_: Can my TYPE=MyISAM still?
<3> TYPE is deprecated and replaced by ENGINE
<2> aha
<2> thank you
<3> np
<4> inviso_: when you say 'keep room for' what do you mean?
<9> reserve storage space, clearly
<4> Sneaky_Bastard: i would've guessed memory
<3> gezick: memory. many of the buffers are allocated per connection. You want to calcuate usage for perhaps 80% of your ram so you're making good use of it
<3> Sneaky_Bastard: strike one :)
<2> inviso_: what is different in MyISAM and InnoDB ?
<3> !m UncleD storage engines
<5> UncleD: (MySQL Storage Engines and Table Types) : http://dev.mysql.com/doc/mysql/en/Storage_engines.html
<2> thx
<4> inviso_: and will that help with getting up index hits as a percentage of questiosn?
<4> and, further, should i be allocating 100% of the memory on the machine running mysql for running mysql? or can i have lighttpd running on it too?
<3> gezick: I'm not sure. If I'm reading the numbers correctly, it either can't cache the indexes because maybe it's too large? (pure speculation) or your queries don't use indexes
<3> gezick: that's a decision you get to make :) At least leave a little head room for OS though
<4> i mean, should i be running php processes on the same machine as i'm running mysql on?
<3> gezick: if you have room and resources for it, sure :)
<4> inviso_: that's what i'm trying to figure out! :)
<10> is there a way to import data into myself from a csv file?
<11> !m Simkin load data infile
<5> Simkin: (Problems with NULL Values) : http://dev.mysql.com/doc/mysql/en/Problems_with_NULL.html
<11> argg....
<11> !m Simkin load data
Return to
#mysql
or
Go to some related
logs:
definition of an ostrege
Dapper drake automatics
#centos
XFree development packages
#osdev
ubuntu compile gaim
#asm
#perl
linux alternative-file-browser
#css
|
|