Ok, I know that most of you reading this would think of this as
painfully obvious, but it's also clear that a lot of beginning
mysql users are timid about actually diving in and discovering
answers. In an effort to answer fewer simple questions, I'm
hoping to get this out to more people who may be beginners with
mysql.
The most common barrier seems to be "I don't have a test
environment!" This is easily remedied:
1. Even if your production enviroment is linux, you can still
install mysql on your windows-based workstation to play around
with. The installation on any environment can be done in minutes,
so lack of SQL is not a good excuse.
2. The perfect companion for testing mysql is MySQL
Sandbox. You can even test replication setups!
3. Even if you just have a production environment, small test
cases can be done inside a …
I’ll be attending and presenting at the 2009 Central Pennsylvania Open-Source Conference. My session is on Maatkit. I see Tom Clark has a session on MySQL performance! I hope to see you there — I’ve really become a fan of these regional conferences.
By the way, I’ve also created a speaker badge by adapting a wallpaper someone else made — you can find it on the sidebar of my blog if you’re also a speaker.
Related posts:
[Read more]About a month ago I blogged about the debug console I was adding to drizzled. I finished this work up and it’s now in the trunk and latest release. This is implemented using the Client and Listen plugin points (which are heavily modified versions of MySQL’s Protocol class), and can be enabled using the ‘–console-enable’. For example:
hades> drizzled --datadir=/Users/eday/drizzle.data --console-enable InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins. 090928 15:22:07 InnoDB: highest supported file format is Barracuda. 090928 15:22:07 InnoDB Plugin 1.0.3 started; log sequence number 46409 Listening on :::4427 Listening on 0.0.0.0:4427 ./drizzled/drizzled: Forcing close of thread 0 user: '(null)' ./drizzled/drizzled: ready for connections. Version: '2009.09.1144' Source distribution (trunk) drizzled> show tables in information_schema; …[Read more]
One of the first questions to answer when running databases on SSDs is what B-tree block size to use. There are a number of factors that affect this decision:
- The type of workload
- I/O time to read and write the block size
- The size of the cache
That’s a lot of variables to consider. For this blog post we assume a fairly common OLTP scenario – a database that’s dominated by random point queries. We will also sidestep some of the more subtle caching effects by treating the caching algorithm as perfectly optimal, and assuming the cost of lookup in RAM is insignificant.
Even with these restrictions it isn’t immediately obvious what is the optimal block size. Before discussing SSDs, let’s quickly address this problem on rotational drives. If we benchmark the number of IOPS for different block sizes on a typical rotation drive we get the following graph:
There are two …
[Read more]
Sometimes problems manifest inside of MySQL but the MySQL server
is really not the problem. A good example is how MySQL uses
reverse DNS lookups for authentication.
The Problem
You are alerted to a problem with a MySQL server either by Nagios
or whatever… You log into the server and everything seems to be
working correctly until you run “show full processlist”. The
majority of your connections are in an “Unauthenticated” state
and the rest are in some other state, probably “Sleeping”. You
speak with the lead developer and they state that there have not
been any code changes for 1 month. It’s an odd problem, when you
see it; however, the solution is not that obscure when you think
about how MySQL uses DNS.
The Solution
When you restart MySQL with “skip-name-resolve” enabled you …
Thanks to everyone who came out to the San Francisco PHP and MySQL meetup! Also, thanks to Michael for organizing such a great event, and Percona for sponsoring the food. I put the slides from the talk up on my wiki for reference or in case you missed it. I believe that there will be a video up at some point as well. While down there I also had a chance to stop by Digg and talked to them about Gearman (they’ve been using it for a while). It was interesting to see how they were using it in a large scale deployment. I was able to get some valuable feedback to future development, and a cool t-shirt. :) Thanks Digg!
I am speaking at Enterprise LAMP Summit 2009 (and also should be speaking at the Camp the next day, but I see the schedule isn’t quite updated yet). My talk at the Summit will not be on the future landscape of MySQL — that’s a mistake. My talk’s title isn’t finalized yet.
This conference is the first weekend in November, in the Nashville area. I hope to see you there!
Related posts:
[Read more]Why a New Utility?
A couple of months back, Tim Procter, Sheeri Cabral and I were discussing about how best to diagnose a MySQL server and/or tune its performance, automating the process as much as possible. The Performance Advisors from MySQL Enterprise do this, but most of our customers don’t have a subscription and Pythian’s collective experience is not necessary reflected by its rules.
In our daily work, we have used Major Heyden’s MySQL Tuner, Mark Leith’s Statpack and our own tools to review a MySQL server configuration parameters. However, all of these tools had limitations in regards of what we wanted to achieve. Our major …
[Read more]Installing Lighttpd With PHP5 And MySQL Support On Fedora 11
Lighttpd is a secure, fast, standards-compliant web server designed for speed-critical environments. This tutorial shows how you can install Lighttpd on a Fedora 11 server with PHP5 support (through FastCGI) and MySQL support.
I want to highlight the importance of reviewing mysql’s initial
set of accounts.
Say you have a mysql on abc.def.ghi.jkl running on port 3306
anonymous account with privileges without a password, then:
1. mysql (if issued on localhost)
2. mysql -h abc.def.ghi.jkl
3. mysql -u ” -h abc.def.ghi.jkl
4. mysql -u ” -h abc.def.ghi.jkl -P 3306
5. mysql -u user_which_does_not_exist -h abc.def.ghi.jkl
will all manage to get into mysql given the way mysql authenticates users is against your username and client host from where you are connecting.
This verification is done versus the following columns in the
mysql.user table, i.e., User,Host and Password columns.
An entry in the mysql.user table with the following values
User=”, Host=’%’ will accept ANY user connecting from ANYWHERE in
the world, thus disabling ANY security. Hence the reason for this
blog post highlighting the importance …