Normalization of big tables by user

Consider web application, processing User requests.

Generally Users is a table in such an application.

Other tables, that contain data releavent to the User (like personal user's posts, mail, financial transactions, e.t.c. ) , generally contain ID of such a user.

When we partition such tables by User ID, we can achieve performance by following parameters:

- Fast drop (or move to archive) user, and users data.
- If there are only several users at the moment on site, we can fill memory cache by only "logged in" users data, and process than this data also only in cache.
- More space, Faster user queries by removing indexes, and processing only data releavant to the user at the moment.

partitioning in Mysql limitation prevents some optimizations

Mysql doesn't support following one big table optimization tip, discovered on Sybase trainings:

Place in memory "day" partition of table.
Store "Old" partitions on disk.

All partitions in Mysql 5.1.31 should be under one storage engine.

Searching for way to solve it by managing buffer sizes.

DB Performance testing framework.

Example of architecture of testing framework for database:

Compilation-based:

1) get definition of database:
2) get (f)lex rules on database statements.
3) define testing profile (confioguration)
4) Write interpreter and tests builder (flex + yacc) based.

+-----------------------+      +---------------+
| DB statements syntax  |      | test profile  |
+-----------------------+      +---------------+
    ||                               ||
    ||                               ||
    \/                               \/
+---------+                    +-----------+
| flex    |                    |  yacc     |
+---------+                    +-----------+
      \\                             // 
       \\                           //
        \\                         //
         \\                       // 
          \|                     |/
+----+   +-------------------------+    +---------------+
| DB |==>|  test framework         |===>| test results  | 
+----+   +-------------------------+    +---------------+
                                              //
                                             //
                                            //
                                           |/
                                        +--------+
                                        | Graphs |
                                        +--------+    

Interpretable:

+-----------------------+      +------------------------+
| DB statements syntax  |      | test processing rules  |
+-----------------------+      +------------------------+
    ||                               ||
    ||        +---------+            ||
    \/        | profile |            \/
+---------+   +---------+      +-----------+
| flex    |       ||           |  yacc     |
+---------+       \/           +-----------+
      \\      +---------+            // 
       \\     | dotconf |           //
        \\    +---------+          //
         \\       ||              // 
          \|      \/             |/
+----+   +-------------------------+    +---------------+
| DB |==>|  test framework         |===>| test results  | 
+----+   +-------------------------+    +---------------+
                                              //
                                             //
                                            //
                                           |/
                                        +--------+
                                        | Graphs |
                                        +--------+    

MySql basics CentOs 5

# mysql --version
mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0

# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.soc
k' (2)

to resolve :

# sudo mysql_install_db --user=mysql


Set password:


/usr/bin/mysqladmin -u root password 'new-password'


start engine:


# sudo /usr/bin/mysqld_safe &

Starting mysqld daemon with databases from /var/lib/mysql

# ps -efa| grep mysql
root 20687 4414 0 23:19 pts/2 00:00:00 /bin/sh /usr/bin/mysqld_safe
mysql 20730 20687 0 23:19 pts/2 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock
rtg 20749 4414 0 23:19 pts/2 00:00:00 grep mysql


change password:


# sudo /usr/bin/mysqladmin -u root password 'NEWpassword'


login and issue some commands:


# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)

mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help ', where is one of the following
categories:
Account Management
Administration
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Language Structure
Storage Engines
Stored Routines
Table Maintenance
Transactions
Triggers


Create user:


mysql> CREATE USER rtg identified by 'password'
-> ;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[rtg@rtgCent usr]$ mysql -u rtg -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Databases; central table

Found that basically specialized database could be presented as the central table, with other tables as just supporting infrastructure for central one.

To found central table in you application just grep | wc source code in loop by all tables names presented in your database.

It is easier than counting statistics on links in local hypertext.

Just another database definition

In fact, this is all you need to know to be a Caveman Database Programmer: A relational database is a big spreadsheet that several people can update simultaneously.

Ugly ER notation

Started day with reading Garcia, Ulman's and Widom book:
"Database Systems: The Complete Book" (2nd ed, Russian translation)

First of all looked through all, and found it very interesting in the details that met my eyes.

Also it has a very good descriptions of terminology generally used.

However the second chapter, passed is ugliest one. I haven't seen application that use notation proposed for years.

Week entities, round and common arrows, rhombuses, in what database applications they are used?
And a lot of "philosophy" there, that is description of a lot of correct and incorrect ways to present data in the model. And the 3rd chapter begins with statement that relational model is different from previous presented.

Ok, it seems that 2nd chapter could be passed without given a lot of attention to.

Resource related to the book:

http://infolab.stanford.edu/~ullman/fcdb/aut07/index.html#lecture

going to play a little with examples from there.