Dontcheff

Archive for the ‘MySQL’ Category

What is the best relational database?

In DB2 database, DBA, MySQL, Oracle database, PostgreSQL, SQL Server, Sybase on May 11, 2013 at 11:45

“I have the simplest tastes. I am always satisfied with the best.” Oscar Wilde

If we look at market figures, then “Gartner 2012 Worldwide RDBMS market share” reports 48.3 percent revenue share for Oracle.

RDBMS_Market_Share

Three major factors make the Oracle database a clear leader:

1. Remains #1 in worldwide RDBMS software revenue share
2. Holds a larger revenue share than four closest competitors combined
3. Leads next closest competitor revenue share by 29%

But why is that so? Let us have a look at the latest DB-Engines Ranking where Oracle is not only #1 in the DB-Engines Ranking of Relational DBMS but also in the Complete Ranking.

The DB-Engines Ranking are measured the popularity of a system by using the following parameters:

– Number of mentions of the system on websites, measured as number of results in search engines queries.
– General interest in the system.
– Frequency of technical discussions about the system.
– Number of job offers, in which the system is mentioned.
– Number of profiles in professional networks, in which the system is mentioned.

What is the best relational database? The best answer given in answers.yahoo.com is the following: “Define “best”. Oracle is like a BMW. Expensive but has all the fixings. But not everyone needs a BMW. MySQL is like a VW Beetle (the old model). Its cheap, and gets you where you need to go. But you have to tweak it to suit your needs.” Nice explanation!

Next, let us look at Gartner’s Magic Quadrant for Data Warehouse Database Management Systems from January 31st, 2013:

Gartner_Magic_DW

The reason Oracle is top on the “Ability to Execute” scale is simple. It can be described by me with just one word: Exadata. Of all the vendors in this analysis, Oracle reports the highest incidence of nontraditional analytics customers: sectors such as hospitality, energy trading, life sciences and food distribution show up in its reference base. According to Gartner, many of the vertical markets where Oracle has the greatest success contain traditional implementers or late adopters of data warehousing. Oracle’s customers range in annual revenue from $100 million to over $10 billion.

The Business Technology Forum raised the same question: Which is the best enterprise RDBMS database? The article is very much to the point coming to the conclusion that Oracle have the upper hand.

Mirror, Mirror on the Wall, which is the best RDBMS of all? You do not expect an answer from the Mirror “It depends” because anyone trying to be diplomatic will answer that way. Well, at least they say that Oracle is most scalable, most feature rich, and just a wonderful RDBMS.

More to read:

Why would you use Oracle database?
Why Oracle is the Preferred Database Platform?
Is Microsoft’s SQL Server really cheaper than Oracle?

The last one favors SQL Server over Oracle but a word of caution on the credibility on the Oracle side: the author calls PL/SQL “P-SQL” and refers to Active Data Guard as “Advanced Data Guard”.

It will take DB2, SQL Server, Sybase, PostgreSQL and all other key RDBMS players at least several more releases before they can approach the current self-management and tuning capabilities of Oracle Database 11g. But when that day comes, Oracle will not be still at version 11.2.0.

12c is knocking on the door.

Oracle12c

Advertisements

The Google DBA, the GUI DBA and the DBA who does all the work

In DB2 database, DBA, MySQL, Oracle database, SQL Server on June 26, 2011 at 12:36

I often neglect notes and comments on people who divide DBAs into groups and types but this DBA’s Journey into the Cloud is worth commenting on:

The exposé is so short that one does not get the impression if these 3 DBA types are mutually exclusive or not. If not, why would then someone profile DBAs into groups?

You obviously use Google and Metalink (called MOS nowadays). And you use GUIs: Grid Control, Oracle Developer, Toad? Doesn’t look impressive if you cannot make it into the last group, right?

And into which group will the guy hired for this Pre-Sales DBA job fit?

Let me change to another classification method and peek at the The DBA Corner of Craig Mullins. He says that the more common types of DBAs are:

– System DBA
– Database Architect
– Database Analyst
– Data Modeler
– Application DBA
– Task-Orientated DBA
– Performance Analyst
– Data Warehouse Administrator

Too many. I might agree that the system DBA and application DBA are different roles but all those analysts? “The performance analyst is usually the most skilled, senior member of the DBA staff, a role that he has grown into due to his experience and the respect he has gained in past tuning endeavors.” Interesting..

Next, I want to see what Don Burleson has to say. What Type of DBA Are You? Only 3 options here:

– The Scientist DBA
– The Can-do DBA
– The Empathic DBA

Looks like classification on emotional basis. Interesting reading though.

Let us look at what Wikipedia has to say. Déjà vu! 3 types again:

– System DBA
– Development DBA
– Application DBA

Development DBA? Used to be called a programmer years ago. I always thought that the Development DBA takes care of the development databases 🙂

What do people have to say about the DBA types in the internet forums. Let us visit DBA support. Funny stuff at the end! Now, the types are 4, with short descriptions listed below:

– The smart and industrious DBA
– The stupid and industrious DBA
– The smart and lazy DBA
– The stupid and lazy DBA

