Kuro5hin.org: technology and culture, from the trenches
create account | help/FAQ | contact | links | search | IRC | site news
[ Everything | Diaries | Technology | Science | Culture | Politics | Media | News | Internet | Op-Ed | Fiction | Meta | MLP ]
We need your support: buy an ad | premium membership

[P]
welcome back

By hurstdog in Site News
Thu Oct 10, 2002 at 10:31:05 PM EST
Tags: Kuro5hin.org (all tags)
Kuro5hin.org

So, I notice today that we're down. I take note of it, think that rusty will have us back up in a jiffy, and then I can read k5 again. That was this morning when I got to work (~7:30am or so). Tonight, about 12 hours later, I'm here, and notice k5 is still down. A few people in #scoop ask me to look into it. (I usually just let rusty handle it, but...) Rusty didn't seem to be around, so I checked in. I got into the database server, and what do I find? 90 or so myqsld threads all in state "Locked".


Every query that I could see (via mysqladmin processlist) was either an Apache::Session trying to get a lock (it emulates row level locking for Apache::Session::MySQL) or "SELECT * from users where uid = N" where N was some random userid. Mostly userid's of -1.

Why did this happen? I don't know. Maybe its related to what happened last week, maybe not. Either way, its running again now, and I'm going to check in on it every once in a while to see how its going.

Oh yes, how did I fix this incredible lock up? I stopped and started mysql, of course. When I had mysql down, some of you might have noticed ISE's. Its not a problem, just scoop complaining it couldn't hit the server

So thats what happened, you may go back to your regularly scheduled k5 programming now.

Sponsors

Voxel dot net
o Managed Hosting
o VoxCAST Content Delivery
o Raw Infrastructure

Login

Poll
did you miss us?
o No 19%
o Yes 43%
o I got lots of work done 27%
o why didn't you do this this morning!! 9%

Votes: 191
Results | Other Polls

Related Links
o Scoop
o what happened last week
o Also by hurstdog


