I recently had a run-in with very popular PHP ecommerce package which makes me want to voice a recurring mistake I see in how many web applications are architected. What is that mistake? The ecommerce package I was working with depended on caching.  Out of the box it couldn’t serve 10 pages/second unless I enabled some features [...]

When it comes to naming things in databases and languages, there are various common standards. For many languages the camel family of namings is very popular. For unix based databases usually UPPER or lower _ is the choice and for databases such as SQL Server and MySQL which allow you to name your columns with mixed casing but could care less what case you express them in selects, you get a mish mush of styles depending on what camp the database user originated from.

So to summarize the key styles and the family of people

• camelCase : lastName - employed by Java and Flex and C and various other C style languages, sometimes PHP developers.
• Pascal Case: (a variant of Camel Case) -- LastName which is employed by C#, VB.NET, Pascal (and Delphi), and SQL Server (and some MySQL windows converts)
• lower case _ last_name : a favorite among PostgreSQL database users. (some MySQL)
• upper case _ LAST_NAME : a favorite among Oracle Users (some MySQL Oracle defectors)

Being at the cross roads of all the above, we often have to deal with the various above as well as having internal schizophrenic strife and external fights. The internal turmoil is the worst and is worse than an ambidextrous person trying to figure out which hand to use in battle. For these exercises, we'll demonstrate one way how to convert between the various conventions. These are the first thoughts that came to our mind, so may not be the most elegant.

Continue reading "Of Camels and People: Converting back and forth from Camel Case, Pascal Case to underscore lower case"

PGDay.EU 2010 has finally been announced. It will be in Stuttgart, Germany, on December 6th to 8th. More details available on the conference website.

We have also sent out our call for papers. If you have done something interesting with PostgreSQL, please go ahead and submit a talk! We are currently looking for talks in both English and German!

In an earlier article Where is Soundex and other Fuzzy string things we covered the PostgreSQL contrib module fuzzstrmatch which contains the very popular function soundex that is found in other popular relational databases. We also covered the more powerful levenshtein distance, metaphone and dmetaphone functions included in fuzzstrmatch, but rarely found in other relational databases.

As far as fuzzy string matching goes, PostgreSQL has other functions up its sleeves. This time we will cover the contrib module pg_trgm which was introduced in PostgreSQL 8.3. pgtrgm uses a concept called trigrams for doing string comparisons. The pg_trgm module has several functions and gist/gin operators. Like other contrib modules, you just need to run the /share/contrib/pg_trgm.sql file packaged in your PostgreSQL install to enable it in your database.

For this set of exercises, we'll use trigrams to compare words using the same set of data we tested with soundex and metaphones. For the next set of exercises, we will be using the places dataset we created in Importing Fixed width data into PostgreSQL with just PSQL.

The most useful are the similarity function and the % operator. The % operator allows for using a GIST/GIN index and the similarity function allows for narrowing your filter similar to what levenshtein did for us in fuzzstrmatch.

Continue reading "Fuzzy string matching with Trigram and Trigraphs"

It is easy for MySQL replication to become bottleneck when Master server is not seriously loaded and the more cores and hard drives the get the larger the difference becomes, as long as replication remains single thread process. At the same time it is a lot easier to optimize your system when your replication [...]

