Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

"I've isolated the bug to a database query"

thedailywtf.com

Post reply on HN
Print view View original

81–90 of 173 posts

Re: "I've isolated the bug to a database query"

#81

Post by thwarted »

dos1 wrote: post #8

While we're sharing horror stories... I once encountered a stored procedure that returned HTML in a result set. It literally created the UI of a webpage. It returned several columns of HTML that the app would place in strategic parts of the page. Well, as years went by, the app required a more innovative and web 2.0 UI. Rather than remove the HTML from the sproc, more columns were returned with more HTML, Javascript…

I had a boss one time who, rather than write a CGI, would write SQL statements against oracle to format the results as HTML.

Things like

select '' from dual; select ''||column1||''||column2||'' from dual;

Re: "I've isolated the bug to a database query"

#82

Post by lyudmil »

tom_b wrote: post #19
kabdib wrote: post #6

One company I was at did a merger with another startup, and most of the other company's engineers quit. Amongst the piles of Visual Basic we found a stored procedure that was about 5 pages long. It took about 18 hours to run; its job was to do a daily report. I hate being afraid of code. I spent a day with it, got to understand it, then rewrote it as a couple of queries and some Java code, whereupon it took about fiv…

"Why do I find all the really howling bad stuff so close to the databases?" Because the database and queries against were were coded as an afterthought. Because the programmers ran all the db access tests against tables with 4 rows of data when the customers would windup with 100 million rows in production. Because most undergrad education around databases is poor and antiquated. Plus, it is not SQL focused - much ti…

I have a slightly stupid question. How is it that we've come to a point where we think of application and database development as separate things? I think this sets the stage for a lot of the problems you're getting at.

On the one hand we make-believe that all you need to know is how to sling code and as long as you can somehow get your data into the database and out, then you've done your job as a developer. I think that's bullshit. No one would use your application without the data, so the database is as much of your responsibility as any other part of the system. You should know how to use it well and should take responsibility for its quality.

On the other hand, there are things that belong in application code, rather than, for example, in a stored procedure. Most things you could do with a stored procedure probably belong in application code. The app is where the business logic is, the database is where the data is. As a DBA you aren't doing your job right if you feel OK about implementing business rules in SQL. It's much poorer at expressing intent than an OO language would be, it's less readable, and doubles the number of places you need to look for domain logic.

The WTF's go both ways.

Re: "I've isolated the bug to a database query"

#83

Post by wolf550e »

ngsayjoe wrote: post #39
jswinghammer wrote: post #7

Earlier quoted context omitted.

The & and | operators weren't good enough for him? That stuff ends up in databases because on your average team the knowledge of databases drops dramatically when you get past selects and maybe left joins. I worked with a few developers who wrote and lived with queries that were taking 30 seconds each to run on their local machines because they couldn't imagine how to fix them and just waited for me to get assigned t…

I tried caching the hell out of Rails, kept adding indexes to my SQL tables, created 4 MySQL replicas, added more RAM to my linodes, tuned many default parameters in my.cnf, but the website was still slow to a crawl. This parameter saved it all "innodb_buffer_pool_size = 768M" ... how am I supposed to know that Rails creates InnoDB by default, i thought MySQL default was always MyISAM.

Dumping the schema from the database itself will reveal the table type. Knowing the physical organization of the schema is required for optimization work. If you only used the logical organization information (UML diagram for example) you were doing it wrong.

In real RDBMS systems, tables can be clustered (index organized in Oracle-speak) or not, nested or not, in different tablespaces, with many different parameters that do not change the semantic of DML/queries but change the performance significantly.

Re: "I've isolated the bug to a database query"

#84

Post by ngsayjoe »

jswinghammer wrote: post #43
ngsayjoe wrote: post #39

Earlier quoted context omitted.

I tried caching the hell out of Rails, kept adding indexes to my SQL tables, created 4 MySQL replicas, added more RAM to my linodes, tuned many default parameters in my.cnf, but the website was still slow to a crawl. This parameter saved it all "innodb_buffer_pool_size = 768M" ... how am I supposed to know that Rails creates InnoDB by default, i thought MySQL default was always MyISAM.

This is changing because MyISAM is not really a true database (in the ACID compliant sense of the term). I guess I would have assumed they were MyISAM too but they don't perform differently enough at low usage levels that you would notice it right? How big was this was application?

About 5 million pageviews / month. The website is quite processing intensive.

Re: "I've isolated the bug to a database query"

#85

Post by ngsayjoe »

ceejayoz wrote: post #47
ngsayjoe wrote: post #39

Earlier quoted context omitted.