Up to now, it looks to me that I am always a mixture of some type. A hybrid? Brian Knight figures it out in his article DBA Job Description: What type of DBA are you? 3 types again:

– Production DBA
– Development DBA
– Hybrid DBA

I really don’t know. 10 years ago, he wrote about the Death of the Production DBA and companies still recruit SQL Server Production DBAs.

If you surf the Internet, you will find all type of DBA classifications: the Oracle DBA and the MySQL DBA; the certified DBA and non-certified DBA; the on-call DBA and the 9-to-5 DBA.

There is even a Marketing DBA: “DBA skill needs to sale out software to vendors. To convince customer in technical point of view marketing team requires DBA help. Marketing DBA tries to satisfy each technical queries of customer and help marketing team. There is no database administration and maintenance directly involve in this DBA role.”

Look once more in the text above: A database administrator role such that there is no database administration. No comment.

But eventually, it looks to me that DBAs can be divided into two groups: those who divide DBAs into groups and those who don’t..

GoldenGate enters the Oracle database

In DBA, Golden Gate, MySQL, Oracle database, Replication, Sybase on June 1, 2011 at 00:02

A friend of mine claims that GoldenGate is a product that is supposed to be used and exist outside the Oracle database. And if you run Oracle on a version below 11.2.0.2, the following query for GOLDENGATE objects will probably return no rows selected. But here is what happens with Oracle 11.2.0.2:

Interesting, right ?

Note that recently Oracle GoldenGate 11.1.1.1 was released. For Oracle GoldenGate on Oracle versions 10gR2 and higher, Extract now manages log retention by default. With this new feature, the documentation says you should be aware of the following:

1. The upgrade will automatically register each of your Extract processes with the database, which will start retaining the logs based on the current recovery requirements for each process.

2. An Oracle Streams Capture process will be created for each Extract process and will have the same name as that process. These Capture processes are non-functional and can operate concurrently with other local Oracle Streams installations.

3. In order for the upgrade to succeed (on 11.2.0.2), you will need to assign the Extract user some additional privileges.

Note first that you need to commit after executing the procedure in the dbms_goldengate_auth package:

SQL> exec dbms_goldengate_auth.grant_admin_privilege('GGATE');
PL/SQL procedure successfully completed.
SQL> commit work;
Commit complete.

Note additionally that the privilege_type can be CAPTURE, APPLY or BOTH. Both (= *) is the default. If you prefer that the GGATE user is granted also the select_catalog_role, you should run the command like this:

SQL> exec dbms_goldengate_auth.grant_admin_privilege('GGATE','*',TRUE);
PL/SQL procedure successfully completed.
SQL> commit work;
Commit complete.

Oracle GoldenGate version 11.1.1.1 contains improved sequence support. To enable this support, you must install some database procedures that support the new FLUSH SEQUENCE command. I recommend that for details you read Samuel Oleleh’s Oracle Goldengate 11.1.1.1 Sequence Support article.

The (Oracle database related) new features of GoldenGate 11.1.1.1 are very well described in the following blob entries:

1. Oracle GoldenGate version 11.1.1.1 new features by Samuel Oleleh.
2. Oracle GoldenGate version 11.1.1.1 released by Miladin Modrakovic.

For all the MySQL users (are there any?), I will note that there is a new ALTLOGDEST option that was added to TRANLOGOPTIONS to specify the location of the MySQL log index file. This value overrides the database default location. Extract reads the log index file to find the binary log file that it needs to read.

For the Sybase users I will mention that the range of values for DBOPTIONS TDSPACKETSIZE has been changed. Additionally, two formerly internal parameters are now available for use in a TRANLOGOPTIONS statement:
– TRANLOGOPTIONS MAXREADSIZE specifies how many records Extract reads from the transaction log at one time. It can be used to improve performance.
– TRANLOGOPTIONS READQUEUESIZE specifies the internal queue size, in bytes, for transaction data. It can be increased to improve performance.

For Oracle GoldenGate installations please check:

1. Pythian’s Oracle GoldenGate Installation, Part 1
2. Pythian’s Oracle GoldenGate Installation, Part 2

As I hinted above MySQL and GoldenGate do not in my opinion go hand in hand. But I would briefly like to mention that Oracle GoldenGate can be used together with Active Data Guard to achieve almost zero planned and/or non-planned down time:

I will soon write more on live upgrades, zero downtime and 100% SLAs 🙂

P.S. As of today, Google returns only two pages with results for dba_goldengate_privileges, try for yourself to see if things have already changed:

The DBA: an architect or an administrator?

In DB2 database, DBA, MySQL, Oracle database, SQL Server on April 26, 2011 at 19:55

Most people in IT comprehend the complexity and importance of the DBA role.

Database architects and administrators are the building block of any IT organizations. The area of database management is so vast that some DBAs specialize nowadays in certain parts of the DBA profession: replication, performance tuning, clustering technologies, etc.

No surprise that CNN Money places both the software architect and the database administrator in the Top 100 for the Best Jobs in America. In fact, software architect is the best job in America and database administrator is #7.

Have a look at all IT jobs that can be found in Top 100 for 2010:

