Open Source does not have market share - does not even have a market?

According to the Wikipedia definition:

"A market is any one of a variety of different systems, institutions, procedures, social relations and infrastructures whereby persons trade, and goods and services are exchanged, forming part of the economy. It is an arrangement that allows buyers and sellers to exchange things"

I'm neither economics nor market expert. But certainly that definition implies that something is exchanged, right? Then let's look up the definition of Market Share

Market share, in strategic management and marketing is, according to Carlton O'Neal, the percentage or proportion of the total available market or market segment that is being serviced by a company.

Wow, again those business words like "company", and "market" All that lead me to think about all the different measurements that are constantly trying to determine the "market share" of open source products.

First, of course, you have all the debate around which numbers are correct. Because there are as many numbers of "market share" as there are ways of measuring them. There is always a heated debate about which numbers are more accurate. Recently for example, Mozilla Firefox "share" has been seen rising quickly, to the point of being already a serious contender for Web client dominance against IE.

What bothers me is that people keep talking about usage figures as if they were "market share" But they are not. When something is essentially free, such as a Firefox download, there is no exchange of things. You just download something and install it. There is no direct exchange of anything between you and the Mozilla foundation, or Canonical, or Red Hat when you use their products.

Yes, the open source community argues correctly that there is in fact a return on that. Be it bug reports, suggestion for enhancements, code fixes , hardware or money donations, or translations, there is usually a return for the successful open source products. But note that it probably applies for the more successful ones only, and that there is no direct exchange.

The ironic situation is that those products are placed and measured against other where their usage, piracy notwithstanding, do have a market. Windows, Internet Explorer or MacOSX do cost money beyond what you're paying for your Internet bandwidth. More ironic is that, if you tried to estimate the economic value of producing things like Firefox, Red Hat or Ubuntu have, you'll probably be in the billion dollar range. Yet they don't cost you anything. And in spite of the vast majority of their users not doing any kind of contribution, direct or indirect, these things thrive and continue to grow, threating to crush their paid for alternatives.

Therefore is not correct to talk about "market share" in this situation. There is no market involved, and the same logic used by business and marketeers cannot be applied here. So please stop using the term "market share" and start using "usage stats" or something like that.

Kill the flat text file

Perhaps I'm not lucky and I always end up working with old systems, but I've become increasingly irritated by a practice that should have died years ago: the flat text file interface. In the stone age era, this was the standard way of exchanging data between systems, as the plethora of options that integration technologies have been creating in the last 20 years or so were not available.

