In my old post there is a bug when run in MySQL 5.1.30 and old, because the status variable Queries was added in MySQL 5.1.31. So i change to choose between Queries and Questions status variables, and I think the Queries represent more accurate result.
http://forge.mysql.com/tools/tool.php?id=217
By the way:
# Queries The number of statements executed by the server. This variable includes
A couple of days ago I posted about scaling writes in mysql. I didn't
say much about read performance in that post because a) it was
irrelevant at the time, and b) there are thousands of articles
all over the web that already cover read performance.
In this post I'm going to cover some of the things that I did to
improve read performance for my own application. It may be
relevant to others, however, you'd still need to read a whole
bunch of other articles to understand MySQL read
performance.
Looking at our access patterns, it turned out that there were two
classes of read queries.
The former dealt with far more data at one go, but was only run once. Queries for this pattern were slow …
[Read more]
We use MySQL on most of our projects. One of these projects has a
an access pattern unlike any other I've worked on. Several
million records a day need to be written to a table. These
records are then read out once at the end of the day, summarised
and then very rarely touched again. Each record is about 104
bytes long (thre's one VARCHAR column, everything else is fixed),
and that's after squeezing out every byte possible. The average
number of records that we write in a day is 40 million, but this
could go up.
A little bit about the set up. We have fairly powerful boxes with
large disks using RAID1/0 and 16GB RAM, however at the time they
only had 4GB. For BCP, we have a multi-master set up in two colos
with statement level replication. We used MySQL 5.1.
My initial tests with various parameters that affect writes
showed that while MyISAM performed slightly better than InnoDB
while the tables were small, it quickly …
Save the date - October 14th, 3pm Paris & Berlin, 2pm London, 4pm Jerusalem - for this free live webinar where you'll have a chance to ask questions to our experts.
This webinar focuses on how ZFS, SSDs and the Open Storage line of products from Sun are changing the rules in the database storage industry. You will learn how to increase data security, scalability, and reduce the price/performance ratio with these technologies. This webinar includes ZFS best practises for databases backup and performance.
To register, click here.
I've been meaning to post some real-world data on the performance of the Infobright 3.2 release which happened a few weeks ago after an extended release candidate period. We're just preparing our upgrades now, so I don't have any performance notes over significant data sets or complicated queries to post quite yet.
To make up for that, I decided to address a particular annoyance of mine in the community edition, first because it hadn't been addressed in the 3.2 release (and really, I'm hoping doing this would include it into 3.2.1), and second, simply because the engine being open source means I can. I feel being OSS is one of Infobright's biggest strengths, in addition to being a pretty amazing piece of performance for such a simple, undemanding package in general, and not making use of that would be shame. Read on …
[Read more]I was recently helping some colleagues check what was happening with their MySQL queries, and wrote a DTrace script to do it. Time to share that script.
First of all, a look at some output from the script:
mashie[bash]# ./querystat.d -p `pgrep mysqld`
Tracing started at 2009 Sep 17 16:28:35
2009 Sep 17 16:28:38 throughput 3 queries/sec
2009 Sep 17 16:28:41 throughput 4 queries/sec
2009 Sep 17 16:28:44 throughput 528 queries/sec
2009 Sep 17 16:28:47 throughput 1603 queries/sec
2009 Sep 17 16:28:50 throughput 1676 queries/sec
^C
Tracing ended at 2009 Sep 17 16:28:51
Average latency, all queries: 107 us
Latency distribution, all queries (us):
value ------------- Distribution ------------- count
16 | 0
32 |@@ 170
64 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ … |
I was recently helping some colleagues check what was happening with their MySQL queries, and wrote a DTrace script to do it. Time to share that script.
First of all, a look at some output from the script:
mashie[bash]# ./querystat.d -p `pgrep mysqld`
Tracing started at 2009 Sep 17 16:28:35
2009 Sep 17 16:28:38 throughput 3 queries/sec
2009 Sep 17 16:28:41 throughput 4 queries/sec
2009 Sep 17 16:28:44 throughput 528 queries/sec
2009 Sep 17 16:28:47 throughput 1603 queries/sec
2009 Sep 17 16:28:50 throughput 1676 queries/sec
\^C
Tracing ended at 2009 Sep 17 16:28:51
Average latency, all queries: 107 us
Latency distribution, all queries (us):
value ------------- Distribution ------------- count
16 | 0
32 |@@ 170
64 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ … |