Rebuilding bad bitcask partitions in Riak

We had one of our Riak nodes that in a couple weeks time started eating up all it's disk recently.

Here's what we noticed:

1. Two of the twenty or so partitions on the node were 5 to 10 x the average size of the other partitions. The average partitions size was between 20 and 30 GB, and yet 2 of the partitions were 160GB and 210GB.
2. The logs showed that we had run out of open files even though we have the riak user set for max_open_files at 100k.  As it turns out, during the hardware maint I had started riak from a sudo -i session which gave the shell the default 1024 max_open_files setting.
3. After restarting Riak with the correct max_open_files setting, we noticed a lot of 0 byte bitcask files which we removed, as well as some invalid bitcask hint files which we cleaned up.
4. Once all the invalid bitcask files were cleaned up, we realized that any merge process against the 2 large partitions always failed, implying there were some corrupt bitcask files, or the merge process was timing out.
5. Rather than rebuild the whole node, we decided just to rebuild the specific partitions.

Here's the process we used for rebuilding the specific partitions:

- Stop Riak

# riak stop

- Move the bad partitions elsewhere for backup purposes

# mv /{riak_datadir}/riak/bitcask/{partition} /{backup_dir}/

- Start Riak

# riak start

- Wait for riak_kv process to start

# riak-admin wait-for-service riak_kv riak@{riak_node_name}

- Attach to riak and start the repair process

# riak attach
(riak@{node}) 1> Partitions =  [{part 1},{part 2},...{part n}].
(riak@{node}) 2> [riak_kv_vnode:repair(P) || P <- Partitions].

Note: to quit the riak attach shell, use cntl-D, not cntl-C  (otherwise you will stop riak)

- Check status of the repair process

# riak-admin transfers

All in all, Riak recovers quite nicely, and it wasn't terribly difficult to find out what was going on.

On a side note, Basho does a great job if you have the benefit of using their support .

By all means, learn from my mistakes as a DBA!