Display: Sort:
welcome back | 41 comments (41 topical, editorial, 0 hidden)
Awesome! (none / 0) (#1)
by DesiredUsername on Thu Oct 10, 2002 at 10:38:42 PM EST

Now can you migrate us to the beefy server?

Play 囲碁
nope (5.00 / 1) (#2)
by hurstdog on Thu Oct 10, 2002 at 10:42:05 PM EST

Actually I didn't even know we had a new server. I haven't been following k5 with my new job and all, no time :( First I heard about it was talking with rusty last week when k5 went down, and innodb was spouting about "looks like you found a bug!". Link to a story on it?



[ Parent ]
Uhhh... (5.00 / 1) (#4)
by DesiredUsername on Thu Oct 10, 2002 at 10:45:36 PM EST

check the site news. I think the two most recent, especially the most most recent, has the gist.

Play 囲碁
[ Parent ]
just saw those (none / 0) (#5)
by hurstdog on Thu Oct 10, 2002 at 10:50:21 PM EST

And I read some of the old diaries from rusty. I'm all informed now, but I have no access to the new server.



[ Parent ]
Now you know how I feel! (none / 0) (#28)
by Inoshiro on Fri Oct 11, 2002 at 04:09:27 PM EST



--
[ イノシロ ]
[ Parent ]
I feel better (5.00 / 2) (#3)
by xunker on Thu Oct 10, 2002 at 10:44:47 PM EST

I feel better that I'm not the only one suffering from the bad MySQL vibes today... I was begining to take it personally.

All morning I had beening envisioning a giant, unholy Dolphin monster leaping out of a sea of molten lead to bounce me on his pointed nose like a beachball before devouring me like a herring.

Good reason to migrate towards PostgreSQL, perhaps (4.40 / 5) (#6)
by kcbrown on Thu Oct 10, 2002 at 11:09:11 PM EST

Locked tables are a common occurrance under MySQL when it's operating with a heavy load. I've seen it happen on countless occasions.

Everything I've read indicates that PostgreSQL handles high loads much more gracefully. Perhaps it's time to start thinking of migrating towards it?

arg (5.00 / 1) (#9)
by hurstdog on Thu Oct 10, 2002 at 11:49:42 PM EST

People have been saying this for years....

Why don't use you [database|language|webserver|operating system] [x|y|z] because it would fix problem [A|B|C|D|...|ZZZ]

I think the current problems with k5's uptime can be fixed by more ram in the database machine and getting off of using Apache::Session. Regarding your locked table comment, we use InnoDB, so it shouldn't be a problem (I'm not sure if all of the threads were waiting for a locked table or row, but it was most likely table...). Where I think the problem is coming from is Apache::Session emulating row-level locking since it doesn't know we're using innodb.

So far, the Scoop team just doesn't have the developer resources to attack the port, and its not very high on anyone's priority list. Someone once ported it to Oracle, and they said a pgsql port was coming soon, but they got busy and stopped work on it before it was finished.

Anyway, switching something out of Scoop like that is a big bit of work, and though its something we'll probably do in the future, its not high on the list, because we'd rather stick with what we know, and exhaust all options for getting this working.

Though if you want to work on a pgsql port, then load test it and show that its faster, k5 will switch in a heartbeat.



[ Parent ]
So which would take more developer work? (5.00 / 1) (#11)
by kcbrown on Fri Oct 11, 2002 at 01:28:40 AM EST

Getting off of Apache::Session, or migrating to PostgreSQL?

I haven't seen the code so I can't comment, but just based on reading the Perl documentation for Apache::Session and friends, it looks to me like it would probably be a dead heat.

In which case you're probably better off in the long run migrating towards PostgreSQL.

And the right way to do that, of course, would be to make your database interface code as database independent as possible. Abstract things out where you have to, including the SQL calls you have to make. You have to touch all that code anyway, so you may as well restructure it so that future database migrations will be much easier.

I'm actually interested in porting Scoop to PostgreSQL. I think it would be a reasonable way for me to contribute to the community that has given me so much. Just point me to the Scoop source and some good documentation on how to set up a Scoop instance and on the architecture of the source and I'll see what I can do with it.

The only thing I won't be able to do very well is load testing. I just don't have the facilities for that, because to do it properly would require multiple client machines banging on the server simultaneously. Or, so my experience says.

[ Parent ]

*points* (4.00 / 2) (#12)
by nstenz on Fri Oct 11, 2002 at 01:45:19 AM EST

Just point me to the Scoop source and some good documentation on how to set up a Scoop instance and on the architecture of the source and I'll see what I can do with it.
http://scoop.kuro5hin.org/

[ Parent ]
Apache::Session (none / 0) (#22)
by hurstdog on Fri Oct 11, 2002 at 12:02:44 PM EST

Isn't that big of a problem, iirc, there are only 2 functions in scoop that handle session data, so we'd just need to change those. Migrating to pgsql, though, would be huge. Some potential problems

  • around 300 sql queries in the codebase, which would all need to be checked to make sure no mysqlisms persist.
  • many many people using scoop on mysql, so we need to maintain backwards compatibility.
  • This list was supposed to be longer but I can't think of anything else to put here

Abstracting all of the queries out to a Scoop::DB object is a good idea, with plugins for the various different databases to use. ( i.e. Scoop::DB::Oracle, Scoop::DB::MySQL, etc). Thats how we were thinking of doing it. And if I remember correctly, thats how slashcode does it as well.

Thanks for volunteering to help, by the way. We get lots of comments to the effect of "I've never looked at the code, I don't know what a database is, and the closest to programming I've come is doing a crossword, but you should implement feature X, because I don't think it should be hard". Inevitably feature X is very difficult to do, either in code, or performance, or whatever. Search through old Site News stories, and meta section stories for examples, there are lots. So again, thanks for volunteering, and we look forward to having your help. Check #scoop on irc.slashnet.org to get up to date on the current state of scoop.

-Andrew

[ Parent ]
So what version should I do the port from? (none / 0) (#34)
by kcbrown on Fri Oct 11, 2002 at 08:23:03 PM EST

I'm usually inclined to start my port from the stable code base (0.8.1 in this case, I believe), but it may work better overall if I start from someplace else. But I need to start from a code base that properly installs and runs. I don't want to run afoul of other people's development bugs, which is why I'm inclined to shy away from the CVS version.

[ Parent ]
use cvs (none / 0) (#36)
by hurstdog on Tue Oct 15, 2002 at 10:59:20 AM EST

it is the most stable, and there are tons of features added to it.



[ Parent ]
more stuff (none / 0) (#35)
by Lazy Jones on Sat Oct 12, 2002 at 01:06:20 PM EST

* This list was supposed to be longer but I can't think of anything else to put here Er... Lots of places where double quotes are used for text values (instead of $dbh->quote). This is very bad (a MySQLism). -lj

[ Parent ]
SQL abstraction (none / 0) (#15)
by fluffy grue on Fri Oct 11, 2002 at 02:10:48 AM EST

I like the way phpBB handles different SQLs... basically, it has an abstraction layer which wraps all of the different SQL queries in a higher-level API, and then it has a separate implementation for each SQL.

Kind of dumb how this is even necessary though, considering that the whole POINT to SQL was to keep code DBMS-independent to begin with.
--
"Is a sentence fragment" is a sentence fragment.
"Is not a quine" is not a quine.

[ Hug Your Trikuare ]
[ Parent ]

yeah (none / 0) (#21)
by hurstdog on Fri Oct 11, 2002 at 10:47:21 AM EST

Its rather ironic, in that writing the database code for your system (not including the connection code, which is of course dbms-dependant) that you have to structure your sql to a particular db. I've been looking lately at how SPOPS handles it for OpenInteract. I'm probably going to end up using OI at my current job, so I'll get a good hands-on feel for how it works :)

Also, since I'm starting grad school in january to get my Masters (and PhD if I enjoy taking classes remotely), I've been thinking about what to do my Thesis on. One of the thoughts I had was relational db <-> OO codebase interaction, and how would you write a maintainable, fast, easy-to-use implementation. Ideally I would do it in a few different languages with a few different databases, using the same general design pattern. Though its just a thought, and I'm sure its been done before, it might be a fun thing to research. Get a little bit into Databases and Software Engineering.



[ Parent ]
Re your thesis (none / 0) (#26)
by gauntlet on Fri Oct 11, 2002 at 03:24:47 PM EST

hey, Hurstdog:

If I was looking for something to study for a thesis, and I was looking at databases and OOP, I'd take a hard look at Object Oriented Databases. There are resources out there, but beyond being a thesis project, I haven't seen it well implemented.

Into Canadian Politics?
[ Parent ]

What are the justifications to an OODBMS? (none / 0) (#29)
by fluffy grue on Fri Oct 11, 2002 at 04:39:31 PM EST

Personally, I haven't seen anything to indicate that an OODBMS is functionally any different from an RDBMS...
--
"Is a sentence fragment" is a sentence fragment.
"Is not a quine" is not a quine.

[ Hug Your Trikuare ]
[ Parent ]

justifications? (none / 0) (#37)
by gauntlet on Wed Oct 16, 2002 at 12:16:34 PM EST

I don't know about justifications. I do know that I have often wished that I could design objects in a database that were composite, and objects that were inherited.

The best example I have was the desire I had at once to design a system that would allow me to record important pieces of information for various pieces of hardware in an inventory system.

Monitors have a screen size, Hard drives have a disk space, various objects all have different things.

Rather than having to design an interface for each different type of hardware, or a form for each different type of hardware I want, I could create a "hardware" object, and have other types of objects be inherited from it. This would allow interfaces to either use detailed information about an existing hardware type (e.g. "monitor") in order to display it specifically, or use general information about the "hardware" type in order to display it generally.

There are other ways of doing that, of course, but it seems like OOD would help.

I think the primary justification for it, though, is that it would be of advantage for object oriented software to not have to map relational data to an object model when retrieving information from a database. It could, in fact, off-load the responsibility of keeping objects in memory to the database, and if the database was particularly well suited to the task, might speed up application time.

In all honestly, I'm talking out my ass, here, but these are the things that I seem to remember thinking about about 3 years ago when I looked at it first.

Into Canadian Politics?
[ Parent ]

Good points (none / 0) (#38)
by fluffy grue on Wed Oct 16, 2002 at 01:36:09 PM EST

That would require some sort of RTTI to be useful, though, and it could probably be faked in an RDBMS without too much difficulty by having each object be represented as a property list, though setting it up and doing queries on that would be a pain. Maybe have a table for a "property pool," where each property is a 3-tuple of owner object ID, key, and value, and then the object table would contain object IDs and properties common to all objects (plaintext identifier, serial number, type description, etc.). This would be Smalltalk-style OO.

Then when creating a new object, it could simply copy all of the properties from another object, and there could be a bunch of default object types to copy from.

It'd still be pretty efficient, and can be implemented on any ordinary RDBMS while giving all of the advantages of an OODBMS.
--
"Is a sentence fragment" is a sentence fragment.
"Is not a quine" is not a quine.

[ Hug Your Trikuare ]
[ Parent ]

Pain is an understatement (none / 0) (#39)
by gauntlet on Wed Oct 16, 2002 at 04:35:26 PM EST

I thought about doing that for my little project, but if you have an "obect" table and a "properties" table, the properties table is going to hold values of all types, and it would be impossible to create an index of monitors on screen size, for example.

The implementation I had been thinking of, actually, was having three tables; one each for text, time, and numerical data. The same problems occur, though. It is impossible to index objects of a particular type on any of their properties. Granted, you would be able to index objects by their type, and properties by their object, and create combined object/property/value indices on the property table. You could even run queries in a stored procedure to create a table that indexed certain object types by certain properties, referring to the appropriate rows in the two tables. You could then create views that used this table to access the object and property tables to make it look as though they were indexed properly.

But it doesn't need to be portrayed that way to the user. The data that a RDBMS keeps in a flat file is respresented logically in a relational database for the user. Why can't the data in a relational database be represented logically in an object structure for the user?

Granted, there's more to object orientation than "is a type of" relationships, but you get the idea. I just think there's a lot of potential there that is totally unexploited as of yet.

Into Canadian Politics?
[ Parent ]

Indexing (none / 0) (#40)
by fluffy grue on Wed Oct 16, 2002 at 04:51:16 PM EST

That was specifically why there was a link back from each property to the object which contains it. Unfortunately, this only allows searches on a single property at a time... like, you could say, "SELECT FROM props WHERE KEY = size AND VALUE = 17" (or whatever... I've not done much in SQL, and it's been a while) and then look up the resulting object IDs in the objs table. Or you could even do a JOIN for extra craptacularness. But you'd need some way of doing set intersections/unions to support multiple-property queries... for example, to find all ViewSonic 17" monitors you'd have to do a search on SIZE=17 and another on MANUFACTURER=ViewSonic, take the intersection of the resulting ObjID sets, and then do the lookup.

Fortunately, unions and intersections are trivial if the sets are both sorted the same way, but it'd probably need support code outside of SQL... unless SQL supports SELECTs on the results of a query. Like (pseudocodeishly), "Q1 = SELECT FROM props WHERE KEY = Size AND VALUE = 17; Q2 = SELECT FROM props WHERE KEY = Manufacturer AND VALUE = ViewSonic; SELECT FROM objs WHERE ObjID IN Q1 AND ObjID IN Q2." Which almost certianly would NOT be done in an algorithmically-efficient way, so it'd probably be easier and faster to just get the results of the queries and then sort/intersect them yourself.

Which really doesn't seem all THAT painful to me.
--
"Is a sentence fragment" is a sentence fragment.
"Is not a quine" is not a quine.

[ Hug Your Trikuare ]
[ Parent ]

my inclination (none / 0) (#41)
by gauntlet on Wed Oct 16, 2002 at 05:35:30 PM EST

select
o.type,
p1.value,
p2.value
from
object o,
property p1,
property p2
where
o.oid = p1.oid
and
o.oid = p2.oid
and
p1.name = "brand"
and
p2.name = "size"
and
p1.value = "Viewsonic"
and
p2.value = "17"

Even here, there are problems. The property table is being hit twice for two properties of the same type of object. Furthermore, the indexes that are being used are either inefficient because they are using textual indexing to index numerical data, or (if you have the property tables divided out by type) the include potentially millions of records that don't apply to the type of data you're trying to get.

There are ways of solving those problems by creating additional index tables "as though" the monitors were all held in a single table. That, however, is analogous to creating indexes in a relational structure. Since we just need to indicate to the database what structure the data has (this relates to that, etc), and it will come up with the appropriate indexes, it should follow that if we simply increase the abstraction with which we look at the data, describe the data in terms of "this is one of those", we can eliminate a bunch of work while making the data easier to use.

I would argue that what would be easier and faster is if the DBMS did all this crap for you. And if you don't think that what you described is painful, you're free to try it. I respectfully disagree.

Into Canadian Politics?
[ Parent ]

DB tasties. (none / 0) (#27)
by Inoshiro on Fri Oct 11, 2002 at 04:00:16 PM EST

When you start modelling really complex problems, I've found that the best solution is to have a proper 3nf design. Have a layer of container objects that reflect apropos information from the db (like a top level object which contains a collection of related subobjects) filled out in the db connection layer. Then work with those objects, and commit them when done.



--
[ イノシロ ]
[ Parent ]
DBI (none / 0) (#23)
by tzanger on Fri Oct 11, 2002 at 12:46:57 PM EST

I like the way phpBB handles different SQLs... basically, it has an abstraction layer which wraps all of the different SQL queries in a higher-level API, and then it has a separate implementation for each SQL.

Yeah PHP had a pretty shitty database connector since every one was different. I've been a huge fan of Perl's DBI for precisely that reason.

Kind of dumb how this is even necessary though, considering that the whole POINT to SQL was to keep code DBMS-independent to begin with.

If you write for SQL92/95, you will be compatible. That's been one of my biggest grips against MySQL (and even Oracle for that matter) -- there's way too much proprietary bullshit that you are tempted to use. Just like drugs, the first hit's free.



[ Parent ]
Impossible... (3.00 / 1) (#31)
by dipierro on Fri Oct 11, 2002 at 06:19:56 PM EST

C'mon, Rusty can't even manage to move the database to a new machine, how do you expect him to switch from MySQL to Postgres?

[ Parent ]
Yay (none / 0) (#7)
by Stick on Thu Oct 10, 2002 at 11:20:05 PM EST

I slept through it all.


---
Stick, thine posts bring light to mine eyes, tingles to my loins. Yea, each moment I sit, my monitor before me, waiting, yearning, needing your prose to make the moment complete. - Joh3n
Rusty has to stop this crashing shit (5.00 / 3) (#8)
by thenick on Thu Oct 10, 2002 at 11:44:23 PM EST

I'm actually doing work and my boss is going to start expecting more from me.

 
"Doing stuff is overrated. Like Hitler, he did a lot, but don't we all wish he would have stayed home and gotten stoned?" -Dex

Kick Ass (4.00 / 1) (#10)
by resquad on Fri Oct 11, 2002 at 12:38:39 AM EST

Thanks dude.

I dont see a need to complain (yes I actually DO pay), it got fixed.  Shit happens.

But you kick ass.


-----------
"I WIN THE END!" -Me

Thanks (1.25 / 4) (#13)
by Ludwig on Fri Oct 11, 2002 at 01:46:58 AM EST

Hurstdog is so fucking sexy...

Hey, what happened to that picture with the El Camino, you pasty fairy?

thank you for fixing it. (none / 0) (#14)
by aphrael on Fri Oct 11, 2002 at 02:10:13 AM EST

a couple days ago rusty said he was going to be offline for a week. i was terrified k5 would stay down the whole time ... kudos to you for jumping in and fixing! thank you thank you thank you ... :)

Oh. (1.53 / 13) (#16)
by tkatchev on Fri Oct 11, 2002 at 03:33:05 AM EST

"Open sores" strike agaun.

   -- Signed, Lev Andropoff, cosmonaut.

you should probably (4.00 / 1) (#30)
by Wah on Fri Oct 11, 2002 at 05:35:33 PM EST

post comments like this in your diary. And see a doctor.

[also, subscribers get handy "spell check"©®™ functionality, but you should save money and take care of that condition first]
--
You didn't know we had cameras in your room, Parent ]

He was using a spell checker (3.75 / 4) (#32)
by roam on Fri Oct 11, 2002 at 06:46:39 PM EST

GNUspelCheck

___
Are they like hamsters?
Specifically, can I tape up a chinchilla, slather him in axle grease, and shove him up my ass? - Patrick Bateman


[ Parent ]
Every time he goes on vacation... (none / 0) (#17)
by wiredog on Fri Oct 11, 2002 at 08:42:30 AM EST

the system goes tits up.

Earth first! We can strip mine the rest later.
and right after I posted this.. (none / 0) (#18)
by wiredog on Fri Oct 11, 2002 at 09:14:35 AM EST

it did it again.

Earth first! We can strip mine the rest later.
[ Parent ]
The bad thing is (4.00 / 1) (#19)
by Cro Magnon on Fri Oct 11, 2002 at 10:28:56 AM EST

it's so slow when it's up, I could hardly tell the difference. :-P
Information wants to be beer.
many thanks [n/t] (4.00 / 1) (#20)
by martingale on Fri Oct 11, 2002 at 10:29:31 AM EST



Where is rusty, anyways? (none / 0) (#24)
by dram on Fri Oct 11, 2002 at 02:04:46 PM EST

Maybe somebody should call him and make sure he is still alive. He hasn't been on IRC in almost five and a half days. Has anybody heard from him in that amount of time?

-dram
[grant.henninger.name]

He's alive. (3.33 / 3) (#25)
by vectro on Fri Oct 11, 2002 at 02:34:03 PM EST

He's just on his yacht, polishing his monocles.

“The problem with that definition is just that it's bullshit.” -- localroger
[ Parent ]
If you check his diary (4.50 / 2) (#33)
by aphrael on Fri Oct 11, 2002 at 07:53:46 PM EST

not the most recent entry, but the one before that, he said he'd be offline for a week.

[ Parent ]
welcome back | 41 comments (41 topical, 0 editorial, 0 hidden)
Display: Sort:

kuro5hin.org

[XML]
All trademarks and copyrights on this page are owned by their respective companies. The Rest 2000 - Present Kuro5hin.org Inc.
See our legalese page for copyright policies. Please also read our Privacy Policy.
Kuro5hin.org is powered by Free Software, including Apache, Perl, and Linux, The Scoop Engine that runs this site is freely available, under the terms of the GPL.
Need some help? Email help@kuro5hin.org.
My heart's the long stairs.

Powered by Scoop create account | help/FAQ | mission | links | search | IRC | YOU choose the stories!