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
I was thinking today of how people tend to use MySQL in modern applications and it stroke me in many cases MySQL is not used to process the data, at least not on the large scale - instead it is used for data storage and light duty data retrieval. Even in this case however the cache (ie memcache) is often implemented to offload MySQL.
When it comes to large scale data processing other techniques are used. Google uses some bigtable based technologies for search and Google Analytics (which is quite typical log processing application), when you look at recommendation task as well as a lot of other personalized services they may not use MySQL (and SQL at all). In many cases you would see absolutely custom solutions are implemented, in other cases you would see Hadoop or Sphinx used to crunch data in parallel or it even could be …
[Read more]
So I read an interesting blog recently that's linked from
Sheeri Kritzer's
Blog. It went to say that "Embedded SQL" is bad. Which is to
say that stored procedures are good and that you should never let
a developer mess around with the database. In Sheeri's blog there
is an ongoing argument about it as you read this post.
Well, I already wrote in my blog that I do support the stored
procedures approach. Maybe not fanatically as the original blog
post, but I do agree that developers are not really interested in the
database, just the results they are looking for.
So I had an idea. I was thinking how can help manage the way
developers interact with the database, to give both …