DBA Blogs

SQL Developer Data Modeling Update

Jared Still - 10 hours 5 min ago
Oracle has released an 'early adopter' version of the the data modeling enhancements to SQL Developer.

See the OTN article for details.

I haven't tried it yet, it will be interesting to see just how well it works.
Categories: DBA Blogs

Alter table Muth add partition Colin…

Tyler Muth - Sun, 2008-10-12 20:20
alter tabe Muth add partition Colin weight 4 pounds 13 ounces length 18 inches / begin   for i in 1..infinity   loop     delete from Muth partition Colin where garment = 'Diaper';     insert into Muth partition Colin ('Milk');     dbms_lock.sleep(10800);   end loop; end; More pics here if you’re interested.       
Categories: DBA Blogs, Development

What Would You Tell a Potential Blogger?

Tyler Muth - Sun, 2008-10-12 20:15
I have a great opportunity to speak to a group of University students about blogging.  I have a lot of advice based on my experiences, but I discovered early on that one of the most valuable aspects of blogging is that you can learn more from your community than they can learn from you (if [...]
Categories: DBA Blogs, Development

mysqlbinlog --server-id before MySQL 5.1? awk to the rescue!

Pythian Group - Sun, 2008-10-12 12:55

Recently I had an interesting issue crop up. Due to an unfortunate migration incident in which involved master/master replication and not checking to see if replication was caught up, we ended up with an infinite replication loop of a number of SQL statements. awk helped immensely in the aftermath cleanup.

The basics of the replication infinite loop were (more…)

Categories: DBA Blogs

Delayed Block Cleanout -- through Instance Restart

Hemant K Chitale - Sun, 2008-10-12 09:58
Continuing my previous post on Delayed Block Cleanout, in this post, I show how a database shutdown doesn't guarantee that all the modified blocks in a table are "cleaned out" before the shutdown.

I run a large update and commit it. Next, I shutdown the database. The first query against the table after the subsequent restart still has to examine modified blocks, confirm if the updates to rows in the block have been committed and then execute a cleanout of these modified blocks. Thus, we see "consistent gets - examination" and "cleanout%" statistics being incremented for the first query after the restart :


SQL> select blocks,num_rows from user_tables where table_name = upper('test_d_b_c');

BLOCKS NUM_ROWS
---------- ----------
47069 3242752

SQL> SELECT COUNT(*) FROM TEST_D_B_C;

COUNT(*)
----------
3242752

SQL> @Report_select_Statistics
SQL> set echo off

Statistic Value
---------------------------------------------------------- ----------------
cleanout - number of ktugct calls 0
cleanouts and rollbacks - consistent read gets 0
cleanouts only - consistent read gets 0
consistent changes 0
consistent gets 47,513
consistent gets - examination 207
db block changes 0
db block gets 0
redo entries 0
redo size 0
undo change vector size 0
SQL>
SQL> REM Run an update on 60% (not all) of the rows
SQL> UPDATE TEST_D_B_C SET COL_1 = 'HKC_'||SUBSTR(COL_1,4,11) WHERE MOD(COL_4,10) > 3 ;

1945344 rows updated.

SQL> COMMIT;

Commit complete.

SQL>
SQL> REM We will now shutdown and restart the database
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 2023688 bytes
Variable Size 264244984 bytes
Database Buffers 461373440 bytes
Redo Buffers 6361088 bytes
Database mounted.
Database opened.
SQL>
SQL> connect hemant/hemant
Connected.
SQL> REM Now let's query the table
SQL> REM Will the first execution suffer delayed block cleanout ?
SQL>
SQL> SELECT COUNT(*) FROM TEST_D_B_C;

COUNT(*)
----------
3242752

SQL> @Report_select_Statistics
SQL> set echo off

Statistic Value
---------------------------------------------------------- ----------------
cleanout - number of ktugct calls 46,969
cleanouts and rollbacks - consistent read gets 0
cleanouts only - consistent read gets 46,969
consistent changes 0
consistent gets 94,910
consistent gets - examination 47,367
db block changes 46,973
db block gets 3
redo entries 46,971
redo size 3,383,012
undo change vector size 204
SQL>
SQL> SELECT COUNT(*) FROM TEST_D_B_C;

COUNT(*)
----------
3242752

SQL> @Report_select_Statistics
SQL> set echo off

Statistic Value
---------------------------------------------------------- ----------------
cleanout - number of ktugct calls 46,969
cleanouts and rollbacks - consistent read gets 0
cleanouts only - consistent read gets 46,969
consistent changes 0
consistent gets 141,993
consistent gets - examination 47,367
db block changes 46,973
db block gets 3
redo entries 46,971
redo size 3,383,012
undo change vector size 204


It is the first full table scan query that shows "consistent gets - examination" and "cleanout%" statistics. It did 46,969 examinations and cleanouts and generated 3.383MB of redo (for the correspondng 46,971 redo entries).
Categories: DBA Blogs

Log Buffer #118: a Carnival of the Vanities for DBAs

Pythian Group - Fri, 2008-10-10 10:01

The 118th edition of Log Buffer, the weekly review of database blogs, has been published on Ward Pond’s SQL Server blog.

Log Buffer is the only platform-neutral, distributed, human-edited article on database blogs. It receives several thousand views each week, and publishing an edition on your own blog brings those views to you. Hosting an edition of LB also introduces you and your blog to your colleagues in the DB blogosphere. Write me an email and I’ll get you started.

Now, here’s Ward Pond’s Log Buffer #118.

Categories: DBA Blogs

Introducing The Latest Oracle ACE Director (Aces High)

Richard Foote - Fri, 2008-10-10 06:38
I’ve just found out over the past couple days that a nomination for me to be made an Oracle ACE Director has been accepted by Oracle Corporation. It’s always pleasing to have your efforts acknowledged and appreciated by the Oracle community and so it’s really nice to be recognised in this manner by Oracle. The Oracle ACE Program was setup a [...]
Categories: DBA Blogs

It’s a Good Time to be Involved with MySQL

Pythian Group - Thu, 2008-10-09 17:28

In many parts of the world times are uncertain. I live in the United States and we are in the middle of a financial meltdown that many fear may be as bad as the Great Depression. Because the world’s economies are so linked it is causing severe distress in many other countries as well. I just read that two trillion dollars have been lost from nest eggs in the last 15 months here in the States.

I am not going to turn this into a rant about who is right, who is wrong, or  what should be done about it to resolve the problem. This isn’t the place. I probably don’t even have the right answer. I have a different angle.

If you are involved with MySQL as a database administrator, or if you work directly with MySQL in some other aspect, you can probably breathe a little easier. Why is this? MySQL Server has grown in market penetration for a long time. It is now a significant section of the RDBMS pie.  I predict that this market penetration will only continue to grow. As this economic downturn/recession/whatever continues, companies will look harder for ways to save money. What better way to do so than replace your proprietary RDBMS that can cost you significant amounts of money, with MySQL Server? For all intents, the same functionality is there, the speed and flexibility is certainly there, and there is a giant company behind MySQL now, providing “enterprise-ready” support.

The market is crying right now for MySQL database administrators. We don’t cost any more than Oracle or Microsoft DBAs, you know. Just a couple of years ago, very few companies hired MySQL DBAs. They hired developers who also did database administration, or a system administrators who also managed the MySQL server. Now, as the number of database servers increases and the amount of data grows they want real, honest-to-goodness database administrators. If you have production experience with MySQL server in any significant amount you will not have any problems finding a job. I don’t think this is going to change anytime soon. So, even if your company succumbs to the times, there are others out there who need your experience. Don’t be dismayed! (more…)

Categories: DBA Blogs

Does anybody really know what time it is?

Pythian Group - Thu, 2008-10-09 12:06

This is a post about SYSDATE() and NOW() and CURRENT_TIMESTAMP() functions in MySQL.

Firstly, note is that of these three, only CURRENT_TIMESTAMP() is part of the SQL Standard. NOW() happens to be an alias for CURRENT_TIMESTAMP() in MySQL.

Secondly, note that replication does not work well with non-deterministic functions. And “hey, what time is it?” is non-deterministic. Ask it twice, with a second apart between asking, and both times you get different results (with at least second precision).

You can start to see the problem here….but there’s more…. (more…)

Categories: DBA Blogs

Audit a MySQL Instance with MySQLTuner

Pythian Group - Thu, 2008-10-09 09:20

Quite often we need to perform a so-called “MySQL instance audit”. This common DBA procedure should give you a general view of the MySQL environment. You may be interested in a basic understanding of what kind of operation MySQL performs, how much memory does it use, or how well does it look from the performance point of view. There is no easy out-of-the-box way to do such an audit on a MySQL server. You can use SHOW STATUS and check the list of system variables, but this way can hardly be called DBA-friendly.

Fortunately there are several tools to make this process easier. Among most popular are mysqlreport and MySQLTuner. In this post I’d like to give a brief overview of MySQLTuner.

So, what can MySQLTuner do? Quoting the documentation: “MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. Within seconds, it will display statistics about your MySQL installation and the areas where it can be improved.”

It’s not magic — they don’t use any “hidden” or unknown MySQL features to provide the report. What they do is use SHOW STATUS metrics and provide a user-friendly report, interpreting data this or that way. What makes such tools really valuable is the way they interpret that data.

(more…)

Categories: DBA Blogs

MySQL: RENAME TABLE on Transactional Tables Can Jeopardize Slave Data

Pythian Group - Wed, 2008-10-08 18:09

Do you have a master-slave MySQL set up?  Ever do DDL changes on the master?  You may be hit with a serious data integrity bug.  Read on.

One of our clients does a regular rename tables on the master to keep the current table small and archive off old data.  We’d occasionally be hit by a ‘duplicate key’ error on the slave on the current table and have to resolve it manually.  Digging into the issue, I managed to replicate it on demand and filed bug 39675 with MySQL, which subsequently has been verified and slated for fix, though from what it seems only in version 6.0.  The bug affects all versions of MySQL from 4.1 to 6.0.

In a nutshell, here is what happens. The rename tables command only checks for pending transactions or locks in the current session.  If there is a pending transaction in another session on the table being renamed, the rename will succeed, but the order in which the transaction is written to the binlog will be different from the order in which the transactions were applied on the master.  This means that the data on the slave will now be out of sync for this table.

Here’s a test-case:

(more…)

Categories: DBA Blogs

Hard choices: back on the road

Beth Breidenbach - Wed, 2008-10-08 00:22
I'm having a Doug Burns moment. Those of you in the Oracle blogosphere likely recall Doug going to work for Pythian (a top-notch remote DBA support company) for a bit. In the end, whilst he loves the company and still has a great relationship with them, he had to decide it wasn't the right place for him. I recall reading his post and thinking how important it is for each person to understand
Categories: DBA Blogs

Reporting from Perth AUSOUG Conference 2008

Pythian Group - Tue, 2008-10-07 19:18

My presentation was on the first day, right after the keynote, and following my habit of reviewing the slides before the show, I spent this keynote hour one-to-one with my MacBook. I could never underestimate the importance of this review following one fiasco I had in the past when I neglected to thoroughly review the existing slides of my older presentation before presenting it.

At the beginning of the session, once again I realized that DBA audience at AUSOUG conference is relatively small percentage. On the other hand it might be Tim Hall who has stolen my DBA audience to his session about PL/SQL 11g new features. Yeah… it must have been Tim as it turned out later he is the number two speaker in Australia. ;-)