1. Software Architect
7. Database Administrator
17. Information Systems Security Engineer
18. Software Engineering / Development Director
20. Information Technology Manager
21. Telecommunications Network Engineer
24. Network Operations Project Manager
26. Information Technology Business Analyst
28. Information Technology Consultant
30. Test Software Development Engineer
31. Information Technology Network Engineer
33. Information Technology Program Manager
35. Computer and Information Scientist
37. Programmer Analyst
38. Applications Engineer
49. Systems Engineer
60. Information Technology Specialist
66. Systems Administrator
67. Web Developer
70. Technical Services Manager
77. Information Technology Systems Manager
79. Information Technology Training Specialist
88. Technical Writer
95. Information Technology Project Coordinator
96. Web Project Manager

For those of you who have spent years in database architecture and administration, this comes as no surprise perhaps.

For those who might be puzzled and for the ones who would like to read more on the subject, check this article from Datavail called Database and DBA Management.

Last year, Sloane Stricker presented the Top 10 reasons DBAs quit. I would like to add what triggers this and what managers should try to do in order to keep the DBAs in the company:

– DBAs need at least 20% white time: that is time on their own to read white papers, blogs, forums, metalink, etc. Time to test, research and debug new features and old issues. Time to attend online seminars, conferences, Oracle Open World, etc.
– Try to find what motivates each DBA. They are all individuals with own preferences and interests. Bonus intensives should be real personal. I value very much the team work but it is just one finger that presses “Enter” upon shutdown immediate.
– Keep their salaries enough competitive. Do not just tie them to the company’s job grade or job band. There is nothing wrong with you most senior DBA having higher annual income than your IT Director. Really!

For those of you who might be more deeply interested in the topic, check the Gartner/Burton paper: Realigning Database Administration to Jump-Start a Data Management Organization. Very useful for midsize and big organizations! Here is the conclusion of the article:

“The formation of a Data Management Organization (= DMO) is Gartner’s recommended solution for correcting the sorry state of data management in the majority of enterprises. Competitive pressures mean that time is of the essence, Gartner sees the reorganization of the existing database administration groups into an embryonic DMO as a fast-track, but effective, first step in this process. This guidance document provides a framework to direct this organizational transformation.”

As it might sound a bit out of context here, I recommend that you read the whole article.

And finally back to the question in the title: it is both. The DBA should nowadays be both an architect and an administrator. The time of DBA 2.0 is over, it is now time for DBA 3.0!

On the Total Cost of Database Administration

In DB2 database, DBA, MySQL, Oracle database, SQL Server on March 25, 2011 at 10:42

Have you ever read those “white papers” distributed over the internet on total costs of database administration? One claiming that “Microsoft SQL Server required significantly less effort to install and maintain than Oracle”, another one saying that “DBAs can perform typical administrative functions in 41 percent less time when using Oracle Database compared to Microsoft SQL Server”.

You can just stare in disbelief in their “facts” and “arguments”. The former quote above is from ComputerWorld “The Total Cost of Administration Champ: Microsoft SQL Server 2005 or Oracle Database 10g?”, the latter is from TechRepublic “Oracle Database 11g vs.
Microsoft SQL Server 2008”
.

If Oracle is the Lexus of relational databases, is then your Oracle DBA cost as high as the service cost of an expensive car? Should you consider switching to SQL Server? Nobody is saying that IBM’s DB2 is cheap but very often the comparison is between Oracle and SQL Server.

You might have seen Don Burleson’s article Oracle 10g v. SQL Server. I agree with Don that reducing the number of expensive DBAs is hardly an area of opportunity for the IT manager to save money. But when you often show managers figures like the ones below:

Managers will sooner or later start believing some of those “research figures”. Some of them at least. You see, somebody claims that one DBA can administer 9.9 Oracle databases/31.2 Microsoft databases , another one mentions 15.2 Oracle databases/65.4 Microsoft databases. Probably the figures are not far from reality although I have seen that with some automation the numbers can grow much higher (100-150 databases per DBA) although I can’t take seriously studies giving me numbers about users supported by DBA. Sorry, what users? Internet users, database users/schemas? Come on..

It is not up to the number of databases a DBA handles or the gigabytes per DBAs or even the brand. It is up to the skills and experience of person doing the database administrations, it is up to environment and the DBA team handling the work. It is much about the set-up of the databases and their complexity. Of course the automation involved in the DBA work is a significant factor as well. But generalizations like the ones above and below are just mildly insane. If someone tries to convince you that you will save a lot of money by putting your databases in “the Cloud”, just turn and run 🙂

About 5 years ago, Forrester did the following forecasts, decide for yourself if predictions were TRUE or FALSE. At least I disagree with the performance tuning curve.

1. DBA to databases ratio:

2. Database administration cost is declining:

3. Database administration challenges:

Database administration has been and will remain the most complex part of IT. The new features added by vendors like Oracle, IBM and Microsoft to the relation software only make administration more difficult, complex and hard to learn and master from scratch.

P.S. There is a book written in 1954 called “How to Lie with Statistics”. You can get it now for 5.99 from amazon.com