Posts Tagged ‘FrOSCon’

SecuRich

I got to meet, and share a meal with a most interesting Darren Cassar at FRoSCon/OpenSQLCamp, who’s the mastermind behind SecuRich. Some sparse notes, while we await his slides. I think there’s some great potential here, and SecuRich is exciting and should be given some more love.

Designed to work with Sybase and MySQL in mind (because he’s hacking on migrating Sybase to MySQL).

How often do we audit user privileges and access levels? How often do we forget temporary elevated privileges?

What you have in MySQL today: Authentication against ‘username’@’hostname’, and the password is hashed by PASSWORD() function. There is wide range of privileges, and the granting of privileges is controlled.

What are limitations in MySQL today: Password limits are not available (password size limit, password history, password complexity meter, password minimum age), its quite complex to manage, there are no roles, it is easily unsecured (if you provide an access to the MySQL database, you can try brute force attacks, etc.). Once you drop the database, the grants are still there – obsolete grants are not removed.

SECURICH has password limits, reduces complexity to manage, has roles, is a lot more secured, and soon, there will be removal of obsolete grants.

Compatible with MySQL 5.0 and later, as it uses INFORMATION_SCHEMA extensively. It requires I_S.processlist, which is only available in MySQL 5.1 though.

I don’t see why this wouldn’t work on Windows, besides some scripts written in BASH. My only experience with this is however on Linux and OSX.

Sharding for the masses: Introducing the SPIDER storage engine (OpenSQLCamp @ FrOSCon)

This is the Sharding for the masses: Introducing the SPIDER storage engine by Giuseppe Maxia, given at OpenSQLCamp, at FrOSCon, in August 2009. These are somewhat live notes, and the slides are available too.

Why sharding? Scaling, of course. The MySQL way to solve this, is replication (even Yahoo! and Google use this).

When the master doesn’t have enough resources to cope with what you do (i.e. large data sets), replication chokes.

You can use proxies for sharding. There exists MySQL Proxy (can be programmed using a scripting language – Lua), HSCALE (built on top of MySQL Proxy), SpockProxy (a fork of MySQL Proxy, without LUA scripting, specialised for sharding), in the market these days. This however, is the single point of failure – everything has to pass through one proxy.

Enter SPIDER – a MySQL storage engine, built on top of the partitions engine. It associates a partition with a remote server, and is transparent to the user. Its developed by Kentoku Shiba.

Installation: Get 5.1.37 sources, then get the source code for Spider 1.0, and then get the patch for condition pushdown.

Why the condition pushdown patch? Remote server works less, by receiving the condition. The SPIDER engine without the condition pushdown patch is still fast, but it can be more than 10x faster with condition pushdowns.

http://dev.mysql.com/doc/refman/5.1/en/condition-pushdown-optimization.html (works with NDBCLUSTER), http://dev.mysql.com/doc/refman/5.4/en/condition-pushdown-optimization.html (works with MyISAM). The patch by Kentoku, will add cond_push and cond_pop, to ha_partition – so now, every storage engine that uses table partitioning can get condition pushdown through ha_partition.

You need to setup the engine first: http://datacharmer.org/downloads/spider_setup.sql (the SQL is also available in the DOCS).

spider_remote_employees.sql – use this in conjunction with http://launchpad.net/test-db/ – a good example of how to use the SPIDER storage engine.


i