Talk about:
MySQL Server limitations: skeletons, “official secrets”, embarrassing things in the server
Why this Talk
- MySQL and Sun needs to become more transparent in what they are doing
- when users know the limitations they can go around them
- It’s easier to trust someone when they acknowledge a problem (hear hear!!)
Threads
- Problems one connection per thread doesn’t work in all cases
- no way to give priority to thread
- no way to ensure that we have X active threads running
Symptoms
- too many context switches
- no multi-core cpu scalability (efficiently)
- Solutions (one of many)
- –thread-handling=–pool-off-threads (MySQL 6.0)
Work to be done
- all Innodb concurrency patches
- spawn more threads when threads are blocked
- removing overal mutex contention in the server
- give higher/lower priority to some threads
- allow ‘super’ user to login when all threads are in use
Memory as a resource
Single biggest problem is no single memory allocator (server/engine)
Privileges
Problems
not modular/pluggable (extendable to things such LDAP)
no ROLES
Symptoms
- hard to maintain lots of users
- hard/impossible to use external authentication
Solution
get the community to implement an authenctication module (four summer of code projects for LDAP)
Pluggable storage engines
Problems
storage engines are depending on internal MySQL structures
Symptoms
storage engines con only be used with the exact MySQL server version they were compiled against
Parser
Problems
- state machine to loarge
- not pluggable
- not cacheable
- still Bison
- Bad error messages
Symptoms
- Parsing has a high overhead for simple queries (12% time spent in parser)
- Parser takes a lot of code space
Modularity
Problems
- Server is very monolithic
- Few defined interfaces (not often stable)
- Server and libraries not documented
- Multiple Execution paths
- no rewrite state for optimizer
Symptoms
- hard to change code without introducing bugs
- hard for newcomers to understand the server
Stored Procedures and Triggers
Problems
- stored procedure are not cacheable across connections
- we only support SQL (but this is changing )
- Pre-locking of all tables (deadlock-free algorithm)
- all cursors are materialized
- trigger code is not shared across open tables
- no constaint of resources
- we don’t support stored procedures as table
Replication
Problems
- replication is not fail safe
- no synchronous options
- no checking consistency option
- setup and resync of slave is complicated
- single thread on the slave
- no multi-master
- only InnodDB synchronizes with the replication (binary) log
Symptoms
- slaves can catch up with master
- hard to do clean fail overs
- we are dependent on Innodb
Solutions
- use backup to setup slave
- replicate CHECKSUM TABLE and do consistency checking on slave (such as maatkit)
Table names
Problems
- tables are stored as files (nam.frm)
- file system may be case sensitive
- falcon has it’s own interpretation of how things should be done
Symptoms
- SELECT * from TableName and SELECT * from ‘TABLEname’ may or may not refer to different tables depending on file system
- hard to move apps between OS’s
- doing alter table of all tables to Falcon may delete data from tables on Unix fro table names that only differ in case
Solutions
- use –lower-case-table-names when running Windows and Mac
- add modes to be backword compatabile, ANSI compatible and PostgreSQL compatible
Why Falcon and Maria
Problem
MySQL/Sun doesn’t have it’s own transactional storage engine
Solution
Falcon/Maria
Interesting. This is the first time I have heard MySQL officially say this although I suspected this for some time.
Open Source Project?
This part of the discussion was a very frank assessment of how things are. Monty would like to make things transparent. He would like to change the development model to attract outside developers. One major change is giving outside developers commit and decision rights to the MySQL server code base.
Release Policy
Problems
- MySQL ships releases before they are ready
- Benchmarks are given out which show “partial truths”
Symptoms
- MySQL 5.1 was declared RC way too early
- features are removed in RC releases
- major code changes are done each month in RC code
- Users are not happy with the releases until six months after GA (see 5.0)
- Critical bugs are still open in 5.1 and not scheduled to be fixed before GA:
- – bug 989
- –bug 30414
Solution
- wait to declare something GA until code stabilizes and critical bugs are fixed
- create a release policy and an independent release policy board
The good news
Monty says Sun is much more open source friendly than MySQL AB has been lately and is driving MySQL in the right direction.
Overall, this was a very fair assessment of the current state of MySQL. It is good to know that people are thinking about these things. While I have always thought they worried about this “stuff”..sometimes it feels like MySQL doesn’t because of a lack of information coming from them. Can I just say that Monty is really cool? He has no need to continue working and yet he obviously enjoys immensely what he does. And his honesty is very refreshing.