Here are 3 recent ' oops... wish I hadn't done that :/ ' mistakes I've made since joining moz that you might as well avoid (I'm sure there will be more, but they better not be the same)

Reviewing config files for MySQL, but not all of the defaults 

    We recently migrated a few MySQL databases to a new datacenter, and took advantage of the migration to upgrade the MySQL version(s) at the same time.  Of course I made sure to have another DBA review all my new config changes that were deprecated or new that we wanted take advantage of.  After things were migrated and the new applications were tested, things seemed fine - just a few bugs here and there, primarily around character set issues ( which, obviously, would be a dev problem, right? ).

Oops...  the default character set for MySQL is not utf8 !

     Lesson Learned:  

Don't just check that the configs have the correct settings; check for settings that are not there (defaults), as they are the ones that may be great for most, but perhaps not so great for you.

Restricting the # connections allowed for a user

    In order to introduce a quick 'throttling' mechanism for a job based system with a MySQL backend we implemented user resource restrictions.  It worked like a charm in staging, so we rolled it out to prod; as soon as the job connections exceeded 30 they were disconnected and had to wait till there other jobs completed, and all the devs had to do was change the user name for the job connection url.  Sweet!

   Later that day we started getting host denied errors...

Oops...  max_connection_errors matters !

     Lesson Learned:  

Make sure to take time to understand secondary effects of changes over time, as often issues don't show up immediately   That's why 'soak' or 'bake time' is not something that should be overlooked when implementing changes.

Upgrading percona toolkit and with Capistrano

    Awesome!  I got my Capistrano scripts working great - it's time to upgrade our percona toolkit on all our servers, so let's start with all our slave instances first ( it is production... ).  What's cool it with cap I can deploy it to all the slaves at once.

   What's not so awesome is I can also upgrade and restart mysql on all the slaves at once too :/

Oops...   'apt-get -y upgrade' percona-toolkit still upgrades everything, including MySQL !

     Lesson Learned:  

Make sure staging / test systems exactly mimic production when verifying deployment changes, and if you're working on a different distribution of Linux or MySQL than you are used to make sure to read the man pages!  ( apt-get install -y percona-toolkit is what I wanted, and what the man page said to do )

Yes, even those of us that have been around the block a few times still make mistakes .... 

MySQL binlogs - Don't forget to do your homework!

Now that I'm back doing just database stuff, I've come to realize I've gotten a little sloppy about doing my homework.  Homework's never been my favorite thing in the world, but it often reduces stress when your under the gun during an outage or upgrade...

We had a MySQL database server that's been slow on DML changes, and based on the slowest statements being 'COMMIT', we had a good mind it was an issue with the binary logs, or the disk subsystem that MySQL was writing the binary logs to.

Investigation with iostat (iostat -xtc 10), we were able to narrow it down to a disk subsystem being fully utilized and queuing.  

Easy fix right?  Just change the location of the binlogs via the startup option in my.cnf and restart.

If I had done my homework, I would have known that restarting MySQL with a new location for binlogs would reset the binary log to mysql-bin.000001, and thus break replication to the slave... Oops.

No need to panic - just check the master's log position and run the following on the slave:

 mysql> change master to master_log_file=mysql-bin.000001, master_log_pos=123

Perfect - the slave caught up and was good to go - except that I neglected to recall the fact we had enabled the application and started writing to the master before I checked the log position....  Oops. (2)

Luckily, replication was up to date when we restarted, so I was able to use the handy dandy mysqldump utility to load all the transactions from the new binary log up to the point in which I checked the master:

mysqlbinlog --stop-position=123 mysql-bin.000001 | mysql   

Moral of the story: Doing your homework is usually a good thing! 

Firewalls... what can you say?

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 needed to enable shared server mode ( used to be MTS ); Nothing fancy, no connection pooling, no RAC or anything like that.

So, having done this in the past, we setup the dispatcher to use a dedicated port (rather than the dynamic ports, which firewalls love). Started testing things and realized we kept getting forced disconnects.

somewhat cryptic errors:

org.hibernate.util.JDBCExceptionReporter: SQL Error: 17002, SQLState: 08006
org.hibernate.util.JDBCExceptionReporter: IO Error: Size Data Unit (SDU) mismatch

Then we spend a couple days working with devs, trying different dispatcher/server process scenarios, open a support ticket, etc. Nada.

So we try the next obvious thing and take the firewall out of the equation, and sure enough, everything works fine. So we go back to playing with more configurations, turning off sql inspection on the firewall, etc, etc. Then we start doing network packet traces and analyzing those (another couple days go by).

Then our network engineer notices one of the packets has a tcp option he hasn't seen before 'URG'. Hmm... Sure enough, those packets are generated on the database side, but never make it through the firewall.

A bit more reading/googleing and it becomes clear:

Oracle has a sqlnet option called disable_oob that is off by default, and this allows Oracle to send packets marked 'URGENT' for things like client disconnects, and obviously for some things around dispatchers & shared processes.

Anyway, let those packets pass through the firewall, and it starts working like a charm. One week down, more packet sniffing than I care to do again the rest of the year, all so I can get it setup and forget about it for 5 years till I run into the issue again... :)

More info: Oracle Docs on SQLNet

MySQL Remote Connections for Replication

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 server

Things to check:
1. Replication user password and host
2. Ability to ping the remote server
3. nslookup the remote server and then nslookup the ip for the reverse lookup

The last one solved the problem.

Much Ado about a space

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 extra space infront the start up parameters in the Configuration Manager. SQL server started up after the extra space was removed.

Error occurred while opening file 'master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

Lost and Found ?

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 seems very straight forward to change to ignore-db or ignore-table (because of cross database updates).

After a few weeks in QA and a few weeks in staging - no problems, no issues, no complaints... let's go for it!


