For People That Have Managers

Interesting take on what managers are thinking: http://quickbase.intuit.com/blog/2012/01/09/10-things-your-boss-isnt-telling-you/

Help MySQL Stay Free

There's a campaign started by Monty Widenius to save MySQL from the evil clutches of Oracle. You can read about it here.

Pick Two

There's a saying in the computer business, "Good, Fast, Cheap. Pick Two." This saying represents the trade offs we have to make every day on the front lines of IT.

Recently, we have started playing around with MySQL Enterprise Monitor (MEM). MEM is MySQL's offering for an event driven monitoring application similar to Oracle's Enterprise Manager. There are four pieces to MEM; the monitoring agent that gathers that db statistics, the query analyzer that allows you to "trace" queries, the connection proxy that allows you to redirect connections, and the data repository.

Our first stab at configuring MEM was putting the monitoring agent, query analyzer, and query proxy on the host being monitored with the repository db on a different machine. This actually worked pretty well until we had some big queries run through the query proxy. CPU utilization spiked because both the query proxy and the database had to handle the same set of data.

So MySQL Support suggested we move the proxy, query analyzer, and monitoring agent to a separate host. This "middle-tier" would handle the extra CPU load we were generating and keep our db host performance consistent. This solution worked very well, except we weren't getting host related statistics like CPU utilization on the DB host.

No problem, right? We moved the monitoring agent to the db host and kept the proxy and query analyzer on the middle-tier. Now we had our host related statistics, except we couldn't get the query analyzer to work because it has to have a monitoring agent present in order to work properly.

The only solution MySQL Support can offer us at this point is to have two agents monitoring the same database; one on the middle-tier host and one on the db host. OK, we'll do what needs to be done, but I have two fundamental problems with that configuration. First, my database background tells me that duplication will come back and bite you in the ass someday. My other very practical issue is now we are storing twice as much data in the repository for 10% more information.

So while the concept of MEM is great, the actual implementation is a compromise.

Note: Some of the above configurations are not currently supported by MySQL. Use at your own risk.

information_schema.tables.table_rows

Here's something to be aware of. The table_rows column in the information_schema.tables table does not accurately reflect the number of rows in the actual table.

This weekend I was moving a db from one host to another. As a good DBA, I took the row counts from each table I was moving so I could check it on the other side. When the copy was done, I queried information_schema.tables again and dumped the results to a file. Running the two files through diff, I found that almost every table had a different number of rows.

I knew that couldn't be possible because I used a method where nobody had access to these tables when I was doing the copy.

On serverA, the information_schema reported that table1 had 2337 rows. On serverB, the information_schema reported that table1 had 2341 rows after the move. Doing a "SELECT count(*) FROM table1" yeilded 2343 rows on both serverA and serverB.

SQL Developer Connecting to MySQL

We introduced Oracle SQL Developer to our developers about a year ago. They seem to like it for querying Oracle, but the MySQL connectivity was quirky in previous releases.

Now that Oracle SQL Developer 1.5.4 has been released, I figured I'd give it a try again. I downloaded the software and it started right away with no problem (although I wasn't paying attention and I forgot to migrate over my settings).

Once the software starts, go to Tools > Preferences > Database > Third Party JDBC Drivers and choose the appropriate JAR file.

Then I added a connection with the correct parameters and Voila! I was querying MySQL data from an Oracle tool!

Adobe Automation

I have a friend who needs some Adobe Acrobat automation. The project description is:

We need to find a programmer who is willing to do some high-end Adobe form work for us.

We need to embed a "send to" button in some of our company forms so we can email the form to clients who can input the data and submit the form back to us by clicking on a submit button on the form.

If anybody has this skill set and is interested, please email me at marist89 (at) excite %dot% com and I will forward to the appropriate party.

Phone Interview tips for DBAs

You only get one chance to make a first impression. I would think the following tips would be common sense, yet I am surprised every day.

1. When on a phone interview, don't put the interviewer on hold.
2. I don't mind calling you on your cell, but if the call drops, you're done.
3. I know you might be nervous, but don't cut me off in mid-sentence.
4. When you take my call, don't be driving in a car.
5. I don't expect you to pick up the phone on the first ring, but please pick it up. If I get your Voicemail, I'll try you again in five minutes. After that, you're on your own.

A dying breed?

I usually get a feel for "what's hot" in the marketplace by the number of books on the shelves at my favorite bookstore. A couple years ago there was six shelves at my Borders store filled with books on Oracle. Oracle for Dummies, PL/SQL, and scores of Certification books. I was quite surprised to find only three books on Oracle at the same store this past weekend.

