This is the 184th edition of Log Buffer, the weekly review of database blogs. I’ve edited a couple of Log Buffers before, but this is the first time I get to post directly to the Pythian blog. Just one of the many perks of being a Pythian employee ;)

On the Oracle front:

It is always good to start the day with a pop quiz to get the brain into gear: Charles Hooper posted a 3-part series with seemingly innocent True/False questions. He covers sorting, SQL tuning and …

Persistence Smoothie: Blending NoSQL and SQL – see user feedback and comments at http://joind.in/talk/view/1332.

Michael Bleigh from Intridea, high-end Ruby and Ruby on Rails consultants, build apps from start to finish, making it scalable. He’s written a lot of stuff, available at http://github.com/intridea. @mbleigh on twitter

NoSQL is a new way to think about persistence. Most NoSQL systems are not ACID compliant (Atomicity, Consistency, Isolation, Durability).

Generally, most NoSQL systems have:

• Denormalization
  
• Eventual Consistency
  
• Schema-Free
  
• Horizontal Scale
  

NoSQL tries to scale (more) simply, it is starting to go mainstream – NY …

Most of this stuff is not PHP specific, and Python or Ruby or Java or .NET developers can use the tools in this talk.

The session on joind.in, with user comments/feedback, is at http://joind.in/talk/view/1320.

Slides are at http://talks.php.net/show/confoo10

“My name is Rasmus, I’ve been around for a long time. I’ve been doing this web stuff since 1992/1993.”

“Generally performance is not a PHP problem.” Webservers not config’d, no expire headers on images, no favicon.

Tools: Firefox/Firebug extension called YSlow (developed by yahoo) gives you a grade on your site.

Google has developed the …

Baron makes an excellent point in Why you should ignore MySQL’s key cache hit ratio — ratio is not the same as rate. Furthermore, rate is [often] the important thing to look at.

This is something that, at Pythian, we internalized a long time ago when thinking about MySQL tuning. In fact, mysqltuner 2.0 takes this into account, and the default configuration includes looking at both ratios and rates.

If I told you that your database had a ratio of temporary tables written to disk of 20%, you might think “aha, my database is slow because of a lot of file I/O caused by writing temporary tables to disk!”. However, that 20% ratio may actually mean a rate of 2 per hour — which is most likely …

I recently granted ALTER access in MySQL so a user could run the ALTER TABLE command . However after I granted the necessary privileges, the user was still not able to perform the tasks needed. Reproducing the issue using a test instance, I granted a test user the required privileges and MySQL reported no errors or warnings when the ALTER TABLE was run:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.1.41-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant alter,create,insert on *.* to 'test'@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> show errors;
Empty set (0.00 sec)

mysql>

The reason I granted the addition CREATE and INSERT privileges is that according to the MySQL documentation ( …

Applying binary logs to a MySQL instance is not particularly difficult, using the mysqlbinlog command line utility:

$> mysqlbinlog mysql-bin.000003 > 03.sql
$> mysql < 03.sql

Turning off binary logging for a session is not difficult, from the MySQL commandline, if you authenticate as a user with the SUPER privilege:

mysql> SET SESSION sql_log_bin=0;

However, sometimes you want to apply binary logs to a MySQL instance, without having those changes applied to the binary logs themselves. One option is to restart the server binary logging disabled, and after the load is finished, restart the server with binary logging re-enabled. This is not always possible nor desirable, so there’s a better way, that works in at least versions 4.1 and up:

The mysqlbinlog utility has the --disable-log-bin option. All the option does is add the SET …

Just the facts:
What: MySQL user community dinner
Who: me, you, and many MySQL community members
When: Monday, April 12th – Meet at 6:30 at the Hyatt Santa Clara or at 7 pm at the restaurant
Where: Pedro’s Restaurant and Cantina – 3935 Freedom Circle, Santa Clara, CA 95054
How: Comment on this blog post to add your name to the list of probable attendees

I was sad that last year there was no community dinner, and I missed the one the year before when Jonathan Schwartz and Rich Green made an appearance. This year I am determined not to miss it, and so I am calling for a community (pay-your-own-way) dinner on Monday, April 12th, at Pedro’s – a Mexican restaurant that has vegetarian and vegan options. I think Monday is a better time because many folks arrive …

There are those that are very adamant about letting people know that using INFORMATION_SCHEMA can crash your database. For example, in making changes to many tables at once Baron writes:

“querying the INFORMATION_SCHEMA database on MySQL can completely lock a busy server for a long time. It can even crash it. It is very dangerous.”

Though Baron is telling the truth here, he left out one extremely important piece of information: you can actually figure out how dangerous your INFORMATION_SCHEMA query will be, ahead of time, using EXPLAIN.

In MySQL 5.1.21 and higher, not only were optimizations made to the INFORMATION_SCHEMA, but new values were added so that EXPLAIN had better visibility into what MySQL is actually doing. As per …

Dave Edwards has offered me to write this week's Log Buffer, and I couldn't help but jump at the opportunity. I'll dive straight into it.
OracleI'll start with Oracle, the dust of the Sun acquisition has settled, so maybe it's time to return our attention to the regular issues.

Lets start with Hemant Chitale's Common Error series and his Some Common Errors - 2 - NOLOGGING as a Hint explaining what to expect from NOLOGGING. Kamran Agayev offers us an insight into Hemant's personality with his Exclusive Interview with Hemant K Chitale. My favorite quote is:

Do you refer to the documentation? And how often does …

Welcome, everyone, to the 177th edition of Log Buffer, the weekly review of database blogs. It was another week heavy with technical posts, so let’s waste no time, and get it all started with . . .

PostgreSQL

David Fetter shares his recipe for adding only new rows: “Let’s say you have a table and a data set, and would like to add only those rows in your data set that aren’t already in the table. There are hard ways, but here’s an easy one.”

Simon Riggs, the Database Explorer, offers his thoughts on …