SQL Antipatterns, by Bill Karwin
I remember that when I finished reading The Lord Of The Rings, I
felt a pang of disappointment. "What? Already finished? What am I
going to read now? What can give me the same pleasure and sense
of accomplishment that these wonderful pages have given
me?"
That's how I felt when I came to the last page of SQL Antipatterns. And, no, Bill Karwin doesn't
tell imaginary tales from a fictitious world. This book is full
of very real and very practical advice, but all the material is
presented with such grace and verve that I could not put it down
until the very end. I read it cover to cover in just a few hours,
and I savored every page.
What is this Antipatterns, anyway? The title may deceive a casual
bookshop browser into believing that it's about some
philosophical database theory. Digging further, you realize …
It doesn’t exist until it is nicely documented. Now Aspersa has documentation. Writing these tools has taught me how powerful and flexible Bash can be. Solving MySQL problems is a lot easier with good tools!
Related posts:
- Aspersa, a new opensource toolkit
- Using Aspersa to capture diagnostic data
- MySQL manual gets improved searching
- …
Sqlexamples.org is a community project that is focused on collecting real world solutions for specific problems. Additionally, we want to collect database know-how that is related to SQL or NoSQL databases of all kinds. Content is indexed and freely available to everybody. We would like to invite every single database developer and administrator out there to take part! It does not matter which database you prefer, Oracle, MS SQL, MySQL, PostgreSQL, SQLite, CouchDB or MongoDB...
Here’s a little trivia that you might find helpful. Suppose that I have the following in the MySQL slow query log (abbreviated for clarity):
# User@Host: root[root] @ localhost [127.0.0.1]
# Time: 100919 17:58:52
# Query_time: 9.648427 Lock_time: 8.648039
select sleep(1) from t limit 1;
To get this into the slow query log, I set the long_query_time to
0 and opened two sessions. In one session I ran LOCK TABLES
t WRITE
, and in the other I tried to select from that
table. As you can see above, 1) LOCK TABLES contributes
to the Lock_time number, and 2) the Query_time
is the sum of execution time and lock time.
Now, I’ll set long_query_time = 2 and run the same test. What happens? Nothing shows up in the slow query log, because 3) the time spent waiting for table locks doesn’t count towards the slow query time threshold.
A final note: …
[Read more]Relational Database Index Design and the Optimizers
Relational Database Index Design and the Optimizers. By Tapio Lahdenmaki and Mike Leach, Wiley 2005. (Here’s a link to the publisher’s site).
I picked this book up on the advice of an Oracle expert, and after one of my colleagues had read it and mentioned it to me. The focus is on how to design indexes that will produce the best performance for various types of queries. It goes into quite a bit of detail on how databases execute specific types of queries, including sort-merge joins and multiple index access, and develops a generic cost model that can be used to produce a quick upper-bound estimate (QUBE) for the …
[Read more]We're proud to introduce the sqlexamples.org community, a resource for database developers and administrators. Our aim is it to improve the availability of free (as in free speech) SQL and NoSQL related database examples of all kinds. We're not just focused on MySQL. Related is for example:
- syntax examples
- database schemata
- database related source code
- <your idea here>
A lot of valuable database related content gets published day by
day in countless blogs all over the web. Our aim is it to archive
and index this knowledge in a central database, open and
accessible for everyone. When you want to help us building such
an useful archive, all you have to do is to submit your RSS feed
to sqlexamples.org . Additionally, content can be published
directly on our platform if you like.
…
[Read more]- jsTerm -- ANSI-capable telnet terminal built in HTML5 with Javascript, Websocket, and Node.js. (via waxpancake on Twitter)
- MySQL EXPLAINer -- visualize the output of the MySQL EXPLAIN command. (via eonarts on Twitter)
- Google Code University -- updated with new classes, including C++ and Android app development.
- Cloudtop Applications (Anil Dash) -- Anil calling "trend" on multiplatform native apps with cloud storage. Another layer in the Web 2.0 story Tim's …
The newest release of Percona Server includes a trivial change that I think will be extremely valuable. This is the addition of a new thread state, “Waiting on query cache mutex.” Fixing the query cache to make it scalable is hard. Fixing the server to report when the query cache is a bottleneck is not hard. It has historically been very difficult for users to diagnose what’s wrong with their server when the query cache is locking it intermittently. Now it will be trivial: they will look at SHOW PROCESSLIST and the evidence will be unmistakable.
Related posts:
[Read more]I collect a lot of configuration values from my database servers and most of these values are stored by date.
So often I end up with values such as:
I´ve been unsuccessfully
been trying to figure out how to convert this in SQL into
something like the following:
config_date config_value
2010-09-01 value_1
2010-09-02 value_1
2010-09-03 value_2
2010-09-04 value_3
2010-09-05 value_3
2010-09-06 value_3
2010-09-07 value_3
2010-09-08 value_4
2010-09-09 value_4
2010-09-10 value_1
2010-09-11 value_5
2010-09-12 value_5
2010-09-13 value_5
config_from config_to config_value
2010-09-01 2010-09-02 value_1
2010-09-03 2010-09-03 value_2
2010-09-04 2010-09-07 …
CouchDB is a document-oriented database written in Erlang that addresses a particular “sweet spot” in data storage and retrieval needs. This blog post is an introduction to CouchDB for those of us who have a relational database background.
A CouchDB database doesn’t have tables. It has a collection of documents, stored in a B+Tree. A document is a collection of attributes and values. Values can be atomic, or complex nested structures such as arrays and sub-documents. When you add a document to a database, CouchDB stores it in the B+Tree, indexed by two attributes with special meaning: _id and _rev.
CouchDB lets you store related data together even if it isn’t all the same type of data; you can store documents representing blog posts, users, and comments — all in the same database. This is not as chaotic as it sounds. To get your data back out of CouchDB in sensible ways, you define views over the database. A view stores a …
[Read more]