Welcome to Log Buffer, the weekly roundup of database blogs.
Kicking off this week in Log Buffer #191 are posts from Alisher Yuldashev:
Randolf Geist blogs on an Advanced Oracle Troubleshooting Session – PGA/UGA memory fragmentation for when a batch process takes significantly longer than expected.
…
[Read more]We’re Open Sourcing some command line tools for accessing some modern system calls on Linux.
The project is named linux-ftools and is over on Google Code.
You’ll have to checkout the hg repo locally and build yourself for now.
Specifically fallocate, fadvise, and fincore (and a few other smaller tools).
They need a bit more cleanup , specifically error handling (and maybe a few more features).
I’d like to try to get these into Debian.
These were designed for use with MySQL but you might be able to use them in your setup as well.
[Read more]
The MySQL Enterprise Spring 2010 Release includes key
improvements designed to help you proactively optimize MySQL
performance and identify potential issues before they can become
costly outages or slowdowns. In this webinar we will explore the
new features of the MySQL Enterprise Monitor 2.2 that are
designed to save DBAs and Developers time and effort in keeping
MySQL systems running at the highest levels of security,
performance and availability. Join us and learn about:
A useful feature of statistical systems in social networking backend's is the analysis of when members are mostly, or better said, in average online. Imagine, that we have a database table to store DATETIME-timestamps of when users logged in, which could look like this:
I may or may not be interviewed on the Swedish language Radio Vega. The background to this confusing statement is that the Finnish government today publishes a report on the use of open source in Finnish companies and associated risks related to that. I was a contributor to the report since Monty Program is a Finnish open source company. I guess since I speak Swedish, the reporter had scheduled a telephone interview with me after the press conference.
Calpont is pleased to announce high performance sub-query support with the latest alpha version (1.1.2) of InfiniDB. We’re especially happy about this release because polls done by MySQL have consistently shown – year after year – that slow subquery performance is one of the top items the MySQL community wants fixed in the server. It’s been complex work for sure, but the Calpont engineering team has come through again and delivered high...
Users of HeidiSQL 3.x may have missed this feature since grid and
BLOB editing was rewritten for v4: A preview area for images. Now
it's even more powerful than before: it detects and loads various
kinds of images (JPG, PNG, GIF, BMP, PSD, TIF and more). Also,
the new preview area does not take valuable space when you're in
some grid, as it's placed below the database tree:
I know it has been a long time between drinks/posts, but I've
been busy -- I promise! :)
Today I spent a considerable amount of time trying to figure out
why an INSERT SELECT ON DUPLICATE KEY UPDATE was not behaving as
I would expect.
Here is an example to illustrate:
CREATE TABLE t1 (
id INT AUTO_INCREMENT,
num INT NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);
CREATE TABLE t2 (
id INT NOT NULL,
num INT
);
INSERT INTO t1 VALUES (1, 10);
INSERT INTO t2 VALUES (1, NULL);
INSERT INTO t1
SELECT id, num
FROM t2
ON DUPLICATE KEY UPDATE
num=IFNULL(VALUES(num), t1.num);
To convert the above query into plain English -- I'm saying,
INSERT into table t1 the id and num …
We have all been caught out with using SET and not realizing that the default GLOBAL Scope (since 5.0.2) does not change the current SESSION scope.
I was not aware until today that changing GLOBAL scope has some exceptions that also automatically affect SESSION scope.
What I expected with a change in the GLOBAL scope is no affect SESSION scope. For example.
mysql> SHOW GLOBAL VARIABLES LIKE 'read_buffer_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | read_buffer_size | 131072 | +------------------+--------+ 1 row in set (0.00 sec) mysql> SHOW SESSION VARIABLES LIKE 'read_buffer_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | read_buffer_size | 131072 | +------------------+--------+ 1 row in set (0.00 sec) mysql> SET GLOBAL read_buffer_size=1024*256; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE …[Read more]
By default MySQL allows you to create user accounts and privileges with no password. In my earlier MySQL Best Practices: User Security I describe how to address the default installation empty passwords.
For new user accounts, you can improve this default behavior
using the SQL_MODE variable, with a value of NO_AUTO_CREATE_USER.
As detailed via the 5.1 Reference Manual
NO_AUTO_CREATE_USER
Prevent the GRANT statement from automatically creating new users
if it would otherwise do so, unless a nonempty password also is
specified.
Having set this variable I attempted to show the error of operation to demonstrate in my upcoming “MySQL Idiosyncrasies that bite” presentation. …
[Read more]