Who am I?

1. MySQL and Fedora:A Developer's OverviewBrian AkerDirector of ArchitectureFudconApril 2006 MySQL AB

2. Who am I? • Brian Aker • Director of Architecture, MySQL AB • Author of mod_layout, the apache streaming services mod_mp3, Slash (Slashdot’s CMS System), and lot of other things on Freshmeat.... • http://mysql.com/ • http://krow.net/

3. MySQL • Open Source Database Company • Swedish company started by Monty Widenius and David Axmark • Employs Developers World Wide • 200^H^H^H300 employees • 26+ countries

4. Agenda • What's in MySQL 5.0 • Fitting in with the Fedora Community • Differences in packaging • Contributing to the OSS movement • MySQL and India • Resources • What is coming in the Future? • Q&A?

5. What is in 5.0? • Views • Stored Procedures • Triggers • Precision Math • Instance Manager • XA • GUI Tools • Cluster • Information Schema • Cursors • Archive • More…..

6. CREATE VIEW Syntax CREATE [OR REPLACE] [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] CREATE OR REPLACE VIEW view1 ...

7. Stored Procedure Syntax • CREATE PROCEDURE procedure_name ([parameter [...]]) [characteristic ...] routine_body • CREATE FUNCTION function_name ([parameter [,...]]) RETURNS data_type [characteristic ...] routine_body • ALTER and DROP also fully supported

8. CREATE TRIGGER Syntax • CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name FOR EACH ROW trigger_action • CREATE TRIGGER tg1 AFTER INSERT ON t1 FOR EACH ROW <compound SQL statement>;

9. MySQL 5.0: Precision Math • Exact calculations with well defined rounding • At least 56 digits precision • Very fast with static memory allocation Sample: create table d2 (n decimal(64,3)); insert into d2 values (233221213212312312321321321321), (34543543324325435435435), (32432432432454374435234543456); Query OK, 3 rows affected (0.00 sec) select sum(n) from d2; +------------------------------------+ | sum(n) | +------------------------------------+ | 531307360376620022163982600424.000 | +------------------------------------+ 1 row in set (0.01 sec)

10. XA Support • MySQL supports distributed transaction processing: • Two-phase commit with XA protocol available in InnoDB as of 5.0 • Commit grouping to improve performance • XA JDBC driver • In MySQL: • the server acts as a transaction manager or resource manager • storage engines can be implemented as a resource manager Application TX Scope MySQL TX Scope MySQL Other XA RM OR XA Engine1 XA Engine 2  MySQL acts as RM  Queries on ACID TX tables included  Included in app TX  MySQL acts as TM  Storage engines act as RM  Different XA engine can be in same TX 5.0 InnoDB is the first XA-Compliant Storage Engine 5.1

11. MySQL Cluster • Objects in Clusters can now reach 20,000 without a recompile (before it was 1600) • Query Cache support • Multi Read Ranges • batched reads for IN clause conditions)

12. Information Schema • INFORMATION_SCHEMA is a new, virtual database. • INFORMATION_SCHEMA can be queried via a SELECT statement, so there is no need to learn a new set of commands to be able to access the metadata you need. • MySQL creates and populates the tables automatically. mysql> SELECT table_name, table_type, engine -> FROM INFORMATION_SCHEMA.tables -> WHERE table_schema = 'tp' -> ORDER BY table_type, table_name DESC; +------------+------------+--------+ | table_name | table_type | engine | +------------+------------+--------+ | t2 | BASE TABLE | MyISAM | | t1 | BASE TABLE | InnoDB | | v1 | VIEW | NULL | +------------+------------+--------+

13. Archive • New Storage Engine • Substantially reduces storage requirements >50% • Information is stored packed via gzip, unzipped on the fly • Insert and Select only • Great for historical data you need to keep online but not for blazing response times

14. Where we fit in with the Fedora Community • Just a package out of 1,500 • (okay, really 13/1795) • Want to forge better relationships • If you're a LAMP user, on Fedora, it should all “just work” • Ship more Extras packages that rely on MySQL in FC • These could be our tools (Query Browser, Adminstrator) • Could be other apps (mytop, Gforge, SequioaERP) • Working out “Alternatives”, like the NDB cluster, is the hard part • Fix bugs with the package maintainer

15. How Red Hat and MySQL package differently • RPMs are both provided at dev.mysql.com or within Fedora • For the longest time, Fedora only shipped MySQL 3.23 • There were licensing misconceptions with MySQL's FLOSS exception • A lot of applications built against 3.23 client libraries • #include <asm/atomic.h> • Depends on kernel headers to build • sparc/sparc64/ppc64 don't enable atomic_* functions as inlines • i386: kernel not compiled for SMP -> compiled code will not be SMP-safe (omits lock prefix before instruction)

