Showing entries 81 to 90 of 438
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: Professional (reset)
Upcoming MySQL presentation in New York

On Tuesday I will be speaking in New York at the Effective MySQL Meetup group where I will be giving the presentation “MySQL Idiosyncrasies That Bite”. For more information and to register, check out the Meetup Event. There are just 10 seats left.

To promote the upcoming Percona Live event in New York, for attendees to the Meetup there will be a draw for a FREE ticket to the May 26th event.

Effective MySQL New York is the only MySQL group now operating in New York. Please join our group for the latest information and events for the MySQL community.

Basic scalability principles to avert downtime

In the press in the last two days has been the reported outage of Amazon Web Services Elastic Compute Cloud (EC2) in just one North Virginia data center. This has affected many large website includes FourSquare, Hootsuite, Reddit and Quora. A detailed list can be found at ec2disabled.com.

For these popular websites was this avoidable? Absolutely.

Basic scalability principles if deployed in these systems architecture would have averted the significant downtime regardless of your development stack. While I work primarily in MySQL these principles are not new, nor are they complicated, however they are fundamental concepts in scalability that apply to any technology including the …

[Read more]
Use Replication for backups? Are you schemas consistent?

Many people have a master/slave MySQL environment of various different topologies, and many use the slave as a backup.
Is your slave schema identical to your production schema? As long as an SQL statements completes without an error, your slave schema can differ. Common examples are different indexes or storage engines for example. However if you use the slave as backup, you want to ensure when you recover, you are recovering a production environment, not a slave environment.

While the changes may be small, the can lead to different results. For a client I found that the default value of a price field was 10.00 in one schema and 0.00 in another. Not withstanding using defaults for important fields and not defining in an INSERT is a different issue, it highlighted the different schemas can easily exist.

My tool of choice is SchemaSync. The reason why I like this command is …

[Read more]
I vote for Planet MySQL moderation

How this happens or who does it is obviously a larger and more complex conversation however it is better then involving innocent animals.

How is it that trivial $#*! gets voted and has a string of comments I can not explain, however Planet MySQL should have practical MySQL related content. I would vote for the front page and RSS feed to show only moderated content, and content that did not pass the cut (and there is a lot of this) can be available on a less important page if necessary.

Rather then complaining like many open source communities, let us propose a way to improve the system we use.

Checked that MySQL backup log lately?

Running a MySQL backup and ensuring it completed successfully and backup files exist is not enough. In my B&R Quiz from Checked your MySQL recovery process recently? one important step is “Do you review your backup logs EVERY SINGLE day or have tested backup monitoring in place?”

This is what I found when reviewing a backup log for a client today.

mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES

The backup script was completing, backup files were in place (and are listed in the log file) however these errors were occurring.

Further investigation was less then one minute of …

[Read more]
How to block DROP statements on the slave

This was a request yesterday on a mailing list about how to I block DROP statements from running on a slave. There were responses including MySQL could not do this, or try other replication technologies however I thought I would share how you can easily do this on a slave.

You simply deny access on the slave. It is as simple as changing the MySQL privileges for the user(s) in question to remove DROP or ALTER capabilities.

Of course this will break replication rather then simply ignore the statement however I suspect that is the intended goal anyway. The first statement that uses the table will fail regardless. With appropriate monitoring of replication you should know in about 15 seconds. (Hint: If you don’t monitor replication or monitor it frequently, DO SO NOW!)

These leads to the question, what if the statements are legitimate? There are several solutions. Enable the specific privileges on the slave when the DBA …

[Read more]
MySQL conference schedule

I am one of the crazy individuals(*) that will be speaking at both the regular O’Reilly MySQL Conference and the IOUG Collaborate conference both being held in the second week of April. My 4 presentations are:

[Read more]
Upcoming NY Presentation – How Better Indexes Save You Money

For all those in New York this is an upcoming MySQL presentation held in conjunction with our colleagues at General Assembly on March 22nd 2011.

This presentation “How Better Indexes Save You Money” will be discussing how one simple technique can result in huge MySQL performance improvements and with zero code changes necessary. Many people think they know indexes, however MySQL and MySQL Storage Engines have some specifics that differ from more traditional RDBMS products. Learn some of the key analysis and verification techniques and be able to see immediate potential results in performance.

You can find more details at Meetup.com EffectiveMySQL. This new group is all about highly technical MySQL related content “no fluff, just stuff”.

Part 2 – Simple lessons in improving scalability

Given the popular response from my first lesson in improving scalability where I detailed simple ways to eliminate unnecessary SQL, let me share another common bottleneck with MySQL scalability that can be instantly overcome.

Analyzing the writes that occur on a system can expose obvious potential bottlenecks. The MySQL Binary Log is a wealth of information that can be mined. Simple DML Counts per table can be achieved by a single line command.

Let’s look at the following example output of a production system:

mysqlbinlog /path/to/mysql-bin.000999 |  \
   grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter"  | \
   cut -c1-100 | tr '[A-Z]' '[a-z]' |  \
   sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set …
[Read more]
Optimizing UPDATE and DELETE statements

While most people look at performance optimizations for SELECT statements, UPDATE and DELETE statements are often overlooked. These can benefit from the principles of analyzing the Query Execution Plan (QEP). You can only run an EXPLAIN on a SELECT statement, however it’s possible to rewrite an UPDATE or DELETE statement to perform like a SELECT statement.

To optimize an UPDATE, look at the WHERE clause. If you are using the PRIMARY KEY, no further analysis is necessary. If you are not, it is of benefit to rewrite your UPDATE statement as a SELECT statement and obtain a QEP as previously detailed to ensure optimal indexes are used. For example:

UPDATE t
SET     c1 = ‘x’, c2 = ‘y’, c3 = 100
WHERE c1 = ‘x’
AND     d = CURDATE()

You can rewrite this UPDATE statement as a SELECT statement for using EXPLAIN:

EXPLAIN SELECT c1, c2, c3 FROM  t WHERE c1 = ‘x’ AND    d = CURDATE()

You should …

[Read more]
Showing entries 81 to 90 of 438
« 10 Newer Entries | 10 Older Entries »