MySQL 5.6 Views and Stored Procedures Tips

Featured

MySQL LogoI recently tuned an existing application that used dozens of views and hundreds of stored procedures using MySQL 5.6.

There seems to be three attitudes towards using views and stored procedures (SPs) with MySQL:

  1. don’t use them at all to increase portability
  2. just use SPs to reduce network traffic in large reporting queries (my choice)
  3. go crazy and use them everywhere like old-school Oracle Enterprise apps.

Here are some notes on using views:

  • before creating views, review your schema to ensure keys have matching types and charsets for good performance. It’s much easier to spot schema problems in a text listing than to guess why a view is slower than expected at execution time. (This is doubly true for MySQL Cluster.)
  • MySQL currently doesn’t have CREATE VIEW FORCE, although MariaDB will later in 2014. The FORCE option will greatly simply view administration and also mysqldump output, which creates temporary tables to ensure views can be created regardless of table/view ordering issues
  • When looking at the MariaDB source code, it’s apparent that some view options were never actually implemented, like RESTRICT/CASCADE

And some notes on stored procedures (SPs):

  • if a SP makes a stateful session change, like set sql_log_bin=0, ensure that isn’t going to be a problem later if an exception condition doesn’t reset it
  • after running a SP, SHOW PROFILES will list all the queries executed with performance statistics
  • SPs that do non-essential SELECTs or INFORMATION SCHEMA queries probably need to be reviewed by a DBA for fundamental problems like non-atomic “reading before writing”

Both views and SPs are relatively new MySQL features, so budget some extra development and testing time when using them, especially with replication.

[MDEV-6365] CREATE VIEW Ignores RESTRICT/CASCADE Options
mysqlperformanceblog.com: Using MySQL triggers and views in Amazon RDS

Velocity Conference Santa Clara 2014 Tips Game Cards

Featured

The O’Reilly Velocity Web Operations & Performance Conference is June 24-26 in Santa Clara.

Next to the messages/jobs board was a Web Ops & Performance Tips board:

- use source maps to debug compressed JS and CSS
- use ::before to optimize font rendering
- use local storage to persist markup and templates to reduce requests and payload
- avoid CSS block rendering in chrome by not using screen media type until after. Then put screen back to element
- use gatling stress tool for load generation/perf testing (Apache Licence 2.0)
- learn curl
- learn POSIX before recreating another tool that already exists. Bill Joy (?)
- “if you do it more than twice a week, automate”
- it takes no skills to do NoOps! :)

Yahoo Mail Basic is Broken, but Full Featured Works

If you’re still using Yahoo Mail Basic version and have frequent connection timeout problems since mid-2013, then I recommend clicking on the upgrade link.

Full Featured version (free) does not appear to have those problems. The main drawback of this version is that Inbox paging was replaced with infinite scrolling.

Yahoos: something is misconfigured in handling Yahoo Mail Basic web browser requests …

It usually takes 3 Send requests to send an email after composing it, and often the same for refreshing Inbox. The browser status bar says, “Waiting for us-mg6.mail.yahoo.com…” for a long time on connection attempts.

Full Featured appears to work fine on the same computer (Macbook Pro Leopard, Firefox browser and Comcast Internet connection.

Oh, and please add a Settings option to disable infinite inbox scrolling. :)

AWS Pop-up Loft, San Francisco

Featured



Amazon Web Services pop-up loft (Ask an Architect area, lecture hall, kitchen/lounge)
Photo credit: Amazon.com.

I happened to be in SF today, so I went to the Amazon Web Services pop-up loft on Market St.

Amazon rented an empty storefront for 4 weeks for lecture sessions upstairs, and a computer lab and an ‘Ask an Architect’ bar downstairs.

One of the hosts said the loft was a shell in May, and they had to build out everything: the kitchen area, 2 bathrooms and various partitions.

I asked the experts about new EBS and RDS features, and they had answers as well as a $100 AWS credit.

The weather was sunny and warm in SF.

Lots of street performers and hustlers, including a very smooth male R&B singer. A young rapper named Rap2K15 was selling hand-made CDs.

Update 2014 06 23: Apparently a drawing was held, and I was one of 3 winners of a free general pass to the AWS:Reinvent Conference :)

Update 2014 06 24:

AWS Bootcamp

Full-day AWS overview, including EC2, S3, RDS, VPC and IAM, with 2 labs.

“Provisioning and Managing AWS Infrastructure with Chef” with special guest George Miranda, Chef Technical Consultant, Chef

George talked about using Chef tools like chef metal, knife and chef zero and a minimal amount of ruby to make an AMI and provision a MySQL server and 5 Nginx web servers.

Slides

@gmiranda23, chef-ami-factory

Update 2014 06 26:

Dealing With Obstacles at Scale, Bob Hagemann, Twilio

To reduce pain:

- UTC timezone
- UTF8
- use thin AMI and chef/puppet instead of thick AMI
- wrote boxconfig a few years ago (like netflix asgard)
- remote admin mainly
- small teams 3-8
- services should run in 3 AZs
- monitoring with nagios, cron, pingdom
- haproxy on each host as proxy
- MySQL, MHA, LVM. Manual failover.
- SQS DLQ
- global low latency with route53
- http://github.com/twilio
- @bobzilla42
- Uses freeswitch plus own telcom sw
- billing system 100s QPS
- Ops team is about 8 people
- VPNs to HQ and carrier-approved colo
- three founders, one came from Amazon.

925 Market Street, SF
June 4 – 27, 2014 (likely closed on the 27th for dismantling)
Free registration, tshirts and lunch. Closes 5:30 pm, 6:00 pm or 8:00 pm daily.
Muni 30 and 45 return from Market St. and 5th to Caltrain.

@AWSstartups #AWSloft