My few jokes on the initial slides were not as good as they would be with larger audience but, at least, resulted in smiles so I guess I can call it success to a certain degree. The presentation itself went well I think but I wasn’t too trilled about it and there are few places I want to change before I present it at Gold Coast. Well, live and learn. I’ve got some positive responses afterward and even a piece of photography art tagged by “excellent” courtesy to Francisco Munoz Alvarez:

Alex Gorbachev presenting in Perth

I managed to fit comfortably within 45 minutes with few question during and after the presentation. It’s been traditionally very difficult for me to manage 45 minutes slot but I guess I’m getting better at it.
(more…)

Categories: DBA Blogs

SQL Server: Understanding and Controlling Connection-Pooling Fragmentation

Pythian Group - Tue, 2008-10-07 15:08

I got the idea for this article when one of our clients complained that their server’s performance was degrading during business hours. They thought it was weird that at the same time, SQL Server would list more than 1200 connections on SQL server Activity Monitor.

The server hosts more than 50 databases that serve an ASP.NET application hosted on some servers in a web farm. These servers issue connections to the databases in a distributed manner to balance the web application load. I tried to discover what these connections were doing and to what databases they were connected. Connections grouped by database:

select  db_name(dbid) , count(*) 'connections count'
from master..sysprocesses
where spid > 50 and spid  @@spid
group by  db_name(dbid)
order by count(*) desc

