MTU Problems When Using Vista Internet Connection Sharing (ICS) with an XBox 360

So like other people, I don't really like the idea of paying $100 for an XBox 360 wifi adapter, so I'm using Internet Connection Sharing through my laptop to get my XBox connected to a wireless access point (yes, there are easier ways of doing this, but like usual, I'm doing something strange).

But when I did this, the XBox kept complaining that my MTU was set too low since it needed an MTU of 1364. This is a little silly since a proper IP stack should automatically fragment its packets to accomodate the MTU, but whatever.

I ran the command netsh interface ipv4 show interfaces to find out what the MTUs on my Vista laptop were, and they were all set to 1300. This seemed a little strange because 1300 is oddly low and also a nice round number that a human must have decided on. But I certainly hadn't set my MTU to 1300, so I'm not sure why it was set to that.

I spent a while browsing around, but finally I figured it out. It was that annoying Cisco VPN client on my laptop. I have no idea why they set the MTU so low (what sort of intermediate router would use 200 bytes of overhead per packet? It's silly to reserve so much), but the VPN Client comes with a Set MTU program that I was able to use to set everything back to normal, and then my XBox stopped complaining.

Running TPC-H Queries on MySQL

Getting the TPC-H queries to work on MySQL isn't too hard, but it isn't always clear what to do. I took the instructions from here and converted them to work with MySQL.

Once TPC-H is started, you can create a database and tpch account:

mysql -u root -p
mysql> CREATE USER 'tpch'@'%' IDENTIFIED BY 'password';
mysql> CREATE DATABASE tpch;
mysql> GRANT ALL ON tpch.* to 'tpch'@'%';
mysql> USE tpch;
mysql> \. tpch/gen/dss.ddl

Then, in the gen directories, you can modify the query generator to generate queries that are as close as possible to what you need:

cp makefile.suite makefile
#Modify makefile to use
# CC = gcc, DATABASE=SQLSERVER, MACHINE=LINUX, WORKLOAD=TPCH
#In tpcd.h, SQLSERVER section:
# change #define SET_DBASE "use %s;\n"
# change #define SET_ROWCOUNT "limit %d;\n\n"
# change #define START_TRAN "BEGIN WORK;"
# change #define END_TRAN "COMMIT WORK;"
make

Then you can start generating database data at the right scale factor

./dbgen -s 1

And also modify the constraints/indices file so that it's compatible with mysql:

# Modify dss.ri
# use a search and replace in order to remove "CONNECT TO TPCD", remove references to "TPCD." and remove the lines "COMMIT WORK;"

Then you can load all the data into the databases with

mysql -u tpch -p
mysql> use tpch;
mysql> LOAD DATA LOCAL INFILE 'customer.tbl' INTO TABLE CUSTOMER FIELDS TERMINATED BY '|';
# Same with orders, lineitem, nation, partsupp, part, region, supplier
mysql> \. dss.ri

You then need to change the case of the table names because the queries use lower-case table names I think whereas the dss.ddl uses upper-case names for the tables:

mysql> alter table NATION rename nation;
# Ditto for supplier, region, partsupp, part, orders, lineitem, customer

Finally, you can test some of the queries

cp dists.dss queries
cd queries
../qgen -c tpch -s 1 1

I think the queries still need to be modified a bit to be compatible. I think queries with a limit may need the semi-colon moved around, the precision indicator during date arithmetic in query 1 may need to be removed, and the method for naming columns in query 13 might need changing.