More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
What Have We Used MySQL For?
During MySQL initial development, the features of MySQL were made to fit our largest customer. They handle data warehousing
for a couple of the biggest retailers in Sweden.
From all stores, we get weekly summaries of all bonus card transactions, and we are expected to provide useful information
for the store owners to help them find how their advertisement campaigns are affecting their customers.
The data is quite huge (about 7 million summary transactions per month), and we have data for 4-10 years that we need to
present to the users. We got weekly requests from the customers that they want to get 'instant' access to new reports from
this data.
We solved this by storing all information per month in compressed 'transaction' tables. We have a set of simple macros
(script) that generates summary tables grouped by different criteria (product group, customer id, store ...) from the
transaction tables. The reports are Web pages that are dynamically generated by a small Perl script that parses a Web page,
executes the SQL statements in it, and inserts the results. We would have used PHP or mod_perl instead but they were not
available at that time.
For graphical data we wrote a simple tool in C that can produce GIFs based on the result of a SQL query (with some
processing of the result). This is also dynamically executed from the Perl script that parses the HTML files.
In most cases a new report can simply be done by copying an existing script and modifying the SQL query in it. In some
cases, we will need to add more fields to an existing summary table or generate a new one, but this is also quite simple, as
we keep all transactions tables on disk. (Currently we have at least 50G of transactions tables and 200G of other customer
data.)
We also let our customers access the summary tables directly with ODBC so that the advanced users can themselves experiment
with the data.
We haven't had any problems handling this with quite modest Sun Ultra SPARCstation (2x200 Mhz). We recently upgraded one of
our servers to a 2 CPU 400 Mhz UltraSPARC, and we are now planning to start handling transactions on the product level,
which would mean a ten-fold increase of data. We think we can keep up with this by just adding more disk to our systems.
We are also experimenting with Intel-Linux to be able to get more CPU power cheaper. Now that we have the binary portable
database format (new in Version 3.23), we will start to use this for some parts of the application.
Our initial feelings are that Linux will perform much better on low-to-medium load and Solaris will perform better when you
start to get a high load because of extreme disk IO, but we don't yet have anything conclusive about this. After some
discussion with a Linux Kernel developer, this might be a side effect of Linux giving so much resources to the batch job
that the interactive performance gets very low. This makes the machine feel very slow and unresponsive while big batches are
going. Hopefully this will be better handled in future Linux Kernels.
(Continued on next question...)
