Blog Profile / Open Query Blog
| URL : | http://openquery.com/blog/ | |
|---|---|---|
| Filed Under: | Programming / Database Development | |
| Posts on Regator: | 258 | |
| Posts / Week: | 1 | |
| Archived Since: | June 30, 2008 | |
Blog Post Archive
LEVENSHTEIN MySQL stored function
At Open Query we steer clear of code development for clients. We sometimes advise on code, but as a company we don’t want to be in the programmer role. Naturally we do write scripts and other necessities to do our job. Assisting with an Open Source project, I encountered three old UDFs. User Defined Functions [...]
InnoDB without PRIMARY KEY
Having an InnoDB table without a PRIMARY KEY is not good. Many have known this for years, but exact opinions as to why have differed. From observation, it was clear to me that it impacted performance. InnoDB stores its row data in the leaf nodes of the primary key B+tree structure, that means that it [...]
Tool of the Day: MOSH (Mobile Shell)
Today I nominate MOSH (Mobile Shell) from MIT in our “tool of the day” category. With people working remote, we sometimes encounter connectivity issues. But even when working from a stable connection, it’s sometimes just a pest when you close your laptop even though you hadn’t quite finished looking at something on that SSH connection… [...]
MariaDB 5.5 LIMIT ROWS EXAMINED
SELECT … LIMIT has always been very useful, particularly for web applications, restricting the number of rows in the result set to the amount that’s immediately required. To have web apps performing well, it’s always important to only retrieve as many rows as you need and no more. The SQL_CALC_FOUND_ROWS option was added later, so [...]
Storage caching options in Linux 3.9 kernel
dm-cache is (albeit still classified “experimental”) is in the just released Linux 3.9 kernel. It deals with generic block devices and uses the device mapper framework. While there have been a few other similar tools flying around, since this one has been adopted into the kernel it looks like this will be the one that [...]
SPDY protocol available in nginx 1.4
Nginx 1.4 can now do SPDY (draft 2). It’s hiding away in a separate file http://nginx.org/patches/spdy/README.txt So what is SPDY? In a nutshell, it does multiplexing, prioritization and compression of HTTP/HTTPS requests over a single TCP/IP connection. It also enables the server to push data before requested. These enable a browser or web services client to [...]
Fedora 19 – MariaDB Test Day 2013-04-30
From https://fedoraproject.org/wiki/Test_Day:2013-04-30_MariaDB, this installment of Fedora’s Test Day focuses on the replacement of MySQL with MariaDB. If you’re a Fedora (or RHEL or CentOS user), do take a peek at the page and see if you can pitch in – it might be a little bit of work for you, but with great benefits in terms [...]
Galera pre-deployment check
One of the first things we do when preparing a client’s infrastructure for Galera deployment is see whether their schema is suitable. Avoiding quirks and edge cases, we can say that Galera simply requires all tables to be InnoDB and also have a PRIMARY KEY (obviously having a PK in InnoDB is important anyway, for [...]
Query pattern: OR across different tables
When a query uses a construct like SELECT... FROM a JOIN b ON (...) WHERE a.c1 = X OR b.c2 = Y execution will inevitably degrade as the dataset grows. The optimiser can choose to use an index merge when dealing with two relevant indexes over a single table, but that’s obviously of no [...]
Fatal Half-measures in Incident Response
CSO Online writes about a rather sad list of security breaches at http://www.csoonline.com/article/721151/fatal-half-measures-in-incident-response, and the half-hearted approach companies take in dealing with the security on their networks and websites. Show More Summary
MariaDB Foundation
You may have already seen the announcement MariaDB Foundation to Safeguard Leading Open Source Database. We at Open Query wholeheartedly support this (r)evolution of the MySQL ecosystem, which appears to be increasingly necessary as Oracle Corp is seriously dropping the ball with security updates and actually just general development and innovation. Show More Summary
MariaDB security updates
Important Security Fix for a Buffer Overflow Bug: MariaDB 5.5.28a, 5.3.11, 5.2.13 and 5.1.66 include a fix for CVE-2012-5579, a vulnerability that allowed an authenticated user to crash MariaDB server or to execute arbitrary code with the privileges of the mysqld process. This is a serious security issue. We recommend upgrading from older versions as [...]
MariaDB C client libraries and the end of dual-licensing
Finally there is an LGPL C client library for MariaDB, and thus also for MySQL. Monty Program and SkySQL have been working on this for some time. Admittedly there was already the BSD licensed Drizzle client library which was also able to talk to a MySQL/MariaDB server, however its API is different. The C client [...]
Serving Clients Rather than Falling Over
Dawnstar Australis (yes, nickname – but I know him personally – he speaks with knowledge and authority) updates on The Real Victims Of The Click Frenzy Fail: The Australian Consumer after his earlier post from a few months ago. Colourful language aside, I believe he rightfully points out the failings of the organising company and the big [...]
Web Logs and Statistics
I’ve previously covered how user perspective and that browser to web server latency is a dominate cause of web server performance issues. Assuming you’ve resolved those as best as you can how to you measure server performance at a per request level. Web servers allow you to put their processing time into the logs however its not there by default. [...]
Optimising Web Servers
I was lucky enough to attend PyCon-AU recently and one talk in particular highlighted the process of web server optimisation. Graham Dumpleton’s add-in talk Web Server Bottlenecks And Performance Tuning available on YouTube (with the majority of PyCon-AU talks) The first big note at the beginning is that the majority of the delay in user’s perception of a [...]
The Optimiser Conundrum
We’ve been helping a long-term client who runs some fairly complex queries (covering lots of tables and logic on a respectably big but mainly volatile dataset). We tend to look first at query structure and table design, as fixing problems there tends to have the most impact. This contrary to just tossing more hardware at [...]
The Data Charmer: Is Oracle really killing MySQL?
http://datacharmer.blogspot.it/2012/08/is-oracle-really-killing-mysql.html An insightful post for my former (MySQL AB) colleague Giuseppe Maxia about how Oracle’s actions affect the MySQL landscape. My own comment exploring why it’s happening (from Upstarta perspective) is on his blog post rather than here. Show More Summary
One-way Password Crypting Flaws
I was talking with a client and the topic of password crypting came up. From my background as a C coder, I have a few criteria to regard a mechanism to be safe. In this case we’ll just discuss things from the perspective of secure storage, and validation in an application. use a digital fingerprint [...]
Understanding SHOW VARIABLES: DISABLED and NO values
When you use SHOW VARIABLES LIKE “have_%” to see whether a particular feature is enabled, you will note the value of NO for some, and DISABLED for others. These values are not intrinsically clear for the casual onlooker, and often cause confusion. Typically, this happens with SSL and InnoDB. So, here is a quick clarification! NO means [...]