16. How Red Hat and MySQL package differently II • No access to the extra storage engines: • NDB Cluster • ARCHIVE (large data store, no indexes, small footprint) • CSV • BLACKHOLE • FEDERATED (stores data in remote databases; MySQL specific) • Get the -max package to satisfy these requirements • mysql-shared-standard required for upgrades

17. Some Core/MySQL statistics • MySQL versions in various Cores: • Core 1&2: 3.23.58 (rebuilt in Core 3) • Core 4: 4.1.11 • Core 5: 5.0.18 • In FC-4, packages that depend on the 3.23 client libraries: • mysqlclient10-devel, MyODBC, libdbi-dbd-mysql • FC-5 ships the 3.23 client libraries, but nothing else depends on it (besides its -devel package) • mysqlclient14 (4.1.14) is provided

18. How you can contribute to the OSS movement • With Fedora, you have Extras • package maintenance, documentation, translation • ...or contribute to a package within Fedora, with upstream • MySQL has many entry points • Translation of documentation/applications • Get access to translation repository • In locale directory, find how_to_translate.txt • Sign Translation Agreement, granting MySQL AB all exclusive rights to translation • Requires: Love for your language/dialect, and proficient English

19. How you can contribute to the OSS movement II • Conversion of applications • Plenty of applications out there use another database • Make them run on MySQL • Requires: knowledge of SQL, working knowledge of application's language DB query methods • MySQL code-base • BitKeeper is still used for code • Sane build scripts exist • http://dev.mysql.com/doc/refman/5.1/en/installing-source-tree.html • Requires: knowledge of various bits used in MySQL • Work on the GUI tools • Work on the embedded version of MySQL

20. Resources • We have mailing lists • http://lists.mysql.com/ • mysql-list is very high traffic, but is a great place to learn/contribute • Internals list if you’re into the codebase • We have forums • http://forums.mysql.com/ • These come with RSS feeds! • http://dev.mysql.com/ is a great resource • The manual is excellent!

21. Business Intelligence/Data Warehousing • Table/Index Partitioning • Full Text Search Enhancements • Better XML Handling – XPath • Archive engine enhancements High Availability • Disk-based Cluster • Row-based Replication • Cluster replication Easier Manageability • Task Scheduler • Transaction support for Federated Engine Higher Performance • Faster alter table • Faster add/drop index for MySQL Cluster • Faster data import operations • Better problem user and SQL identification • New Performance/Load Testing Utility • New MyISAM memory option

22. Business Intelligence/Data Warehousing Table/Index Partitioning • Perfect for data warehouses or other VLDB situations • Increases performance – only needed partitions are accessed • Eases space/data management burden on DBA • Supports range, hash, key, list, and composite methods • Supported by all storage engines in MySQL • More options than Microsoft, DB2, or Sybase partitioning • Partition key must be integer (or convertible to integer) • Local indexes only – no global primary or unique keys • Parallelism not supported in this version

23. Business Intelligence/Data Warehousing XML Xpath Support • Address parts in XML document • Allows manipulation of • Strings • Booleans • Numbers • Models XML document as tree of nodes XML

24. Business Intelligence/Data Warehousing Archive Engine Enhancements • Faster I/O operations • Lower Memory requirements • Autoincrement column support • Unique key support • Non-unique key support

25. High Availability MySQL Cluster Disk-Based Data • Tables now can be designated to be disk-based • Tablespaces used to store table disk data • Tables can be either disk or main memory • Indexes still main memory only

26. High Availability Row-Based Replication • New replication option – statement-based replication retained • Handles all replication scenarios (deterministic, etc.) • Safest form of replication • Common to most other RDBMS’s • Statement-based approach still available • Mixed mode available that does statement and row-based replication

27. High Availability MySQL Cluster Replication • Replicate data from one MySQL Cluster to another • Limitation – no cross-server logging of add/drop/alter tables

28. Higher Performance Better Problem User/SQL Identification • SHOW PROCESSLIST command now system table • General and Slow Query Log now system tables • Can easily query tables to find all or just inefficient SQL • General/Slow Query Log use CSV engine and can be read by MS Excel SQL

29. Higher Performance New Performance/Load Testing Utility • Named “mysqlslap” • Simulates many concurrent connections to MySQL server • Repetitively runs designated SQL load • Can test multiple engines • Performs creation of test schema and population of data

30. MySQL 5.1 Rollout Schedule • Beta begins March 1, 2006 • Release Candidate expected in the Fall • GA Fall/End of 2006

31. Thanks! Colin Charles Community Engineer colin@{mysql.com,fedoraproject.org}