MySQL still has some respect with about a 1/2 shelf. .Net clocked in with about 5 or 6 shelves with a smattering here and there of Java and the associated technologies. Good old Perl shared half a shelf with PHP. But I feel left behind because I don't know Excel Macros (about 12 shelves).

Maybe Oracle has really achieved a self-tuning, self-managing database and we don't need books anymore.

Please excuse me while I apply four more patches to my "up-to-date" 10.2.0.3 installation.

No soup for you!

Have you read about Sprint cutting people off?

Seems as though Sprint is taking on the attitude that if you call them too much, you'll get dropped. Sounds like they think they're an insurance company not a phone carrier. I wonder if you only call twice in the life of your contract they'll give you a refund.

I deal with customer service departments every day. Lets face it, most customer service agents are great reading off a script, but the second you go off script they're clueless. And that's if you can even understand them.

Another point is that the way most customer service departments are setup, there's no incentive for the Customer Service Agent to actually solve your problem. They get financial incentives for closing problems, not solving problems. Ever open a trouble ticket with a certain database vendor and they work on it for a while and then say you have to open a new trouble ticket because they can't solve the problem?

Everybody knows (I know Tom, there are exceptions, but in general) that it costs more to attract customers than to keep them. I can't see many people actually wanting to talk to somebody half a world away on the phone 50 times a month. But that's just me.

Extra credit: Where does "No soup for you!" come from?

I think the database is broken

Below is a somewhat fictional somewhat realistic view of upgrades.

"I think the database is broken" was my first call this morning.

"OK, what error message are you getting?" was my reply. Since I knew we changed some things over the weekend, I figured something got left out and needed fixing.

"My process that normally takes 38 minutes is done in 3."

"OK, does it error out?"

"No."

"Do the results look right?"

"Yes."

"So why do you think it's broken?"

"Because it finished in 3 minutes."

"Well, we upgraded the database version this past weekend and put the database on a faster host, so that's probably the difference."

"But it used to take 38 minutes."

...long pause on the phone by me, expecting something else...

"I can slow it back down if that's what you want...."

On the flip side, I go to my developers to see how things are going and they report increases anywhere from 4-10 times faster depending on how much the db is being used. One savvy developer corners me and says something along the lines of "Can't we move this db to a faster host?"

"Huh? This is the almost latest, greatest, fastest host there is. You've got 3.0Ghz of Dual Core processing power underneath your db where before you had some clunky Sparc processors."

"You know there's 3.2Ghz processors out, right?"

"So, let me get this straight. You're 10 times faster than Friday, and you want to be 10.1 times faster?"

"Yup, that extra speed will really help."

"I need to get back to my office, call me if you have any problems."

Why is it always the database?

When something is slow, why do we always point to the database? A computer system is so much more than "the application" and "the database". It's networks, routers, disks, memory, array controllers, middle-tiers, JDBC Drivers, Operating Systems, and client versions (which, usually comes round to "It's Oracle/MySQL, fix it"). Come to think of it, it's pretty amazing that we get decent performance at all.

Everybody is guilty of it from the novice to the experience person who knows better. Heck, I am sometimes guilty of it myself.

I'll get a call, "Application XYZ is slow, is the database OK?"

"Let me check."

A couple minutes later I indicate everything is fine on my end. I hear three days later that "XYZ was slow because of the database". Argh. Next time that person needs help tuning a query he'll get pointed to the documentation for EXPLAIN PLAN.

Why do we always point to the database first? Is it because the database is usually the guilty party? Or do we point fingers at what we don't understand?

Keywords

One thing that Statcounter provides me is how often certain keywords are found in my blog. Some of the more popular are:

• ORA-27054: I assume this is by people trying to backup using RMAN over NFS. I hope they found my problem and most importantly, my solution.
• Some combination of DB Links and ORA-01017: I'm sure people found my distributed issues with 9i and 10g working together. But that's not always the issue. If you are using what I call "unauthenticated" database links (no password in the definition) then the user authentication on both the source and remote database need to be the same. If we're talking an OS Authenticated user, then that user has to be OS Authenticated on both. If it's a regular db user, the passwords have to match.
• Lots of people looking for some varient of backup and methodology. I hope they found lots of info in the Backup Top 11, but I don't think I've ever described my backup methodology for Oracle. Maybe in a blog post in the future.
• I get lots of hits on the Quick and Dirty Backup for MySQL. Seems like a lot of people are looking for an easy solution to MySQL without having to fork over some cash for backup software.
• I also get lots of hits on The MySQL to an Oracle DBA series, but not too many through keyword searches.
• For the guy who's searching "mysql php round differently", I'd look at your datatype in the database to make sure you're using the proper number type.
• Looking for information on "_db_writer_flush_imu"? Better know what you're doing.
• Oh, and the person looking for "ingres porn server"...good luck on that.

