DBA Blogs
SQL Developer Data Modeling Update
See the OTN article for details.
I haven't tried it yet, it will be interesting to see just how well it works.
Alter table Muth add partition Colin…
What Would You Tell a Potential Blogger?
mysqlbinlog --server-id before MySQL 5.1? awk to the rescue!
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…)
Delayed Block Cleanout -- through Instance Restart
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).
Log Buffer #118: a Carnival of the Vanities for DBAs
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.
Introducing The Latest Oracle ACE Director (Aces High)
It’s a Good Time to be Involved with MySQL
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…)
Does anybody really know what time it is?
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…)
Audit a MySQL Instance with MySQLTuner
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.
MySQL: RENAME TABLE on Transactional Tables Can Jeopardize Slave Data
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:
Hard choices: back on the road
Reporting from Perth AUSOUG Conference 2008
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:
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…)
SQL Server: Understanding and Controlling Connection-Pooling Fragmentation
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?
Constraints With Oracle Total Recall (Remember A Day)
AIOUG - TechNight in Bangalore
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
DBD::mysql on OS X Quirks: Architectures, MySQL Binaries and the Filesystem
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…)