This showed some databases having more than 300 connections associated with them.

What about logins used?

select  loginame , nt_username, count(*) 'Connections count'
from master..sysprocesses
where spid > 50 and spid  @@spid
group by  loginame , nt_username
order by count(*) desc

This showed a mix of windows domain accounts (those with values in column nt_username, e.g: domain\user) beside SQL authentication accounts (those with column nt_username empty, e.g: “sa”).

In order to reduce the number of times that new connections must be opened, applications may use connection pooling. This was clearly not the case here, and all these connections resulted in what is known as “pool fragmentation”.

So what’s connection pooling? how does it work, what can cause pool fragmentation and how can we avoid/reduce it?

(more…)

Categories: DBA Blogs

Constraints With Oracle Total Recall (Remember A Day)

Richard Foote - Tue, 2008-10-07 05:02
11g has many new and potentially useful features which I’m slowly investigating and evaluating. One feature that has the potential to be particularly useful where I work is “Oracle Total Recall“. It provides the facility to automatically capture all changes to any tables you choose, in a “secure, tamper proof” manner. This enables all changes to [...]
Categories: DBA Blogs

AIOUG - TechNight in Bangalore

Virag Sharma - Mon, 2008-10-06 17:27

The All India Oracle User Group with a 4 lakh -strong developer base and a community of seven-lakh users in India is organising a second meeting in Bangalore on October 22 to share experiences and provide insights into shape of things to come. It is the second largest technology network of professionals for Oracle after the US. Soon these TechNights will be held in other parts of India, such as Pune, Mumbai and Delhi.

