Posted by From the DBA’s perspective there are some crucial things e-commerce applications should do. This is by no means an exhaustive list, and I welcome comments with additions.
Cache data
So WebSphere Commerce has DynaCache built in for this. There are also third party cachers like Akamai. In any case, caching is critical, especially for pages like the home page or sale pages or popular search pages that get product (or other) data from the database. Without some caching methodology outside of the database, there’s a limit on how far your website can scale, and how fast it can perform. Such data should not be queried thousands of times an hour from the database.
In addition, it’s better if your caching solution can do selective cache invalidation – requiring only incremental changes to the cache. I’ve seen a full cache clear take a site down and throw the database server against the wall for a full half hour – you need a way to not have to clear the full cache during peak hours.
Run only tested and known SQL
In my opinion an internet-facing application should never let users write their own sql – even in a controlled environment that limits them in some ways. If I had my choice, every sql would be fully analyzed by a dba prior to be included in any application, and this is even more true for e-commerce applications. It only takes a single expensive Cartesian product to cause major problems, and nearly all of your queries should be small and fast.
For reporting, the best choice is moving data to an ODS(and then allow selected users to write their own SQL there) or to run reports against the standby if you can bring it up in read-only mode. If you must run reporting against the same database that handles all of your transactions, then you must have a DBA validate that SQL and test it during your slow times, and preferably never run it during peak times.
Block SQL injection attacks
The database itself doesn’t look for SQL injection – that is the application’s job. Any e-commerce application should have controls against SQL injection. My favorite xkcd comic:
Use the lowest possible isolation level
Since concurrency(allowing many users to do stuff at the same time) is critical, the lowest possible isolation level is what we want. WebSphere Commerce uses CS as it’s standard, and has a column in most tables called OPTCOUNTER that it uses in the process of optimistic locking. This reduces locking and deadlocking problems in the database – which is one of the major impediments to greater concurrency.
Not cause deadlocks
Deadlocks are not a database problem. The symptoms sure show up on the database, but they are symptoms of an application problem. For more information on deadlocks, see Deadlocks vs. Locktimeout.
There are things at the database level that can contribute to deadlocking – several db2 registry parameters if your application supports them, and of course keeping runstats current and indexing for queries that are doing table scans can all reduce deadlocking.
Interact with HA solution
If you’re using type 2 JDBC drivers, db2 interacts with HADR and takes care of fail over with the use of Automatic Client Reroute. But if you’re using type 4 JDBC drivers, then the application itself must store the standby server and have the logic around how/when to connect to the standby. WebSphere Commerce does this, of course, but any e-commerce application should do it too.
Allow DBA to create indexes
Vendors have various levels of rigidity in their data models, but one area where they must allow flexibility is in creating indexes. DBAs must be able to analyze sql and index for it, especially if there is any possibility at all of custom SQL. Many vendors have enough flexibility in their product that they cannot possibly index for every use case, and this means that DBAs have to analyze and look for indexing opportunities.
