sorted by: recent | see :
popular
Content Tagged with SQL + postgresql
Log Buffer #106: A Carnival of the Vanities for DBAs
Greetings from Wisconsin! Welcome to the 106th edition of the Log Buffer. Mr. Edwards is on a brief holiday and kindly asked me to fill in for him. So join me as we take a tour of some of this week’s database blogging activity.
I’d like to start by sharing the story of MySQL engineer Andrii Nikitin’s young son, Ivan. The short story is that Ivan is in need of a bone marrow transplant and that would also require travelling outside of their native Ukraine for the procedure. The family is asking for donations to cover the cost of the operation and trip, so please consider donating via the previous link.
Now, moving on to the database topics, we begin with my own area of (relative) expertise, Oracle. The big news this week is the July 2008 CPU, or Critical Patch Update, which Oracle releases quarterly. The Integrity blog has a nice summary of what’s included in this latest patchset. I’m sure plenty of you will be heading to Oracle OpenWorld 2008 as well, and OTN’s Justin Kestelyn announced a community preview page with some sneak peeks of what to expect (spoiler: beer!).
In a quick round-up of Oracle 11g topics, Amit Bansal at AskDBA.org has a nice guide for installing Oracle 11g on Solaris 10. Meanwhile, Oracle performance guru (and fellow Wisconsin native) Greg Rahn makes a great case for using incremental global statistics on partitioned tables. I see that Tim Hall has taken a break from reviewing summer blockbusters to write up a nice article on AWR baseline enhancements in Oracle 11gR1, and Alex Nuijten at AMIS has a nice presentation on using Oracle 11g’s virtual columns to clean up your data model.
(more…)
Log Buffer #105, a Carnival of the Vanities for DBAs
This week, database blogs seemed to talk about conforming to stereotypes as well as breaking them.
Fulfilling the stereotype of Microsoft software being unsecure, Microsoft released a very important SQL Server update that Aaron Bertrand notes patches “four elevation of privilege vulnerabilities.” That’s right, not one, not two, but four!!! At least there is a patch now……go forth and patch! Usually it is MySQL that throws ambiguous warnings or errors which are not an accurate representation of the actual error, but Bertrand also notes that there is A Little Management Studio Oops.
Contrary to stereotype, Microsoft is giving away content with NO DRM! Kalen Delaney asks, Did You Know the History of SQL Server? and shares a link to a chapter from a book on SQL Server 2000 in the Inside SQL Server series.
The PSS SQL Escalation Services team has fought many times about SQL Server I/O Bottleneck, I don’t have one, YES YOU DO! The team gives some reminders about how to interpet averages properly.
Allen White asks about Tools for the Reluctant DBA — that is, a programmer or administrator so good at databases that they were promoted to DBA, but may not actually want the job. Check out the comments and add your own.
Tibor Karaszi shares his stored procedure to find physical index details. Now you can use one stored procedure does what a stored procedure plus 3-4 tables ordinarily does.
Log Buffer #104: a Carnival of the Vanities for DBAs
Andrew Clarke has published to 104th edition of Log Buffer, the weekly review of database blogs, on Radio Free Tooting, marking LB’s second year. Happy Birthday, LB!
Log Buffer always needs editors, so if you you’d like to present your view of the week that was in DB blogs, contact me, the Log Buffer coordinator. You’ll be joining some of the best bloggers around, and making yourself and your blog a little better known to readers around the world.
And now, here’s Andrew Clarke’s Log Buffer #104.
P.S.: To our readers in the U.S. — Happy 4th of July!
SQL HOWTO - FreeRADIUS Wiki
Log Buffer #103: a Carnival of the Vanities for DBAs
Welcome to the 103rd edition of Log Buffer, the weekly review of database blogs.
Starting with Oracle stuff, Chen Shapira (just a simple DBA on a complex production system) is looking for great PL/SQL. Why? To become a better PL/SQL programmer. “But,” she writes, “for PL/SQL , I?m a bit stuck. I can still read my own code for bad examples, but where can I find examples for great code? . . . Somehow, there is simply no open-source code written in PL/SQL that I can read to get a good idea of how PL/SQL should be written.” Niall Litchfield recommends the contents of $ORACLE_HOME/rdbms/admin. Any other ideas to help Chen?
There was no shortage of people offering their help, as always. Beginning here on the Pythian blog, Grégory Guillou posted the fifth of his ten-part series on Oracle silent mode, this part covering adding a node to a 10.2 RAC. Says Grégory, “Even if you don?t leverage RAC?s ability to add or remove nodes to gain in agility, it?s still very likely you?ll come to these techniques when you want to upgrade some of your Servers or Operating Systems.” The post includes links to the earlier articles in the series.
On his Core IT for geeks and pros, Tanel Poder expounds another use case for WaitProf. He begins, “I recently diagnosed a performance issue where the ‘events in waitclass Other’ occasionally took significant part of the session?s response time. . . . So, what to do when this ‘events in waitclass Other’ wait becomes significant in the response time profile?” This is where waitprof, his custom tool, comes into play.
Jeff Hunter of the So What Co-operative posts Something to be aware of, Part II, in which he and Oracle Support tussle over NUMA in Linux, and where he is heard to say, “My next step is talking to a Duty manager. I really don’t want to do that.” And Part III, which Jeff begins, “I escalated my issue to a duty manager . . .”
On Oracle Applications DBA, Aviad Elbaz knows how to do tricks with a pipe, as he shows in his article on how to execute TKPROF on trace files larger than 2GB. Aviad, I think Bilbo and Gandalf would be proud.
Are you like me, shamefully ignorant about the whole Business Intelligence thing? Do you, too, secretly believe it might be an oxymoron? Mark Rittman is here to help, with an introduction to the Oracle BI Applications.
Vivek Sharma of Database/SQL Experiences informs us that he will be contributing a seminar on “Real Time Performance Tuning” to the very first event mounted by the IOUG (All India Oracle User Group). This is their TechNight, taking place in Hyderabad on Friday, July 18th.
Log Buffer #102: a Carnival of the Vanities for DBAs
Welcome to the 102nd edition of Log Buffer, the weekly review of database blogs.
Since it was DB2’s 25th birthday this week, as Anant Jhingran reports, let’s start with it.
From ZDNet this week came a story that IBM was considering the open-sourcing of DB2 — big news, naturally, whether true or not. Matthew Aslett of 451 CAOS Theory says, Open source DB2? I don?t think so, suggesting that it was merely theorizing on the part of one IBM executive, and hype on the part of ZDNet. “Then of course,” Matthew continues, “there is the issue of why IBM would open source DB2. Where is the business driver? Despite solid competition from Oracle and Microsoft, the company is doing pretty well with DB2 as it stands, thanks very much, and open source databases have had minimal impact on the established vendors.”
Matt Asay of The Open Road likewise comes down on the uh-uh side of this. “It was . . . no surprise to see IBM quickly follow up ZDNet’s article with a blunt statement: ‘IBM has no plans to open source DB2.’ Of course it doesn’t. . . . The day that it needs DB2 to undermine Oracle’s database, however, we may see IBM making a similarly bald statement…in the other direction.”
On An Expert’s Guide to DB2 Technology, Chris Eaton offers some news on DB2 videos, including ChannelDB2’s video tutorials, IDUG’s video competition, and a funny video from IBM on the making of a “viral” industrial video. Such as itself.
On to MySQL now. Keith Murphy made his debut on the Pythian blog this week, with a link to some video tutelage of his own — his presentation to the Boston MySQL Users Group on backups. Jay Pipes also offers the slides for his address on Join-Fu: The Art of SQL (I and II). (I rarely cover presentation materials when I do Log Buffer, but these ones of Jay’s looks fantastic.)
Mark Schoonover reports that Keith’s and his 2008 MySQL Magazine survey is officially closed, adding, “Now the fun begins compiling all the data and pretty charts made. We’ve had 432 responses!”
Baron Schwartz of xaprb describes how to write a lazy UNION in MySQL. No, it doesn’t deal with posting a complaint about undefended job benefits. Baron explains: “Something occurred to me a couple of weeks ago: why not write a UNION that stops executing as soon as one part of it finds a row? Then you can UNION to your heart?s content and not incur the overhead of that second lookup unless you need it. For lack of a better term, I?m calling this a lazy UNION.”
Log Buffer #101: a Carnival of the Vanities for DBAs
Welcome the the 101st edition of Log Buffer, the weekly review of database blogs.
This edition was originally claimed by Ward Pond for his SQL Server Blog. Unfortunately, Ward is, in his own words, “dealing with the aftermath of a burst appendix,” which is a very good reason not to spend your time at the computer. Ward, heal up soon! We’ll see you on LB before too long.
In lieu of the normal Log Buffer, I throw it open to our readers. Please leave a comment mentioning your favourite database blog items from the week that was, and anything else you care to say about them.
LB will be back to normal next Friday. See you then!
Log Buffer #100: a Carnival of the Vanities for DBAs
Log Buffer, the weekly review of database blogs, is 100 editions (and almost two-years) old today! Lewis Cunningham has returned to LB to publish The Big 100th edition of LB on An Expert?s Guide to Oracle Technology.
No speech, but I would like to thank Log Buffer’s readers and especially all of Log Buffer’s editors for making LB a worthwhile and fun stop in the database “blogosphere”. It’s very easy to see why LB editors are successful in what they do — they are consistently enthusiastic, diligent, and adaptable. And I enjoy working with them.
Okay, okay — I can hear the orchestra starting to play me off, so let’s get to it. Here’s Lewis’s Log Buffer #100.
Log Buffer #99: a Carnival of the Vanities for DBAs
Welcome the the 99th edition of Log Buffer, the weekly review of database blogs.
Since PGCon recently happened right here in Ottawa, let’s start with some posts about it, and about PostgreSQL. Josh Berkus came to the conference with his Database Soup. It sounds like he enjoyed himself: “So, that’s pgCon. It was exciting and fun. All of you PG geeks who missed it should be kicking yourselves about now, and putting in budget requests for next year.” He has day one highlights; day two highlights, and also some photos from the conference.
On Tending the Garden, Selena Deckelmann (who knows a thing or two about Postgres conferences) has her PGCon review, with links to conference webpages.
Robert Treat of zillablog also has his take-aways. “The biggest thing to come out of this past week to me was recognition of the continued growth of the postgresql community. With a number of regional conferences springing up over the past year, I wondered if PGCon would be able to match the experience from last time, and was happy to see that it far exceeded it. . . . One thing I noted was that we have a very large presence of Postgres in Ottawa, I think larger than what we had at PGEast for the Baltimore/Washington Area.”
On Greg’s Postgres stuff, Greg Sabino Mullane has an item on verifying master-slave replication with check_postgres.pl, a montoring script for Nagios and other systems.
Hubert Lubaciewski of select * from depesz; looks at different approaches to counting the number of distinct elements, such as distinct sessions per day.
On An Expert’s Guide to Oracle Technology, Lewis Cunningham examines the recent news of the worlds largest database running on Postgres. How large? 2 Petabytes! “Let’s put that in perspective,” writes Lewis. “1 petabyte is 1,000,000,000,000 bytes. Yahoo has two of those. . . . [They use] this database to analyze the browsing habits of its half a billion monthly visitors. How would you like to tune those queries?” Lewis also notes that this is not the plain-vanilla PostgreSQL that you or I would download and run on our website box.
There are conferences on the horizon of the Oracle world too. Dan Norris reminds us that ODTUG is just around the corner. That’s the Oracle Developer Tools User Group’s Kaleidoscope 2008 event, taking place in mid-June in New Orleans.
Gareth Roberts of In Depth Apps announces the call-for-papers of the New Zealand Oracle Users Group’s (NZOUG) 2008 conference, coming in October.
In other Oracle matters — Chen Shapira, just a simple DBA on a complex production system, has some thoughts on cumulative distribution. “How do we calculate Cumulative Normal Distribution? This means calculating the probability that a random variable from a normal distribution, will end up equal to or less than X.”
Log Buffer #98: a Carnival of the Vanities for DBAs
The 98th edition of Log Buffer, the weekly review of database blogs, has been published on Jeff’s SQL Server Blog.
Log Buffer is a shared project of the DBA blogging community, so you’re welcome to edit and publish an edition yourself. LB’s 100th anniversary edition is still up-for-grabs (and there’s plenty of room besides that), so read the Log Buffer homepage and the guidelines, and then email me.
Here’s Jeff Smith’s Log Buffer #98.
Why is PostgreSQL getting dramatically more patches?
…the volume of patches [to PostgreSQL] has risen dramatically during the past few years.
This is total hearsay — I don’t have hard numbers, haven’t verified it myself, etc etc. But consider the source!
What can be responsible for this increase in patches to PostgreSQL?
Bruce Momjian, Open Source development, patches, PostgreSQLWhy is MySQL more popular than PostgreSQL?
There is much discussion of why MySQL is more widely adopted than PostgreSQL. The discussion I’ve heard is mostly among the PostgreSQL community members, who believe their favorite database server is better in many ways, and are sometimes puzzled why people would choose an inferior product.
There are also many comparison charts that show one server is better than the other in some ways. These don’t really seem to help people with this question, either!
I can’t answer for everyone, but I can put it in the form of a question: if I were to replace MySQL with PostgreSQL, what things do I rely on that would become painful or even force a totally different strategy? The answer turns out to be fairly simple for me: replication and upgrades.
Replication
Love it or hate it, MySQL’s built-in replication is absolutely key to much of what I do with MySQL. I can truthfully say that it has lots of problems and limitations. But I can also say this about it:
- It’s included by default with the server. PostgreSQL’s have historically not been included. (I think this is about to change, but I’m not sure.)
- It is conceptually very simple. You could call that a weakness and a limitation, but you could also say that it enables a tremendous amount of flexibility. I tend to hold with the latter view. PostgreSQL’s replication technologies have a very different complexity profile. That scares me.
- It is easy to set up (it takes just a couple of commands) and is easily scriptable. This is mostly due to its simplicity. I am happy because I know it inside and out.
- It is generally very low overhead. PostgreSQL’s main replication system is built on top of triggers and is said not to scale very well. (Disclaimer: this is only what people have told me; I haven’t battle-tested it. But I’m afraid of it.)
- There is only One Way To Do It. PostgreSQL has lots of different replication systems. That in itself is a pretty significant deterrent for me.
Regardless of the technical strengths and weaknesses of each database’s replication systems, it is my perception that MySQL’s ultimately lets me do incredibly flexible and useful things; in general it is Just Enough and has just the right combinations of qualities for lots of purposes. And each of its weaknesses is easily avoided or worked around, or just sidestepped — because MySQL replication’s simplicity and flexibility lets me easily choose a different approach.
In-Place Upgrades
MySQL’s files are extremely portable between versions, between operating systems, and even between platforms most of the time (unless you have a system that doesn’t use IEEE floating-point format, but who does these days?). That means an upgrade is dead simple.
This may not seem like a big deal, but I work with a lot of data. When you do that, you have to consider the alternatives: what if I couldn’t upgrade in-place?
That’s the current state of PostgreSQL. You have to dump and reload your data, and when you have a terabyte of data, that’s no fun. The workarounds usually involve replicating your data to another server, switching to the other server, upgrading, and switching back. But why should you have to have another server just to upgrade your data?
I see this as a significant — even critical — sticking point. It’s something I just don’t have to think about most of the time with MySQL
Are PostgreSQL’s other strengths enough?
Not for the systems I work on. These two problems seem extremely difficult for me to work around. I rely so heavily on MySQL’s replication and in-place upgrades that it feels too daunting to live without them.
What I’m trying to do here is give some psychological insight into what makes me feel happy with MySQL, and afraid of the thought of having to solve these problems with PostgreSQL. It may or may not apply broadly; my sense is that these are concerns for others as well, but I could be wrong.
If I were primarily a PostgreSQL user, I’m sure there would be similar feelings the other direction. This would explain why some people in the PostgreSQL camp seem to recoil away from MySQL. I’d be interested to hear why that is, too.
mysql, replication, upgradesLog Buffer #97: a Carnival of the Vanities for DBAs
The 97th edition of Log Buffer, the weekly review of database blogs, has been published on Brian “Krow” Aker’s Idle Thoughts.
We have Jeff Smith and Ward Pond standing by for two upcoming editions. And if you’d like to contribute, make yourself known in the DBA community-at-large (and have some fun in the process), you too can do Log Buffer! Read the homepage and send me, the Log Buffer coordinator, an email.
And now, Brian Aker’s Log Buffer #97.
Log Buffer #96: a Carnival of the Vanities for DBAs
This is the 96th edition of the weekly review of database blogs, Log Buffer.
Let’s start this one in SQL Server Land, with a question from Dennis Gobo — should SQL Server have the CREATE [OR REPLACE] PROCEDURE syntax? There are, he writes, advantages: “When scripting out a database you don?t have to generate if exists…..drop statements,” and disadvantages: “I can overwrite a proc without even knowing it.” Of course, the commenters have opinions of their own, and the piece becomes a straw poll for the desirability of that syntax as a feature.
Aaron Bertrand has one too: when was my database/table last accessed? Writes Aaron, “SQL Server does not track this information for you. SELECT triggers still do not exist. Third party tools are expensive and can incur unexpected overhead. And people continue to be reluctant or unable to constrain table access via stored procedures, which could otherwise perform simple logging.” He looks at 2008’s built-in auditing, and for those who can’t wait for that, illustrates a workaround for 2005.
Linchi Shea explores something else from 2008, Page Compression, focusing on how the number of processors affects the rebuilding a table with page compression.
Jamie Thomson, the SSIS Junkie writes that he has made a submission to Connect on the matter of absolute and relative paths in SSIS. “. . . I have always agreed that stipulating the use of absolute paths within SSIS was the right thing to do (and indeed I have championed it) however of late I have changed my mind. Support for relative paths would greatly simplify package deployment and package management . . . What do you think? Should SSIS support relative paths?” So far, it looks like a shoo-in.
Brian Knight also explains another little quirk, SSIS Case Sensitivity: “The case sensitivity can in some cases create behavior that is not expected and may give you bad results if you’re not careful. . . . One such example is with the Lookup Transform, where comparisons against the cache are case sensitive. If you do not expect this, you may have a miss in a match that is actually a hit.”
In the MySQL ’sphere this week, there is plenty of talk about the openness or otherwise of MySQL. (more…)
Log Buffer #95: a Carnival of the Vanities for DBAs
The 95th edition of Log Buffer, the weekly review of database blogs, has been published by Mark Schoonover on his Mark’s IT Blog.
We can look forward to LB#98 Jeff Smith’s Jeff’s SQL Server Blog on May 23rd. There’s always plenty of room for more editors, so don’t waste another minute — send an email to me, the Log Buffer coordinator, and get started!
Without further ado, here is Mark Schoonover’s Log Buffer #95.
Baron Schwartz on a podcast at MySQL Conference and Expo 2008
I did an interview with Barton George from Sun while I was at the conference last week. Barton has now posted the interview. If you’re quick, you can listen to it before I do.
Topics: everything and anything, including Maatkit and PostgreSQL.
Baron Schwartz, Barton George, maatkit, mysqluc08, mysqluc2008, Podcast, SunLike it or not, it is the MySQL Conference and Expo
The conference that many of us just went to is called the MySQL Conference and Expo, but a lot of people don’t call it that. They call it by the name it had in 2006 and earlier: MySQL User’s Conference. In fact, some people say (or blog) that they dislike the new name and they’re going to call it the old name, because [… insert reason here…].
I call it by the new name that some people dislike so much. Why? Because it is a conference and expo, not a user’s conference. There’s no reason to pretend otherwise. The conference is organized and owned by MySQL, not the users. It isn’t a community event. It isn’t about you and me first and foremost. It’s about a company trying to successfully build a business, and other companies paying to be sponsors and show their products in the expo hall. Times have changed.
I’m not saying any of this is bad. Being successful in business is a good thing, and having sponsors and partners is fine too. I’m just pointing out that trying to make it be a user’s conference, just by calling it one, isn’t going to work.
If community members want a community conference, we’ll have to make one. MySQL/Sun cannot do this for us, because then it wouldn’t be a community conference.
There’s a simple test of whether people want this: if it happens, then the community wanted it badly enough to do something about it.
The PostgreSQL East 2008 conference I went to a few weeks ago was a great example of how this works. And the attendance fee was $75, not thousands. A conference doesn’t have to be expensive.
Who wants a conference by, for, and of the community?
Community, conferences, mysql, mysqluc08
SQL
MySQL
community
postgresql
Conferences
commentary
mysqluc08
Log Buffer #93: a Carnival of the Vanities for DBAs
Welcome to the 93th edition of Log Buffer, the weekly review of database blogs.
Conference season is upon us, so it’s been a busy week. There was the MySQL Conference & Expo, so let’s look at that.
Arjen Lentz posts about Sunday’s community dinner, including the arrival of an unexpected guest. Two photos: one of Pythian’s Paul Vallée getting some Sun; the second from the pre-conference dinner.
Zack Urlocker has a couple pieces with both photos and links to video of the keynote addresses from Marten Mickos, Jonathan Schwartz, and Rich Green. From Wednesday, and from Thursday.
Congratulations are due to Baron Schwartz, Diego Medina, and Sheeri Cabral. Baron reports from the conference that the three of them were awarded the 2008 MySQL Community Awards, and his piece makes for a very apt acceptance speech. Here’s Kaj Arnö’s more official post on the Community Awards.
Baron also has good summaries of the conference course: day one, and day two.
Elsewhere on the MySQL scene, much ado about the immediate roadmap for the DBMS, as introduced at the conference. Jeremy Cole got things going, writing, MySQL to launch new features only in MySQL Enterprise: “MySQL will start offering some features . . . only in MySQL Enterprise. This represents a substantive change to their development model ? previously they have been developing features in both MySQL Community and MySQL Enterprise. However, with a shift to offering some features only in MySQL Enterprise, this means a shift to development of those features occurring . . . only in MySQL Enterprise.” This post got a lot of comments, including from MySQL boss Marten Mickos.
Log Buffer #92: a Carnival of the Vanities for DBAs
Welcome, readers, to the 92nd Log Buffer, the weekly review of database blogs.
Brian “Krow” Aker started an interesting blog-thread with his post, The Death of Read Replication, the crux of which is that object caches, such as memcached, make the DBMS itself a little less central, particularly in “Web 2.0″ scenarios. “What does this mean? Less database servers. Bringing down your load means you push off the load to another tier. . . . Why do I need to go through MySQL at all… unless I just want it as a backup or for ad-hoc reporting?”
Ronald Bradford responds with an overview of the MySQL-plus-replication scene. Farhan Mashraqi concurred with Brian’s post, while Arjen also agrees, adding, “I’m not sure the new memory based MySQL storage engines coming out are so relevant, they might be fixing the wrong thing in the wrong place.”
Ronald (who, by the way, is on-deck for a his third Log Buffer on the 25th) also surveys both the storage-engine stuff to be had at the MySQL Conference, and the prevalence lately of talk about Kickfire in MySQL blogs, something also mentioned by Peter Zaitsev on the MySQL Performance Blog.
Peter has another question on his mind: should you have your swap file enabled while running MySQL? He wants to hear your approach to this matter, having himself experienced variable results. Lots of responses already.
Here on the Pythian Group Blog, Paul Moen posted about a situation in which SHOW SLAVE STATUS lies.
Moving into Oracle stuff, our Alex Gorbachev also pointed out something that doesn’t quite work: the ASMCMD cp command in ASM 11g. He sure gives it a try, but finally concludes: “I couldn?t make the cp command work even a single time.” Except maybe on datafiles.