Home
MySQL Users Conference

State of MySQL AB

This is a talk about how well things will be doing soon.

His point is that profit is important to be a business and to fund the professional dedicated work that makes a project a player, and that freedom is important to make sure your work is collaborative and successful.

Good point.

HP Talks about Linux

This talk is all about what HP does to support open source. Basically a profile of a huge business that has been well-served by embracing this goofy movement.

Very businessy, not too interesting on my end.

Views in MySQL 5.0

I hope to learn about good ways to use views here.

Views are basically tables, but they are "derived tables." They don't physically exist. Each view has some underlying tables, whichever they are based on. You can lay views on top of other views.

Views do not update when tables update, only when the data inside the tables do.

Heads up, inserting into views changes the underlying table! You can use "CHECK OPTION" while creating the view to restrict this, either locally or cascading.

You can alter them, but since they're virtual this is just convienient. You can also replace them with CREATE OR REPLACE VIEW.

SHOW is used to get info about views. Use same syntax as for base tables.

You CANNOT change the data in a base table by changing the environment. you CAN change the data ina view by changing the environment! Environment settings often change the returns of queries, and views are built from query returns

you can use mysql_fix_privilege_tables script to grant sane view defaults when upgrading a mysql.users table.

Obviously you need privileges on underlying tables to create views. You can specify separate privs for viewing views and viewing the underlying tables too.

Okay, now on to using views!

Use them to copy or duplicate tables.

You can also fake data constraints with views, by putting a fancy where clause on the view and updating through it. use CHECK OPTION

Use it instead of a subquery. What?

Hide things, either rows or columns. You can distill sensitive data into a view and provide access to that instead of to the base table.

MySQL Security

Dont' get stuff stolen, tampered with, etc.

You must pay attention to all the layers, network DB OS Hardware and physical

Oh man, this should be good: Tip #1 - don't run MySQL as root! wow!

You can use SSL with mysql 4.1, making transfer safer. Uses openssl. You can use this between replicating servers and between client/server. This is enabled by adding a REQUIRE SSL directive to the end of your GRANT statements. That REQUIRE statement can get much more complex, required x509's particular issuers, etc.

You can skip-networking when possible too, duh.

4.1 has a new password protocol. If you need to connect with 4.0 clients, use --old-passwords on server start

Pay lots of attention to the mysql.users table and don't have more than you need in there. First match in the table determines privileges!

Use descriptive usernames, even the name of scripts or functions - you can SHOW PROCESSLIST and see exactly why or who the connects are from. You can then duplicate script-level security at the db level with privileges for each account.

GRANT has very granular abilities, make use of them. You can even put resource limits into grant statements in recent mysqls!

You can use views to hide rows or columns from tables. This allows users to do updates on only matching rows in the view!

Tour of the Source Code

Monty and Brian

There was nothing interesting this hour, so I went with this for recreational purposes. Hopefully there will be some code-wrangling and source-control tips too.

This was fun, but I'm ducking out to see the Deadly Sins session

Deadly Sins in MySQL

Arjen Lentz

Well. The first tip is to always quote strings. mmmkay.

don't use select * order by rand() limit 1; that's a full table scan.

Never use select *

if you use calculations in your where clause, mysql needs to calculate that on the entire table.

Join the table against itself when you need to do multiple group-bys.

Using strings/large values as a primary key in innodb is very space-wasteful

Run slaves in read-only mode.

Joins on non-equal data types require casting and can invalidate indexes.

Best Practices DBD::mysql and perl

GAR this is too basic.

The new DBD::mysql will support the embedded server. Kinda neat, but probably not for us.

you can choose whether the client or the server holds the results of a select with mysql_use_result and mysql_store_result. Perhaps we can leverage this for some large-sql performance gains? experiment.

Wrap some sig handlers around your dbi activity so you choose what happens.

You can debug with trace level in the connect string or with the $dbh->trace() method.

There are some cacheing options in DBI, allow prepared statements to be cached as well as connections.

For mass inserts, use a prepared statement and loop over the executes

InnoDB Talks

End of the new features talk

use innodb_file_per_table to create one file per table. Multiple tablespaces allows you to spread out innodb data, restore individual tables, and free space effectively with drop table or optimize table. You can also move tables as files around from one server to another, but only if the table files are "clean." SHow innodb status to check. There are no apparent disadvantages, so go with multiple tablespaces.

Use show innodb status to see what's happening

5.0.3 introduces a more compressed version of innodb. This is a default. 5.1 also offers a zip-compressed style thing, as an option. Never try to compress already-compressed data, duh, and be aware that this increases the damage, but not likelyhood of data corruption.

Semi-synchronous replication - Hmm. looks like very new InnoDB tables will not consider a transaction commited until replication has occurred. This is "semi" because it doesn't wait for the transaction to succeed on the slave, just for the binlog to get shipped off to the slave.

Advanced Tuning

Use short primary keys. You should insert your data in primary key order.

Don't change your primary keys.

Index tricks: UNIQUE key indexes are more expensive in innodb. Use prefix indexes for long values.

Optimize table is important.

Too many active threads is a serious efficiency hit.

Beware "locking reads" - they cause trouble and inefficency.

if you must lock data, do it in the same order in all transactions, and lock everything you need as early as possible in the transaction.

Chop large activities up into many transactions.

innodb_buffer_pool_size - very important, set it as high as possible.

innodb_file_per_table - enables the multiple tablespaces discussed above.

check innodb_flush_method (sp?) this can be set to an advantageous setting for linux to avoid double-buffering (mysql and OS).

Increase innodb_log_file_size to roll log files less often and force less page flushes.