07:56 MarkDilley joined #geoloqi
08:16 workshop joined #geoloqi
08:51 MarkDilley joined #geoloqi
13:21 <aaronpk> oh god dammit
13:23 Loqi joined #geoloqi
13:25 Loqi joined #geoloqi
13:25 <aaronpk> omg rss bots
13:34 <aaronpk> holy hell
13:42 <plamb> any idea what the adoption of the barbird layer has been like?
13:42 <aaronpk> oh i've been meaning to ask you, it looks like the batch jobs stopped. I got a couple SMSs while in SF last week tho!
13:43 <plamb> thats awesome
13:43 <plamb> daniel is looking into it
13:43 <plamb> did they seem like decent content?
13:43 <aaronpk> yea actually
14:18 <adaburrows> aaronpk: Just saw your graph! How many users is that? Think your upgraded server setup can handle it?
14:20 dch joined #geoloqi
16:11 workshop joined #geoloqi
17:13 <aaronpk> adaburrows: heh, definitely struggling at the moment
17:46 MarkDilley joined #geoloqi
18:37 <aaronpk> !weather 99999
18:37 <aaronpk> !weather 99999
19:14 dch|away joined #geoloqi
19:41 MarkDilley joined #geoloqi
20:02 dch joined #geoloqi
20:09 IntractableQuery joined #geoloqi
20:22 <kyledrake-home> aaronpk: If you need any help with the databases let me know.
20:22 dch joined #geoloqi
20:29 dch|away joined #geoloqi
21:05 <aaronpk> kyledrake-home: gonna need to do some work on this scaling thing sooner rather than later
21:05 <kyledrake-home> aaronpk: Gotcha
21:06 <aaronpk> definitely been a good stress test
21:07 <kyledrake-home> aaronpk: I recently met a hardcore database guy at a meetup, I pinged him for some advice on good stuff for geo and he had some good advice
21:07 <aaronpk> actually it's not even necessarily the geo part that's killing it
21:08 <kyledrake-home> aaronpk: What's the bottleneck?
21:08 <aaronpk> i need better separation between storing the incoming points and querying the rest of the DB
21:08 <kyledrake-home> ah.
21:09 <aaronpk> if only so that the phones can keep logging data even if the rest is down
21:10 <kyledrake-home> Agreed, good for data integrity
21:11 <Wraithan> aaronpk: master + slaves is good
21:13 <aaronpk> yea, I had a slave ready on standby luckily but I hadn't actually switched many queries over to it yet, been doing that a bunch over the last couple hours
21:15 <kyledrake-home> aaronpk: I'll be here all night, if I can be of any help just ping me
21:17 <aaronpk> cool thanks
21:17 <Wraithan> aaronpk: what db backend do you use?
21:17 <Wraithan> I am of no use then
21:25 <aaronpk> the good news is the servers aren't hitting swap yet
21:25 <kyledrake-home> Blind thoughts incase they're useful: Perhaps a few indexes are missing? "show full processlist"? Slow query log
21:26 <aaronpk> been loking at the slow query log, but that's sometimes misleading
21:26 <aaronpk> i've been moving queries over to the slave wherever I can to take the load off the master
21:27 <aaronpk> I think the bottleneck is unfortunately in storing new points
21:27 <aaronpk> that's the analysis of show full processlist anyway
21:27 <kyledrake-home> Run this on master incase you haven't, it should give you an idea of what's going on: show full processlist;
21:27 <kyledrake-home> ah
21:27 <kyledrake-home> InnoDB or MyISAM?
21:28 <kyledrake-home> MyISAM has a full table lock
21:28 <aaronpk> I thought I had set those tables to innodb, let me check
21:28 <aaronpk> yea they're innodb
21:29 <aaronpk> gah, there might be too many indexes on that table making inserts slow
21:30 <kyledrake-home> Very possible.
21:30 <kyledrake-home> I have a command for that
21:30 <aaronpk> for what?
21:31 <kyledrake-home> If you trim down indexes you can get a boost if they're too big
21:32 <aaronpk> probably can't do that while it's running tho right?
21:33 <kyledrake-home> You can.
21:33 <kyledrake-home> Searching now
21:37 <kyledrake-home> "For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index."
21:37 <aaronpk> oh god, lol
21:37 <kyledrake-home> Yeah, exactly
21:37 <aaronpk> doesn't that create a temporary table and copy everything?
21:39 <kyledrake-home> Probably. And that last sentence makes me wonder if it even does much for InnoDB. Not recommending this one right now
21:40 <aaronpk> so the problem right now is i've got a bunch of INSERTS queued up
21:40 <aaronpk> I don't know if it's the actual insert that's slow, or if there's other things slowing down the DB
21:41 <kyledrake-home> How's the CPU/Mem/HD throughput
21:41 <aaronpk> loadavg is 3.11
21:42 <aaronpk> disk is writing at 500k-3M/second
21:43 <kyledrake-home> That's pretty high. top -s 1 ?
21:43 <kyledrake-home> -s just makes it update every second
21:43 <kyledrake-home> -s 1
21:43 <aaronpk> oh, didn't like it for me
21:44 <kyledrake-home> ah right linux.. -d 1
21:44 <aaronpk> it's -d 1 apparently
21:44 <aaronpk> mysql at the top, but only at 3%
21:44 <kyledrake-home> Memory?
21:45 <kyledrake-home> What's RES saying
21:45 <aaronpk> 58m for mysql
21:45 <kyledrake-home> That's the problem. We need to juice that up. I bet the config is set to factory
21:45 <kyledrake-home> How much memory is on the system?
21:46 <aaronpk> it's got 4gb
21:46 <kyledrake-home> Dig up your my.cnf in /etc and look for key_buffer
21:46 <aaronpk> it's not in my.cnf so it's using the default
21:47 <aaronpk> show variables shows: | key_buffer_size | 8384512
21:47 <kyledrake-home> Aha.
21:47 <kyledrake-home> Alright
21:47 <kyledrake-home> in [mysqld]
21:47 <kyledrake-home> key_buffer = 2048M
21:48 <kyledrake-home> That will allow mysql to take 2 gigs of the memory.. if you're using a lot of memory then lower that a bit
21:48 <aaronpk> can I change that with SET GLOBAL key_buffer_size=204M?
21:48 <kyledrake-home> Really juice that guy up. Minimum 768M
21:48 <aaronpk> or does it need a mysql server restart?
21:48 <kyledrake-home> It may. let me check
21:49 <kyledrake-home> There's some other numbers to turn up but this is the big one
21:49 <kyledrake-home> You can
21:49 <kyledrake-home> SET GLOBAL key_buffer=768M;
21:50 <aaronpk> so it's key_buffer not key_buffer_size?
21:50 <kyledrake-home> It's not going into my mysql. I know that's the right for the my.cnf
21:51 <kyledrake-home> what version of mysql
21:51 <aaronpk> 768 megabytes to bytes
21:51 <Loqi> !calc 768 megabytes to bytes
21:51 <Loqi> 768 megabytes = 805 306 368 bytes
21:51 <aaronpk> thanks loqi
21:51 <Loqi> you're welcome
21:52 <kyledrake-home> haha
21:52 <kyledrake-home> There's another one we'll need too
21:52 <kyledrake-home> innodb_buffer_pool_size
21:53 <aaronpk> right now it's 8388608
21:53 <aaronpk> 8388608 bytes to kilobytes
21:53 <Loqi> !calc 8388608 bytes to kilobytes
21:53 <Loqi> 8 388 608 bytes = 8192 kilobytes
21:54 <kyledrake-home> key_buffer_size will work, yeah
21:54 <kyledrake-home> That's from the my.cnf on my hackintosh that I use for power computing
21:54 <aaronpk> ok key_buffer_size is now 768M
21:55 <kyledrake-home> alright, let me check the innodb one
21:56 <kyledrake-home> See if the key_buffer_size fixes it, if not you might want to jump innodb_buffer_pool_size too. Keep that number under key_buffer_size.. 70% perhaps
21:56 <aaronpk> damn, Variable 'innodb_buffer_pool_size' is a read only variable
21:57 <kyledrake-home> Yeah, that one would likely require a restart
21:57 <kyledrake-home> Jumping these buffer sizes make a huge different. MySQL doesn't scale up unless you tell it how much to take to avoid crashing machines
21:57 <kyledrake-home> query_cache is nice too but kindof worthless here since it resets every time a write occurs
21:58 <aaronpk> k i'm gonna have to restart the master
21:58 <kyledrake-home> I wouldn't be surprised if key_buffer_size fixes the problem considering there's no I/O bottlenecks elsewhere.. this feels like a software config issue
21:58 <aaronpk> iirc the slave figures itself out when the master comes back
22:00 <kyledrake-home> There's some tricks to improve writing by upping the data file extend chunks, but I don't think that's the problem here (yet)
22:00 <aaronpk> ok so now the trick is figuring out if this worked
22:00 <kyledrake-home> Check throughput. See if disk/cpu/mem is going up
22:01 <aaronpk> disk looks about the same, but apache isn't queuing up requests yet
22:02 <kyledrake-home> Check memory. That MySQL process should have gone way up
22:02 <kyledrake-home> Or should be in the process of going up
22:02 <aaronpk> now at 144M res
22:03 <kyledrake-home> Much, much better. So what you just allowed mysql to do is dump more of the table data into memory. So it can do more in-memory query lookups
22:04 <kyledrake-home> You really can't turn that number up enough.. if you have another gig let it have it
22:04 <aaronpk> ok I might try it with the innodb too
22:04 <aaronpk> ok great, i'll give it more
22:04 <aaronpk> yea it's starting to backlog again
22:04 <kyledrake-home> Just keep it <70-80% of system memory
22:05 <aaronpk> i'll give each 1.5gb
22:06 <adaburrows> wonders if your disk throughput is maxed out.
22:06 <kyledrake-home> adaburrows: 1-3MB/s, doesn't sound like it
22:06 <adaburrows> oh, is that all it's at now?
22:06 <kyledrake-home> Yeah.
22:06 <kyledrake-home> Oh also: key_buffer_size allows indexes to be sent to memory rather than read from disk
22:06 <kyledrake-home> Huge huge huge
22:06 <Loqi> that's what she said!
22:06 <kyledrake-home> hah
22:07 <adaburrows> Yeah, that will def be a huge boost.
22:07 <kyledrake-home> After this, show full processlist and if it's taking more than a second, run a describe on the query and look for the nefarious TEMPFILE
22:07 <aaronpk> damn wifi here keeps kicking me off
22:08 <kyledrake-home> I'm -still- looking for the damn index optimizer check query
22:08 <kyledrake-home> There's a mysql command that will scan your table and tell you what size your index can be, and then you can alter it and it trims the index
22:09 <kyledrake-home> Any queries with a huge rows examined implies a missing index
22:12 <aaronpk> hmm...everything stopped
22:12 <kyledrake-home> WTF?
22:13 <adaburrows> Everything?
22:13 <aaronpk> i'm not seeing many requests come in
22:14 <aaronpk> too many screen sessions
22:15 <aaronpk> oh damn, screen inside of screen #fail
22:15 <kyledrake-home> adaburrows: No, but that's cool. So there's this mysql command that will actually tell you how big your columns can be without losing data
22:16 <aaronpk> it's either working great or it's working terribly...
22:16 <kyledrake-home> aaronpk: How can we test?
22:16 <kyledrake-home> My money's on great.. check the memory on mysql again
22:16 <aaronpk> something's wrong, it's returning 500 errors for the location update method, let me check holdon
22:19 <aaronpk> oh god what? Internal error: Database error: Unknown table engine 'InnoDB'
22:19 <kyledrake-home> What did we change?
22:19 <kyledrake-home> only key_buffer?
22:19 <aaronpk> innodb_buffer_pool_size=1524M
22:19 <kyledrake-home> turn that off for now
22:19 <kyledrake-home> innodb
22:20 <kyledrake-home> err, uncomment innodb_buffer_pool_size=1524M
22:20 <aaronpk> ok that's back
22:20 <kyledrake-home> jesus
22:21 <kyledrake-home> What was key_buffer_size set to?
22:22 <kyledrake-home> I think innodb_buffer_pool_size needs to be lower than key_buffer_size by quite a bit
22:22 <kyledrake-home> But key_buffer_size should itself speed things up dramatically, so hold off on the innodb one for now
22:25 <aaronpk> ok so far so good
22:25 <aaronpk> that's going to help inserts too?
22:25 <kyledrake-home> Not likely, but it will make all other operations use substantially less disk access
22:25 <kyledrake-home> It gives mysql permission to dump data into cache. It will dump table data and indexes too
22:25 <kyledrake-home> Check your mysql error log
22:26 <kyledrake-home> Make sure there's nothing crazy sounding in there
22:29 <kyledrake-home> There may be something about how it couldn't allocate all that memory which would confirm my key_buffer_size > innodb_buffer_pool_size hypothesis
22:31 <kyledrake-home> Did you change my.cnf or uncomment/add more than innodb_buffer_pool_size? There's apparently a skip-innodb in my.cnf in Debian by default
22:32 <aaronpk> no, just added the innodb_buffer_pool_size
22:34 <aaronpk> ok i think it's getting better, but not quite there yet
22:35 <aaronpk> Innodb_buffer_pool_pages_free shows 0, that means I shoud give it more, right?
22:35 <kyledrake-home> Awesome. In your /var/mysql, there should be a *.err file which may explain that innodb error.
22:36 <kyledrake-home> The buffer is the big one. Let me research the others
22:36 <aaronpk> hm I don't have a .err file
22:37 <kyledrake-home> You are correct. That number needs to go up
22:37 <aaronpk> i'll try 256m
22:37 <kyledrake-home> Yeah try that
22:38 <aaronpk> ok not getting the crazy innodb error
22:38 <kyledrake-home> *phew*
22:38 <kyledrake-home> Yeah that was it. You can juice that to 768Mish
22:38 <kyledrake-home> or more of course.. keep it to 60-70% of key_buffer_size
22:38 <aaronpk> now 15000 pages free
22:39 <kyledrake-home> Much better
22:39 <aaronpk> k let's see if that makes a difference
22:40 <kyledrake-home> Keep checking innodb_buffer_pool_size, it feels a little low to me there. It's much higher on my hackintosh
22:41 <kyledrake-home> innodb_buffer_pool_size | 8388608
22:41 <aaronpk> 8388608 bytes to megabytes
22:41 <Loqi> !calc 8388608 bytes to megabytes
22:41 <Loqi> 8 388 608 bytes = 8 megabytes
22:41 <aaronpk> 8388608 kilobytes to megabytes
22:41 <Loqi> !calc 8388608 kilobytes to megabytes
22:41 <Loqi> 8 388 608 kilobytes = 8192 megabytes
22:41 <kyledrake-home> ah, sorry, wrong var
22:42 <aaronpk> 13927 pages free, so it's definitely caching things now
22:42 <aaronpk> i'm pretty sure that sped this up a ton
22:42 <kyledrake-home> :)
22:42 <adaburrows> Good job guys!
22:43 <kyledrake-home> adaburrows: Thanks!
22:43 <kyledrake-home> It's something else.. it's doing an excellent job of hiding from me.
22:43 <kyledrake-home> It's a modifying command anyways, so maybe a little dangerous for the moment
22:44 <adaburrows> kyledrake-home: such is the case of interesting features. And dangerous commands in this case == not always good.
22:44 <adaburrows> I've got to head out.
22:44 <kyledrake-home> adaburrow: Have a good night!
22:45 <adaburrows> kyledrake-home: I
22:45 <adaburrows> will be backk
22:45 <aaronpk> I think the bottleneck I am seeing now is due to SSH session lag!
22:46 <kyledrake-home> I hate that.
22:47 <aaronpk> misses his home super fast comcast connection
22:49 <kyledrake-home> I want to see that CPU/memory/Hd throughput go up. That's what will convince me this is working
22:50 <kyledrake-home> brb
22:50 <aaronpk> iotop shows a pretty consistent 430kb/s write now
22:54 <kyledrake-home> HD lowering would not shock me.. less HD reads due to memory cache
22:55 <Wraithan> aaronpk: do you use htop
22:56 <kyledrake-home> I would expect your cpu and/or memory to go up and hd to lower
22:56 <aaronpk> i haven't, just top and iotop
22:56 <aaronpk> mysqld is definitely taking more RAM now, but not much more cpu yet
22:57 <kyledrake-home> I'm 90% sure it was the key_buffer_size and/or innodb_buffer. That's almost always the problem on stuff like this
22:57 <kyledrake-home> I've probably run into this 20 times in my career with mysql
22:57 <kyledrake-home> The defaults are terrible
22:58 <Wraithan> htop's cpu chart tells you how much of it is user/kernel/io
22:58 <kyledrake-home> And yeah, that IO is paltry.. I bet that drive can handle >30MB/s
22:58 <Wraithan> depends on whether it is bare metal or not
22:59 <kyledrake-home> Wraithian: Innodb will incur a hit but not that substantial
22:59 <Wraithan> if it isn't bare metal then the type of virtualization and the virtualized scheduler determine IO
22:59 <aaronpk> cool, htop is neat
23:00 <Wraithan> aaronpk: if you hit ? you can see the color chart for what the different cpu colors mean
23:01 <Wraithan> aaronpk: and you can directly strace processes from inside of htop
23:01 <Wraithan> which can be really handy
23:04 <kyledrake-home> Another thing that may be going on is thread_concurrency, which needs to be set to NUMBER_OF_CORES*2
23:05 <kyledrake-home> It's set to 8 on my quad core
23:05 <kyledrake-home> How's it looking?
23:06 <kyledrake-home> I'm getting a 500 when I try to login to geoloqi.com
23:06 <kyledrake-home> Connection failed: SQLSTATE[HY000] [2003] Can't connect to MySQL server on '127.0.0.1' (111)
23:07 <aaronpk> one sec, retsart ling slave
23:08 <aaronpk> something broke on the slave InnoDB: Error: tried to read 671744 bytes at offset 0 212992.
23:08 <kyledrake-home> uncomment the innodb config
23:08 <aaronpk> looks better
23:08 <aaronpk> what was that about?
23:09 <kyledrake-home> InnoDB is picky, it doesn't like when you change things sometimes
23:09 <kyledrake-home> I'm investigating
23:10 <aaronpk> k, that's not critical right now
23:10 <aaronpk> just wanted to get the slave up to spec too
23:10 <kyledrake-home> Yeah
23:10 <aaronpk> but it seems to be a lot better anyway
23:10 <aaronpk> most of the reads are on the slave now
23:10 <kyledrake-home> is there really no mysql.err present?
23:11 <aaronpk> not as far sas I can tell
23:11 <kyledrake-home> ahh, debian sends to syslog
23:11 <aaronpk> hmm, still a little slow, but I think it's an improvement
23:12 <kyledrake-home> sudo tail -f /var/log/syslog
23:13 <aaronpk> I have /var/log/messages
23:14 <kyledrake-home> Fuck.
23:14 <kyledrake-home> Back up your database.
23:14 <aaronpk> that's the slave luckily
23:14 <kyledrake-home> That drive is dying
23:14 <kyledrake-home> It's failing to read the sector
23:15 <kyledrake-home> And it couldn't recover
23:15 <kyledrake-home> There could be corruption on that database
23:15 <aaronpk> this will be interesting
23:16 <kyledrake-home> It's also possible that the master is having slowdowns from waiting for the slave causing blocking io, but I'm not sure, mysql replication behavior is unknown to me
23:16 <kyledrake-home> I think that's your real problem here
23:16 <aaronpk> I don't think the master slows down with slow slaves
23:16 <kyledrake-home> aside from the buffer size
23:16 <kyledrake-home> I wouldn't expect it to
23:16 <aaronpk> strangely those errors only cropped up between 23:03 and 23:07
23:16 <kyledrake-home> But it will slow down your redirected queries
23:17 <kyledrake-home> Drive is working harder, it revealed the problem
23:18 <kyledrake-home> I recommend pushing your queries back into master. I think the key buffer size will dramatrically speed things up there. A drive failing on IO will shut down those queries
23:18 <kyledrake-home> Just for sanity might want to check syslog on master too
23:19 <kyledrake-home> dmesg might have more information too
23:19 <kyledrake-home> That drive will need to be replaced very soon
23:19 <kyledrake-home> Is that your server or is somebody else running it
23:20 <kyledrake-home> What drive(s) are in it
23:21 <aaronpk> it's actually kind of old
23:22 <kyledrake-home> Ah, ok.
23:22 <Wraithan> aaronpk: colo'd ?
23:22 <kyledrake-home> That drive needs to get replaced very soon. It could fail any day, and there could be corruption
23:22 <aaronpk> Wraithan: yea, in the pittock building
23:23 <Wraithan> aaronpk: I can spare a 1TB drive that is good if you need one
23:23 <aaronpk> yea I might try moving some things to an EC2 server
23:23 <kyledrake-home> I am available to physically replace that drive as soon as tomorrow morning if you'd like me to.
23:23 <kyledrake-home> Obviously good to plan for it first
23:23 <Wraithan> actually, I have 2 1TB drives I can spare, if you want them for raiding and stuff,
23:24 <aaronpk> ok i'm going to have to think about how to do that
23:24 <aaronpk> thanks, I think I have enough spare drives lying around tho actually
23:24 <Wraithan> Alrighty
23:24 <aaronpk> the main problem with this is that it's not easy to clone the master to set up a new slave
23:25 <kyledrake-home> No, it would have to be re-configured. Any stuff on slave would need to be backed up
23:25 <kyledrake-home> It may be best to wait.. as long as all traffic is going through master it shouldn't be an issue. Worst case scenario cut the cord to slave and do a backup
23:27 caseorganic joined #geoloqi
23:28 <caseorganic> !recap
23:30 <aaronpk> do you have a trick for setting up new slaves?
23:31 <kyledrake-home> For mysql? no.
23:33 <aaronpk> I think i'm going to need a new solution for storing this location data so that it doesn't kill the rest of the server
23:34 <aaronpk> they recommend cloning an existing slave to set up a new slave
23:35 <kyledrake-home> aye.
23:35 <aaronpk> so hopefully the current slave is not corrupt so I can clone it
23:35 <kyledrake-home> You can't just drop in another?
23:35 <aaronpk> otherwise I have to stop the master
23:35 <aaronpk> and clone from it
23:35 <kyledrake-home> ah.
23:36 <aaronpk> mysql replication works by replyaing queries on the slave
23:36 <aaronpk> all queries are logged in the master, and the slaves get sent the queries
23:36 <aaronpk> so in order to maintain consistency, you have to start the save from the same point the master started logging
23:37 <kyledrake-home> You can't rebuild it from the binary log on master?
23:37 <aaronpk> assuming you have a binary log from day 0 you could
23:38 <kyledrake-home> Ah.
23:38 <kyledrake-home> I take it there isn't one
23:39 <kyledrake-home> So of course, one way to do this would be to dump master out with mysqldump, and re-add it to rebuild the binary log. As you mentioned, yeah
23:40 <aaronpk> the problem is once you have a lot of data, mysqldump is no longer trivial
23:40 <kyledrake-home> Elaborate please
23:40 <aaronpk> it takes a long time
23:41 <kyledrake-home> ah, sure.
23:41 <aaronpk> and queries block
23:41 <kyledrake-home> I see no other way to get mysql replication back online. Might just have to shut down the site at 4AM and just do it. How long are we talking here?
23:41 <kyledrake-home> What's the size of those dumps?
23:42 <aaronpk> probably 2-3 hours at this point to do a dump and restore
23:42 <aaronpk> but could be quicker if I could copy the raw files off the disk
23:42 <kyledrake-home> Nasty. But it fixes it forever, which is nice
23:43 <aaronpk> ok well things seem to be stable-ish now
23:44 <aaronpk> I think I"m going to call it a night and cross my fingers that it doesn't die overnight
23:44 <kyledrake-home> Should be fine. keep buffer juiced up on master and re-direct traffic to master if slave is acting up
23:45 <kyledrake-home> I think that'll solve it
23:45 <kyledrake-home> Have a good night!
23:45 <aaronpk> thanks for your help!
23:45 <kyledrake-home> Any time. :)
23:45 <aaronpk> we'll talk more abotu this soon i am sure!