core error

Archive for the ‘mysql’ Category


What

NoSQL is a movement promoting a loosely defined class of non-relational data stores that break with a long history of relational databases. These data stores may not require fixed table schemas, usually avoid join operations and typically scale horizontally.

Why

Since usually the NoSQL data stores scale horizontally quite good, they are a serious choice when handling massive amounts of data. These kind of databases may not have the searching, joining and grouping capabilities a SQL data store may offer but on the other hand they provide really quick access to big amounts of data.

How

some of the most known databases are: BigTable, Cassandra, CouchDB, MongoDB or Membase

This is an example on how to work with MongoDB:

{
    "username" : "bob",
    "address" : {
        "street" : "123 Main Street",
        "city" : "Springfield",
        "state" : "NY"
    }
}

db.users.find({"address.state" : "NY"})

In the first part you can see an example of a ‘document’ (row in the SQL World) to be stored in MongoDB. The second exampleis how you can make a query.

Advertisements

SELECT id, tip_text FROM tips ORDER BY importance:

  1. Denormalize! always design your tables thinking of performance, not in space
  2. Use the slow query log
  3. Avoid wildcards (%) in the beggining of a LIKE statement
  4. Learn how to use EXPLAIN the best way
  5. Use MyISAM if you know that the data in the tables is not very dynamic (i.e. a reporting system)

Sharding

Posted on: 17/07/2011

What

Sharding consists in horizontally splitting a database: divide the rows of a database in groups to improve the performance of the queries on that database.

Why

Sharding reduces the index size and therefore, improves the search performance. Besides, sharding allows to store separate pieces of a database in different hardware improving the performance.

How

This technique should be transparent for the developers and it’s the Systems Engineers job to configure the databases in the most efficient way. Anyway, the developers may be very valuable at the design time since they best know which groups of data will be the most accessed and in which way.

tip: sharding should not be done unless we really need it for the sake of scalability due to the performance hit we may have in the DBMS each time we need to identify in which shard we can find the desired data.


MySQL may work with 2 different storage engines. Which one is better? depends on the needs your application has.

Advantages of InnoDB (by default in MySQL)

  • ensures the integrity of the data in our tables (atomicity)
  • if our application does many INSERTs and UPDATEs, the performance will improve

Advantages of MyISAM

  • Much faster in SELECTs

So, if our table is quite dynamic we would need a lot of INSERTs and UPDATES and therefore we will need to work with InnoDB. In case a table is meant to be a source of data mainly, we will need to use MyISAM



  • None
  • Justin Cooney: Great comic, this made me laugh!
  • Justin Cooney: Interesting, I didn't know about the Google App Engine, I'm glad I found your article on it!

Categories