MySQL Pseudo-Partitioning, Part II

On advice of the MySQL She-BA, I wanted to see what performance I could get with MERGE tables. In my particular application I'm going to need the RI I get with InnoDB, so it's not a solution, but as long as I am investigating anyway...

Seems simple enough, let me the MERGE table over my existing tables:

mysql> create table xyz_mt (
    -> `id` bigint(10) NOT NULL default '0',
    -> `report_dt` date default NULL,
    -> `acct_id` varchar(8) default NULL,
    -> `some_text` varchar(222) default NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=MERGE UNION=(xyz_2007_01,xyz_2007_02,xyz_2007_03) ;
Query OK, 0 rows affected (0.08 sec)

mysql> select id, report_dt, acct_id 
    -> from xyz_mt 
    -> where report_dt ='2007-02-17' 
    -> and acct_id = 'X0000741';
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exists

Doh! That's right, got to change to MyISAM.

mysql> alter table xyz_2007_01 engine=MyISAM;
Query OK, 1000000 rows affected (25.42 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> alter table xyz_2007_02 engine=MyISAM;
Query OK, 1000000 rows affected (17.82 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> alter table xyz_2007_03 engine=MyISAM;
Query OK, 1000000 rows affected (18.84 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

Lets see how it works now.

mysql> select id, report_dt, acct_id
    -> from xyz_mt
    -> where report_dt ='2007-02-17' and acct_id = 'X0000741';
+----------+------------+----------+
| id       | report_dt  | acct_id  |
+----------+------------+----------+
| 20000741 | 2007-02-17 | X0000741 |
| 20967398 | 2007-02-17 | X0000741 |
+----------+------------+----------+
2 rows in set (2.43 sec)

Whoa, that's pretty good, I could live with that. Lets compare the inline view of MyISAM tables vs. InnoDB tables:

mysql> select * from (
    ->    select id, report_dt, acct_id from xyz_2007_01
    ->    union all
    ->    select id, report_dt, acct_id from xyz_2007_02
    ->    union all
    ->    select id, report_dt, acct_id from xyz_2007_03) t
    -> where report_dt ='2007-02-17' and acct_id = 'X0000741';
+----------+------------+----------+
| id       | report_dt  | acct_id  |
+----------+------------+----------+
| 20000741 | 2007-02-17 | X0000741 |
| 20967398 | 2007-02-17 | X0000741 |
+----------+------------+----------+
2 rows in set (15.17 sec)

Slightly faster than InnoDB, but not what I would call Significant. Definitely a viable option for partitioning in v5.0. Thanks to Sheeri for the top.

MySQL Pseudo-partitioning

Got a MySQL project that needs a partitioned table. It's the ideal candidate really. Historical data that naturally would be partitioned by date. I need to keep an active window of data and delete old data quickly without doing DML.

Except partitioning is in 5.1 (more on that later) and I'm on 5.0.

One way I would overcome this limitation in Oracle Standard Edition would be to have multiple tables that hold a month worth of data and put a view on top of them. My users might notice a slight performance degradation, but it would be worth it to drop the data quickly.

So, I tried the same experiment in MySQL. I created three tables of this format:

CREATE TABLE `xyz_2007_01` (
`id` bigint(10) NOT NULL default '0',
`report_dt` date default NULL,
`acct_id` varchar(8) default NULL,
`some_text` varchar(222) default NULL,
PRIMARY KEY  (`id`),
KEY `xyz_rdt_acct_2007_01` (`report_dt`,`acct_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

And then I stuffed them with a million rows each. If I query a particular piece of data on a particular date, I expect to use the "xyz_rdt_acct*" index.

mysql> select id, report_dt, acct_id 
-> from xyz_2007_01 
-> where report_dt ='2007-02-17' 
-> and acct_id = 'X0000741';

Empty set (0.00 sec)

mysql> select id, report_dt, acct_id 
-> from xyz_2007_02 
-> where report_dt ='2007-02-17' 
-> and acct_id = 'X0000741';

+----------+------------+----------+
| id       | report_dt  | acct_id  |
+----------+------------+----------+
| 20000741 | 2007-02-17 | X0000741 |
| 20967398 | 2007-02-17 | X0000741 |
+----------+------------+----------+
2 rows in set (0.01 sec)

mysql> select id, report_dt, acct_id 
-> from xyz_2007_03 
-> where report_dt ='2007-02-17' 
-> and acct_id = 'X0000741';

Empty set (0.00 sec)

OK, I'm looking for two rows in the 2007_01 "partition". Lets create a view on top of those tables to present one interface for queries.

mysql> create view xyz as
-> select * from xyz_2007_01
-> union all
-> select * from xyz_2007_02
-> union all
-> select * from xyz_2007_03
-> ;
Query OK, 0 rows affected (0.00 sec)

And now we query from the view.

mysql> select id, report_dt, acct_id from xyz where report_dt ='2007-02-17' and acct_id = 'X0000741';
+----------+------------+----------+
| id       | report_dt  | acct_id  |
+----------+------------+----------+
| 20000741 | 2007-02-17 | X0000741 |
| 20967398 | 2007-02-17 | X0000741 |
+----------+------------+----------+
2 rows in set (44.36 sec)

Hmmm, that's not cool. 44 seconds to retrieve two rows. Lets try a sub-query just for kicks.

mysql> select id, report_dt, acct_id from (
->    select * from xyz_2007_01
->    union all
->    select * from xyz_2007_02
->    union all
->    select * from xyz_2007_03) t
-> where t.report_dt ='2007-02-17'
-> and t.acct_id = 'X0000741';
+----------+------------+----------+
| id       | report_dt  | acct_id  |
+----------+------------+----------+
| 20000741 | 2007-02-17 | X0000741 |
| 20967398 | 2007-02-17 | X0000741 |
+----------+------------+----------+
2 rows in set (33.47 sec)

About the same, but a little bit better. Lets see what is going on.

mysql> explain select id, report_dt, acct_id from xyz where report_dt ='2007-02-17' and acct_id = 'X0000741';
+----+--------------+--------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type  | table        | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+--------------+--------------+------+---------------+------+---------+------+---------+-------------+
|  1 | PRIMARY      |  derived2    | ALL  | NULL          | NULL | NULL    | NULL | 3000000 | Using where |
|  2 | DERIVED      | xyz_2007_01  | ALL  | NULL          | NULL | NULL    | NULL |  788670 |             |
|  3 | UNION        | xyz_2007_02  | ALL  | NULL          | NULL | NULL    | NULL | 1014751 |             |
|  4 | UNION        | xyz_2007_03  | ALL  | NULL          | NULL | NULL    | NULL | 1096175 |             |
|NULL | UNION RESULT | union2,3,4  | ALL  | NULL          | NULL | NULL    | NULL |    NULL |             |
+----+--------------+--------------+------+---------------+------+---------+------+---------+-------------+
5 rows in set (43.18 sec)

Ah, three full table scans. Hmm, wonder why it's doing that. Lets do the same for the inline view.

mysql> explain select id, report_dt, acct_id from (
->    select * from xyz_2007_01
->    union all
->     select * from xyz_2007_02
->     union all
->     select * from xyz_2007_03) t
-> where t.report_dt ='2007-02-17'
-> and t.acct_id = 'X0000741';
+----+--------------+--------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type  | table        | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+--------------+--------------+------+---------------+------+---------+------+---------+-------------+
|  1 | PRIMARY      |  derived2    | ALL  | NULL          | NULL | NULL    | NULL | 3000000 | Using where |
|  2 | DERIVED      | xyz_2007_01  | ALL  | NULL          | NULL | NULL    | NULL |  788670 |             |
|  3 | UNION        | xyz_2007_02  | ALL  | NULL          | NULL | NULL    | NULL | 1014751 |             |
|  4 | UNION        | xyz_2007_03  | ALL  | NULL          | NULL | NULL    | NULL | 1096175 |             |
|NULL | UNION RESULT |  union2,3,4  | ALL  | NULL          | NULL | NULL    | NULL |    NULL |             |
+----+--------------+--------------+------+---------------+------+---------+------+---------+-------------+
5 rows in set (35.51 sec)

Basically the same thing. Lets see what happens if I only get the fields I want in the sub-query.

mysql> explain select id, report_dt, acct_id from (
-> select id, report_dt, acct_id from xyz_2007_01
-> union all
-> select id, report_dt, acct_id from xyz_2007_02
-> union all
-> select id, report_dt, acct_id from xyz_2007_03) t
-> where report_dt = '2007-02-17'
-> and acct_id = 'X0000741';
+----+--------------+--------------+-------+---------------+----------------------+---------+------+---------+-------------+
| id | select_type  | table        | type  | possible_keys | key                  | key_len | ref  | rows    | Extra       |
+----+--------------+--------------+-------+---------------+----------------------+---------+------+---------+-------------+
|  1 | PRIMARY      |  derived2    | ALL   | NULL          | NULL                 | NULL    | NULL | 3000000 | Using where |
|  2 | DERIVED      | xyz_2007_01  | index | NULL          | xyz_rdt_acct_2007_01 | 15      | NULL |  788670 | Using index |
|  3 | UNION        | xyz_2007_02  | index | NULL          | xyz_rdt_acct_2007_02 | 15      | NULL | 1014751 | Using index |
|  4 | UNION        | xyz_2007_03  | index | NULL          | xyz_rdt_acct_2007_03 | 15      | NULL | 1096175 | Using index |
|NULL | UNION RESULT |  union2,3,4  | ALL   | NULL          | NULL                 | NULL    | NULL |    NULL |             |
+----+--------------+--------------+-------+---------------+----------------------+---------+------+---------+-------------+
5 rows in set (16.09 sec)

Well, look at that. 16 seconds still isn't great, but at least the query is using the indexes.

Lets create a view using the same logic.

mysql> drop view xyz;
Query OK, 0 rows affected (0.01 sec)

mysql> create view xyz as
-> select id, report_dt, acct_id from xyz_2007_01
-> union all
-> select id, report_dt, acct_id from xyz_2007_02
-> union all
-> select id, report_dt, acct_id from xyz_2007_03;
Query OK, 0 rows affected (0.00 sec)

mysql> select id, report_dt, acct_id from xyz where report_dt ='2007-02-17' and acct_id = 'X0000741';
+----------+------------+----------+
| id       | report_dt  | acct_id  |
+----------+------------+----------+
| 20000741 | 2007-02-17 | X0000741 |
| 20967398 | 2007-02-17 | X0000741 |
+----------+------------+----------+
2 rows in set (16.70 sec)

Performance is consistent at least.

mysql> explain select id, report_dt, acct_id from xyz where report_dt ='2007-02-17' and acct_id = 'X0000741';
+----+--------------+--------------+-------+---------------+----------------------+---------+------+---------+-------------+
| id | select_type  | table        | type  | possible_keys | key                  | key_len | ref  | rows    | Extra       |
+----+--------------+--------------+-------+---------------+----------------------+---------+------+---------+-------------+
|  1 | PRIMARY      |  derived2    | ALL   | NULL          | NULL                 | NULL    | NULL | 3000000 | Using where |
|  2 | DERIVED      | xyz_2007_01  | index | NULL          | xyz_rdt_acct_2007_01 | 15      | NULL |  788670 | Using index |
|  3 | UNION        | xyz_2007_02  | index | NULL          | xyz_rdt_acct_2007_02 | 15      | NULL | 1014751 | Using index |
|  4 | UNION        | xyz_2007_03  | index | NULL          | xyz_rdt_acct_2007_03 | 15      | NULL | 1096175 | Using index |
|NULL | UNION RESULT |  union2,3,4  | ALL   | NULL          | NULL                 | NULL    | NULL |    NULL |             |
+----+--------------+--------------+-------+---------------+----------------------+---------+------+---------+-------------+
5 rows in set (15.99 sec)

Plan is consistent as well. Still, not a solution for me, but thought you might be interested in the findings.

The more I learn, the more questions I have. Tomorrow, I test partitioning on 5.1 beta.

Cookbooks

I'm a big fan of what I call cookbooks. Cookbooks are step-by-step directions that if followed, will yield the desired result. In fact, most of my cookbooks are copy & past enabled so that you copy a command from the cookbook and paste it into your window to execute the command. I insist my people use these established methods where available and I get pretty ticked off when they waste a lot of time trying to figure out what has already been learned.

I've installed Oracle on Linux about 50 times over the past few months. After the second succesfull install, I created a cookbook for both 9i and 10g. If followed exactly, any DBA will be able to install Oracle correctly in my environment. I created the cookbook and I know it like the back of my hand. I don't need no stinkin cookbook and proceeded to do a fresh install of 9.2.0.8 when the installer hangs at 18% linking naeet.o.

"Hmm, must be somthing wrong with Linux", I thought to myself.

I checked that the correct versions of gcc and g++ were in my path. I knew I set LD_ASSUME_KERNEL so I didn't have to check that. Then I looked at metlink and found a note that said my LD_ASSUME_KERNEL had to be 2.4.19, which I already knew I set, so that couldn't be it.

I poked around at some other things before going back to my cookbook.

Kernel parameters, check.

ORACLE_HOME set, check.

LD_ASSUME_KERNEL, I know I set that, skip it.

Correct version of gcc and g++, check.

Reran the installer and it hung again at 18%.

The only thing I didn't double check was LD_ASSUME_KERNEL. So I see what's in the environment variable, and lo and behold, it's not set. Seems I set LD_ASSUMEKERNEL instead of LD_ASSUME_KERNEL.

I've sent a stern email to myself about not following process. If I do it again, I'll be fired.

Powered by Zoundry

Opportunity for a MySQL DBA

I am looking for a MySQL Database Administrator to join my team. The position will be located at our Greenwich, Connecticut headquarters. The ideal candidate will have a mix of database technologies, predominantly MySQL, as well as a minimum of three years experience as a Database Administrator on the Linux platform.

Responsibilities

• Administer Backup/Recovery processes
• Install and Configure Database Software
• Identify and resolve performance problems.
• Automate day-to-day tasks as needed.
• Rotating on-duty coverage is expected.
• Manage access to database resources

Required Skills

• 3+ years experience as MySQL DBA in a production Linux environment.
• 1+ years experience with MySQL 5.0.
• 2+ years exposure to Oracle in a production or development environment.
• Day-to-day Database Administration.
• Backup and Recovery using InnoDB Backup, Zmanda, mysqldump.
• MySQL Replication.
• Ability to quickly diagnose and resolve performance using standard tools.
• Some experience with both MySQL and Oracle partitioning.
• Self-starter with initiative and desire to learn.
• Ability to work quickly in a fast-paced environment.
• Ability to communicate clearly, constructively and concisely to peers and end-users.
• Ability to work independently with minimal supervision and drive issues to closure.

Desired Skills

• Automation of DBA tasks (Perl, CGI, Ksh, PHP) .
• Some experience in Oracle 10g, MySQL 5.1.
• DR Strategies including Replication/Standby Database.
• Supporting mission critical systems in 24x7 environment

For further consideration, candidates should send their resume, cover letter, and salary history to recruiting@interactivebrokers.com. Please indicate you are applying for the "Database Administrator" position.

Working with InnoDB Tablespaces

Is it me, or is working with InnoDB tablespaces akin to working with Oracle 6.0?

For example, today somebody filled up a filesystem by unintentially loading a large amount of data. It was development, so this stuff happens. Since it was development, I setup the InnoDB tablespace to start with size 10M and autoextend to whatever free space was available. The one datafile kept autoextending until it completely filled the disk. The developer dropped the table and thought that would free up the space.

I knew that wouldn't be the case, so I had to dump all the data, recreate the tablespace, and reload all my data. Pretty standard DBA stuff so far.

"This time", I thought to myself, " I'm going to create a couple files each 10m and let them autoextend to 10240m so this doesn't happen again".

Speedbump #1: InnoDB doesn't let you autoextend more than one file.

OK, I know my data is about 30G and we'll be doing some development, so I'll create three 10240m files and let the last one autoextend to 10240m. After 20 minutes of combing through the manuals trying to find a "DROP TABLESPACE" or similar command, I finally deduce that you can't drop a tablespace while the server is up.

Speedbump #2: The server (or instance for you Oracle people) has to be down to remove the tablespace.

So I shutdown the server, remove my datafiles per the docs, setup my config file, and restarted the server. The logfile spits out that it's pre-creating the files (as expected) but then throws an error message:

  Error: All logfiles must be created at the same time.

Speedbump #3: You have to recreate the logfiles when you recreate the tablespace.

I don't want to recreate 30G of space, so I shutdown my server, remove the logfiles, and restart the server. Except this time I get another error:

  InnoDB: Cannot initialize created log files because
  InnoDB: data files are corrupt, or new data files were
  InnoDB: created when the database was started previous
  InnoDB: time but the database was not shutdown
  InnoDB: normally after that.

Speedbump #4: You have to recreate your logfiles and datafiles at the same time.

It's not out of the realm of possibility that I'm doing something totally wrong or I don't understand InnoDB very well. Maybe my version (5.0.27) doesn't support these features but they are in a newer version. Or maybe it's just a hassle. I'd be interested in other's practical experiences with managing the InnoDB tablespace.

Powered by Zoundry