Yea, as soon as we deploy and restart MySQL to pickup the configs, replication fails and stops!

And of course to make it lots of fun, replication on a couple other servers failing at the same time for unrelated reasons, and then a migration of another application that night having issues...)

So look around a while, check the MySQL Monitor ... and, wala - lost+found - table does not exist errors!

Yea, ext3 rears it's ugly head again. I'm sure it's probably my responsibility to make sure that lost+found directories are clean up, etc, etc, but it sure made for a headache this week.

The fix (knock on wood) was straight forward - we just added ignore-table=lost%.%

Seemed to do the trick. Maybe we should check out ReiserFS or xfs or zfs.

Either way, if there's a way to break replication, I'm sure I'll find it... ;)


On that note - if you love the file system (linux based) that you are using for your MySQL servers, I'd love to hear your comments (good, bad, or ugly)

What just happened to the database?

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 obviously something wrong with the monitoring system.

Cacti Innodb Row Accesses:






Hmm... it's not the monitor. Must be a change in the app...

So you check with everyone, and no, nothings changed in the app. In fact, nothing even appears to be different (same basic app response times, same # requests/sec to the app, etc, etc)

Database Activity:






So it must be a query cache issue...

Query Cache:






Apparently not... :(

So what could cause that kind of dramatic increase in index rows read / sec ?

In reality, looking at the data distribution, the optimizer apparently decided to 'explain' it all away....

Current explain plan:

mysql> \e
-> ;
+--------------+-------------------------------+---------+------+------+----------+
possible_keys
| key | key_len | ref | rows | filtered
| Extra
+--------------+-------------------------------+---------+------+------+----------+
UIDX_TOTAL_RATING_OWNER_ABOUT,IDX_TOTAL_RATING_ABOUT,IDX_TOTAL_RATING_OWNER
| UIDX_TOTAL_RATING_OWNER_ABOUT | 180 | NULL | 5 | 80.00
| Using where; Using index; Using filesort |
+--------------+-------------------------------+---------+------+------+----------+
1 row in set, 1 warning (0.00 sec)


Previous explain plan:

mysql> \e
-> ;
+--------------+-------------------------------+---------+------+------+----------+
possible_keys
| key | key_len | ref | rows | filtered
| Extra
+--------------+-------------------------------+---------+------+------+----------+
UIDX_TOTAL_RATING_OWNER_ABOUT,IDX_TOTAL_RATING_ABOUT,IDX_TOTAL_RATING_OWNER
|IDX_TOTAL_RATING_OWNER | 42 | const,const | 1 | 100.00
| Using where |
+--------------+-------------------------------+---------+------+------+----------+
1 row in set, 1 warning (0.03 sec)

Interesting what a little change in data and a different path through the optimizer can do...

MySQL Replication and bad assumptions

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
   |                          |
   |                          |
   |                          |
   V                          V
 SLAVE C                    SLAVE D

Makes sense, huh?

Then I'll just point our read / write app servers at MASTER A, and point half of our read / only app servers at SLAVE C, and half of our read only servers at SLAVE D. Sweet. Works like a charm.

Oops... Replication shows current everywhere, no alerts going off. But app servers pointed at SLAVE C show higher row counts than app servers on SLAVE D...

Yea, I never bothered to test that. That's what I get for making assumptions - if you could even call it that. When I thought things through, I quickly realized that there was no reason for MASTER B to send any of it's replicated updates on to SLAVE D, because I had told it not to write any of it's updates it received via replication to it's binary log... :)

All you replication experts out there could have probably told me that in your sleep, but of course, I never bothered to ask...

Just a couple lines in the config file could have saved me a few headaches, and a couple embarrassing apologies...

log-slave-updates
replicate-same-server-id = 0

Figures.. ;)

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

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 there's no way it could be an issue with the database - it must be in their caching layer somewhere.