I tried caching the hell out of Rails, kept adding indexes to my SQL tables, created 4 MySQL replicas, added more RAM to my linodes, tuned many default parameters in my.cnf, but the website was still slow to a crawl. This parameter saved it all "innodb_buffer_pool_size = 768M" ... how am I supposed to know that Rails creates InnoDB by default, i thought MySQL default was always MyISAM.

MySQL's default may be MyISAM, but it's a bad default. It is a good thing that Rails uses InnoDB by default.

I think MySQL 5.5 onwards the default would be InnoDB.

Re: "I've isolated the bug to a database query"

#86

Post by ngsayjoe »

wolf550e wrote: post #83
ngsayjoe wrote: post #39

Earlier quoted context omitted.

I tried caching the hell out of Rails, kept adding indexes to my SQL tables, created 4 MySQL replicas, added more RAM to my linodes, tuned many default parameters in my.cnf, but the website was still slow to a crawl. This parameter saved it all "innodb_buffer_pool_size = 768M" ... how am I supposed to know that Rails creates InnoDB by default, i thought MySQL default was always MyISAM.

Dumping the schema from the database itself will reveal the table type. Knowing the physical organization of the schema is required for optimization work. If you only used the logical organization information (UML diagram for example) you were doing it wrong. In real RDBMS systems, tables can be clustered (index organized in Oracle-speak) or not, nested or not, in different tablespaces, with many different parameters…

Yes i know, but it just never cross my mind that my tables have been InnoDB all these while ... among all the optimization, this is one i overlook, well i'm not a DBA afterall.

Re: "I've isolated the bug to a database query"

#87

Post by 3pt14159 »

jcromartie wrote: post #54
endersshadow wrote: post #28

Earlier quoted context omitted.

I work with large databases every day. I cannot tell you the amount of people I've interviewed that weren't able to solve a left join question. Let's leave right and full outer joins out of the discussion--a left join is as advanced as I dare get off the bat in an interview. I've once had a guy I worked with ask for help when he was with a new company. I told him to look at the query plan. He replied, "Yeah, but that…

I must take my SQL skills for granted. It really just "clicks" for me, I guess.

Me too. Most programmers don't understand the "brain" of a select statement. People gtalk me all the time when they are stuck and the answer is often times to remove 90% of the query and replace it with a GROUP BY and a HAVING. Most people don't think of the steps the interpreter goes through because it is a declarative language.

Re: "I've isolated the bug to a database query"

#88

Post by miahi »

msluyter wrote: post #68
endersshadow wrote: post #28

Earlier quoted context omitted.

I work with large databases every day. I cannot tell you the amount of people I've interviewed that weren't able to solve a left join question. Let's leave right and full outer joins out of the discussion--a left join is as advanced as I dare get off the bat in an interview. I've once had a guy I worked with ask for help when he was with a new company. I told him to look at the query plan. He replied, "Yeah, but that…

I work in a very heavy database environment, and it's interesting. Nobody here ever talks about "left" vs. "right" joins. We also don't use ansi syntax (oracle), so for a typical outer join, we'll do either: select * from foo f, bar b where f.baz = b.baz (+) or select * from foo f, bar b where f.baz (+) = b.baz But we don't call one a "left" and the other a "right" join. Just bringing this up to note that the right v…

I also find the Oracle outer join operator (+) easier to use.

I just searched the full (Oracle) sql codebase of the project I'm working on, and it seems that there is no clear winner in the "outer join" vs "(+)" battle (outer = 46%, (+) = 54%). There is no code style requirement for joins.

Re: "I've isolated the bug to a database query"

#89

Post by draz »

endersshadow wrote: post #74
msluyter wrote: post #68

Earlier quoted context omitted.

I work in a very heavy database environment, and it's interesting. Nobody here ever talks about "left" vs. "right" joins. We also don't use ansi syntax (oracle), so for a typical outer join, we'll do either: select * from foo f, bar b where f.baz = b.baz (+) or select * from foo f, bar b where f.baz (+) = b.baz But we don't call one a "left" and the other a "right" join. Just bringing this up to note that the right v…

I never ask for an explicit left or outer join in an interview. It's always a question, like, "Get me the total sales of every salesperson for this quarter given this schema." And then they return it, and I say, "Can I get the list of all salespeople, even if they haven't had any sales?" And that's where it falls apart, sadly.

unfortunately, I must agree with you. 9 times out of 10 candidates cannot answer the question you've posed. One time I even had a candidate with 17 years of experience (!!!) who didn't even know what a join was (masked in a question form). I let it slide, pretended like it was an "advanced" question, and came back to it later (figured he was nervous). The second time around I phrased the question "so, imagine I want to join data from these two tables so that the output would be first name, and location" (2 different tables). Even then, the candidate did not get the hint.... Instead, he offered to change the schema of the fake database and have all the information in one table....
Post reply on HN