MySQL vs SQLite – Suitability Comparison

Partition Question

You could partition by area and area_zone. As a result your CREATE TABLE statement would look something like this.

create table LogEvent
(
    id bigint auto_increment,
    area smallint unsigned not null,
    area_zone tinyint unsigned not null,
    value1 float not null,
    value2 float not null,
    reason char(2) not null,
    primary key(id,area,area_zone)
)
PARTITION BY RANGE COLUMNS (area,area_zone)
(
  PARTITION p01 VALUES LESS THAN (10,20),
  PARTITION p02 VALUES LESS THAN (20,30),
  PARTITION p03 VALUES LESS THAN (30,40),
  PARTITION p04 VALUES LESS THAN (40,MAXVALUE)
); 

You will notice that I modified your PRIMARY KEY definition as a PRIMARY KEY must include all columns in the table's partitionning function. There is also some learning you will have to do to understand in what partition you're rows would be stored. Here is a link that explains how and where rows would be inserted based on the way you define you're partitionning scheme.

http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html

The data types you choose for area and area_zone will depend on how unique both will be. As you said you are expecting millions of rows, the question is, will there be millions of different areas and area_zones. The number of unique areas and area_zones could not go past the limits defined by smallint and tinyint.

Also keep in mind that some partitions will be much larger than others. If you partitioned by ID, the partitions would eventually be about the same size. Using area and area_zone, some may be much larger than others.

InnoDB versus MyISAM

Rolando answers your question about MyISAM versus InnoDB quite nicely in this post:

Choosing MyISAM over InnoDB for these project requirements; and long term options

What columns to Index

For question 3, knowing where to add an index depends on the cardinality/selectivity of the column you wish to index. If a column is very dense, in otherwords it has a large number of duplicate values, then it makes less sense to create an index for that column alone. In your situation, you have two columns, area and area_zone. Area alone may not be selective enough as there may be many area_zones per area. I think you could create a composite index on both columns to obtain a better cardinality and thus a more useful index. Keep in mind that when you add an index, there may be an impact on performance and storage as MySQL must store the data in the index you created as well as in each column being indexed. Here is a MySQL post on numeric data types to give you an idea of the amount of space each type takes.

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

CREATE INDEX idx_area_area_zone ON LogEvent(area, area_zone);

Here is a great article on composite indexes that you may find useful:

http://www.mysqlperformanceblog.com/2009/09/19/multi-column-indexes-vs-index-merge/

Memory Attribution Question

To answer your RAM question, you could start with a relatively amount 2GB and a small InnoDB buffer pool size example (512Mb) since you are saying that SELECTs won't happen often. Once your server is in production, you can calculate how much of the buffer pool MySQL is actually using.

To help you determine that value, I am quoting Rolando from one of his previous posts:

"What you need to calculate is how much of the InnoDB Buffer Pool is loaded at any given moment on the current DB Server." Once your server is in production, you will be able to calculate what percent of your InnoDB buffer pool is actually in use. He provides formulas to help identify that percentage.

What to set innodb_buffer_pool and why..?

If you choose to use MyISAM for your LogEvent table, then you would give that memory to other variables such as key_buffer and join_buffer_size. The ratio of interest as far as memory usage is concerned, would be your Key Cache Hit Ratio

1 - (Key_reads/Key_read_requests)

Which should be as close as possible to 100%

InnoDB for MySQL 5.1 exhibits single-threaded behavior because the number of read and write I/O threads are fixed at 4 each. I was just reminded that versions of MySQL before 5.1.38 has only one thread for read and one for write. Therefore, scaling up hardware will not improve performance at all.

You need to upgrade to MySQL 5.5/5.6 because there are settings that will enable InnoDB to engage multiple CPUs and multiple cores (such as innodb_read_io_threads and innodb_write_io_threads).

• Sep 20, 2012 : Multi cores and MySQL Performance
• Apr 26, 2012 : Is the CPU performance relevant for a database server?
• May 26, 2011 : About single threaded versus multithreaded databases performance

CAVEAT : You must tune MySQL 5.5/5.6 properly because left unconfigured MySQL 5.1 is faster than 5.5/5.6 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

Give it a Try !!!