opendba

Missed the MySQL Conference

2010-04-19T10:14:00.000-07:00

My co-worker got back from the MySQL Conference today. Sadly I didn't make it this year, but sounds like it was great as usual. Sorry I didn't get to see you all, but I'll do my best to catch you next year...

Firewalls... what can you say?

2010-02-09T11:28:00.000-08:00

Every time I've had to setup something 'special' with a database server and a firewall for the first time there always seems to be way to many issues.With MySQL it's been mostly straight forward; opening an additional port or to for supporting the enterprise monitor or something, but nothing crazy.Oracle, however, gets me every time. :(We were setting up a new Oracle 11g instance, and decided we

MySQL Remote Connections for Replication

2009-12-01T15:21:00.000-08:00

Here is another interesting problem:When trying to set up master to master replication from Server A to Server B, the replication user can login from A to B, but not the other way. The error thrown out by MySQL:ERROR 1130 (HY000): Host '' is not allowed to connect to this MySQL serverThings to check:1. Replication user password and host2. Ability to ping the remote server3. nslookup the remote

Much Ado about a space

2009-12-01T12:15:00.000-08:00

After upgrading SQL Server 2005 to SP3 I had difficulty bringing SQL server back online. The error message indicated a problem when opening 'master.mdf' or a problem with start up parameters. The parameters looked ok and I did not want to reinstall the SQL server.... Online research pointed to potential master database corruption, however the problem turned out to be a simple one -there was an

Varbinary to string

2009-11-19T14:34:00.001-08:00

I've experienced a particular frustration with SQL Server many times over the years: the seeming impossibility of programmatically fetching a varbinary value from a table, and converting it to the string value required by a procedure call in the same batch. One example would be when troubleshooting replication by extracting a xact_seqno varbinary(16) value from the MSdistribution_history table,

solving frequent replication distribution latency errors

2009-11-19T13:53:00.000-08:00

We have many millions of commands a day passing through a 2-node peer-to-peer replication topology. At some point, replication lost its ability to keep up with distribution. After much research, we replicated the issue to Microsoft and were told that our distribution database was too big. They recommended that we set immediate_sync on the publication to false.To me, it made no sense why this

Shrinking data files, part 1

2009-11-02T19:32:00.000-08:00

You've probably heard admonishments to avoid shrinking data files, especially on a heavily used database. The standard usage of DBCC SHRINKDATABASE or DBCC SHRINKFILE (not using the TRUNCATEONLY option of these commands) will move any data at the tail of the file to the beginning. The most frequently cited negative consequence of this action is serious fragmentation of the data you moved as data

SQL Server: Named Instance Or Default?

2009-07-21T13:32:00.000-07:00

I was looking for an easy way to tell if the current instance of SQL Server is a default or named instance since we use both. I could not find anything online. After a little bit of poking around, I figured it out -its quite simple. Go to 'My Computer' and right click for the 'Manage' option. Then under 'Services' look for the SQL Server service entry. If its a named instance it will have the

Lost and Found ?

2009-03-27T21:46:00.000-07:00

Sometimes you just have to laugh at the crazy things that can kill a good evening. I had this brilliant idea to change our replication setup on one of our Master-Master replication server setups this week. I got sick of having to restart MySQL every time we wanted to add a new database and have it included in the list of replicated databases - we were using replicate-do-db in our configs.So it

What just happened to the database?

2009-03-14T23:06:00.000-07:00

It's always fun when you come into work and notice that one of your database monitors/graphs has changed from showing almost no row accesses/sec to 40,000 rows/sec in a matter of minutes. And then by lunch time they are showing 90,000 rows/sec. What's up with that?MySQL Enterprise Monitor Row Accesses:Well, of course the first thing you do is check all your cacti monitors, because there is

Partition Management....(Don't forget the Management)

2009-01-23T07:31:00.000-08:00

You'd think by now I'd know better than to cut corners. But for some reason, I still somehow seem to decide once in a while for short term gains over long term benefits...A while back I was overloaded with stuff so I set up a year's worth of new monthly partitions in one of our reporting databases. I did not, however, chose to create any nice job to automatically create new partitions or

MySQL Replication and bad assumptions

2008-11-25T17:57:00.000-08:00

Sometimes I amaze myself in my capacity to make assumptions about how things should work, especially when it comes to test plans... ( You know what happens when we assume, right? )I had this great idea to setup a couple slaves off a master-master replication set something like this:MASTER A <--------------> MASTER B | | | | |

MySQL Partitioned Tables with Trigger enforced FK constraints

2008-10-04T14:00:00.000-07:00

Well, I suppose its' true you can't use the official MySQL ALTER TABLE statement to do it, but if you're willing to trust your trigger coding abilities you can.All you need is an extra table and a couple triggers.The concept is fairly straight forward:Create a before insert trigger on the child table that validates the parent exists in the parent table.If there is no parent found, then insert a

MySQL Data Woes... or, Making Use of the Information_Schema

2008-10-01T15:26:00.000-07:00

To be fair - I highly doubt it has anything to do with MySQL.Database corruption - that's a different issue, often related to a bug in database code; but this was a data corruption issue, which is always a pain in the you know what to figure out and get fixed.I spent the last day trying to figure out the weirdness in a couple of our databases, digging around, explaining to developers that

Where are all the good MySQL DBAs?

2008-09-28T21:12:00.000-07:00

We've been looking for a good MySQL DBA at work for a little over a year now. At this point I'm beginning to wonder if DBA's are a dying breed all together...Maybe it's the fact that we're in the Northwest (Seattle), or perhaps they've all gone to work for Percona?Perhaps everyone has more fun developing? (We haven't had much trouble filling our Sr. Development positions or QA positions..)It's

MySQL: Improving your skills with Forums

2008-06-11T12:11:00.001-07:00

I haven't been a big user of the MySQL Forums till recently.I'm not sure why - partly because I like to think I know something about what I do ( I just need to remember that there is always someone else who knows more), partly because I have a need to re-invent the wheel as often as possible, and partly because I dont' like waiting around for an answer...It can be a little overwhelming when you

MySQL: replicate-* rules should be dynamically configurable

2008-04-23T13:55:00.000-07:00

I wonder what the best way is to get a feature request more visibility (convert a feature request to an actual work item).We use replicate-do-db on all our slave servers , so after many, many, restarts of our slave servers, I checked the bug list for any feature requests surrounding this, and about 6 months ago, one was opened: replicate-* rules should be dynamically configurableSadly,

Speaking at the Conference

2008-04-18T12:49:00.000-07:00

I had a great time speaking about partitioning at the MySQL conference this week.Special thanks go out to Mattias Jonsson as well for helping answer some of the questions afterwards. The room was packed - which makes me excited for the future of MySQL partitioning!I hope everyone got something out of the session, and I'd gladly welcome any comments (positive or constructive).I plan on doing some

Just a DBA in a developers world...

2008-04-10T18:15:00.000-07:00

Sometimes I wonder where this whole web development thing is going...I was asked to setup some backup and recovery tools for the BerkeleyDB Java Edition (JE).Hmm... DB... I could probably argue that DB should be dropped from the name if you add Java edition to it. I suppose maybe it's Oracle's plan to drop BerkeleyDB for the newer improved BerkeleyDB Java Edition (JE). Maybe that's why there

MySQL: Getting Creative with Partitioning (Performance Results)

2008-03-31T19:30:00.000-07:00

I decided to run some very basic performance test comparing the non-partitioned table with a primary key, and a partitioned table with a primary key and a unique constraint enforced via a secondary table explained in my previous post.Overall, it appears that with partitioning, as the data/rows scale, the inserts actually get faster :) This is what I would expect theoretically, so score one for

MySQL: Getting Creative with Partitioning

2008-03-24T18:41:00.000-07:00

Lately, I've been trying to keep up with at least one of the MySQL Forums: Partitioning.It's a great way to keep on top of issues surrounding partitioning, and also get an idea of what people are trying to do with the new 5.1 feature. Richard came up with an interesting problem that I jumped into only to realize that I hadn't done my homework, and my initial suggestion wouldn't work at all due

MySQL: Finally an ability to trace/profile

2008-03-19T12:46:00.000-07:00

Finally! The ability to look a little closer into what's happening with SHOW PROFILEHere’s how it works:mysql> set profiling=1;mysql> select count(*) from mysql.user;+----------+| count(*) |+----------+| 5 |+----------+1 row in set (0.00 sec)mysql> show profile;+--------------------------------+----------+| Status | Duration |+--------------------------------+-----

Best Practices - Oops...

2008-03-18T14:01:00.000-07:00

Yea, yea, yea... best practices.I spent about a week troubleshooting issues on a MySQL 5.1 replication issue where certain transactions causing duplicate key on index errors stopped replication. Easy enough to fix, right? mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; mysql> start slave;Hmm... nice until it happens almost every minute...So, after reading every link I could find on the internet,

MySQL: Innodb internals

2008-01-18T10:36:00.000-08:00

Went to the MySQL Performance Tuning Class last week. I would highly recommend it to any DBA new to MySQL, or any MySQL DBA looking to boost their tuning skills and understanding of some of the various features as they relate to performance.The next few posts will have to do with information I gleaned from that class...It's always good to understand what's happening underneath the covers when it

SQLServer: What am I running anyway?

2007-12-13T09:41:00.000-08:00

Microsoft has enough crazy patches and service packs to drive a dba nuts! (that said, Oracle, Mysql, and most other databases have their fair share as well). Question is though, what version are we running anyway? For those that don't come from the Microsoftie world, the whole daily build number thing in the version is a little out of control.IE:Server A:1> SELECT @@VERSION2> goMicrosoft SQL

SQLServer 2005: Full Text Issue

2007-11-29T09:31:00.000-08:00

In my opinion, there is security, and then there is the absurd paranoia. Microsoft tends to ride this line a little too much lately. Trying to be the end all to security, they forget that we have firewalls, access lists, etc, etc that should be considered when locking things down.The interesting issue I've run into with Full Text searches now, is , somewhat not surprisingling, a known Microsoft

SQLServer 2005: Full Text Search Monitoring

2007-11-16T09:57:00.000-08:00

Seems more and more lately that a DBA can't get out of being the Full Text query expert. Whether that is an in-database technology such as Full Text/Intermedia Text, or an external database technology such as google appliances / FAST appliances. The typical DBA is always stuck in between somewhere, and looks like we have to understand at least how to monitor and troubleshoot these indexing

MySQL : the beauty in beta

2007-10-19T10:34:00.000-07:00

Beta or not, here we come. Yea, you'd think I'd learn to check out the known bugs list before letting developers/qa have at the new database servers running in the lab with MySQL 5.1.21-b . I mean, how bad could beta really be?Well, as we should all know, beta is called beta for a reason, but sometimes we open source fanatics tend to jump on board, dealing with issues along the way. This

MySQL innodb plugin

2007-10-17T13:34:00.000-07:00

Recently I've spent hours compiling MySQL 5.1.22 and trying to get my cent0s configured for innodb and partitioning. I tried every imaginable way to run configure, use different plugins, etc.I messed with this forever, then finally looked at the config options, and sure enough, the ./configure expects innobase, not innodb as the plugin name: from: http://www.innodb.com/support/tips On MySQL

SQLServer: Image Replication - Oops

2007-10-15T12:22:00.000-07:00

Yea, it's always something, huh?Better mind your default configuration options with SQLServer 2005 if you plan on transactional replication with LOBs. It's nice that they only replicate the appropriate changes, but you'll get an error if you are using images with sizes > the default replication size allowed.See the Microsoft article for more info.Fun... :)

SQLServer: Transactional Replication Issues

2007-10-01T09:21:00.000-07:00

After seeing a few friends at the SQLPass 2007 Summit in Colorado and discussing some of the finer 'features' of pier to pier transactional replication and large database systems (my friends work on the replication team at Microsoft), I quickly ran into one of my big grips with P2P replication and SQLServer 2005.I had noted that being able to set up P2P from a 3rd party backup tool is essential

MySQL Migration Toolkit

2007-09-28T09:59:00.000-07:00

I've been evaluating the latest version of the MySQL Migration Toolkit to work on migrations from SQLServer 2005 to MySQL 5.1Here are some of the things I found so far:Ease of use: Very simple and fast to get up and running - I was able to get multiple databases migrated from SQLServer to MySQL without any special setup or configuration changes. Gui is straight forward and intuitive.Took a

SQLServer: Tracking Down Stat Issues

2007-09-05T15:12:00.000-07:00

The old debate of auto update statistics. I've had issues with it in the past, and coming from an old UNIX / Oracle background, I've always been of the opinion that I know best ( probably not, but it's fun to believe that).The advantage of use sp_updatestats over update statistics is that is looks at sys.sysindexes for the status of indexes and whether or not they need to be updated or not,

SQLServer: Setting up Access from UNIX

2007-08-03T13:31:00.000-07:00

These are the detailed instructions for getting t-sql setup to run on UNIX.This assumes that you have perl 5x installed, and CPAN as well....Update CPAN to the current available version cpan > install Bundle::CPANUse CPAN to install the DBI::Bundlecpan > install Bundle::DBIInstall FreetdsDownload files from hereGunzip and untar the freetds tar.gz fileRead the README!Run configure tool to

SQLServer: The problem with SQLServer is the OS it runs on :)

2007-07-09T13:21:00.000-07:00

Yea, No matter what way you slice it, the really problem I have with SQLServer, is the operating system it runs on. Seems time after time my SQLServer issues are more related to the OS than anything else. The latest issue surrounds dual core processors and parallelism. By default, SQLServer's optimizer seems to look at the number of processors on a systems, multiply it by 2, and use that for

SQLServer 2005: Capturing Processes Waiting / Blocked

2007-06-22T14:16:00.000-07:00

OK - it's out of order (this is supposed to be a followup on partitioning ), but I'm always writing some code and then later losing it and re-creating it, so I decided to put this up today...Everyonce in a while we've had short 'blocking / waiting' where things back up in the system. It's kindof a pain in the you know what when it disappears after a few minutes, so I wrote some code to grab all

SQLServer 2005: Automated Partitioning Part 1

2007-06-08T10:34:00.000-07:00

Partitioning is great, but it has to be automated if you want to do anything regularly with it. And as it typically goes, if you can do a task in repeatable order with T-SQL commands, you ought to be able to automate it, right?In the next few weeks I'll layout a hopefully straight forward way to automate your partitioning in SQLServer 2005. Though the examples will be done with date range

TUNING: Taking Advantage of New Features

2007-05-31T15:09:00.000-07:00

Wouldn't it be great if we always were able to design things from scratch? So many things could be re-hashed and re-designed. One of the real reason's we're (DBA's that is) frustrated all the time with poorly scalable products, is that we don't pro-actively encourage evolving architectures in a meaningful, planned way.It's always those developers throwing things over the wall, or the feature

MySQL: getting started for newbies

2007-05-25T11:43:00.000-07:00

When MySQL first came out and started popping up all over the web in the e-commerce arena, if you were like me you looked at it as a wannabe. 'Call me in a few years when you grow up'. Yea, sometimes the Oracle in us goes to our heads a little to fast...MySQL has really gone mainstream in the last few years. Companies that never would have considered MySQL in the past for critical database

VMWare: The greatest thing since sliced bread

2007-05-18T11:03:00.000-07:00

So if you like to try out new things, and you're not using VMWare yet, get with the program!Really, though - it's so easy now (and the VMServer is free) . Just download and install.VMWare lets you run mini virtualized server environments within your current operating system. Yea, there is a little overhead, but for testing and trying out new databases, configurations, applications, it's

SQLServer 2005: DMV's and determiniing fragmentation

2007-05-14T10:20:00.000-07:00

Dynamic Management Views (which are not all views, some are stored procedures / functions) are very useful and if you're a DBA working with SQLServer, you should know them inside and out, and have you're list of scripts ready at hand.Here's a couple that work well for determinining fragmentaion issues:*Note: Though these can be done on line, the due use CPU and heavy READ I/O - be sure to know

SQLServer 2000: Denormalizing ?

2007-04-18T12:53:00.000-07:00

Ever get one of those requests to denormalize a table into a view (sometimes referred to as pivot tables) ?Here's some code to use a function and view in SQLServer to denormalize data from a single table to force a pivot table based on a key:create table test_table( key_field int , cat_field varchar (512))insert into test_table values (1,'John')insert into test_table values (1,'R')

SQLServer 2005: Working with partitions

2007-03-13T07:30:00.000-07:00

Now that SQLServer finally supports something the database world would really consider a decent first attempt at partitioning, I think it's high time we all start putting it to practice.Partitioning in the Oracle world is almost taken for granted. It's one of the most straight forward ways to scale a database in terms of size and performance, without looking at additional hardware and/or heavy

SQLServer 2000: Memory Grants Pending

2007-03-05T13:26:00.000-08:00

I tell you -Finding out anything about memory grants pending is virtually impossible. Yea, there are a few references to it out there in the google world, but most just refer to the fact that it's not much more than the title implies. SQLServer has memory grants for processes queued up.At surface value, you would expect it would mean 'get more memory', which is what most of the links imply.As it

SQLServer: Joining Views in Queries

2007-02-13T14:09:00.000-08:00

Two Words: Bad Idea!The problem with this is that the SQLServer optimizer has to determine the best cost for the set of joins, and then base that on a view that has additional joins underneath it.It's almost always more efficient to rewrite the query to join the actual tables, and the optimizer is much faster at finding the Cost. We were also able to determine that when evaluating joins to view,

SQLServer: 2005 Interface Woes

2006-05-17T08:58:00.000-07:00

We've been evaluating SQLServer 2005 for a couple weeks now. It's taken that long just to get used to the new interface. In my humble opinion, it would have been better to spend more time working on features than working on the interface.That said, after a few weeks of playing with it, I'm getting used to the new interface.Drawbacks:Very resource consuming on the desktopBringing up the properties

SQLServer: Issues with update triggers and bulk updates

2005-09-27T16:09:00.000-07:00

It appears that there may be a bug with SQLServer 2000 and some update triggers.We have a constraint update trigger on a table that verifies entries in a secondary table before allowing some fields to be updated. The trigger fires consistently when an update is done one at a time, but when a bulk update is done (refering just to many rows at once), the trigger does not consistently fire.We'll be

SQLServer: To Lock or NOLOCK

2004-11-17T13:06:00.000-08:00

I don't think anyone would argue that one of the biggest problems on SQLServer (besides that it runs on Windows) is how terrible it's locking and lock escalation process is.Then when things seem to go slow when on inserts, the first thing the developers do is modify all selects to use the NOLOCK hint. Yea, so in theory that makes sense, but not only does it imply that data results may be

SQLServer: Statistics - Auto vs Manual update ?

2004-11-16T08:27:00.000-08:00

Have you ever seen 'select statman(....)' running while your SQLServer is under heavy transactional load? If not, you either don't have auto update stats turned on, or you database is not really under heavy load.By default, auto update is turned on, and SQLServer keeps track of how often indexes / tables are changed. Once 20% or 500 rows are changed, SQLServer starts updating statistics to

ORACLE: Sending Logs/Files via mailx

2004-11-12T09:31:00.000-08:00

This may be irrelevant depending on your choice of monitoring tools, but I tend to prefer good old fashioned shell scripts on UNIX. One issue I've always had with mailx is being able to attach error files.Here's a sample script that seems to work on most UNIX flavors with sh,bash, or ksh:#!/bin/sh#DT=`date +%d%m%y`2>&1 echo "Hey - ORA- error !" >> ${ERR_DIR}/$0.$DT.errORA_ERRS=`grep -i ora- ${

SQLServer: When will we have a real optimizer ?

2004-11-11T16:16:00.000-08:00

Yup...It appears that still in 2000 Server, the optimizer is lacking in working without current statistics. So far it's very inconsistent, but as our databases increase in #rows and transactions, we find that we need to continually update statistics.In theory, if the data distribution does not change much over time (even if the data grows in size), the optimizer should chose similar plans as

SQLServer: Access from UNIX ?

2004-11-10T09:07:00.000-08:00

Ahh... The age old question. Yup - who really want's to use ODBC anyway?In an effort to consolidate all perl parsing/transformation code for all database extractions we went with perl DBI on Linux. Initially, we figured, no problem.Here are some things we found out:Default perl install on Fudora Core distribution of Linux had issues with SybPerl Resolution: Grab latest stable build from perl.org

SQLServer: DataWarehouses and DATETIME

2004-11-09T08:23:00.000-08:00

So we want to grab significant transactions from yesterday for our datawarehouse, but we're not quite sure exactly what time our DTS extraction jobs will run.What is the easiest way to get all rows that have a datetime column entry from yesterday? With Oracle, we just use trunc sysdate - 1 and trunc sysdate, but I haven't been able to find a good trunc function in SQLServer.Here's what we've come

SQLServer: Partitions

2004-10-29T16:13:00.000-07:00

Initial testing with SQLServer 2000 partitions appears to work simillarly with Oracle from a select perspective.Explain plans show accessing only local partition indexes, and not all partitions (or tables) in the union views. Inserts / updates appear to first check each partition, then perform the transaction only on the necessary partition.Some of the issues we've specifically found with