Like several successful Oracle user groups around the world, AIOUG is also a not for profit organization formed by Oracle users for Oracle users. Let’s join hands across the country to make AIOUG a global lead user group.

AIOUG first TechNight held at Hyderabad, Please click here to see TechNight picturesThe views expressed are my own and not necessarily those of my current or past employer. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine
Categories: DBA Blogs

DBD::mysql on OS X Quirks: Architectures, MySQL Binaries and the Filesystem

Pythian Group - Mon, 2008-10-06 14:50

Yesterday evening, a friend of mine had some issues with installing DBD::mysql, and asked if I had encountered the same issue. The problem, as the output from make test showed, was that certain symbols was missing:

#     Tried to use 'DBD::mysql'.
#     Error:  Can't load '/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle' for module DBD::mysql: dlopen(/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle, 2): Symbol not found: _is_prefix

Fair enough, this is related to a 64-bit issue with MySQL—at least with my Perl version, which is now:

Summary of my perl5 (revision 5 version 10 subversion 0) configuration:
  Platform:
    osname=darwin, osvers=9.5.0, archname=darwin-thread-multi-64int-2level

If you try to link to a x86_64 version of MySQL, then you get the above mentioned error. So, I downloaded an x86 version of MySQL and tried again. The output from perl Makefile.PL:

  cflags        (mysql_config) = -I/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/include  -g -Os -arch i386 -fno-common   -D_P1003_1B_VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_DONT_BREAK_READ -DIGNORE_SIGHUP_SIGQUIT  -DDONT_DECLARE_CXA_PURE_VIRTUAL
  embedded      (mysql_config) =
  libs          (mysql_config) = -L/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/lib -lmysqlclient -lz -lm     -lmygcc
  mysql_config  (Users choice) = /Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/bin/mysql_config

That looks all good and nice, the right paths, versions and everything. So I compiled it OK, but when running make test again: (more…)

Categories: DBA Blogs

Database Migration From Windows to Linux Using RMAN

Alejandro Vargas - Mon, 2008-10-06 11:29
This post describes the procedure required to migrate a database from Windows to Linux using the RMAN Convert Database command. Both Windows and Linux platforms have the same endian format, which makes possible to transfer the whole database, making the... alejandro.vargas
Categories: DBA Blogs