Linux Elevator Algorithm

There has been much debate over elevator algorithms concerning the 2.6 kernel.  For a database system, deadline and CFQ seem to be the most debated.  For a small database with relatively few drives in a RAID array, and relatively small memory, deadline works best.  One of my systems has a 48 x 15k drive SAS RAID array.  When you combine over 2GB/s of throughput, 128GB of memory, command queuing, etc - latency isn't the issue.  Leveraging caches to get a sequential R/W load is dramatically more important.  CFQ does this via implementation of R/B trees ordered by sector.  If you're periodically reshuffling the deck based on time expiration (deadline), you end up seeking all over the disk. 

Converting high performance servers to CFQ over deadline resulted in a 10-30% performance bump depending on load.

OLTP servers might have different experiences, but CFQ in an OLAP environment appears to be the way to go.  (for us! haha)

MySQL Collation and Character sets

Have a table where lower/upper case ASCII characters mean different things. See example:

mysql> create table collation_test(value char(1));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into collation_test(value) values ('T');
Query OK, 1 row affected (0.00 sec)

[A bunch more stuff]

mysql> select * from collation_test;
+-------+
| value |
+-------+
| T |
| T |
| T |
| t |
| t |
| F |
| F |
| f |
| f |
+-------+
9 rows in set (0.00 sec)

mysql> select value,count(*) from collation_test group by value;
+-------+----------+
| value | count(*) |
+-------+----------+
| F | 4 |
| T | 5 |
+-------+----------+
2 rows in set (0.00 sec)

mysql> alter table collation_test CONVERT TO CHARACTER SET latin1
mysql> COLLATE latin1_general_cs;
Query OK, 9 rows affected (0.00 sec)
Records: 9 Duplicates: 0 Warnings: 0

mysql> select value,count(*) from collation_test group by value;
+-------+----------+
| value | count(*) |
+-------+----------+
| F | 2 |
| f | 2 |
| T | 3 |
| t | 2 |
+-------+----------+
4 rows in set (0.00 sec)

MySQL Default values (string / int)

Had a great one today:

Create table mine (id integer);

insert into mine(id) values (0),(1),(2),(3),(0),(1);

select * from mine;
[Nets 0,1,2,3,0,1]


select * from mine where id = "Test";
[0,0]
select * from mine where id != "Test";
[1,2,3,1]

String / int columns

Found a table of ours:
hashid bigint
value_a varchar(16)
value_b varchar(16)

In MyISAM, yes, this is a dynamically sized table. Turns out, data was only integers. Altered the table to be

hashid bigint
value_a integer
value_b integer

Goes to fixed size rows which is great. Also dropped size from 411M to 350M.

Remember the golden rule:
Optimize your dataset before your hardware...

Attention job candidates

Simple question - I encourage you to add a comment with your own solution in any language:

I have a file with one word per line, N number of lines. Count the number of times your name exists in the file. Use any language you'd like, just give me the number.

cat filename grep name wc -l

fopen, fread, strstr, fclose

load file, select count(*)

As a software engineer/progammer it's not hard, why have the last 8 candidates failed to answer this?!

Linux OOM-killer

Please make sure your OOM-killer settings are configured appropriately.

For us, MySQL is configured for ~40G of memory. A developer had a runaway PERL script that ate ~90G of memory before wiping out our production server. OOM-killer should have tagged that as bad and killed it. Unfortunately, it didn't - the server ran out of memory and cratered. Surprised it didn't sprout legs and go skynet on us.

Didn't help that upon reboot the RAID controller found a bad drive causing check/recover tables ot be run while in degrade mode.

Find the bug (Embedded C)

Found this gem a while ago.

void foo (int parameter)
{
int i = 0;
char buffer[64] = {};
int temp = parameter;

for (i=0;i<64;i++);
{
buffer[i] = 0;
}
}