1

MySQL Conference Liveblogging: Disaster Is Inevitable – Are You Prepared? (Tuesday 4:25PM)


Posted by Artem Russakovskii on April 15th, 2008 in Databases

  • Suicide
  • having no backups
  • depending on slaves for backup
  • keeping backups on same SAN
  • having a single DBA – Frank didn't like this one at all
  • not keeping binlogs
  • Restoring from backup
    • how much time?
    • uncompressed backup ready to mount?
    • separate network for recovery?
  • In Fotolog, 1TB of data was severely hit.
    • first problem: backup was highly compressed (tar.gz)
    • uncompressing took hours
    • so keep uncompressed backups (at least last N days)
    • it should be mountable, rather than transferable
  • Frank going over recovery modes at http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
  • Row by row recovery
    • row by row recovery (get the range of ids)
    • custom scripts
    • may not be able to use primary key
    • foreign key based retrieval faster
    • lose 4 seconds for each crashed record

      • …

      Read the rest of this article »

    0

    MySQL Conference: Presentation At The Kickfire Booth


    Posted by Artem Russakovskii on April 15th, 2008 in Databases

    Updated: April 17th, 2008

    I had a chance to visit the Kickfire booth after the keynotes and before the first presentation. They gave me a kicking t-shirt, followed by a presentation on the newly announced Kickfire appliance (now in beta, shipping in Fall 2008). Here are some notes I jotted down:

    • von Neumann bottleneck
    • SQL chip (SQC), packs the power of 10s of conventional CPUs
    • Query parallelization on the chip
    • On-chip memory – 64GB. No registers – no von Neumann bottleneck
    • Beats the performance of a given 3 server, 32 CPU, 130TB box (1TB of actual data – space is used for distributing IO)
    • SQC uses column-store, compression, intelligent indexing
    • SQL Chip, PCI connection, plugs into a Linux server
      • SQL execution
      • Memory management
      • Loader

      • …

      Read the rest of this article »

    1

    MySQL Conference Liveblogging: EXPLAIN Demystified (Tuesday 2:00PM)


    Posted by Artem Russakovskii on April 15th, 2008 in Databases

    • Baron Schwartz presents
    • only works for SELECTs
    • nobody dares admit if they've never seen EXPLAIN
    • MySQL actually executes the query
    • at each JOIN, instead of executing the query, it fills the EXPLAIN result set
    • everything is a JOIN (even SELECT 1)
    • Columns in EXPLAIN
    • id: which SELECT the row belongs to
    • select_type
    • simple
    • subquery
    • derived
    • union
    • union result
  • table: the table accessed or its alias
  • type:
    • join
    • range
    • …
  • possible_keys: which indexes looked useful to the optimizer
  • key: which index(es) the optimizer chose
  • key_len: the number of bytes of the index MySQL will use
  • ref: which columns/constants from preceding tables are used for lookups in the index named in the key column
  • rows: estimated number of rows to read
  • extra…

    Read the rest of this article »

    • 0

    MySQL Conference Liveblogging: The Future Of MySQL (Tuesday 11:55AM)


    Posted by Artem Russakovskii on April 15th, 2008 in Databases

    • Robin Schumacher
    • gives overview of MySQL products
    • MySQL Enterprise
    • MySQL 5.1 announced
      • table/index partitioning -> great for data warehouses, range, cache, key, list, composite, subpartitioning. Partition pruning. Response time greatly improved with proper partitioning.
      • row-based/hybrid replication -> safer and smarter
      • disk-based cluster -> supports bigger DBs
      • built-in job scheduler -> simplified task management
      • problem SQL identification -> easier troubleshooting. Dynamic query tracing is now available, no need to trace things in slow query logs.
      • faster full-text search -> 500% increase in some cases
      • 5.1.24RC available for the conference
    • MySQL 6.0
      • Falcon engine – transactional engine
      • new backup (version 1.0) -> cross engine, non-blocking, to replace mysqldump
    • Falcon
      • planned default transactional storage engine. Q4 GA (general availability).
      • not InnoDB replacement
      • most

      • …

      Read the rest of this article »

    0

    MySQL Conference Liveblogging: Performance Guide For MySQL Cluster (Tuesday 10:50AM)


    Posted by Artem Russakovskii on April 15th, 2008 in Databases

    • Speaker: Mikael Ronstrom, PhD, the creator of the Cluster engine
    • Explains the cluster structure
    • Aspects of performance
      • Response times
      • Throughput
      • Low variation of response times
    • Improving performance
      • use low level API (NDB API), expensive, hard
      • use new features in MySQL Cluster Carrier Grade Edition 6.3 (currently 6.3.13), more on this later
      • proper partitioning of tables, minimize communication
      • use of hardware
    • NDB API is a C++ record access API
      • supports sending parallel record operations within the same transaction or in different transactions
      • asynchronous and synchronous
      • NDB kernel is programmed entirely asynchronously
    • Looking at performance
      • Fire synchronous insert transactions – 10x TCP/IP time cost
      • Five inserts in one synchronous transaction – 2x TCP/IP time cost
      • Five asynchronous insert transactions – 2x TCP/IP

      • …

      Read the rest of this article »