#mysql
25 October 2007
Total 27 pages. You are browsing page 3/27.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
01:04 <****> Surely you can just check in the schemas if there are ny
01:04 <****> nils_: Ok, so this is saying that in some cases a bigger sort buffer is detrimental to perforamance
01:05 <****> MarkR42: I have no central repository of schemas to check..
01:05 <****> MarkR42: my mysqldump'ed backup files are my best bet. :)
01:06 <****> YOu can also dump out the schema out of your dev box
01:06 <****> That is a quick operation
01:06 <****> regardless, zgrep of backup.sql.gz files is done - no Innodb
01:07 <****> If currently turned on, disabling innodb is unlikely to regain a lot of RAM, unless it's been tuned really big
01:08 <****> you really need to run that mytop tool (or just type "show processlist" a lot)
01:08 <****> and get that slow query log sensitive enough that you can see slow queries
01:10 <****> and make sure you're actually caching queries
01:10 <****> oh, wait, i stand corrected.... grep -i, and I have "ENGINE=InnoDB" in at least on DB...(but none of the ones relevant to the discussion here)
01:10 <****> "show global status" gives info about query caching effectiveness
01:10 <****> Trengo: the query cache shows to have data in it - I presume that means it's being used?
01:11 <****> It may be being used, but not being effective
01:11 <****> i.e. if it has very few hits
01:11 <****> I believe it can actually be counter productive under some circumstances (other than wasting memory)
01:12 <****> I have all these mysql-bin.00XX type files in the mysql data dir, and they are huge, taking up heaps of space. wtf are these ?
01:12 <****> it's using 47M of 128M possible, and I'm showing hits 4.9/s (which sounds good, given the 40 q/s rate the server usually floats under).
01:12 <****> That sound good/bad?
01:13 <****> I think the query cache only affects SELECT commands, so if you divide the number of query hits by the number of select commands over some time
01:13 <****> you will see the % of SELECTs which are being cached
01:13 <****> 633 waited table locks, 935,000 immediate. 176 of 256 tables open.
01:13 <****> If it's less than 50% it might be better off
01:14 <****> jsoftw: they are binary log files used for replication and disaster recovery.
01:14 <****> hello all
01:14 <****> I have a MYSQL replication question.
01:14 <****> ki77a77: ok.
01:14 <****> Do you have monitors monitoring response times (max, average, etc) ?
01:14 <****> jsoftw: see http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html to get rid of them if you're not using them
01:15 <****> I am doing a round robin replication with all servers as masters and slaves. Each slave daemon failes because it is looking for the wrong file.
01:16 <****> ex. mysql-bin.000024 instead of mysql-bin.000002
01:16 <****> kevev: You know you can only do that sensibly with a maximum of two servers, right?
01:17 <****> markR42: why is that?
01:17 <****> MarkR42: no monitors I'm aware of, other than whatever MySQL monitors on it's onw..
01:17 <****> because if you have three or more, and one server fails, the queries hitting the other server(s) build up part way around the ring, and it's just a mess
01:17 <****> er own even..
01:18 <****> BrotherJack: No web response time monitors?
01:18 <****> ki77a77: I just removed the reference to them in my.cnf, looked at fstat, and removed them
01:18 <****> BrotherJack: Write some monitors, then when they're written, write some more. Write monitors until your ears bleed.
01:18 <****> :)
01:18 <****> Done.
01:18 * jsoftw looks anyway
01:18 <****> markR42 that is fine. when I bring the server back online it should sync.
01:18 <****> Once your screen is awash with blinking lights graphs and dials, you will still not have enough monitoring
01:19 <****> I have my query as http://pastebin.com/m27000db9
01:19 <****> kevev: But if the server that's offline can't be brought back online, the recovery scenario for the others is nontrivial
01:19 <****> kevev: I'd go so far as to say it's impossible to get right in practice
01:20 <****> jsoftw: if you've altered my.cnf you probably have to restart mysqld...
01:20 <****> understand.
01:20 <****> kevev: I am assuming that you only EVER write to a maximum of one of these servers, the others are readonly, or effectively readonly
01:20 <****> ki77a77: yeah I havbe
01:20 <****> MarkR42: correct
01:20 <****> The records in programs are about 247 but then also the query hangs when I try to run the query through PHP
01:21 <****> What optimization can be performed on the same
01:21 <****> From what i understand, and from my own experiments, reading etc, it is a better idea to have no more than two in a replication ring
01:21 <****> I ahve even Tried Straight_join but then also it hangs
01:21 <****> If you need further readonly replicas, you can hang them off the ring as extra slaves to one side
01:22 <****> We have a single master and several readonly slaves
01:22 <****> with a robust procedure for doing fairly quick manual failover
01:23 <****> MarkR42. I am aware of this. I am implementing something completely new. I just need to figure out why replication is not working.
01:23 <****> MarkR42: one more question, before I have to call this a day - is a very low use rate of the key buffers an indication of anything?
01:24 <****> Your key buffer could be bigger than the sum total of all keys
01:25 <****> True. But it's only using like 4megs .
01:25 <****> But I think myisam mostly doesn't do key scans, so the whole of an index generally doesn't get put in the key buffer, even if it does, it's still scanning just one index of perhaps many
01:25 <****> Which variable are you looking at? key_blocks_used?
01:26 <****> mysqlreport, the section on "Key", Buffer used 4.86M of 128.00M
01:26 <****> Perhaps your app is doing a lot of flush tables, or there are just too many distinct tables; I *think* the indexes are flushed for a table when it's closed
01:26 <****> I don't know what mysqlreport is, what does "show global status like 'key%' give ?
01:27 <****> key blocks used 5730, key_blcoks_unused 110246
01:27 <****> so quite a few unused then
01:27 <****> perhaps your indexes aren't actually that big on tables which use them
01:28 <****> Look at the sum total of your .MYI files
01:28 <****> I can't think of a way that the key buffer could include more blocks than all .MYI files
01:28 <****> MarkR42: could be... I'm possibly wondering if my web-app in qeustion has failed to index some key elements it SELECT's on a regular basis..
01:28 <****> (perhaps some for temp tables)
01:28 <****> The key buffer does NOT store data pages, myisam relies on the OS for that
01:29 <****> I have 487megs of MYI files
01:29 <****> And in any case, indexes aren't necessarily good
01:29 <****> that's for everything on the server, not just the web-app in question.
01:29 <****> Perhaps there are a lot of superfluous indexes which are never being used
01:30 <****> (but they would still be populated a bit by writes)
01:30 <****> timestamp DEFAULT CURRENT_TIMESTAMP,
01:30 <****> lo
01:30 <****> is that not right?
01:30 <****> MarkR42: yeah, most of the databases on this server are rarely used stuff. There are like 5 which account for the vast majority of the DB usage here.
01:31 <****> i wanna a data onto table that save the TIMESTAMP
01:32 <****> http://dev.mysql.com/doc/refman/5.0/en/timestamp.html
01:32 <****> :D
01:32 <****> BrotherJack: ANyway I still think you should decrease the slow query time and see what those slow queries are doing
01:33 <****> And get mytop going
01:33 <****> I have to sleep now
01:33 <****> good luck
01:33 <****> MarkR42: thanks very much for your help. I deeply appreciate it.
01:34 <****> (and nils, and trengo, and probably a few other people who have pitched in to help me with my mess here - thanks to you also, very much)
01:34 <****> BrotherJack cheers and good luck
01:36 * BrotherJack has to get out of here too, has to be somewhere like 10 minutes ago..
01:38 <****> how can i add a default value to a column
01:42 <****> varek: update table set cola = cola + somevalue
01:42 <****> oops, impatient.
Total 27 pages. You are browsing page 3/27.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last