Showing entries 1 to 7
Displaying posts with tag: Optimisation (reset)
JOIN Elimination: An Essential Optimiser Feature for Advanced SQL Usage

The SQL language has one great advantage over procedural, object oriented, and “ordinary” functional programming languages. The fact that it is truly declarative (i.e. a 4GL / fourth generation programming language) means that a sophisticated optimiser can easily transform one SQL expression into another, equivalent SQL expression, which might be faster to execute.

How does this work?

Expression Tree Equivalence

Here’s an introduction to equivalent expression trees from my SQL Masterclass: Let’s assume we want to evaluate the following expression:

A + (A - B)

Now in maths, it can be proven trivially that by the laws of associativity, the above expression …

[Read more]
Cost-based Optimization in MySQL 5.7

Optimiser is the brain of the RDBMS. Optimiser decides the right access method , algorithms , join order and right index to be used for better execution of the query. This blog is made to shed some lights on Cost based optimiser in MySQL 5.7. The cost or statistics are stored in the data dictionary .

What is cost-based optimization ?

  • The cost model is based on estimates of cost various operations occur during query execution.
  • The optimizer has a set of default “cost constants” it will make decision on execution plans.
  • In MySQL 5.7, the optimizer has addition a database of cost estimates to use during the execution plan.
  • These cost estimates are stored in server_cost & engine_cost tables in MySQL schema. For more details Cost Model

[Read more]
Optimising Web Servers

I was lucky enough to attend PyCon-AU recently and one talk in particular highlighted the process of web server optimisation.

Graham Dumpleton’s add-in talk Web Server Bottlenecks And Performance Tuning available on YouTube (with the majority of PyCon-AU talks)

The first big note at the beginning is that the majority of the delay in user’s perception of a website is caused by the browser rendering the page. Though not covered in the talk for those that haven’t used the tool YSlow (for Firefox and Chrome) or Google’s Developer Tools (ctrl-alt-I in Chrome), both tools will …

[Read more]
Database metics - thoughts and measurements


If I were to ask you 'what are the (database) metrics?', what do you think I'm asking? Thats an interesting question and something that requires some thought so lets handle this by actually looking at the following two alternative questions. 'What am I trying to archive [with database metrics]?' and 'how will I know when I have achieved it [by having database metrics]'?

Define the problem

What are we really doing
What are we trying to achieve?

  • Know how much capacity we have
  • Know that our databases are fast


How do we know when we have achieved it?

  • When we can forward plan upgrades when we are near a capacity limit
  • To be alerted of when things are not fast we can we investigate

So, what is capacity

Capacity is knowing how much extra we can do without making things 'slow'. Its a …

[Read more]
Formation Optimisation MySQL

Désolé, il ne reste plus de place pour cette matinée dédiée à l'optimisation des performances MySQL.

Date : 24 novembre 2009

Session 1 : 9h30-10h45

  • Architecture MySQL : Parsing, exécution, optimizer, query cache, binlog…etc
  • Architecture Innodb : Clustered index, bufferpool, hash index, insert buffer, locking model, MVCC, recovery log, checkpoint…etc. Comparaison avec les autres moteurs de stockage (PBXT, MySQL Cluster) et bases de données (PostgreSQL, Oracle ..).


Pause/Café : 10h45-11h00.

Session 2 : 11h00-12h30

  • Évolutions récentes : Google SMP patches,  XtraDB, Oracle innodb plugin 1.0.4
  • Apports de MySQL 5.4
  • Améliorations à venir :  Ahead flushing / Adaptive checkpoint,  …
[Read more]
Free MySQL Cluster Performance Tuning webinar – TODAY!

MySQL Cluster Performance Tuning Best Practices

Are you experiencing current performance bottlenecks in your high availability applications ? Are you designing a new mission-critical application and want to know how best to structure your schema and index strategy for optimal performance? Interested in how to transform your SQL into faster, more efficient queries?

Then this free web presentation is for you! You will get expert insight and learn best practices to help you identify those areas of database and application design that will give you the greatest benefits for performance when using MySQL Cluster.

We will discuss guidelines and best practices covering the following areas:

  • General Design Concepts and Guidelines
  • Schema Optimization
    • BLOB/Text vs VARBINARY/VARCHAR
    • Partition by Key
  • Index Selection and Tuning
[Read more]
INET_ATON & INET_NTOA

I have been spending a few days looking into optimisation for a few of our products, the biggest of which is Intellichat, which this great MySQL function is soon to be used for. Intellichat handles thousands of chats every hour, we average at about 190,000 chats a day, which is increasing daily. Every one of these chats holds information about the chat, from the Agent name, through the the IP address that launched the chat. This data allows us to provide detailed statistics to our customers from which Agent name converts best to which Country generates the most sales for their product.

*** Please note that all data sizes below are taken from the PHPMyAdmin row size and not the individual data size as listed in the mysql manual ***

Since starting out, I had always used a char(15) which uses 16 bytes to store ’192.168.0.1′. Since …

[Read more]
Showing entries 1 to 7