Yesterday (4/19) I attended the AWS Summit in NYC (http://aws.amazon.com/aws-summit-2012/nyc).
I'm a big fan and also a heavy user of AWS especially S3, EC2,
and naturally, RDS. In every point in time I have several dozens
of AWS machines running for me out there in the East region, and
in some cases when we do some special benchmarks and tests,
number of EC2 and RDS machines can easily reach 3-digit. As I
said, I'm a fan...
A few quotes I was able to catch and document on my laptop, on my
laps...:
"When you develop an app for facebook, you must be prepared (and
be afraid) that to your party, not noone will show up, but
everybody will show up!" So true! Simple and true. We all want to
succeed, to have success with our app. We have to think about
scaling from day 1.
"Database was bottleneck for building of sophisticated apps. This
is …
There are a few ways to implement counters. Even though it’s not a complex feature, often I see people having problems around it. This post describes how bad implementation can impact both application and MySQL performance and how to improve it.
A customer asked me for help with performance problem they were facing. I logged into their database and found many client connections waiting for table locks. Almost all threads were stuck on one, small table called hits. What was the reason?
The problem was related to the way they developed a very simple system for counting page views they later used in some reporting. The table structure was:
mysql> SHOW CREATE TABLE hits\G *************************** 1. row *************************** Table: hits Create Table: CREATE TABLE `hits` ( `cnt` int(11) NOT NULL ) ENGINE=MyISAM mysql> SELECT * FROM hits; +---------+ | cnt | +---------+ | 3823273 | +---------+
…
[Read more]This post is about a fairly technical detail of how Galera works. I'm writing it down in preparation for testing this feature so that I can agree with Alex whether to file a bug or not. I'm sharing it on my blog just in case someone else might benefit from learning this.
Galera 2.0 introduces rolling schema upgrades. This is a new way to do non-blocking schema changes in MySQL.
As the name suggests, it is done as a rolling upgrade. Having seen clusters doing rolling upgrades before, I assumed this is what happens:
- Execute alter table on Node 1.
- Node 1 is removed from the cluster and stops processing transactions.
- Node 1 completes alter table.
- Node 1 re-joins cluster and catches up so that it is in sync.
Join GreenSQL at Infosecurity Europe 2012. Visit the GreenSQL booth J83b at the New Exhibitor Zone for live product demos and your chance to win a very cool tech gadget.
Book an appointment: marketing@greensql.com
See live demos and hear from our expert, GreenSQL’s Founder and CTO, David Maman.
For more information please visit our website: http://www.greensql.com/
Schema alteration is a big headache especially when it has already reached to production and things get worse when the relations are involved.Below is a short example for altering tables…
The post MySQL – Alter table-change datatype of column with foreign key first appeared on Change Is Inevitable.
As we know, MySQL workbench is excellent GUI tool for managing MySQL DB servers, creating ERDs (Data Modelling) and for sql development. But with this GUI tool, we are getting some command-line utilities too like mysqldiskusage, mysqlindexcheck, mysqlfailover, mysqldiff, mysqldbcompare etc., Here, I’m describing mysqldiskusage utility, which is not only displays mysql db usage but … Continue Reading …
[Read more]The MySQL Connect Call for Papers opened last Monday. Thanks to all of you who already submitted conference sessions and BOFs.
The CFP is running until May 6; if you have not submitted session proposals yet, you have about 2 weeks left to do so.
Interested in sponsorship and exhibit opportunities? You will find more information here.
We look forward to hearing from you!
We have setup our database servers to log all slow queries > 2
seconds. This can be done by enabling below from your
configuration file or via runtime using SET GLOBAL command.
Append to configuration (my.cnf) under [mysqld]
slow_query_log = on
long_query_time = 2
Using query
SET GLOBAL slow_query_log = on;
SET GLOBAL long_query_time = 2
With this current setup, we filtered all queries from our
application running more than 2 seconds. This is a good way to
identify slow queries and optimise them.
However, there are also queries from the MySQL Enterprise monitor
that were also being logged. In this case, i found out a (2)
graph metrics checking every minute for the total backup time
(total_time) and total lock time (lock_time). It takes about 3-6
seconds with only about 70 records on table.
To …
The Debt of Dirty Data
--Hey, why can't our Insurance and Billing system just talk to
our Admissions system? We just get too many errors in
patient names and numbers, and fixing all of this is becoming a
nightmare.
--Oh, alright let's integrate them then.
Duplicate data, multiple databases, and the need to communicate
new records and updated records, has some organizations in a
frenzy. The mantra "just integrate them" makes it seem like
there's a technology that will make this happen like magic.
It's not just the technology, it's the data.
Even if you follow database normalization rules (arcane,
mathematically-based rules for designing a database for the
non-database types), that doesn't mean you have clean, tight
data. Even still, in all of the databases I've seen, over
the years, I've still yet to see a perfectly designed database
anyway. Add to …
Experienced MySQL DBAs know that temp tables cause major problems
for MySQL replication. It turns out the converse is also
true: replication can cause major problems for temporary
tables.
In a recent customer engagement we enabled Tungsten Replicator with a MySQL application that
originally ran on a server that did not use replication. QA
promptly discovered reports that previously ran in 10 seconds
were now running in as many minutes. It turned out that the
reports used temp tables to assemble data, and these were being
written into the master binlog. This created bloated
binlogs and extremely slow reports. We fixed the problem by
enabling row replication (i.e., binlog-format=row in
my.cnf).
A common DBA response to temp table problems is to try to
eliminate them completely, as suggested in the excellent …