It never really occurred to me that the issue might be the data itself... duh. Especially when it turned out to be an issue with a few fields in a few rows with a little extra utf8 character in front of the actual data that happened to map to an ASCII zero (end of line char, I think). Nice.

Now we have to go check every stinkin database, every table, every char based column and look for that character.... Ugh.

Oh yea - we have an information_schema now in 5.1.x ... :)

So about an hour later after a quick code review with the developers and some validation tests, we had a cool little data issue checker to quickly run everywhere, that uses the information_schema database. I'm sure there's a slicker way to do it (and who knows, there might even be one up on MySQL Forge already). I suppose I should have checked there first, but I then again, it's always fun to re-invent the wheel...

Here's what we came up with:

DELIMITER /

DROP PROCEDURE IF EXISTS check_invalid_data /

CREATE PROCEDURE check_invalid_data(db_to_check varchar(128)) -- pass in db name
BEGIN
      DECLARE done INT DEFAULT 0;
      DECLARE exec_str TEXT;
      DECLARE sql_str TEXT;
      DECLARE exec_cur CURSOR FOR
              SELECT check_string FROM validate_data;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

      DROP TEMPORARY TABLE IF EXISTS validate_data;

      CREATE TEMPORARY TABLE validate_data
      (
              id              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
              check_string    TEXT
      );

      DROP TEMPORARY TABLE IF EXISTS validate_results;

      CREATE TEMPORARY TABLE validate_results
      (
              id              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
              table_info      VARCHAR (256),
              column_info     VARCHAR (256),
              column_length   INT
      );

      INSERT INTO validate_data (check_string)
      SELECT  CONCAT ('INSERT INTO validate_results (table_info,column_info, column_length) SELECT ''',table_name,''',''',
                      column_name, ''',LENGTH(',column_name,') FROM ', db_to_check ,
                      '.' , table_name, ' WHERE ASCII (', column_name ,' ) = 0 AND LENGTH (', column_name ,') > 0 ;')
      FROM     information_schema.columns
      WHERE    table_schema = db_to_check
      AND      data_type IN ('char','varchar','text','mediumtext','tinytext','long','long varchar','enum','set');

      OPEN exec_cur;

      REPEAT
              FETCH exec_cur INTO sql_str;
              set @exec_str = sql_str;
              IF NOT done THEN
                      PREPARE validate_stmt FROM @exec_str;
                      EXECUTE validate_stmt;
                      DEALLOCATE PREPARE validate_stmt;
              END IF;
      UNTIL done END REPEAT;

      CLOSE exec_cur;

      SELECT * FROM validate_results;
END
/
DELIMITER ;

MySQL: Improving your skills with Forums

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 look at the list of all them... Everything from the newbie forum to the forum for creating a custom storage engine. I guess that's what also makes them useful, if you know how to use them to your advantage.

Here's a few of my suggestions/tips towards getting the most out of the MySQL Forums :

1. The Forums are what we make them. The more you use them, the better they get.
2. Don't be afraid to answer a post. Sure, test your suggestion if you can, or at least think about it before you post it, but if you've run across the issue before, or have a good idea of where to dig in further, then by all means post!
3. Don't be concerned about someone else having a better idea.... someone probably will, and that's your opportunity to learn ( this has held me back many times from posting - thinking that someone might have a better answer, so wait for them to post)
4. Be as precise as possible when posting a question. If you can, include a test case, or a snipit of code.
   
5. Include any trouble shooting steps you've already tried in your post.
6. If you solve your issue, remember to post your solution so that the answer is there when I go looking for the answer to that same issue :)
7. Be nice... I don't always know what I'm talking about, but at least I'm trying to contribute...
8. If you post it, everybody sees it... (a note about security, proprietary info, etc)
9. If you have a lot of experience with one area (say, partitioning...) then add that Forum to your google reader, and try to answer as many posts as often as you can. It will encourage more use of the forum, and help you keep your skills sharp as well!
10. If you don't get an answer in one Forum, you might want to check another that has a similar subject. ( A replication issue with MyISAM tables might get an answer in the MyISAM forum if one doesn't come out of the Replication forum)

If you have other suggestions - let me know (and especially if you disagree with any of my suggestions....)

MySQL: Getting Creative with Partitioning

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 to documented restrictions on partitioning in MySQL 5.1. :(

So, the question is, if I can't have a unique index on a partitioned table that does not contain all columns of the partition key, is there some other way to enforce that uniqueness?

We could use a trigger on the partitioned table that try's to insert / update into another non-partitioned table that contains that unique constraint:

   CREATE TABLE part_table
  (
     id INTEGER NOT NULL,
     other INTEGER NOT NULL,
     PRIMARY KEY (id,other)
  )  
   PARTITION BY KEY (id)
  PARTITIONS 20;

So the restriction can be seen by trying to create a unique index now on column 'other' :

 mysql> CREATE UNIQUE INDEX  test ON part_table  (other);
 ERROR 1503 (HY000): 
   A UNIQUE INDEX must include all columns in the table's partitioning function
mysql>

Create a new table for constraining the index that is not partitioned:

   CREATE TABLE unique_index_table
  (
     id INTEGER NOT NULL,
     PRIMARY KEY (id)
  )  ;

Create a trigger on the partitioned table for enforcing the uniqueness:

 DELIMITER /

 CREATE TRIGGER enforce_unique
    BEFORE INSERT ON  part_table
    FOR EACH ROW BEGIN 
          INSERT INTO unique_index_table  SET id = NEW.id;
   END;
 /

  DELIMITER ;   

Now, enforcement of the unique index can be done on the partitioned table:

 mysql>INSERT INTO part_table VALUES (1,1);
 Query OK, 1 row affected (0.00 sec)
 mysql> INSERT INTO part_table VALUES (1,2);
  ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
 mysql> INSERT INTO part_table  VALUES (1,1);
  ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
 mysql> INSERT INTO part_table  VALUES (2,1);
 Query OK, 1 row affected (0.00 sec)
 mysql> INSERT INTO part_table  VALUES (3,3);
 Query OK, 1 row affected (0.00 sec)
 mysql> INSERT INTO part_table  VALUES (3,2);
  ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
 mysql>

So, now it really becomes a question of performance. Functionally it works, but it it
really the best workaround?

I'll try some tests to compare the performance of this table partitioned with a trigger
enforced unique index against a non-partitioned table with a regular unique index
and see how the results are.

I'll post them as soon as I have the results. If anyone has a better way, I'm interested in knowing....

MySQL: Finally an ability to trace/profile

Finally! The ability to look a little closer into what's happening with SHOW PROFILE

Here’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 |
+--------------------------------+----------+
| starting                       | 0.000020 |
| checking query cache for query | 0.000250 |
| Opening tables                 | 0.000015 |
| System lock                    | 0.000010 |
| Table lock                     | 0.000009 |
| init                           | 0.000022 |
| optimizing                     | 0.000007 |
| executing                      | 0.000018 |
| end                            | 0.000004 |
| query end                      | 0.000002 |
| freeing items                  | 0.000019 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000002 |
+--------------------------------+----------+
13 rows in set (0.01 sec)

In this case, we also get info on inserts (where you don’t with explain)

mysql> create table test (id integer not null auto_increment, my_name varchar(20),primary key (id));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test (my_name) values ('Phil');
Query OK, 1 row affected (0.01 sec)

mysql> show profile;

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000069 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000134 |
| System lock          | 0.000004 |
| Table lock           | 0.000006 |
| init                 | 0.000017 |
| update               | 0.000093 |
| end                  | 0.000003 |
| query end            | 0.000003 |
| freeing items        | 0.002233 |
| logging slow query   | 0.000003 |
| cleaning up          | 0.000002 |
+----------------------+----------+

12 rows in set (0.00 sec)

Here's the steps I used to build the binaries for MySQL 5.1.24:

1) install bitkeeper client http://www.bitmover.com/bk-client2.0.shar
shell> /bin/sh bk-client2.0.shar
shell> cd bk-client2.0
shell> make
shell> make install

2) grab the current 5.1 source tree
shell> bkf clone bk://mysql.bkbits.net/mysql-5.1 mysql-5.1

3) Prepare the source for building
shell> cd mysql-5.1
shell> ./BUILD/autorun.sh

4) Build the snapshot
shell> CC=gcc CXX=gcc ./configure --prefix=/opt/app/mysql \
 --localstatedir=/opt/app/mysql/data --libexecdir=/opt/app/mysql/bin \
 --with-server-suffix="" --enable-thread-safe-client --enable-local-infile \
 --with-pic --with-fast-mutexes --with-client-ldflags="-static" \
 --with-mysqld-ldflags="-static" --with-zlib-dir=bundled --with-big-tables \
 --with-ssl --with-readline --with-embedded-server --with-archive-storage-engine \
 --with-blackhole-storage-engine --with-ndbcluster --with-csv-storage-engine \
 --with-example-storage-engine --without-falcon --with-federated-storage-engine \
 --with-partition --with-innodb --with-extra-charsets=all --enable-community-features \
 --with-comment="MySQL Enterprise Server (GPL) - Snapshot from `date +'%m-%d-%Y'`"
shell> make
shell> make test

5) Make the final installer package
shell> ./scripts/make_binary_distribution

6) Then just install the binaries with the file mysql-5.1.24-rc-linux-x86_64.tar.gz

Best Practices - Oops...

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, forum posts, upgrading to 5.1.24, I finally started to think I just didn't know what I was doing with replication.

Last ditch effort... ask dev and application engineers to help out...

"Oh... you mean one of the application servers is pointed at the slave server in read/write mode and is trying to insert data there as well?..."

:)

Yea, that's why it's a best practice to have your slave servers set READ ONLY .... duh...

Anyway, live and learn..

SQLServer: What am I running anyway?

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 @@VERSION
2> go
Microsoft SQL Server 2005 - 9.00.2164.00 (X64)
Jun 14 2006 12:39:07
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

Server B:

1> SELECT @@VERSION
2> go

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So, what is the difference between these two servers?

Besides the obvious X86 vs X64, the only really difference is the nightly build number. They are both actually SP1.

So we should really be checking this way:

Server A:

1> SELECT SERVERPROPERTY('productversion'),SERVERPROPERTY('productlevel')
2> GO

9.00.2047.00 SP1

Server B:

1> SELECT SERVERPROPERTY('productversion'),SERVERPROPERTY('productlevel')
2> GO

9.00.2164.00 SP1

Just another dorky pet peeve of mine :)

SQLServer 2005: Full Text Issue

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 Bug / Feature

The question I'd like to pose Microsoft is this:

If the work break files I've installed came from the CD / Microsoft bits that I got from Microsoft, should I be able to assume that they've verified where they've came from? And for that matter, If I install a third - party work break file after my SQLServer Full Text install, shouldn't it be my responsibility to verify the validity of those files?

Why should Microsoft automatically be checking these for me? (and why, should they do it every time I load the word breaker info into memory?

BTW - if you are having the problem, it presents itself something like this:

Running SQLServer 2005 (or possibly other servers), with Full Text Indexing, you notice that the initial call to the query engine is slow (surprisingly, 45 seconds consistently). Subsequent queries perform as expected. Then after 10 or 15 minutes, the query processing engine appears to 'go to sleep' and you have another 45 second response time for a query to indexing service.

SQLServer 2005: Full Text Search Monitoring

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 technologies.

Here are a few important views/selects for monitoring Full Text Indexes withing SQLServer 2005:

Important dm views:

sys.dm_fts_active_catalogs /* info about the active ft catalogs */
sys.dm_fts_index_population /* population types for the ft ndexes */
sys.dm_fts_population_ranges /* detailed info about the population of ft indexes */

Important system procedures:

sp_help_fulltext_catalogs /* info on names and locations of ft indexes */

sp_help_fulltext_tables /* info on tables and indexes used by ft catalogs */

Example Queries:

/* Get Status of Full Text Indexes Currently Being Populated */

SELECT

DISTINCT DBNAME (database_id),
OBJECT_NAME (table_id)

FROM sys.dm_fts_index_population population
JOIN sys.dm_fts_population_ranges ranges
ON (population.memory_address = ranges.parent_memory_address)
WHERE ranges.session_id <> 0

/* Get List of Full Text Index Population Counts as Compared with Table Counts */

SELECT catalog.name,

OBJECT_NAME (population.table_id) AS table_name,
catalog.is_paused,
catalog.status_description,
catalog.row_count_in_thousands AS ft_index_rows_in_thousands,
tables.row_count / 1000 AS table_rows_in_thousands,
population.population_type_description AS population_type,
population.status_description AS status,
population.start_time

FROM sys.dm_fts_active_catalogs catalog
CROSS JOIN sys.dm_fts_index_population population
INNER JOIN sys.dm_db_partition_stats tables ON population.table_id = object_id
WHERE catalog.database_id = population.database_id
AND catalog.catalog_id = population.catalog_id
AND catalog.database_id =

(SELECT dbid FROM sys.sysdatabases WHERE name = DB_NAME () )

AND tables.index_id = 1;

MySQL : the beauty in beta

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 sometimes ends up causing a lot of heartache tracking down issues that turn out to be 'known bugs' in a beta version.

Lesson learned (again) :

Yesterday I received a note from one of our lead developers, that went something like this:

---

It seems that our test mysql instance is very picky about honoring desc versus asc queries (qa confirmed it).

If you run this query it will return in asc regardless of the order by.

select id
from registry_service.ALIAS
where (owner_uri_path_id = 700 and owner_uri_ref='Account/3760')
order by id desc;
----data returned ---
107538
107539
107540
107541
107542

Now if you change the column that you are ordering by, it seems to work…

select name
from registry_service.ALIAS persistent0_
where (owner_uri_path_id = 700 and owner_uri_ref='Account/3760')
order by name desc;
---- data returned----
'7SortAliasTest.20071018.141531980.141'
'7SortAliasTest.20071018.141531980.131'
'3SortAliasTest.20071018.141531980.151'
'0SortAliasTest.20071018.141531980.161'
'0SortAliasTest.20071018.141531980.121'

I’m very confused ; can you help me understand what’s going on?

---

Well it took all of 2 minutes and 1 Google query to get an answer.

Known beta bug ( http://bugs.mysql.com/bug.php?id=31001 ).

Just a little foresight on my part could have saved development and test both a lot of hair pulling and time wasted had I evaluated the known bugs list before letting dev and qa have at the new MySQL instance. They could have know that this issue was there, and it would be fixed in the 5.1.22-rc release, which I already have loaded on our new development servers.

Shoulda, Woulda, Coulda...

SQLServer: Image Replication - Oops

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: The problem with SQLServer is the OS it runs on :)

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 the number of parallel threads when running dml in parallel. This was all find and dandy till the OS reported 16 processors on a 8 proc / dual core machine.

Yea, cause then SQLServer says, ohh - I can do this in parallel.... let's see... 16 x 2 = 32 threads! Yea baby!

Uh... great, now my query takes 2x as long because I'm waiting in a line at the door to get in.

Figures.

Take this into account when doing bulk loads, large queries, things that the optimizer might want to parallelize. Either set the max degree of parallelizm at the server level, or use MAXDOP at the query level.