This the first delivery in a series of articles targeted at showing developers how db4o (an open source database that leverages today's object-oriented languages, systems, and mindset) is being used in several Android projects to avoid all the pitfalls and hassles of object-relational mapping while benefiting from an elegant and straight forward way to evolve a domain model which, in the end, translates into faster, easier upgrades for users.

More: http://java.dzone.com/articles/leverage-db4o-your-android

A new 8.0 development release is online. Below  is the list of changes since our last 8.0.146 release. We highly recommend all 8.0 users to upgrade to this build since we have fixed 2 serious issues ( bold ). Both issues were introduced with the new BTreeIdSystem in the 8.0 branch. Production and Stable builds are not affected. COR-2008 - [Android] ClassCastException trying to cast Db4oDatabase to GenericObject COR-2003 - [.Net] Investigate failing.Net 4.0 tests due to cecil update COR-2001 - With low custom max stack depth, PendingSet processing order expected by static field handling is not maintained COR-2000 - Defragmenting string field indexes is broken with btree id system COR-1998 - Introduce ID mapping sanity checking to be used in dedicated db4ounit fixture COR-1997 - Commit throw an UniqueFieldValueConstraintViolationException after defragment COR-1996 - java.lang.OutOfMemoryError in UnicodeStringIO.read COR-1992 - [Android] Review all ' ...

A new 7.12 production release is online. Here is the list of changes since our last 7.12.145 release: COR-2001 - With low custom max stack depth, PendingSet processing order expected by static field handling is not maintained COR-1978 - Transient field modifier is not being respected in C/S mode without classes in the server COR-1977 - Execution of sub queries descending into collection members only seem to use a single collection member as root COR-1950 - Respect transient modifier on static fields COR-1946 - Storage implementation to be able to call #saveAs() on an open database COR-1945 - Transaction.objectContainer() should always return an ObjectContainer with transactional behaviour, also in events. COR-1902 - System.Byte and System.SByte casting problem in Db4objects.Db4o.Internal.Handlers.ByteHandler COR-1822 - Committed callbacks should provide a means to discover if the committed event was raised by this clients commit or another one in C/S mode  Yo ...

( cross posting from SSD Performance Blog ) In previous post On Benchmarks on SSD, commenter touched another interesting point. Available free space affects write performance on SSD card significantly. The reason is still garbage collector, which operates more efficiently the more free space you have. Again, to read mode on garbage collector and write problem [...]

I recently did a quick analysis of the distribution of writes to InnoDB's log files. On a high-traffic commodity MySQL server running Percona XtraDB for a gaming workload (mostly inserts to the "moves" table), I used strace to gather statistics about how the log file writes are distributed in terms of write size. [...]

An email list posting this week revealed that Postgres will be used on the International Space Station by the end of the year, specifically "to store the data on orbit and then replicate that db on the ground". Postgres has always been heavily used by NASA and other government agencies, but this is a uniquely "high"-profile use of Postgres.

Check it out!

This is part two in my six part series on business intelligence, with a focus on OLAP analysis. Part 1 - Intro to OLAP Identifying the differences between a data warehouse and a data mart. (this post) Introduction to MDX and the kind of SQL which a ROLAP tool must generate to answer those queries. Performance challenges with [...]

(cross post from SSD Performance Blog ) To get meaningful performance results on SSD storage is not easy task, let's see why. There is graph from sysbench fileio random write benchmark with 4 threads. The results were taken on PCI-E SSD card ( I do not want to name vendor here, as the problem is the same [...]

(cross posting from SSDPeformanceBlog.com ) All modern solid state drives use NAND memory based on SLC (single level cell) or MLC (multi level cell) technologies. Not going into physical details - SLC basically stores 1 bit of information, while MLC can do more. Most popular option for MLC is 2 bit, and there is movement into 3 [...]

ICOODB 2010 is approaching and we'll be there helping promote object database technology. Please join us!

Here are the details for the event:

3rd International Conference on Objects and Databases
September 28-30, 2010
Frankfurt am Main, Germany

2 tutorials, 1 workshop, 10 research papers, 10 industry
presentations,  3 keynotes and 1 keynote panel...

(continues...)

So lets imagine you have the server handling 100.000 user accounts. You can see the CPU,IO and Network usage is below 10% of capacity - does it mean you can count on server being able to handle 1.000.000 of accounts ? Not really, and there are few reasons why, I'll name most important [...]

This year we're participating in OSCON as a Sponsor and organizing some BOFs. I will be on the conference 21 and 22 if you're interested to chat. Here is the list of currently scheduled BOFs which I'll be hosting: Running Databases on Flash Storage Sphinx Search 2010 XtraDB, XtraBackup, Maatkit, Percona Server See you there. [...]

This is the first of a series of posts about business intelligence tools, particularly OLAP (or online analytical processing) tools using MySQL and other free open source software. OLAP tools are a part of the larger topic of business intelligence, a topic that has not had a lot of coverage on MPB. Because [...]

This post is the third of a series that started here. From the previous of this series, we now have two working EC2 instances that are EBS based. The first instance is the monitor, usually an m1.small type instance and the second instance is hamysql, a large instance type. So far, we have configured [...]