Assumptions:
RHEL
x86_64
EXT3
RAID
What Raid to use?
RAID-10
Why?
It's faster. RAID-5 offers more disk space but the parity bit
messes things up, unless you have some uber hardware-raid card
that caches that operation. Personally I am not a fan.
Stripe Size:
128K - this is really good for INNODB, you'll see a huge boost in
responsiveness by making your Stripe Size 128K. I had a 64K
stripe size, and I was blown away by the improvement of
128K
Mount options:
mkfs.ext3 -T largefile | mkfs.ext3 -T largefile4
Unless your going to have millions of files, this is a good
option.
Make sure /etc/fstab mounts the mysql partition or the data that
mysql resides on with noatime.
atime is accesstime: this is a huge boost in performance,
tracking each time …
Ok, so I knew about innodb_table_monitor and innodb_tablespace_monitor. I’ve tried them before, looked at the output and given up, partly because it didn’t serve the purpose I wanted it to at the time, and also because it’s format was a little cryptic.
What I didn’t know was there are actually 4 monitors via this “create table functionality”. You can also do innodb_monitor which is the same as SHOW INNODB STATUS, and you can also do innodb_lock_monitor .
Another thing I didn’t know is that these commands don’t send the output just once, it’s on a timer. I’ve found the timers to be different. For innodb_monitor you get every 15 sec, as well as a nice line given time of averages which seems to always say 16 seconds.
===================================== 070601 15:11:25 INNODB MONITOR OUTPUT ===================================== Per …[Read more]
I've been looking through the Fedora Core 7 release notes this
morning. One thing which stood out was their inclusion of
Smolt.
Smolt is a hardware profiler which sends out put
back to a public webserver being run by Redhat. Turn it on, and
Redhat can see what hardware you are running. This type of phone
home service is one that we have talked about for a long time at
MySQL.
Would users be open to us sending back non private data about
their systems? OS, machine type, config parameters, storage
engines used... information found in show variables. What is the
level of detail?
Would any detail be too much? Even if the code laid dormant
unless turned on, what would users think?
I'm happy to see one of the open source vendors take this leap. I
think open information like this is great, since it allows
software writers to …
In a previous post, I mentioned the availability of the
dtrace extension for Cool Stack's PHP. Using this extension
and the Cool Stack MySQL, it is possible to analyze the
performance of your application running on this stack. At
JavaOne, we demoed this using the open source MediaWiki and
SugarCRM applications. dtrace is especially useful in
analyzing complex multi-tier applications like AMP. Thanks to
Angelo Rajadurai for the creation of the scripts that I describe
below.
Analyzing PHP calls
So, let's look at a simple dtrace script that counts how many times a particular PHP function is called :
#!/usr/sbin/dtrace -Zqs
php*:::function-entry
/arg0/
{
@[copyinstr(arg0)]=count();
}
You can copy the …
[Read more]I've been following the comments to Jamie's latest post and I have to say that most of the people who left comments are not thinking logically about this. Most (not all) are outraged that Microsoft would try to prevent Jamie from enabling his most excellent TestDriven.Net product on the Express line of products (Visual C# Express, etc).
As a consumer I would love to be able to use plugins in the Express products. I use Visual C++ Express to code on the MySQL server frequently. However, I'm smart enough to realize when I've been given something for free. Let me say this slowly and clearly: Microsoft is a business, not a public service. They didn't have to make the Express products available at all. It takes a great deal of time and money to build something as complicated as these …
[Read more]Ronald Bradford has published the 47th edition of Log Buffer, the weekly review of database blogs, on Technical Notes and Articles of Interest. Get in touch if you’d like to edit and host an edition of Log Buffer on your own blog. The Pythian Group Blog is widely read, so being a part of the [...]
Log Buffer #47: a Carnival of the Vanities for DBAs
June 1st, 2007 - by Ronald Bradford
Welcome to the 47th edition of Log Buffer, the weekly review of database blogs. No time to wait, lets read more about this week’s database blogging activities.
The PostgreSQL Conference for Users and Developers wrapped up this week and Peter Eisentraut gives us a review including the lightning talks and wrap-up session with a charity auction in PGCon Day 4. Meanwhile Alex Gorbachev is at Miracle Scotland Database Forum - Day One, sounds like from his post there is a lot …
[Read more]I've been following the comments to Jamie's latest post and I have to say that most of the people who left comments are not thinking logically about this. Most (not all) are outraged that Microsoft would try to prevent Jamie from enabling his most excellent TestDriven.Net product on the Express line of products (Visual C# Express, etc).
As a consumer I would love to be able to use plugins in the Express products. I use Visual C++ Express to code on the MySQL server frequently. However, I'm smart enough to realize when I've been given something for free. Let me say this slowly and clearly: Microsoft is a business, not a public service. They didn't have to make the Express products available at all. It takes a great deal of time and money to build something as complicated as these …
[Read more]
Counts, we all love to show counts in our applications. Under a
high traffic website, it's visually appealing to show a big
number. But the cost of generating that big number realtime can
be very expensive on our mySQL backend.
For example:
$sql = SELECT COUNT(*) FROM VERY_BIG_TABLE WHERE key_part1=NUM AND no_key IN (1,2,3) AND key_part2=NUM
Would you think this is fast? In a dev environment yes, but this
query has to get the exact row count that meets the requirements
of the WHERE clause.
In many cases we use a count to solve a BOOLEAN term in our code,
for example
$count_result = db_fetch($sql)
if ($count_result) {
// do something
} else {
$SHOW_ERROR = 1;
}
Don't use a count for this purpose. Just do something like
[Read more]
$sql = …
As Roland pointed out, recently a bunch of us took the MySQL Cluster Certification Exam. I was strongly encouraging people to take it (and, in fact, got the majority of people to take it… except those working on urgent customer issues or were rather new to the product). That’s one of my cunning plans (number 8 IIRC) - to have anybody doing anything to do with MySQL Cluster inside MySQL AB to be certified (in cluster, not insane).
Response from the exam is overwhelmingly positive and as it’s new, afterwards I discussed a few questions with Roland (in fact, all the ones I got wrong). The (good!?) news is that I legitimately brain-farted on some.
As for bragging rights… I currently share the top score. I’ll also (happily) buy a (large) beer …
[Read more]