Probably because of its ubiquitous availability, this practice was noticed by the office tool creators (yes, Lotus 1-2-3, I'm looking at you) and they built nice functions in the applications to deal with flat text files. Spreadsheets became common office tools, and people grew up using text files as their only way of getting data from systems. Perhaps because of that, a whole generation of office workers assumed that this was "the way" of doing things. When those office workers face a technology problem, they of course tend to prefer to use the tools and techniques they know better. After all, if you can open in Excel a flat text file and understand its contents, what's wrong with using that to talk to other applications?

The answer is, a lot of things. And some, but not all of them, related to performance. But that's not stopping the flat text files flying around. Even in this year, new functionality is built into applications that rely on flat text file interfaces to move information across applications even if much better ways of doing it exists.

Let's review the most common sins incurred by the practitioners of the flat text file interface approach.

Internationalization

30 years ago the world was 7-bit ASCII. Then information systems became global, standardized and multilingual. Probably, English is no longer the dominant language of computer users across the world. Yet the flat text file does not have any means of specify encoding, nor most legacy tools are able to handle international character sets correctly - remember, they were designed and created when the world was 7-bit ASCII. Before Unicode became universally accepted, there were a lot of workarounds that tried to deal with this problem in a way that did not broke legacy applications. Most of them were around using, yes, you guessed it, 8-bit ASCII. But 8-bit ASCII is not enough to cover even the different character sets across the western world, much less deal with oriental character sets.

The result is that data quality deteriorates as information moves across systems in those flat text files. Each time a file is exported and/or imported, an assumption is made about source and target character data sets that most of the time is not tested, if not plain wrong.

And no, flat text files do not have a way to encode character set information in them, unless of course you make them non-flat and irregular. Therefore, each time you try to compare what should be the same information in two different systems, you have all kinds of mismatches in textual data that are simply due to wrong character set conversions. In some cases, there are so many of them that they make the signal/noise ratio in the data so low that the actual data quality audit is impossible.

Cost

A flat text file has to be written by a program somewhere, according to some agreed format between parties. It then has to be transferred to the other system and read back by the other machine, interpreting the agreed format. Simple, isn't it?

No, it is not simple. Think about it. Now think again, twice. Each time you exchange information across systems you're performing essentially a data transformation to adapt from one data model (or database schema, if you prefer) to another. Now, by writing a flat text file, you're introducing yet another transformation, the one you're doing from the source or target data model to the flat text file format. So you're paying the cost not only of two data transformations, but at least three. Usually, text files have a format that closely matches one of the two data models, with the other unlucky side having to perform the bulk of the transformation job. In extreme cases, I've witnessed otherwise intelligent people engage in even more ridiculous exercises, trying to define so called "neutral data formats", in which the two parties have to perform extensive data transformations to be able to use the data in their applications.

Still thinking that it's cheaper to use text files? Ah, that's because you have lots of people who can write programs that read and write text files, isn't it? Well, perhaps it's time to look for more qualified resources, as the incremental cost of learning other technologies for application information exchange is slim compared with the additional costs you're incurring by performing all those additional transformations. Don't want to invest in that knowledge? Think again, you're basically stagnating yourself or your team in old technologies that simply are being replaced by application connectivity and middleware.

Ah, I hear you cry, but flat text files can easily be audited, can't they? I have to say, yes, they can. Have you ever heard of application log files? What's stopping you writing something on an application log or journal instead of keeping your flat text files around? Isn't that easier, safer and subject to regular application security than keeping a folder somewhere with a bunch of flat text files? Answer left as an exercise to the reader.

On top of that, each time you need to transfer additional attributes, you have to modify two programs, the one that writes and the one that reads. How's that impacting the cost?

If you think all of the above is not enough to stop using text files, read on.

Synchronization

The flat text file is static. Your data is dynamic. Your business is dynamic. Enough said. Writing something to a text file means that it has to be processed later. Means that it cannot be changed at the source unless you're sure that the other side has captured the previous change. Means that there will be a delay between the time one application has a piece of data and the other has the same piece. This causes no end of race conditions, unless both applications are changed to reflect the special state the data is while it is being sent across. Which introduces yet more artificial complexity in data management.

Not only that, but those files have to be moved around. Which means another protocol, be it FTP, CIFS (Windows file sharing) or similar, that is involved in the transfer. Some of those protocols insist in introducing some additional layers of character set translation, require customized firewall settings and need to be taken care of. Not only that means additional cost, but also another point of failure where things can go wrong.

Experienced developers have a solution for this problem, usually closing the system for end user access while the flat text files are being read or written. Sometimes, I've seen flat text files as some sort of journal used to defer the import or export, usually to be done on a night batch process. Those nightly jobs can become monster bottlenecks, where all useful application processing is deferred to be done during the night. Cross your fingers, because if anything goes wrong it will have to be fixed the following day, delaying whatever processes depend on the data being moved, sometimes making the regular services stop to give the systems enough breathing room to do their monster nightly batch runs during the day.

You can fix this, of course, by having continuous monitoring of your beloved flat text files import and export processes. Remember to add those costs to the total cost of support.

If you believe that replacing the flat text file with some other synchronous or asynchronous solution will involve expensive distribute transaction technologies, I have an answer for that: only if you want it. Your systems will be much more robust and data integrity much higher if you use transaction monitors or messaging queues. But even if you don't use any of those facilities you'll end up in a much better shape than if you used a flat text file. The explanation is, your method will update an entity at at time and you can have full error control during the transfer (be it transactional or not) and alert the user or the support staff of any problems on the spot. Whereas when you read your flat text file records and act on them, you're going to generate the same error condition over and over. Fixing problems as soon as they appear is much easier and faster on the long run than just deferring the fix to the time you analyze the results of your flat text file data load, when a lot of damage may be already done.

Performance

And now to the final point. After what has been said, the people still favoring the flat text file are about to show their last card: sometimes it's faster to import or export a flat text file than to do it in other ways, right?

Yes, sometimes it is. Large data loads are probably done faster by using some intermediate storage where the application or database can perform bulk reads or writes. Of course, this intermediate storage is our beloved flat text file(s). Usually, those large data loads are one off jobs. In practice, there are very few, if any, applications that need to perform those on a regular basis. Even large data warehouses are updated incrementally, simply because there is a point where there is not enough time to rebuild them from scratch each time a new bit of data arrives. And in those cases, the bulk of the time is spent refreshing the computed results, not the base level data.

Still, the idea of using the same formats for your one-off and incremental loads looks attractive. Again, look at the reasons explained above and think if it is still worth enslaving yourself and paying those costs forever just because you want to keep the same text file format forever. Specially the first time you need to change that written in stone flat file format used by all those programs around.

As for performance, while there is no discussion that databases usually like to work on data sets as big as possible, the incremental updates are not usually big enough to provide a significant performance improvement over doing them on the spot. And remember, doing the information transfer at the time it is done on the source means that the sender application already has most, if not all, the data that needs to be sent to the other application, which means that you are saving at least another read if you don't defer the transfer for later. In any case, you're saving the read/write time of the flat text file. If you're still doing significantly better with bulk batch loads it probably means that there is still some fundamental flaw in the way the two applications are exchanging data, as taking that logic to the extreme would mean that you would stop using anything but flat text files for exchanging information, even inside the application, wouldn't' you?

Final words of hate against flat text files

I realized that this posts reflects that I passionately hate text files. Perhaps it's because I've had enough of them and my hatred is irrational, but I cannot see how any of the above arguments can be challenged. I've yet to face a situation where using tools less than 10 years old, one has to resort to flat files for data transfer, yet I keep seeing them being created over and over. By this time, I think that you've enough arguments to stop trusting anyone that says that flat text files are "flexible", "efficient" or "cheap to implement". They aren't. Of course, someone selling you this idea probably has a vested interest in you using flat text files, as they know too well that you'll become dependent on them forever to maintain them. Do not trust them.

If you come across one of these consultants, mention briefly SOA, REST, RPC, SAP RFC, RMI, .Net remoting, Oracle SQL Links or the myriad other technologies developed over the last 20 years to address all those needs.

I sometimes dream about the end flat files. Somehow, I break during the night into the customer data center and replace each and every single flat text file interface with a modern, performing approach. I then add a trigger for each change in the target database that writes a record in the flat text files that should have been or written to transfer the data. I bet that in most cases it will take them a week or so to realize that there is something different. Well, for some customers the job would take me much longer than a night, as the number of different flat files flying around is in the hundreds, if not thousands.

Flat text files should have died a long time ago as a way of exchanging data among applications. Let's leave the accountants and finance people play with them for their Excel spreadsheets, saving them to learn anything new and us from having to police them in abusing databases. Let's drop them as soon as we can for anything else.

Google Appengine (III) – Tuning means code refactoring

One of the last tuning gigs I was involved with was related to a content recommendation system. The original implementation was a one by one translation of a statistics algorithms into SQL sentences. It generated a huge number of precalculations stored in a multi million record table that were later on reused when visitors were looking for content. It was even worse than that, as the result size grew exponentially with the number of users.

The solution involved creating a set of stored procedures that were able to calculate in submillisecond times the same results without needing to store any intermediate results. Ah, and I discovered a few bugs on the original implementation that rendered the results of all those precalculations incorrect. Overall the customer was very satisfied with the end result.

After we were done with the testing and verification of results, something that is not an easy task with recommendation systems as it basically involves asking people if the results are more or less matching with their tastes, we set out to incorporate the changes to the live system.
Apart of creating the stored procedures, the changes in client application code amounted to five lines of code. Total. This is not unusual. And application down time was zero. Nothing. Users were using the old recommendation and in their next request they were using the new.

Application code changes are expensive, so usually the ratio of cost/benefit has to be quite high to be worth doing any performance optimization work in this area (except if you're dealing with SAP, of course, where everything is ABAP code)

Fact is, by its own design, SQL is an intermediate middleware layer sitting between application code and your data. Performance tuning happens mostly at the SQL layer or below. Seldom application code is changed.

Not so with Google AppEngine.

GAE SQL Dialect

GAE implements something remotely similar to a very small subset of SQL. Now, SELECT, FROM and WHERE have become so popular keywords that each time anyone wants to implement something even remotely related to a query language is using those keywords.

Be it WMI, Lotus Notes ODBC, GAE, Hibernate or whatever, they want to make it easy to learn at least the basics.

But beyond those keywords, they don't provide anything remotely similar to the richness and expressiveness of the SQL language. Simply put, GAE SQL dialect (called GQL) does not go beyond the simplest of data accesses. Fundamentally, GQL is not a set oriented language. Furthermore, whereas SQL provides many different ways of reaching the same final result, GQL usually provides on, if any at all.

Object model

Say that you're playing with performance improvements. You're looking for ways of minimizing the number of IO operations, the number of indexes or both.

Due to the limitations of GQL, none of the different options can be expressed using the query language. The possible choices range from using references, property lists, inheritance, or a combination of them all. The key part of in all of them is that you'll need to change application code to even try them.

This has a number of implications that make the evaluation of different options much more convoluted than in the traditional SQL server scenario.

• Each time you want to test something, you need to do an extensive data conversion from one object model to another. This means time invested in data conversion code, time invested in running and verifying that code. Furthermore, if you finally decide to go for a change in pursuit of better performance, you have to execute that code in the live environment, and that probably means application downtime.
  
• No development environment. GAE SDK does not implement the BigTable functionality or indexing. You cannot simply compare performance of different approaches on the local development environment. You need to use the live GAE for that.
  
• Cost. Free GAE has limitations in the amount of IO you can do and the storage you can use, if you reach those during testing, you'll exceed your quota. Either you'll have to pay or wait for quota renewal.
  

All this is a bit contradictory. While GAE really makes rapid application development easy and attractive, it is still too young to have developed a set of performance best practices. Your best bet is to reach out for all the documentation available and try to get it right in the early stages.

Good documentation, wrong format

Fortunately, Google has created quite good documentation and tutorials and you can get a lot of learning out of those. Especially the Google IO sessions are valuable insights into how the App Engine operates. Unfortunately, most of that is in video format.

Mind you, I'm not whining about the quality of the conferences. They are excellent, and I'm surprised about how good the GAE team are at that. It is a common topic among the developer community that software development profiles do not value much the quality of verbal communication. Not so with the GAE team.

But video as a documentation platform has a lot of problems. First, you have to watch them end to end to really decide if the content is interesting as they are not structured in a book format with chapters, sections, etc. You cannot read the first paragraphs to decide if it's relevant to the topic you're interested in. And second, they cannot be used as reference material or something that you can bookmark to return later. Third, I can read a lot faster than listen to an speech, so what could have been digested in 15 minutes takes an hour instead.

The supporting materials, mostly slides, help to alleviate those problems somewhat. Ironically, they probably also provide the text that also makes those contents easily searchable on Google.

Now, if I just had time to put together a real GAE app.....

GAE - Abstracting complexity and sharding counters

The history of application development is littered with inventions addressing the problem of filling the conceptual gap between a machine serially executing some instructions acting on data and a business person trying to automate some process. Over the years, layer upon layer of abstractions have been developed, from assemblers, compilers, operating systems to the highly abstracted REST and SOA specs of today.

If you're exploring the Google AppEngine (GAE) you probably have already come across sharded counters.

Basic counters explained

Many applications present some kind of counter to the end user: message boards tell you the number of responses to a topic. Auction systems give you how many people are bidding for an item. Digg tells you how many people have rated an item. And so on, there are too many examples of counters used everywhere.

For performance reasons, it is a common practice to keep those counts stored in the database. It's not that they cannot be calculated at page rendering time, but in some cases the performance hit of retrieving those records and counting them is considered to be excessive.

As always with performance tuning, there is a compromise here, as keeping the counter accurate each time something in the database related to the count changes is expensive and complex in terms of database operations. But for web apps where the count is being retrieved hundreds of times more than needs updating, the trade off is usually worthwile.

Let me remark that as always, "usually" is the pivotal concept. There are specific situations where this does not apply and is not worth performance wise to keep the counter updated. Always apply common sense first, apply some commmon sense second, and third do some performance testing.

The Google Datastore and counters

Since the Google Datastore does not have any analytical capabilities and given its constraints in response times, any counters except the most trivial ones, cannot be calculated at page rendering time. There is simple no other option, as the GAE puts sever restrictions on the amount of data that can be fetched and the time it should take a page to be served.

This is all well and good, Google in this case is simply watching your back so that your users get good response times and its infrastructure is not abused.

However, due to the way the Datastore engine works, a single data item is stored in a single machine. That can create problems if lots of application instances try to update the counter at the same time. Google's advice to solve that is that you "Shard" your counter. "Sharding" a counter means to create many counters and incrementing one of them randomly. Each time you need to present the counter value, you issue a query to retrieve all the counters and add them together.

This nicely avoids the botteleneck in high concurrency situations, but notice how this has increased the overhead of retrieving the counter, and more important, forces the designer to make a decision on the data type of a simple counter.

This breaks the rule of abstractions making things simpler. Instead of the engine being able to nicely scale to your requirements, you need to tweak the engine to adapt to the expected workload. Of course, if someone has to consider that decision is probably happy to face scalability problems, as that can only mean that his/her site is being successful.

I'm not picking up on Google for this, but this is another example where GAE strikes me as unbalanced. On one side it offers excellent tools to quickly and scalably put together web applications, with a low barrier of entry in terms of learning. On the other side, it makes you face design considerations that seem to be more the province of specialized parallel processing engineers.

GAE, continued

I've continued exploring Google AppEngine since my last post. Honestly, I've not had much time to devote to self education or blogging lately, so at this moment my opinions are not yet based on real world, live application, experience. And they are mostly opinions, not hard, evidence based, facts.

GAE fundamentals

I'm new to Phyton and Django. Both of them have been a pleasant surprise. The Phyton language is focused on being practical on a day to day use and have good performance. It's also well designed in two ways: one, there is nothing that looks odd or added as an aftertought, althought I'm sure that the language was not born with all the features it has today. That speaks very well of the language designer.

Second, the libraries and syntax almost lead you to choose the "right" approach to solve a problem, in a way that is structured and readable instead of going the quick hack route. (Perl asbestos suit:on) I've always hated how you could in Perl devise many different solutions to the same problem, the quickest to write being almost always the least readable and plain ugly (Perl asbestos suit:off) While it is perfectly possible to write good structured and object oriented code in PHP and non-.NET VB, there is nothing in both languages to prevent a quick and dirty approach or encourage doing it the right way.

In fact, Python blocking convention could be an advantage here. Enforcing proper indentation discourages deep nesting of code blocks, increasing the chance that a problem is break down into smaller, more manageable pieces. Of course, crappy code can be written in any language you want, and the likelyhood of crapiness increases with the popularity of a programming language, so this is by no means a guarantee that you'll always come across clean and nice Python code. Off topic question, does anyone know what is the shape of the crapiness increase function? Linear? Log? Exp?

If you can forgive the lack of a statement terminator, the weak typing, or the odd blocking scope syntax, then Phyton could not be a bad choice as your next general purpose programming language.

The Django motto "A framework for perfectionists with deadlines" holds true everywhere you look at. I'm convinced that there's a huge productivity advantage using Django over a raw, no framework, approach. I'm no expert in web frameworks, so I'm not sure if the productivity delta is convincing enough for a PHP or .NET web developer to switch. But Django surely is easy to learn, extensible, consistent, scalable, and covers the whole gamut of web app scenarios.

Unfortunately, what is built into GAE is not 100% Django. Not even 1.0 Django. Apparently, Django 1.0 was not released on time for GAE to include it. Also, the GAE storage engine is not even remotely similar to a relational database, and thus the persistence part of the Django framework cannot work unchanged under GAE. To make things a bit worse for the newcomer, instead of not including the supported Django modules, code that imports and references them is accepted by the GAE SDK, only to find those modules missing at run time. Of course, if someone digests all the Django AND GAE documentation beforehand this is not a problem, as you're not going to use a Django feature missing in GAE. But for those learning by exploring, example, and reading documentation at the same time, it makes learning somewhat frustrating and does not add to the "perfectionist with deadlines" motto.

In summary, I've learned a lot and still continue learning, but looks like Google has done overall a good choice on those two fundamentals for their App Engine.

Warning: in my opinion, hands on experience is a primary source of knowledge, at least as important as learning the theoretical fundations. Therefore, what follows could be an entirely wrong opinion on what is GAE market niche not much based on experience but on what I've learned from second hand or lab experiments. In fact, I've yet to set up a live GAE application.

An engine looking for apps

The way one desings your data model is very much influenced by the limitations of the persistence layer that you use. For any given system, you tend to delegate each task to the layer that is best suited to handle them. That means that if you're using a relational database, that layer usually handles querying and data integrity in as much as possible. And in a relational database, that "in as much as possible" is a lot, probably most of it.

Application design for GAE datastore is no different. Python code is going to handle the roles that the Datastore does not support, and given that there are no joins , analytical functions or complex WHERE clauses, that's going to be a lot. In fairness, Google are not calling their storage engine a database. Because it's not. Google calls it a "Datastore", which much more accurate describes what it does and what it does not. It took me a while to realize the difference, as I was hoping that my initial impressions were just a consequence of ignorance. So I kept looking for capabilities similar to a relational engine. But they are simply not there. On the other hand, transactions are there, indexes are there, and a query language is there, but with restrictions that don't put them on par with your basic RDBMS.

So the GAE Datastore is ideally suited to store and retrieve data, and huge amounts of it. But I keep asking myself, what is the point of being able to store such huge amounts of data?

Of course, if you are a Google engineer, this is a no brainer. There are lots and lots of uses for this data, anything that enhances your knowledge and/or service to your customers, and thus your revenue. And probably Google's first choice would be to target ads. But from flat reporting suppporting day to day business processes to decision support related information, those vasts amounts of data are an invaluable asset to your business. So you should be able to take advantage of them, right?

Yes, but currently only if you're a Google engineer. Because then you have access to a nice MapReduce implementation with a few (thousands?) nodes to run it. Armed with that, you can analyze, slice and dice and otherwise play with your data to your heart's content. But unfortunately, you cannot do any of that with GAE. At least today.

And that is what I keep asking myself each time I revisit the GAE. As it stands today, there are alternatives that provide, if not the huge storage and scalability, at least the analytical and reporting power that current applications need today. So nobody is going to move its shopping cart, blog or bulletin board to GAE. Nobody that needs database complexity above the basic level, at least. So what are the kind of applications Google expects to host in GAE? Today, anyone thinking of taking advantage of cloud computing is looking at the likes of Amazon EC or watching closely how Microsoft is adding relational capabilities to its Azure storage engine and waiting for them to be mature enough to jump in.

I can only think of three answers to this. One, Google is just seeding the field and creating some nifty toys expecting that the next Web 3.0 revolution is sparkled by something hosted in AppEngine. This revolutionary application has not yet written or even imagined by anyone, but Google expects GAE to be the platform of choice for running it. Two, Google is going to expand GAE so that its storage engine offers more capabilities that make it attractive to existing applications. It surely would be nice to have access to MapReduce-scale resources or more sophisticated database functionality built in. Third, Google is trying to compete with Amazon EC and Microsoft Azure and falling behind them.

I just hope that it's not the third one. In any event, I've learned to look at AppEngine database performance as the combination of Python and Datastore tuning. This is different from relational performance, where one looks to non-SQL code as the part that usually slows down database operations.

Changed beyond recognition and the EAV data model

One of the much maligned performance drags an application can have is its abuse of the so called Entity Attribute-Value (EAV) model for data storage. However, as with many catastrophic events, the performance problem is the result of the failure of multiple elements in the decision chain. Let's examine the motivations behind the EAV on each of the parties involved.

The buyer (or designer) dilemma

You're a person in charge of selecting an standard package. Or you're in charge of design or development of a bespoke package in your medium to large business. At some point, you'll be collecting the so-called "requirements" from future "customers", or application users.

No matter how hard you try, at some point in this process it will became clear to you that you cannot exactly define the data model with enough precision to guarantee that the database design is not going to change along the life of the application.

Usually, what is specially troublesome is to define the information structures to represent data for entities that are external to your business: your consumers and customers. This is because the amount of data that the business has at some point in time depends heavily on the number of initiatives addressing them. The more initiatives, the more disparate information is being collected from the outside.

On top of that, in extreme cases it's not even clear what is the purpose of this information. Somehow everybody agrees that having customer age information is good, but they are not sure of how to cluster it. And if they know how to group ages, this grouping is likely to change in the next six months. Worse yet, they start talking about targeting promotions according to age.

So you design, or request to the potential suppliers, some provision for storing additional entity data in your application. You don't know exactly what information you'll store, how much of it or how often. Buy you surely know that no matter how complete the data model definition appears, someone in the near future is going to request to store something that was not in the data model design.

The marketing dilemma

Whether you're internal developer, consultant or purchaser, you need to be able to answer with a big smile and a resounding YES to the question: does your application support additional data fields? This helps a lot, as any concerns about suitability of the solution can be addressed, "hey, we can always add this bit later" is the answer to all the problems.

No matter how hard you argue about this being the right solution or not, nothing beats the convenience of "unlimited" additional "fields" that can be added to the application.

The implementors dilemma

Now it's time to deliver. How do you comply with the requirement?

Imagine that you're creating a standard, packaged application. To support additional attributes, you start exploring and arrive at pretty much three options.

1. Provide some interface from your application that allows users to change the data model. Of course this interface must be portable across as many database flavors you're going to support, adapting to the specifics of each data model. Or just make everything of the most generic "string" data type you're able to find. In any case, your database API needs to have support for data definition statements. Last time I checked, the most popular ones did not.
   
2. For each entity add to it additional columns such as "ADDED_ATTR_1",
   "ADDED_ATTR_2", "ADDED_ATTR_3", and so on. The trouble with this approach is that you're not meeting marketing requirements. The number of fields is not "infinite". On top of that, the names are not exactly descriptive and you have the same problems of data model specific. Note that regardless of your choice, your app will need also some metadata tables to store what those additional attributes mean in the particular customer context.
   
3. In this way, you arrive at the EAV design. You simply define a table
   like this:

   ADDITIONAL_INFO(
     ENTITY_NAME
     ENTITY_ID
     ATTRIBUTE_ID
     ATTRIBUTE_VALUE
   )

   That's it, you have designed a generic facility that is able to store customer age. Simple, if customer with ID 35687 is 25 years old you just store ('CUSTOMER',35687, 'AGE','25') Repeat that for all customers. GPS Coordinates? No problem, ('CUSTOMER',35678,'LOCATION','1.123 2.45')
   
   And here, you have it, the origin of the EAV model in relational databases.

Changed beyond recognition

By itself, this technique (should I call it "pattern"?) is not good or bad. As stated, it is a solution for a problem and seems to be a good compromise between flexibility, customization and usability.

Those big applications (Siebel & SAP are the examples closer to me) ship with at least rudimentary facilities to customize their behavior. These facilities are no match for a full featured IDE like Eclipse of Visual Studio, since they are intended to handle small changes in functionality so they don't need to have the bells and whistles of your regular Java or C# IDE. But using those you can easily add to the views the additional data items coming from this table. It's all solved, right?

Yes, up to this point everything fits with the original plan. You have a generic mechanism for extending entities that is flexible enough to accommodate all future needs. However, two things stand in the way to data model nirvana.

First, you'll have to face the problem of exploding data volumes in this table. Let's suppose you have X customers and wish to add four additional data items to the customer entity. You then have 4*X records in this table. Worse yet, each time you display your customer data you are accessing this table four times, making it a point of contention far worse than the original customer table. If you use this table to add detail to transactions the volume explodes even more.

Second, you essentially give up on the database providing any kind of integrity on those additional data items. In general, you cannot enforce integrity constraints, nullability or uniqueness, unless you immerse yourself in writing some complicated triggers. You were doing this because you wanted to store arbitrary data, therefore it seems ilogic to try to enforce rules on what is essentially arbitrary data.

Third, and this is the worst problem, this can easily go out of control. Specially with packaged applications, one must always resist the temptation to rewrite them. As it often happens, the usual way of deploying such packages entails some choices when there is a mismatch between the business process as supported by the package and the same process run by the company. Guess who usually wins? The company, of course. After all, the process has been there for years and even may be regarded as competitive advantage, so why they should bend to the standard packaged way?

Very few companies take the brave step of changing their processes to fit the package. Instead, they customize the application to fit with their purposes. But this when taken to the extreme, specially in business driven projects with no technical expert on the business side leaves the original package unrecognizable. In some cases, I've seen EAV entries used as foreign keys to other tables!

Aside from the problems that this creates at version upgrade time (some packaged applications have not been upgraded in 12 years just because of the cost of redoing all the customization) this also invalidates any assumptions made by the application original developers. Assumptions made about performance, usage scenarios, volumetrics, everything can be invalidated.

In fact, my completely unscientific research indicates that there is an ongoing new trend against packaged applications as the universal solution for all in-house development problems. More specifically, big IT analysis groups are discouraging business to go the packaged application route and write their own from scratch if they anticipate having to change or customize more than 20% of the functionality they are going to use. The argument is that it's cheaper on the long term, as completely overriding application functionality often takes more work than creating that functionality in the first place. On top of that, the development environments for those packaged applications are usually orders of magnitude less productive than standard IDE's.

The performance tuning dilemma

Whereas classic data modeling techniques translate "entities" into tables and attributes into columns, the EAV model tries to store different entities in the same table and also allows for entities to have a variable number of attributes. This works under some circumstances, but almost never works with relational databases.

As for performance, the overall consensus around EAV is that it's bad. In some cases it's just bad as when you get a rainy day just when you intended to have that nice bike ride, in some others bad as when you go thru extensive dental surgery. Some of the problems you may encounter when trying to optimize an EAV design:

As hard as they try, RDBMS optimizers still have lots of problems dealing with a high number of table joins in the same statement. The access plans will be dominated by he need to access the EAV tables over and over.

Yes, I know, relational databases were supposed to be built to solve the problem of handling joins. But never in the scale required for the EAV explosion. In a well designed data model, very few if any operations need to join 12 tables. But remember, EAV will require a join for each attribute accessed.

CPU utilization will be very high since you'll want to access EAV attributes using an index lookup, trying to avoid scanning a large table when you're interested only in always a small subset of it. What is retrieved in a "sane" data design as part of the entity now requires an extra lookup and table access per attribute.

Contention on data and index pages will be very high as those attributes are all the time accessed by everybody even in the simplest of cases. Database without row-level locking will suffer the most, as without partition you cannot ensure that the attributes for one entity and another are in different pages or tables.

What can be done to improve performance of EAV applications? The problem when tuning such designs is that, at least in my case, I always try to avoid changing the data model as part of the exercise. Changing the data model and putting all those attributes in tables where they belong means to change the data model and by definition this means changing also controller logic. Which lands us in very expensive territory, if possible at all (do you have source code?)

• The best I've been able to do with those applications without touching the data model has been always around storage layout. Partitioning the table using DMBS features. Slicing the big table based on ENTITY_NAME will reduce overall contention and confuse the optimizer a bit less because you're now more or less localizing access according to entity.
  
• Making sure that the EAV tables are cached (if appropriate) and do not fragment with frequent updates is another way of at least avoiding the performance deteriorate even more.
  
• Apply (1) and (2) to indexes if possible. Needless to say, ensure that

  (ENTITY_NAME,ENTITY_ID)

  is the primary key.
  
• Check SQL statements and verify that they always access the table using ENTITY_NAME and ENTITY_ID values. Yes, someone knows that there is not a single object other than customer 24 with ID=24 in the whole system, but the RDBMS cannot use the primary key to access it.
  
• Avoid usage of EAV values as foreign key, if possible.
  

None of those techniques will produce any spectacular performance improvements, so your reputation may suffer. But you can always point your clients to this article so they understand a bit better the situation. And someone would possibly think again before committing this mistake in the future.

Does this means that EAV is essentially bad? No, I was not trying to express that. What I was trying to express was that this EAV idea is of very limited applicability in relational databases. It will not scale or support anything beyond the simplest "let's store the second telephone number here".

For those that really need this kind of flexibility, my advice would be to look for hash table based object storage or document indexing. Of course, you'll miss the ability of the relational database to join things together, so perhaps a mixed approach would be better.

And by any means, follow the expert advice: if you need to change more than 20% of your purchased package (make that 15%) throw it away and write your own.

SQL Server Express installation

How things have changed, that what I was thinking when I finished the install of SQL Server Express edition.

But, as almost always, I've started with the end of the story.

A close relative is soon going to change roles at work. She's moving to a system administration position, where, among other tasks, she's going to have to deal with a lot of MS SQL server tasks.

I was therefore asked about the best way to learn "SQL" After some more questions it became clear to me that "SQL" was in fact Microsoft SQL Server. I've always been amazed at how well Microsoft has done in the marketing side, since for quite a lot of people, even technical ones, the word "SQL" means "Microsoft SQL Server", apparently ignoring all the rest of the relational databases out there.

Since I know too well that the SQL language is not that standard, my advice was to get some sort of limited free SQL Server edition and use that as a learning platform. You can probably learn any other RDBMS and translate most of your knowledge to MS SQL, but there will be always parts of the language, storage and indexing concepts that are specific to each product.

How wrong I was, not anything sort of pared down RDBMS. MS makes available for download the full SQL server software suite, including the multidimensional database engine. There are restrictions on its use, of course, and I must confess that I read them, understood them, confirmed that it was Ok to use for learning and then and forgot the details. Remember, you should read them to make sure you're not accidentally infringing. If you want to learn the ins and outs of the MS product this is the way to go, as nothing is missing from the commercial package.

I've always been very supportive of these kind of offerings. People get a chance to get marketable skills and vendors get a pool of people that at least is familiar with the software that can help paying customers get the most out of their purchased product. Before the big players made those kind of packages available, I've seen too many sales reps declaring "piracy" on a junior developer taking software home just to be able to learn more in his/her spare time.

Back to the story. I proceeded to download the package. At this point, I faced the reality I have been able to ignore for the last few years. Windows server product installations have become complex. Way more than they used to be. I had first to read the prerequisites and install and update the required OS components. After a couple of reboots, I was ready to execute the installation program.

But I (wrongly) assumed that installation was going to be more or less the same as it was before. In the past, to get a basic installation working it was just a matter of accepting the defaults and letting the installer do its thing. Oh well, and perhaps a reboot or two. The end result was probably not efficient or secure, but at least got you to point of having something that worked. However, this path of least resistance has been taken to the extreme by Microsoft, to the point of being the root of much of the problems Windows is famous for.

First, there are the security problems. In terms of security, Microsoft always in the past compromised safety in the name of usability. That philosophy bounced back to them in many dire ways. Users got used to the idea that they could change anything in their machines without restrictions and started to complain when Vista was released, as it was for them an anomaly to respond to UAC prompts. The lives of malware creators were much easier as in this default environment any user level action could trigger a system change without further permission.

Application installations also dumped by default into the hard disks tens of megabytes of code, templates, galleries and whatever else that were not really going to be used in 90% of the cases. This also created a competitive disadvantage since latest netbooks have limited disk capacity and thus cannot easily accommodate the standard Office installations.

Seems that Microsoft is trying to fix those problems. In MS SQL Server case, I was confronted with a lot of choices. The installation package wants you to specify exactly what you want to install, where and which user accounts should run the services. Which made me trying to get on with the most obvious answers without checking the documentation first. The installation program then went ahead, only to be stopped at the last stage with an error stating the user account I specified was not valid to run the service. Complaining to myself, I then read the documentation and realized that I had not given the correct access rights to the user account.

Ok, I thought, let's tweak the service startup parameters and change the account for the SQL Service. Ooops, since the initialization scripts of the database were not executed this instance cannot be started, regardless of the user you choose. Ok, let's run again the installation. No way, you've already an installation of SQL server in the machine and the install program rejects overwriting it. Ok, let's remove the instance that cannot start. No way, the instance cannot be removed without the service being started at least once first, which cannot happen until you the installation executes the installation scripts. Which cannot happen until the instance can be started, which cannot happen until....

I found a way (and perhaps it's not the best, I'm no expert on database installation procedures) of this chicken and egg problem by using the uninstall program icon. This program does not need your database instances to be in an usable state and just wipes all the installation from the machine. After doing that, I ran again the install program taking care this time to specify the correct user account with the correct access rights and everything went fine.

Moral of the story? Microsoft listened to criticisms. They tried to make their products more secure by default and less bloated by default. By doing that, they've sacrificed the ease and convenience that were once sales arguments for their products. I guess that this is no longer relevant for them, giving that they have a market dominand position in a lot of markets, databases however not being one of them.

This also gives good arguments in favor of a standard for application packaging. In the last few years, the only software installs that I've actually done myself have been Debian packages. They are self-contained, install automatically the dependent packages (no need for you to track and install separate updates or service packs) and offer secure reasonable defaults that you can change later. And best of all, no reboots. Unless, of course, you're upgrading your kernel.

If Microsoft could dictate such standard, lives would be much simpler for everybody. Unfortunately, Microsoft has different ways of updating across its own range of products, so this is probably never going to happen.

The future of query optimizers

Future performance optimizations

While learning about the Oracle 11g invisible index feature, I really became aware of how complex the modern cost based optimizers have become. Essentially, Oracle has added a feature to its cost based optimizer that allows you to make an index invisible to its optimizer.

This brought to me fond memories from when I had to revisit an already tuned statement, as performance was mysteriously been dropping over the last few days, or hours, without an associated increase in data volumes or system loads.

In almost all cases, and after some investigation, I realized that the previous statement access plan was no longer optimal. This was always due to one of those: either the size of the result set was drastically different, the cardinality of some of the columns involved had changed, or finally, the data volumes, while not having increased in a meaningful way, made some of the assumptions of the CBO invalid. In all cases, the fix was always either running again the statistics collection utilities or rewording the statement, maybe adding also optimizer hints, so that a different access plan was used.

And yes, having at those times a feature that made indexes invisible to the CBO would have been nice. Very nice in fact. But have we gone too far?

Too clever and too rigid?

At the time, I just accepted that this was a shortcoming of the CBO, for all its advantages one could not reasonably expect the CBO to always get it right. What I was being paid for after all? No system is perfect and the Oracle CBO is specially good at picking the best access plan. Very seldom I've had to use optimizer hints or other tricks force it to use a better alternative, and for most of the work in an application (famous 80-20 rule) the CBO does a good enough job.

But recently I was involved in some work for a content recommendation system.

Initially it was only a performance problem, but after becoming familiar with the code and algorithms, I was able to improve the recommendation accuracy as well as doing one of those spectacular "14 hour process converted into a 0.02 sec on line query" feats.

Now, I'm no expert at statistics, and I sometimes have tried to understand all the complexities of the NetFlix submissions and would need six months to even understand the finer points of all the algorithms. But one thing became clear: all of the try to predict future trends based on the past. And the best algorithms even adjust hemselves over time to correct their own mistakes. Of course, all of them reach a point where they cannot get better accuracy and their results stabilize. They will never be able to reach 100% accuracy, as their mistakes when predicting are also their bases for improving their predictions.

Sorry for the digression, what does this has to do with database performance, after all? Somehow after reading the Oracle specs I was under the impression that Oracle had created a fix for a problem that their CBO has created in the first place. By the way, if you check the documentation of other RDBMS, for example MySQL and Postgres, you'll find that they don't have a similar feature (so if you're doing one of those feature list comparisons, here's one for Oracle) Even the fairly good, sometimes even better than Oracle, Postgres genetic algorithm for selecting an access plan does not allow you to selectively "hide" its indexes. In short, Oracle allows me to protect the CBO from itself. This lead me to think, why this is necessary? Should the problem have not happened to begin with?

Tuning approach today

More or less, the standard way of approaching database tuning always start with the 80-20 rule. Simply said, for 80% of the application, performance is perfectly acceptable. In those cases, nobody is willing to invest time in improving performance, because there is no real benefit in doing that. What we should not forget, however, is that the database in fact could be performing very poorly in that 80% in terms of resource usage. But as business priorities are what drives the focus on performance improvement areas, it does not make sense to look at them.
But, and this is important, that does not mean that we're wasting resources there.

I predict that with the recent rise of application service providers and cloud computing, the issue of utilization ratio of large farms will be important in the future. Let's say that I have a cluster: if it is being used at 50% capacity and I can make it magically 10% more efficient that's really going to make a 10% improvement in my ability to serve more customers. Note that 10% is equally applicable to the 80% that performs satisfactorily from a business point of view as well as the usually problematic 20%.

But it is not practical to even try to tackle the 80%, simply because of the sheer size of the job. As applications are growing in size and complexity, no single team of manageable size can even think about doing such systematic analysis. Simply looking at thousands of access plans is not viable. We must trust the CBO, it's our only way of keeping our ability to grow on application complexity.

The four traps with current optimizers

Access plan generation is one of the features that differentiate RDBMSs in the market. One that is probably very complex to implement, as theoretical principles and real world experience must be merged to produce something that is "almost" always right. And by "almost" we should read "good enough in most cases". Unless you're dealing with really tight constraints, It's really not that relevant if a SQL statement takes 0.01 or 0.02 seconds to execute, as the difference usually does not have a major impact in overall system performance (again, warning, of course there will be extreme cases where this will actually be very important) What is important is that the database maintains a consistent, real world reflection of the complexity and data volumes that it is handling. One finds reasonable that a statement that deals with small data volumes executes quickly, and statements handling million of rowws are completed in a reasonable amount of time.

And current optimizers do quite a good job at that. But they fall into four traps.

• The context trap. As I've said many times, they don't know the business context of the data, nor the business priorities. So they cannot say "nah, no problem in not using this index this time. The system is not loaded at the moment, I have power to spare and will finish a bit later than usual, but nobody will care about this" or "hey, it's really critical that we execute this process in two hours, go head over heels if necessary and make it faster" The on site presence of some
  performance specialist is usually a symptom that a database has fallen into this trap.
  


• The complexity trap. As seen by the Oracle example, sometimes they try so aggressively to optimize that they overlook opportunities for optimizing. The presence of optimizer hints is usually a tell-tale sign of this trap.
  


• The change trap. An access plan is expensive to generate, and is usually cached to avoid having to paid the cost of generating it again. This means that the plan is not able to change should the cardinality, data volumes, etc, change. The existence of features to hide or avoid to use data structures whose only reason of existence is to improve performance is a symptom that the system is into this trap.


• The 80-20 trap. As I've said, some of the most frequent operations can
  in fact be under optimized, but as long as we don't have any feedback from system owners, we'll never notice or care.

Something is wrong here, and I just realized that the optimizers should learn from the content recommendation systems. As far as I know, the optimizers currently in use generate an access plan and then store it to be reused later. None of them try to verify that their assumptions were right, or that the resource consumption was in line with their expectations. In short, none of them learn from their mistakes.

Adaptive access plans

This, I predict, will be the next step in cost based optimizers: the Adaptive Access Plan. In the future, databases will still keep a log of the statements they execute, but they will add to that information about how long took to execute with a certain parameter set. They will keep track of how many reads were made, how many of them were from the memory cache. They will analyze that history and adapt their access plans to the data volumes, system concurrency factors, previous execution times
and available resources.

In that bright future, we'll see performance improve over time as the system adapts its access plans to changing data patterns. Databases will tune themselves, not merely suggesting different ways of running, but even running in parallel different access plans and choosing the one that performs best for future executions.

Will this be the end of the database tuner? Probably not. The context trap, unless some sophisticated Turing test compliant software is created, will remain the realm of human beings.

PS: I'm sure that there is people playing around with the idea of adaptive access plans, and some of them even are thinking of getting a patent granted on it.Consider this article as prior art.

More on tuning tools from industry luminaries

It has been a long time ago since my post about tuning tools, but I found a very interesting paper written by some of the best people in the database world. There is a point where they say

"... Moreover, at the current time, the automatic tuning aids in the RDBMSs that we are familiar with do not produce systems with anywhere near the performance that a skilled DBA can produce. Until the tuning aids get vastly better in current systems, DBAs will turn the knobs."

(by the way, I'm still in the process of digesting all the very interesting content available there. If you want to have a broader view of where the database world is heading to, the vldb papers are a good place to start)

While this is missing the why part of the question, which I argued that those tools don't know the context well enough to tune as efficiently as a skilled person does, it is really comforting to know that I'm not the only one with that opinion.

A missing off-topic post

This post was going to be quite large, and hopefully entertaining. This post took me the good part of an hour to write and was, I supposed, interesting to those upgrading or about to upgrade to Ubuntu 8.10

Unfortunately, this post (well, its first version) was written as a mail message to a @blogspot account. Of course, and given the volume of mail that I handle on my day to day job, I usually clean my send items folder as soon as the message is sent. Which is what I did with the previous version of this post.

Only to find later that something is broken either in Blogger system to handle incoming mails as draft posts or I've not understood yet how it works. Which means that this post was lost.

So you'll have to make do with a brief summary of what was previously a quite entertaining story about my adventures upgrading my desktop Ubuntu from 8.04 to 8.10. Because in no way I'm going to rewrite it, I probably could not do it anyway as my writing stamina for the week got nearly exhausted.

But in summary, if your hardware is less than three years old, probably your upgrade to KUbuntu will be a good experience provided that your home network setup is not very complicated or you have previous experience tweaking by hand configuration files. In that case it's not going to be that bad.

But KUbunto 8.10 is not for revving up mom and pop five year old desktop. Not because the distribution is not optimized or KDE4 is not fast, but because there has been a surprising lack of attention to the essential details that make it user friendly in the same way that 8.04 with KDE 3.5 is.

And I hope to someday learn how to create draft blogger posts by sending mail...

(Update: I found the problem, and it was entirely my fault. I sent the message to a @blogspot.com address. Had I read more carefully the instructions, I would have sent it to a @blogger.com address, which works as advertised. The mail handling is probably so perverted now by the spammers that the @blogspot.com server does not even send an error response back when a wrong address is used)

Optimizing MySQL data loads

Classic Mystery

Sometimes, performance problems can be like a classic mystery novel. Hey, just a bit of fantasy sometimes help to get the stage. Here we are, surrounded by the usual suspects, and pondering which one is the most likely to blame. And then the underdog detective comes up pointing to the least likely person to have committed the murder. Of course, all the police detectives that have been tracking the evidence scattered around the crime scene and changing its opinion all the time about who was the most likely person to blame during the book, nod in disagreement and manifest their disbelief.

Of course, the characters themselves, had their read some books of this type before, would know that they were wasting time all along. Everything they've done so far has only created reasons to discard all the usual suspects and almost reach the end of the book with no good solution to the problem.

But somehow, and perhaps with a bit of cruelty, they allow the strange person in the room to explain the logic behind. What they really want is to have a bit of relief from all the tension and stress that having to face an unsolvable mystery in your hands create. After all, they want to see how the rest of the world is as lost and clueless as they are, or even more. When you start your explanation they even refute and joke on each fact that you state, and about how absurd your reasoning is.

However, after the first laughs they shut up and listen, as facts begin to stack one on top of another, without any cracks in the logic. At some point, the audience seems mesmerized, following each sentence in silence, their minds trying to anticipate what is going to be your next logical conclusion.

And suddenly, it all makes sense. Everyone is convinced that should be right. Of course, when the real culprit faces detention, he (or she) either gives up and confesses or runs away.

Database tuning mysteries

Performance tuning, while less dramatic, sometimes has all the ingredients of a classic mystery novel. Well, except for the runaway (of course, there is always the possibility if one cannot reach the customer performance targets, but it's a bit more professional to not bill them in that case) Also, there are no documented cases of databases escaping from their tuners. But the best tuning jobs usually are the ones where the best solution for the problem is the least unexpected one. Talk about lateral thinking.

Recently I engaged with a potential customer that wanted to improve the load times of a text file into a MySQL database. This potential customer had a very well defined problem. A text file containing roughly 110.000 records had to be loaded frequently on its database.

The crime scene

Data was loaded into the IMPORT_DATA table. For each record on that table, a comparison was made to check if the record met some conditions. If true, the data record was stored in the DATA_VERSIONS table, looking it up first to see if there was an existing record with the same values. In each case, either the LATEST_VERSION was updated with the current timestamp of the day to reflect the latest time this record was received or a new record was added to LATEST_VERSION with the current timestamp and the primary key of the new record.

The net result is that all records ever received from the customer that met the conditions to be interesting were stored in the DATA_VERSIONS table, with a timestamp in the LATEST_VERSION table to reflect the last time they were loaded.

The baseline time I have from the basic operation was around 10 minutes. The customer wanted to optimize the load to be as fast as possible. He was talking about creating table partitions, using the BlackHole engine (more on that later), tuning its memory allocation, index structure, whatever.

The data was loaded from a plain text file, one line per record, with fields separated by the pipe (|) character.

The essential clues

The way all this process was done was curious, at least for me. Each time a row was inserted into the IMPORT_DATA table, a trigger was fired. The trigger in pseudocode read something like:


If RecordIsInteresting Then
    Look up record in DATA_VERSIONS
    If found
        Update LATEST_VERSION for the primary key record with timestamp
    Else
        Insert record into LATEST_VERSION
     End If
End If


The condition at RecordIsInteresting read more or less like FIELD_1 = 'A' AND FIELD_14 = 'B'

False suspects

Mmmm... By this time, you'll probably already have a good mental model of the process. And also, if you've faced situations such as this one, I can almost hear your thoughts. Hey, let's check the look ups. Was the DATA_VERSIONS correctly indexed for the look up of the just loaded record? Was the LATEST_VERSION table indexed also for the existence check? Was the IMPORT_DATA table emptied before the load started?

A good start, then. But it turned out that the answer to all those questions was yes. Everything was correctly indexed, access plans looked optimal. I even learned that MySQL is pretty efficient using the LOAD DATA ... REPLACE, as the timings were not that different versus LOAD DATA ... APPEND. Even so, the data volumes loaded were within the reach of machine CPU and RAM so no resource seemed to be exhausted. Repeated tests with detailed checking of operating system statistics did not reveal any particular bottleneck. Machine resources were used efficiently, and the CPU was not waiting idle for the disks to read the data.

So all the usual first aid measures were not going to help. This is were the fun really begins, as improving performance in this way would not be anything to write home about (much less a complete novel) But then, mystery novels had those characters there just as a vehicle for the reader to discover the essential facts about the crime.

The conventional thinking

Enter the expert and veteran detective that is looking at this case as his golden opportunity to become a media star. As clearly something has to be done to solve the mystery, the veteran takes a long and hard look at the problem. Of course, he examines all the false starts. And after a while, he has an idea. After all, why use a trigger to process the records one by one when all of them can be processed at once? This is a classic optimization technique that I've already covered. After all, what's the point of doing it record by record if you can do all of them in one single pass?

So the expert sits down and writes something like



INSERT INTO DATA_VERSIONS
SELECT
    *
FROM
    IMPORT_DATA
WHERE
    RecordIsInteresting
ON DUPLICATE KEY UPDATE
    TimeStamp = Now

INSERT ON LATEST_VERSION
SELECT
    *
FROM
    IMPORT_DATA
WHERE
    RecordIsInteresting
ON DUPLICATE KEY UPDATE
    TimeStamp = Now



With a smile, the expert disables the trigger on the IMPORT_DATA table, loads the data and executes the two sentences. Before claiming victory, he checks the timings. Now, he's down to 8 minutes! What has happened? He checks that the RecordIsInteresting condition is covered by an index (it is!) Not very proud of his achievement, but satisfied nonetheless as he has won were others have failed, he announces that his improvements deliver 20% better performance.

Lateral thinking

Enter the underdog. Great, he says, at least we know something. Wait, what exactly do you mean? - answers the expert. Of course, we know that MySQL triggers are pretty efficient. It's only a 20% overhead, which means that MySQL developers have done quite a good job in their trigger runtime. But, have you asked yourselves if the customer is going to do anything with the IMPORT_DATA information after the load?

- Why? - answers the expert. Of course, he does not want to do anything with the IMPORT_DATA table. That's why he was thinking of using the BlackHole engine for this table, since his only interest was in the data during the trigger execution. Of course, my optimization will not work with the BlackHole engine, as it depends on the data being available to be processed in a single pass.

Realizing what he had just said, the expert creats the IMPORT_DATA table with the BlackHole engine and tests the original data load timing. Well, it now takes 9 mins to load, meaning that he's still able to claim his 20% optimization. Relieved, he then proceeds to probe the underdog that the time it takes MySQL to actually store the data is not that relevant after all.

- Oh, yes, I see, so the IMPORT_DATA information is useless after the load. Can I do a quick check? - asks the underdog.

The underdog bangs away at the keyboard for ten minutes or so, then he claims - See, you can load the data in 1 minute. That's it.

The expert is amazed. At first, he does not believe it. He checks and rechecks the resulting LATEST_VERSION and DATA_VERSIONS tables and everything is correct. Well, now, please show us what amazing MySQL technique you've used, he says.

The underdog is, at heart, a shy person. So it takes him a moment to sort out his argument. It's not that simple. I had to check how many interesting records were in the table. As it turns out, only 10.000 out of the 100.000 records loaded met the RecordIsInteresting condition. So I just said to myself, what if I dont' load those at all?

- But how do you know which record is interesting or not without loading them?, asks the expert
- Well, I wrote this awk program to filter the file before loading it.



# Records are separated by |
BEGIN { FS = "|" }

# First line is for headers, keep it to not having to change the MySQL LOAD DATA statement
NR == 1 { print $0 }

($1 == "A") && ($14 == "B") { print $0 }



- The awk program takes two seconds to execute and creates a file with just the 10.000 interesting records. If you load that file instead of the original one, the results are exactly the same. The only difference is that you're saving MySQL to read a record from a text file, splitting the line into field values, filling the temporary buffers necessary, firing a trigger and evaluating a condition 90.000 times. You only use MySQL to process the records you already know that are interesting.

... end of the story. The real mistery is that my real customer never heard of this solution, as I lost contact with him just after we reached an agreement on the pricing. I don't know where he is, and I hope that he does not mind if I use his problem as an excuse to poke fun on classic mystery novels.

PS - I had very good fun when I was young reading those novels. And I must admit that I never, ever was able to guess who was guilty in advance.
PPS - of course names have been changed to protect the innocent

The Google Application Engine

In the beginning, there was timesharing

In the beginning, there was timesharing. This was in the early, early days of computing, before most of us were even born. At the time, computers were big, expensive, complex and extremely difficult to operate. Not to mention underpowered in comparison with, say, a domestic media player. Since most business did not have the resources -read money- or even the need to use one of those monsters full time, you basically rented time from the machine. This model of using applications was perceived as a win-win, as the customer did not have to pay the up front costs of maintaining all that infrastructure and the supplier could maximize its return on what was at the time, a significant investiment.

Over the years, as demand for usage of computers increased and prices went down, more and more business found that it was profitable in the long run to own and operate the monsters by themselves. Thus, the timesharing business model gradually faded, althought some parts of it survived. Specially for the big mainframe iron, IBM still continues to "rent" their software and "cap" the CPU cycles that a modern mainframe uses based on a monthly fee.

I'm not sure if this model is still used literally. I'm sure that 10 years ago it was still in use (anyone remembers the defunct Comshare company?) But as most everything in the computer industry, the model has not survivied literally, it has morphed over time while keeping the same conceptual underpinnings.

Cloud Computing

"Cloud Computing" is the latest industry buzzword. Basically, it means that when you use applications, those applications are not running in hardware you own, you don't even know what hardware is used. It also means that the provider of those applications has to worry about availability, scalability and other concerns that were responsibility of the company IT department. It also means that the provider can take advantage of economies of scale, by for example reusing transparently its machine resources to support many different customers across different timezones. It can also spread geographically its machines to save on communication costs and improve response times, and apply other optimizations.

So, are we seeing the return of timesharing? Some would say that yes, this is essentially the same model applied in the 60s, only using bigger, more powerful and cheaper machines. And yes, this is a repeat of the SaaS (Software as a Service) wave that was the latest industry buzzword five years ago. Of course there are a lot of similarities, but there are a couple of points that make things different now.

First companies adopting cloud services are making a choice from their current established infrastructure and the supplier offerings. They have experience, can compare costs and are much more conscious of the trade offs, limitations and opportunities that each model has. This was not the case with timesharing, since the costs were so high that none could really afford to supply internally the same computing resources. This time it's a choice, not an obligation.

Second, some software categories have become "commoditized" For some software categories (Mail clients, Office productivity apss, even CRM) one can choose from different offerings and there is a clear relationship of the price/features ratio. The timesharing of the past was almost always done on either higly specialized number crunching applications (hardly classificable as commodities) or in completely custom built business software.

The Google Application Engine (GAE)

In the past months we've seen the launch of Amazon E3, a notable offering that is proving to be a cost effective alternative to housing or hosting providers and also have the resources to scale should the needs of your application increase. As a counter offering, Google has launched its beta Application Engine. Its main difference is that Amazon has split their offerings into infrastructure categories, such a storage service (S3) and a "super hosting" service (EC) whereas Google's offering is centred around an application hosting environment that includes more built in facilities, but is also more restricted than Amazon's.

Basically, Google offers to share its enormous infrastructure, for as yet undisclosed price. The advantage is obvious, Google has proved already that they can scale to a massive number of users and storing massive amounts of data without disruptions and without degrading the quality of the service provided to the users. They have been developing and refining their own technology stack for years, all of that with the eye on massive scalability. It's very unlikely that anybody outside Yahoo, Amazon or Microsoft can match that, much less with limited resources in comparison with what Google can put together.

Google has not yet provided all the details, and the service is still in beta. But some of the main points of the offering are already apparent. There are already lots of reviews where you can find their strengths and weaknesses, written by people much more knowledgeable than me at web development. But for me, the interesting part is their storage engine. Google provide an abstraction called the "datastore", that is, an API that allows programs hosted in the App Engine servers to store and retrieve data. You don't have any other means of accessing the data persisted than the datastore API, so you better familiarize with it before delving further in any application design.

Google datastore, limited?

The most striking feature of the datastore is that neither it's relational, nor it tries to be. It's a sort of object oriented database, where Python (the only language the App Engine supports so far) objects defined in terms of Python classes are stored and retrieved. Optionally, indexes can be defined on properties, but an index only indexes a single property. Primary keys are usually automatically generated, and relationships between objects are handled via "Reference" types.

The datastore can then be queried and objects retrieved. The Google Query Language (gql) has familiar SQL syntax, but outside of the syntax is a completely different animal. It neither allow joining entities, nor to use very common combinations of conditionals in its WHERE clause. There are no analytical functions (COUNT, SUM) and there is a limit of 1000 objects per result set.

Is this a rich environment for building data intensive applications? It all depends on your idea of "rich" and "data intensive" Of course all the missing features can be worked around in some way or another. But, and this is the difficult part, probably they should not be. By that I mean that anyone can provide iterative code to perform the joins, aggregations and whatever else you find missing from gql, but that does not mean you should do it.

For one thing, the GAE has strict limits on the CPU cycles that a request can consume, so you'll probably use them quickly if you write too much code. Those limits are there to protect Google shared resources (nobody likes its machine cycles being sucked up by the guy from the next door) So joining entities like you were joining tables is out of the question. Same for counting, aggregating and other time consuming operations.

Scaling in a scale that you cannot even start to imagine

All those limitations are, at the very least, irritating. Some of them even make completely unfeasible some kind of applications. At first, I was felt deceived by GAE. However, after browsing some more documentation I soon realized what GAE is all about.

Some background is in order: I live and breathe business applications. By that, I mean that I'm used to the scale of ERPs or Data Warehouses that span, at best (one would say sometimes worst) thousands of users. That's a formidable challenge in itself, and one that takes a dedicated army of engineers to keep running day to day. The usual mid sized system is aimed at a medium size business or a department inside a big corporation, never tops 150 users. And I've devoted a significant part of my professional efforts to study those environments and to make them perform better.

Now, Google plays in another different league. They provide consumer applications. While business users can be in the thousands, consumers are in the millions range. That takes another completely different mindset when planning and scaling applications. The GAE restrictions are not there just because Google engineers are timid or have developed an incomplete environment. They are there because GAE can make your application scale very big. Awfully big. With that in mind, you can start to explain to yourself why GAE datastore has so many restrictions. Those restrictions are luxuries that you can only afford when you can limit your user base to most a few thousands users. If you want to scale beyond that, you have to give up on some things.

Consumer applications are different

In short, don't expect to see shortly an ERP built atop of GAE. It's not the ideal environment for that. But it's equally unlikely that there is ever consumer level demand for your own ERP, isn't it? Think of GAE then as an ideal platform for consumer level applications. Then it starts to make sense. Think of Amazon EC as a super scalable hosting service, where you basically can make your application scale in terms of hardware, but you're going to be on your own when defining how to make that hardware perform.

Even so, GAE and related environments bring a new set of challenges, at least for me, in terms of how to efficiently store and retrieve data. I'll keep posting my findings, and I'm sure it will be a lot of fun to explore and discover.

Persistence engines

How things came to be this way

Anyone involved long enough in software development ends up discovering a truth that is long ago known by other craftsmen and engineers: each problem is best solved with the tool more appropriate to solve it. Only problem for software is, it is such a young discipline that is all the time trying to tackle new problems not previously solved. Thus, sometimes it's very difficult to know beforehand if a tool is going to be adequate or not until you actually try it.

All software development tools, from assemblers to compilers to scripting languages, are a testament of this constant evolution of software to meet the ever increasing requirements of solving new problems using computers. It's easy to see that over time, as long as the more generic tools are developed and consolidated, more and more tools are created to attack more and more specific problems.

Structured data persistence was one of the most often attacked problems since the inception of the first computers, so at some point (Codd et al?) invented the concept of the relational database. This was so successful that the concept has survived to this day and is used extensively as a standard means of storing and retrieving data.

Reuse, encapsulation, inheritance and polymorphism was another recurring problem that software projects, specially at large scale, had to face. Thus, the object oriented programming model was invented and expressed in a number of different languages. Some of the more successful ones (C++, Java, C#) are the standard means of developing software today, usually supported by a large number of pre-built components that provide a number of useful features that can be reused across many different projects.

Sometimes when facing a new problem, software designers even find worthwhile to create a new tool just for the purpose of solving that specific problem. That's how small (and sometimes big) scripting languages are born and a lot of other tools (parsers, code generators, etc) are created. Other kind of tools that are also in wide use are "Frameworks", which are collection of software components that work together to solve a problem. The distinction between frameworks and applications lies mainly in that frameworks are designed to be used in applications, but they don't usually perform any function directly useful to the business objective of a piece of software.

Frameworks are also means of abstracting complex subjects into more manageable pieces. Specially with object oriented languages, properly designed frameworks allow programmers to selectively use the parts that they are interested in without having to learn the whole thing at once.

So, in a perfect world, developers have a wide range of options to choose form when creating a new application. In practice, this is not the case and often business standardize around a set of tools that are used across projects. Three factors conspire together. First, the complexity of tools mean that learning them represents a usually significant investment in time. Second, the biggest the number of skilled resources available for a tool makes much easier and cheaper to hire people for your shop. Third, humans have a natural tendency to avoid change and thus once you find something that has a reasonable price and works for you, you tend to keep it forever.

The end result is that usually applications are built using a set of components consistently across the same or many applications. One of the latest category of frameworks that have been consolidated are the persistence frameworks (or engines) Those are a set of classes that make your objects get stored or retrieved from a relational database without you (the programmer) even being aware of all the exchange among the application server and the database.

The object-relational impedance mismatch

Wonderful as they are, persistence engines have a problem: they are designed to store objects into relational tables. Objects have methods and data put together, plus (depending on the language) visibility rules. Tables are just tuples of data with relationships defined between them. (Digression: now it's time for me to hide from object/database purists looking to kill me since I've oversimplified concepts tremendously, but for the purpose of this discussion it's enough) Now it's easy to see that somehow in the translation, but suffice to say that the relational model is a subset of the object model, in the sense that everything that can be expressed with tables and relations can be expressed with objects. But not the other way, as you cannot express methods or polymorphism with relational structures.

Notice that there are some Object Databases that do away with the persistence engine and allow you to store and retrieve objects without losing anything on the way back. But, as you've guessed, these are not relational. While these engines someday may enjoy popularity, they are not presently part of the standard tool set in the majority of shops. And that's not likely to change in the near future. So for now we're stuck with relational databases. There have been some attempts, most notably from Oracle, to introduce objects in the relational database. While the technology does have its merits, it has never been very popular and the rest of the major industry players have not followed suit.

The performance compromise: how bad is that?

As always, there is no free lunch. For the persistence engine to work as transparently as possible, there has to be some kind of compromise. You give something, you get something. Since this is a blog about database performance, you are probably expecting me now to tell you how bad is it. Well, surprisingly not too bad. Provided that you use the persistence engine for what is intended to be used.

Here is the list of things that persistence engines are intended to be:

1. A way to abstract reading and writing objects to your database


2. A way to map your database tables into objects


3. A way to perform basic operations (CRUD: Create, Read, Update, Delete) on single instances of objects (read: database tables)


4. A way to abstract business process rules in your object layer instead of doing it in your database layer. This means that things that "sales that are above x limit should be approved by manager, and those above 2x should be approved by Finance Director" are much better easily expressed in object methods than in SQL triggers


Here is a list of things that persistence engines are not intended to be:


5. A way to avoid the relational database model, SQL language, or basic principles of database design. Sorry, you'll have to concede some things to your object model, because it is stored in a relational database. Please don't believe claims like the ones of the EVL design, they are simply not true.


6. A way to solve every database related problem under the sun. It's always best to choose a simple engine that solves 80% of the problem that a complex one that solves the 100%. The logic here is that the remaining 20% probably has a better tool around.


7. A way to forget about performance, the persistence engine takes care of that, does it?


8. A way to perform analytical queries that allows you to avoid writing SQL

Please do not disregard persistence engines as useful tools just because of points 5-8. The strengths of the first for make more than enough for them, as most applications have most of their bulk in the advantages. In short, properly used persistence engines can make your application much easier to write and will not cost you too much in terms of performance. But the engine will not solve all of your problems. Special mention should be made of so-called analytical queries (those that have SUM(), GROUP BY, etc) Those are specially badly done using persistence engines, as they were not designed to do that. Consider that:


SELECT SUM(sales) as TotalSales FROM ORDERS WHERE CustId = xxxx;


Is hundred times more efficient than:

resultSet = engine.Query("SELECT FROM ORDERS WHERE Customer = ", xxxx);
totalSales = 0
foreach( rec in resultSet ) {
totalSales = rec.Sales + totalSales
}


Chainsaws are not good for juggling

The golden rule to avoid performance problems with persistence engine is to apply the same logic that you would apply if you were using a hammer. I'm sure that when facing a screw, even if you've pounded thousands of nails before, you'll not even think of using the hammer, would you? The moment you feel that you're forcing a tool to do something it's not suited to do is the time to stop and reconsider your choice of tool for the task.

In short, avoid fighting with the tool. When you start fighting with the tool, it's a symptom that you should stop and think.

Please don't swap

While browsing the database forums, it has come to my attention that some people are looking closer at their database system memory management and trying to adjust its configuration settings.

Of course, the more complex the database the more parameters you can change. RDBMS's like Oracle for example have dozens of different parameters that can alter the memory usage of the database to better fit with your application needs. This is reasonable, and I've seen spectacular improvements on specific systems just because a single memory setting was changed.

As always, the usual rules of tuning apply here, change only one thing at a time and change only when you need to do it. And like any other tuning effort, the process should iteratively make a change and test it.

Different operating systems and databases have different means of checking the results of your changes. The most expensive ones like Oracle and DB2 have even dedicated packages from third parties that can help you monitor detailed usage of resources, CPU, disk IO, cache and memory usage. The less sophisticated ones may lack those capabilities, but usually the operating system has already enough facilities (/proc filesystem, Windows Performance Monitor) to give you at least a good overview of the resources that are being used and how much.

It therefore follows that each change is usually followed by a number of tests that try to reproduce the most stressing scenarios and evaluate the performance of each. Let me say that this is sometimes a nightmare. I'm happy with the scenario "I have this 2 secons query and I need to execute it in 0.02 secs" because that makes for quick and short tests, and those allow a much more rapid change/test cycle. But sometimes when you're dealing with processes that take eight hours to execute, it's a pain in the neck to have to wait from one day to another just to know the effects of a change.

One of the closely monitored parameters is always, or should be, swap file usage. But I've read some comments that somehow were implying that if your system was not using the swap file it was not correctly set up. Let me dispell that myth once and for all.

The origins of the swap file

In the beginning there was memory, just one kind of it. The primitive computers fifty years ago did not have the distinction between RAM and disk memory. All memory was just memory, and some rotating drum or other device provided that. Then the transistor industry began the craze and the distinction between fast memory (RAM) and disk memory was born. Essentially, the CPU can use a limited (but the word "limited" applies to 2GB here) address space of memory that can be used in a matter of nanoseconds. Were this memory the only available, most enterprise databases of today would not exist, as they easily exceed that size without a blink. That's why there's a second type of memory which is usually called "mass storage" or just the disk, that has much much greater capacity. However, as always there's no free lunch, and this memory is much much slower to access. And much slower means 100 or 1000 times slower.

That's why the operating system always tries to load into RAM the program instructions that have to be executed, as it would greatly increase the time of a program to run if its instructions had to be fetched from the slow memory instead of the fast one. Other data, such as the application own data, is usually stored on disk and recalled on demand. Of course, when that happens the program execution slows down enormously, as it has to wait for the disk to retrieve the requested piece of data, but not as much as if the instructions themselves were retrieved from the disk one by one.

This balance of disk memory and RAM memory has served the computing landscape well over the last thirty years or so. As applications became more complex, they required more and more memory for its instructions to be stored. As applications handled progressively biggest amounts of data, they required the auxiliary, slower memory, to be bigger. Technology was always provinding updates in the form of fastest CPUs, fastest RAM and fastest disks. Faster RAM has remained to this day a scarce resource in comparison, with disk drives reaching terabytes while RAM merely increasing in gigabite steps. Oh, and way more expensive than disk space. Thus, RAM was a resource that was carefully administered and assigned by system owners, because a program that could not allocate the amount of RAM needed in the machine would usually stop dead cold its processing thus frustrating the run.

However, as computers were applied to more problem areas, it became clear that it was just not possible to anticipate in advance how much RAM you were going to need. Perhaps in some situations yes, were the number of applications and its usage pattern was precisely know, it was possible to specify how much RAM you would need at each point and thus have enough of it in the machine.

With the advent of the workstation and the multiuser machine this became impossible. Since everybody could use the machine at any time with any mix of applications, it was no longer possible to delicately administer the RAM so that every application could run smoothly and not stop because of lack of memory.

Operating system and CPU designers realized that it was not necessary for a machine to actually have all the memory that was being demanded by the applications being executed. It was enough to appear to have it and provide on demand the regions of memory that were used at some point. This would also relieve from the need of manual intervention and adminstration of machine resources. They could just use the fast RAM memory until exhausted, and at that point start to use the slow disk based memory as applications demanded more memory. Of course, at the price of performance, but the reasoning was that it was better to have a slow running application than no application running. Thus, virtual memory was born.

Along those basic concepts, of course the technology has evolved to a point where virtual memory has become a mix of art and science, and every operating system that is in popular use contains a fairly sophisticated implementation of it. Jargon like "commited", "dirty", "shared", "working set" and other words are abundant in the literature, and I confess to know only the basic meaning of them, enough to get the work done. But it's a fantastic facility because it allows you to basically use your machine like it had no limits on its physical memory and get reasonable performance out of it. It's a fasciating dance to watch how the machine allocates and releases virtual memory as you open and close applications or even perform certain tasks with them.

Ok, I hear you ask, what is the "swap file" then? Generically, the place in the disk where the operating system puts the portions of RAM that are being faked as actual RAM is called the "swap file" In some operating systems this is not actually a file but a dedicated disk partition or even a different physical disk.

Why swapping is bad for your server

Of course, this virtual memory thing is all well and good, but it adds one element of uncertainity to the run time of a process, something that is sometimes undesirable. In fact, real time or close to real time systems usually forbid virtual memory altogether to be able to maintain its commitment to execution time. And it works to a point, because when your machine reaches the point of too many processes competing for the scarce RAM space, the machine spends more time bringing memory pages from the disk than actually doing any useful application work. This is what is called trashing and is bad, very bad for performance.

Workstations and servers - two different worlds

All the above applies very well to your workstation or PC machine, as you're usually using it without any predefined pattern or restrictions. You start your word processor now and need some numbers from your spreadsheet, then decide to put all together in some slides. You launch programs and close them without being really aware of how much RAM you're taking up. And you're willing to put up with a small performance loss as your systems start swapping (which is the word used to denote the point where your machine runs out of actual physical RAM and starts using the disk as a slow replacement), as it surely the convenience beats the need for yourself to organize and manage the execution order of your applications. As long as the illusion works, you're living in a fantastic world of almost unlimited memory.

Life of a server is very different. There are some characteristics that make a machine we call a "server". For one thing, it does not usually sits on your desk, but lives in a conditioned place with redundant power, protected from excessive heat or cold, and its access is restricted by a locked door for which only a selected few have the key. It's components are usually higher rated in reliability than its desktop workstation counterparts, even having probably not cutting edge performance.

All that is for a good reason, because a server usually performs some specific function on which the work of many people depends. There are file servers, application servers, database server, authentication servers, proxy servers, directory servers, name servers, and so on. Sometimes, the same machine is performing more than one role, specially where there are no resource usage conflicts between each service. But all of them are usually performing some essential function and either some other system or business process depends on their availability.

A server has to be available and usually have reasonable response time. A server runs a fixed set of processes or services, and usually does not need to deal with unexpected situations like someone sending you a 120MB PowerPoint presentation that you need to print in colour 12 times.

The corollary is, your server memory usage should remain more or less constant. Except under situations where the workload exceeds the planned capacity, your server should be happily working and its resources (CPU, memory, disk) should be used fully. The essential, unforgettable word here is "fully" Fully means completely, but does not mean "in excess"

And "in excess" when talking about memory means swapping.

Memory usage is the key

Memory is the fastest resource available to the CPU. So don't waste it. Check the documentation that comes with your database. The most mature ones have configuration parameters that allow you to precisely tune the memory usage of the database, and be aware that some of them (for example, MySQL) do have different parameters for different storage engines. So your memory usage with MyISAM tables is radically different than with InnoDB tables (by the way, try not to mix the two in the same instance) As always with database tuning, measure, tweak a single parameter, measure again and continue until you reach the good enough state.

But above all, don't allow the database to swap. You'll find documentation that says that having a swap file or partition of at least double the RAM size a good thing. It does not mean that you should use that swap space. It's there just in case, not for regular use.

How much memory should I use?

You may hear from other people that if you're not using the swap file you're not fully using your memory. They are wrong, it's a common misconception that comes from the confusion between physical and virtual memory.You may hear that if your system is not using 100% of physical RAM you're not doing it right. You may hear that if your database is using more than 50% of physical RAM your're not doing it right.

Those are the kind of "myths" that I hate. It all depends on the database server, and even the storage engine used in the case of MySQL. There is no "right" answer that applies unversally.

Oh, yes, there is in fact an universal rule. Repeat with me, don't swap.