MySQL Proxy is being beefed up, to make it ready for prime time.
New features are being added, and old ones are improved and
tested.
Testing the Proxy is an adventure in its own right. It's a tool
that was designed to get in the middle of the scene and change
things. Testing its features is more challenging than testing a
straightforward application, even more complex like a database
server. So the test suite for MySQL Proxy is much more complex
and feature rich than the corresponding suite for the database
server.
While researching to create the next edition of the test suite,
able to cope with more features and corresponding testing
requests, I developed a Lua script that enhances the current
Proxy features and allows you to load several scripts, and use
them all at once.
The script (load-multi.lua) is available in the latest …
Given the very low number of issues reported to us, we can assume that the core features of mysqlnd have matured. Therefore, we spend some time discussing parallel, background and asynchronous fetch ideas for mysqlnd. All have been on our brainstorming list from the beginning but it was just too early to even discuss them before these days. None of the ideas is new in any way as a recent discussion on the PHP Internals list on parallel database queries has shown. However, people seem to expect different things from it and are not sure how to implement it. We trapped into this ourself when Andrey wrote a background fetch proof-of-concept patch recently. Back one step: what is this about, what do you want, please comment!
The status quo
PHP scripts are not threaded. A PHP scripts cannot follow …
[Read more]
Imagine having a database with 100 tables and you want to allow a
user to read from all of it, except one.
But of the table of the knowledge of good and evil, thou shalt
not select of it
That's an old problem, with an ugly solution. You must either
move the forbidden table to another database or to explicitly
authorize the user to read each one of the other 99 tables.
A friend of mine had a similar problem. She has a huge database
with thousand of tables, and she wants to prevent the users from
issuing a "SHOW TABLES" command. Why? Because, with thousands of
tables, the response time of MySQL can easily become very slow.
It is a design problem, not easily solvable, and the best course
of action here is to deny access to this command.
The bad news is that MySQL has no provision for this kind of
restrictions.
The good news is that you can solve this problem with an easy
MySQL Proxy script.
…
Maatkit (formerly MySQL Toolkit) version 1297 contains a significant update to MySQL Table Checksum (which will be renamed soon to avoid trademark violations). The changelog follows. What you don't see in the changelog is the unit test suite! I got a lot more of the code into modules that are tested and re-usable.
2007-11-18: version 1.1.19 * Check for needed privileges on --replicate table before beginning. * Made some error messages more informative. * Fixed child process exit status with 8-bit right-shift. * Improved checksumming code auto-detects best algorithm and function. * Added --ignoreengine option; ignores federated and merge by default. * Added --columns and --checksum options. * Removed --chunkcol, --chunksize-exact, --index options. * --chunksize can be specified as a data size now. * Improved chunking algorithm handles more cases and uses fewer chunks. * Do not print --replcheck results for servers that are not …[Read more]
Maatkit (formerly MySQL Toolkit) has for some time been released both as a bundle, and as individual tools. It's too much work to maintain the individual packages, and I don't think it really benefits anyone much, if at all. While the tools will still be versioned separately, I'm going to discontinue releases of the individual packages, and just release the one uber-package from now on.
This will also make it easier for me to manage the name change, but that's just an extra incentive; I've been considering this for a while.
By the way, Sourceforge indicated it would take up to a couple of days to finish the project's rename, but it took only a few minutes. Lots of broken links; I've asked for a permanent redirect from the old URLs to the new.
I am so lucky I married an archaeologist.
Choosing a new name for MySQL Toolkit has been a hassle. I wanted to avoid a literal name, such as, um, MySQL Toolkit. Short is good. And so on, and so on. All the while, the Phoenix/Firebird/Firefox naming debacle was in my thoughts. I only want to do this once.
Read on for a fun lesson in Egyptian mythology, courtesy of my wife!
Maatkit (formerly MySQL Toolkit) version 1297 contains a significant update to MySQL Table Checksum (which will be renamed soon to avoid trademark violations). The changelog follows. What you don’t see in the changelog is the unit test suite! I got a lot more of the code into modules that are tested and re-usable. 2007-11-18: version 1.1.19 * Check for needed privileges on --replicate table before beginning. * Made some error messages more informative.
I'm visiting customer in Zurich next week and also was invited to
give a talk on MySQL Performance Wednesday November 21.
If you're in the area please feel free to drop by I'll start by talking
about Query Optimization and we'll keep it interactive to see
what else audience would like to know.
Entry posted by peter | One comment
[Read more]I just implemented a script-cache for MySQL Proxy and had to learn a bit more about lua internals. As a result I wrote two functions which are generic enough for general consumption:
-
luaL_loadfile_factory() -
luaL_loadstring_factory()
They also show how to write a lua_Reader for
lua_load().
The #lua channel on freenode was a
great help, as always.
LUA provide several loaders:
- luaL_loadstring
- luaL_loadbuffer
- luaL_loadfile
and you can create your own if you have to.
Now, why the need for a new loader ?
The proxy we have
- a global scope caches the scripts
- this cache reloads the scripts if they changed on disk
- each thread executes the same script
- each thread has its own …
Premature Optimization ... we all know it. The proxy is now in a
state were we can start to optimize the code a bit. After getting
some complaints about the performance with keepalive
I took a deeper look into the problem and came up with:
- a global script-cache with reload on cache
- less GC-runs
- less overhead per lua-script-call
It is all in SVN now, enjoy it.
To test if we make progress I use mysqlslap in its
most basic form:
$ mysqlslap --host=127.0.0.1 --port=4040 \
--create-schema=mysqlslap --iterations=1000 --concurrency=10
which only connects and closes the connection again.
The run-time over 10.000 connects is
proxy, no cache, lua_gc() 11.553s proxy, cache, lua_gc() 7.065s proxy, cache, no lua_gc() 6.458s