#DBHangOps 11/13/13 -- MySQL setup through Puppet!

Here's the recording. Be sure to view the presentation and show notes below:

Hello everyone!

Participate in #DBHangOps this Wednesday, November 13th, 2013 at 12:00pm pacific (19:00 GMT), to hear about:

• How do you ramp up new DBAs?

  • How do you ramp up a non-DBA to being a DBA?
• How Mozilla manages MySQL through puppet

• MySQL-isms!

  • (From Gerry) Sub SELECTs -- Why aren't these as mature as other databases?
  • (From Daniel) GROUP_CONCAT and other OLAP Style query functions -- Why doesn't MySQL have them?
  • (From Gerry) MySQL DBAs are used to building covering indexes to avoid PK lookups -- Is there a plan …

The new Continuent Tungsten 2.0.1 is now available. Continuent Tungsten 2.0.1 is the first generally available release of Continuent Tungsten 2.0, which offers major improvements to Continuent's industry-leading database-as-a-service offering. 

New features:

Replication

Provides low-impact, real-time replication with up-to 5X throughput over native MySQL and over 100X reduction in

Percona Server 5.6.11-60.3 has introduced a new feature called Log Archiving for XtraDB. This feature makes copies of the old log files before they are overwritten, thus saving all the redo log for a write workload.

When log archiving is enabled, it duplicates all redo log writes in a separate set of files in addition to normal redo log writing, creating new files as necessary.

Archived log file name format is ib_log_archive_startlsn. The start LSN marks the log sequence number when the archive was started. An example of the archived log files should look like this:

ib_log_archive_00000000010145937920
ib_log_archive_00000000010196267520

In order to …

Dear MySQL users,

MySQL Connector/Net 6.8.1, a new version of the all-managed .NET driver for MySQL has been released. This is a beta release for 6.8.x and it's not recommended for production environments.

It is appropriate for use with MySQL server versions 5.0-5.6

It is now available in source and binary form from http://dev.mysql.com/downloads/connector/net/#downloads and mirror sites (note that not all mirror sites may be up to date at this point-if you can't find this version on some mirror, please try again later or choose another download site.)

The 6.8.1 version of MySQL Connector/Net has support for Entity Framework 6.0

The release is available to download at  …

Using the MySQL 5.6 Performance Schema it is very easy to see what is actually running on your MySQL instance. No more sampling or installing software or worrying about disk I/O performance with techniques like SHOW PROCESSLIST, enabling the general query log or sniffing the TCP/IP stack.

The following SQL is used to give me a quick 60 second view on a running MySQL system of ALL statements executed.

use performance_schema;
update setup_consumers set enabled='YES' where name IN ('events_statements_history','events_statements_current','statements_digest');
truncate table events_statements_current; truncate table events_statements_history; truncate table events_statements_summary_by_digest;
do sleep(60);
select now(),(count_star/(select sum(count_star) FROM events_statements_summary_by_digest) * 100) as pct, count_star, left(digest_text,150) as stmt, digest from events_statements_summary_by_digest order by 2 desc;
update setup_consumers set …

Users are complaining about slowness in your system, MySQL load is always high… The more your database has access, the more it may get slow or worse: slowness even if it is running with low load. You are starting to get desperate! The consequences of slowness and high load are disastrous: If your site is slow,... Read More

The post MySQL Performance and Tuning Best Practices appeared first on Devops for Dummies.

Today before Ivan’s tutorial, he told me that in the 10.0.5 virtual machine images he created, he couldn’t find the Cassandra storage engine. I told him it had to be installed separately, and this is true – you have to install some engines separately!

When you do a yum install MariaDB-server MariaDB-client like the installation instructions tell you to do, you don’t get all storage engines (so running SHOW ENGINES might have you wondering what happened to a bunch of engines). This can easily be seen by doing a yum search MariaDB. On a CentOS 6.4 server with the MariaDB 10.0 repository configured, you should see the following:

MariaDB-cassandra-engine.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-client.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-common.x86_64 : MariaDB: a very fast and robust SQL …

Frequently in performance talks I hear speakers talk about the importance of using the correct datatypes for storing values in columns. i.e. representing a number with an INT or BIGINT, storing IP addresses as INT UNSIGNED, and VARCHAR(60) instead of VARCHAR(255).

This advice is correct, but today I thought I would try my best to go into a bit more detail :)

The Reasons

I can think of three reasons why this optimization is true:

1. Using numeric data types as strings incurs some added CPU overhead performing character-set and collation work. i.e. it’s not free to make 'Montréal' = 'Montreal' = 'MONTREAL', but MySQL behaves this way by default.

2. Using correct data types will save space. By ‘space’ usually memory-fit is more important than disk fit, as it can …

Some years back I attended a talk on testing practices where the presenter asked the audience how much test automation have they achieved in their projects. I was the only one who answered almost 100%. He advised me to keep this a secret from my management if I did not want my team to be downsized :). Good advice, but that got me thinking whether test automation actually makes testers redundant.
Having spent a good part of my experience in testing, I have seen this discipline mature over the years.  My opinion is that automation does not take away tester jobs; on the contrary it makes the job more interesting and effective.
  In my mind I classify automation progression in an organization into 4 stages as shown below.


Stage 1 In stage 1, testing is a completely …