Data Conversion Errors - MySQL
mysql> SELECT * FROM foo;
+------+
| x |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
Slide 6
Slide 6 text
Data Conversion Errors - PostgreSQL
testdb=# CREATE TABLE foo (x integer);
CREATE TABLE
testdb=# INSERT INTO foo (x) VALUES ('bad-
number');
ERROR: invalid input syntax for integer: "bad-number"
LINE 1: INSERT INTO foo (x) VALUES ('bad-number');
^
Slide 7
Slide 7 text
Parallels - PHP vs. Python
$ php -r '$x="bad-number"; $y = (int)$x;
echo $y."\n";'
0
$ python -c 'print int("bad-number")'
Traceback (most recent call last):
File "", line 1, in
ValueError: invalid literal for int() with
base 10: 'bad-number'
Slide 8
Slide 8 text
File Layout
Slide 9
Slide 9 text
File Layout - MySQL MyISAM
/var/lib/mysql/dbname
● dbname.MYD - data of all tables
● dbname.MYI - indexes
Slide 10
Slide 10 text
File Layout - MySQL InnoDB
/var/lib/mysql/
● ibdata1 - data of all databases,
including tables and indexes
It is possible to tell mysql, by changing a config flag, to make it use one file for
table.
Slide 11
Slide 11 text
/var/lib/postgresql/9.3/main/base
● 131384/ - directory per database
○ 2654 - one (or more) files for each table/index
○ 2703
○ 2683
○ 2683.1
○ 2683.2
○ ...
File Layout - PostgreSQL
Slide 12
Slide 12 text
Database Maintenance
Slide 13
Slide 13 text
CREATE INDEX - MySQL MyASIM
While CREATE INDEX is in progress:
● Entire table is locked for writes
● May make the db unusable if there is
heavy write-traffic
● A new index file (dbname.MYI) need to
created
Slide 14
Slide 14 text
CREATE INDEX - InnoDB
While CREATE INDEX is in progress:
● Entire table is locked for writes
● May make the db unusable if there is
heavy write-traffic
Slide 15
Slide 15 text
CREATE INDEX - PostgreSQL
● The CREATE INDEX query locks the table
for writes.
● It is possible to CREATE INDEX
CONCURRENTLY
○ Doesn’t hold the lock for entire period
○ Other write operations can go on in parallel
○ Slower than plain CREATE INDEX
● Each index is a new file
Slide 16
Slide 16 text
Takes long time as it needs to rewrite:
● the index file (dbname.MYI) for MyISAM
● the ibdata1 file for InnoDB
DROP INDEX - MySQL
Slide 17
Slide 17 text
DROP INDEX - PostgreSQL
● Almost instantaneous
● Just need to delete the files corresponding to
that index
Slide 18
Slide 18 text
ADDING NEW COLUMN - MySQL
Entire table data needs to be rewritten.
Slide 19
Slide 19 text
ADDING NEW COLUMN - PostgreSQL
Almost instantaneous if the new column has a
default value.
Slide 20
Slide 20 text
Connection Model
Slide 21
Slide 21 text
Connection Model - MySQL
A thread for each connection
PROS
● TODO
CONS
● TODO
Slide 22
Slide 22 text
Connection Model - PostgreSQL
A process for each connection
PROS
● better concurrency
● complete isolation
CONS
● lot of overhead for creating new conn