10 Apr 2013, 22:30

SQL/Database Roundup - 10/4

Spending some time on MySQL these days, time for a SQL/Database roundup :

MySQL

Production

  • If you are interested in MySQL and want to know more about administration, replication, optimisation, etc ; the must read book is High Performance MySQL (I highly recommend it) and you can follow the related blog.
  • Percona provides a bunch of scripts to get information about the status of your database, its health, replication status, etc.
  • Percona provides a bunch of templates/scripts/reports/graphs to integrate with your monitoring solution (Nagios/Cacti) ; it covers mainly MySQL but you also have some for Apache, Nginx, MongoDB, etc.
  • More about Percona MySQL graphs in Cacti which capture all the activity of your MySQL server
  • If you are more interested on Postgres monitoring :

And as a side note, yes, Percona provides also their own MySQL based server with some optimisations and additional features and some other tools

Performance :

  • Indexes : the neglected performance all-rounder : where you will learn obvious things :
    • Defining indexes is part of developper jobs and not DBA
    • Indexes are not taught and poorly documented as it's not strictly part of SQL language
  • Need for speed (in French) : a mix of best practices / tools / methodology on optimisation for your database (mainly postgres)
  • MySQL Tuner : a script that would review your MySQL configuration and makes suggestion to improve performance and stability. Full support up to MySQL 5.1 ; Alike, you also have MySQL Tuning Primer.

19 Mar 2013, 22:14

Browser Diet : How to lose weight in the browser ?

Browser Diet is an initiative to promote best practices on front end web performance. It includes so far 24 best practices on different categories :

  • HTML
  • Javascript
  • CSS
  • JQuery
  • Images
  • Server optimisation

For each best practices, bad and good practices are introduced and a tool are suggested to achived this best practices.

If you are familar with YSlow or Page Speed, you may know most of them. However some best practices are mode "code oriented" and the fact that some tools are suggested or some links to useful ressources make it worth to read even if you are familar with webperf topic.

13 Mar 2013, 22:12

Postgres Roundup - 13/3

While skiing this week, you'll only have one blog post from me this week. So, I let you discover the Postgres world with some interesting resources.

For those who don't know Postgres, it's an opensource object relational database management system, initiated in 1995 and which know a rise since Ingres increased its license feeds and also Oracle bought Sun and thus MySQL. MySQL was a very popular database for Web projects at least (it's the M in the LAMP acronym : Linux Apache MySQL PHP). For a detailed history, cf Postgresql on Wikipedia.

So beyond the official documentation :

31 Jan 2013, 20:58

Database : Use the index, luke !

Disclamier : I'm not a SQL Guru, nor DBA but as I saw the document being quoted several times, it should have some revelancy.

Use the index is about best practices on SQL indexations and optimisations and content is both in English and French :

It concerns all main databases : MySQL, Oracle, Postgresql, IBM DB2 and Microsoft SQL Server 

CHAPTER 1 - Anatomy of an Index

The first chapter is the only one that doesn’t cover SQL specifically; it is about the fundamental structure of an index. An understanding of the index structure is essential to following the later chapters—don’t skip this!

Although the chapter is rather short—only about eight pages—after working through the chapter you will already understand the phenomenon of slow indexes.

CHAPTER 2 - The Where Clause

This is where we pull out all the stops. This chapter explains all aspects of the where clause, from very simple single column lookups to complex clauses for ranges and special cases such as LIKE.

This chapter makes up the main body of the book. Once you learn to use these techniques, you will write much faster SQL.

CHAPTER 3 - Performance and Scalability

This chapter is a little digression about performance measurements and database scalability. See why adding hardware is not the best solution to slow queries.

CHAPTER 4 - The Join Operation

Back to SQL: here you will find an explanation of how to use indexes to perform a fast table join.

CHAPTER 5 - Clustering Data

Have you ever wondered if there is any difference between selecting a single column or all columns? Here is the answer—along with a trick to get even better performance.

CHAPTER 6 - Sorting and Grouping

Even order by and group by can use indexes.

CHAPTER 7 - Partial Results

This chapter explains how to benefit from a “pipelined” execution if you don’t need the full result set.

CHAPTER 8 - Insert, Delete and Update

How do indexes affect write performance? Indexes don’t come for free—use them wisely!

APPENDIX A - Execution Plans

Asking the database how it executes a statement.

APPENDIX B - Myth Directory

Lists some common myth and explains the truth. Will be extended as the book grows.

APPENDIX C - Example Schema

All create and insert statements for the tables from the book.

So you can read the book online or buy it

25 Dec 2012, 22:30

sitespeed.io - to evaluate your site's web performance

Merry Christmas to all :-)

The Webperf advent calendar published a few days ago : "Do you sitespeed ?", introducing the sitespeed.io product, which analysis your site in terms of webperformance, with a synthesis and a per page analysis. The tool is based on web performance best practices such a YSlow ones.

What I like over current tools like YSlow or PageSpeed :

  • it provides a global and detailed overview  ; with PageSpeed/YSlow, you need to run it in your browser againt every page you want to analyse
  • As it's static reports, you can compare them over time

In addition, rules are well described, making easy for anyone to read the reports. You do not necessary need to go on the net to understand what is indicator X or Y.

Now I can see the Bike Team to make them aware of the progress they have to make to improve user experience. Indeed, at the end webperf is about improving user experience and nothing else.

Now you know your score, you can cheat ;-)