Transcription

2 The presentation is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle s products remains at the sole discretion of Oracle.

9 Filesystem Most customers uses EXT3(Linux) and UFS (Solaris) Ext2 could be an option (but recovery is longer) XFS we haven't experienced so much... ZFS You must separate journal (Zil) and filesystem Mount with noatime Raw device is not supported

11 Configuration - Disk Data Storage Use Disk Data tables for Simple accesses (read/write on PK) Same for innodb you can easily get DISK BOUND (iostat) Set DiskPageBufferMemory=3072M is a good start if you rely a lot on disk data like the Innodb_Buffer_Pool, but set it as high as you can! Increased chance that a page will be cached SharedGlobalMemory=384M-1024M UNDO_BUFFER=64M to 128M (if you write a lot) You cannot change this BUFFER later! Specified at LOGFILE GROUP creation time DiskIOThreadPool=[ ] (introduced in 7.0)

12 Configuration - General Set MaxNoOfExecutionThreads<=#cores Otherwise contention will occur unexpected behaviour. RedoBuffer=32-64M If you need to set it higher your disks are probably too slow FragmentLogFileSize=256M NoOfFragmentLogFiles= 6 x DataMemory (in MB) / (4x 256MB) Most common issue customers never configure big enough redo log The above parameters (and others, also for MySQL) are set with

13 Application : Primary Keys To avoid problems with Cluster 2 Cluster replication Recovery Application behavior (KEY NOT FOUND.. etc) ALWAYS DEFINE A PRIMARY KEY ON THE TABLE! A hidden PRIMARY KEY is added if no PK is specified. BUT.... NOT recommended The hidden primary key is e.g not replicated (between Clusters)!! There are problems in this area, so avoid the problems! So always, at least have id BIGINT AUTO_INCREMENT PRIMARY KEY Even if you don't need it for you applications

14 Application : Query Cache Don't cache everything in the Query Cache It is very expensive to invalidate over X mysql servers A write on one server will force the others to purge their cache. If you have tables that are read only (or change very seldom): my.cnf: query_cache_type=2 (DEMAND) SELECT SQL_CACHE <cols>.. FROM table; This can be good for STATIC data

15 Application : Transactions Failed transactions must be retried by the application If the REDOLOG or REDOBUFFER is full the transaction will be aborted This differs from INNODB behaviour There are also other resources / timeouts "Lock wait timeout" transaction will abort after TransactionDeadlockDetectionTimeout MaxNoOfConcurrent[Operations/Transactions] You can then increase this Nodefail/noderestart will cause transaction to abort

16 Application : Transactions Transactions (large updates) Remember NDB is designed for many and short transactions You are recommended to UPDATE / DELETE in small chunks Use LIMIT until all records are UPDATED/DELETED MaxNoOfConcurrentOperations sets the upper limit for how many records than can be modified simultaneously on one data node. MaxNoOfConcurrentOperations= will use 1GB of RAM Despite it is possible, we do recommend DELETE/UPDATE in smaller chunks.

17 Application : Table locks FLUSH TABLE WITH READ LOCK; LOCK TABLES <table> READ; Only locks the table(s) on the LOCAL mysql server You must get the LOCK on all mysql servers

18 Application : Schema Operations Don't use too much CREATE/DROP TABLE of NDB tables It is a heavy operation within Cluster Takes much longer than with standard MySQL

19 Log Only What Needs To Be Recovered Some types of tables account for a lot of WRITEs, but does not need to be recovered (E.g, Session tables) A session table is often unnecessary to REDO LOG and to CHECKPOINT Create these tables as 'NO LOGGING' tables: mysql> set mysql> set ndb_table_no_logging=1; mysql> create table session_table(..) engine=ndb; mysql> set 'session_table' will not be REDO logged or Checkpointed No disk activity for this table! After System Restart it will be there, but empty!

26 Restore ndb_restore is in many cases the MOST write intensive operation on Cluster The problem is that ndb_restore produce REDO LOG This is unnecessary but a fact for now Restores many records in parallel, no throttling.. So 128 or more small records may be fine, but 128 BLOBs. Temporary error: 410: REDO log buffers overloaded, consult online manual (increase RedoBuffer, and or decrease TimeBetweenLocalCheckpoints, and or increase NoOfFragmentLogFiles) If you run into this during restore Try increase RedoBuffer (a value of higher than 64MB is seldom practical nor needed) Run only one instance of ndb_restore ndb_restore -p10... Or even a lower value, e.g, -p1 If this does not help faster disk(s) is/are needed RB Synced every TBGCP

27 Separate Realtime and Reporting MySQL Cluster is really good for Short, many parallel transactions Is bad at most reporting type queries (complex JOINs). For reporting, replicate/copy out data to a reporting server more suitable for the job MySQL Server running Innodb For replication see Realtime Apps Reporting System Replication Mysqldump Complex reporting queries ndb_restore csv LOAD DATA INFILE

High Availability Solutions for the MariaDB and MySQL Database 1 Introduction This paper introduces recommendations and some of the solutions used to create an availability or high availability environment

Oracle Database Concepts Database Structure The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed

Best Practices for Using MySQL in the Cloud Luis Soares, Sr. Software Engineer, MySQL Replication, Oracle Lars Thalmann, Director Replication, Backup, Utilities and Connectors THE FOLLOWING IS INTENDED

Performance Report Modular RAID for PRIMERGY Version 1.1 March 2008 Pages 15 Abstract This technical documentation is designed for persons, who deal with the selection of RAID technologies and RAID controllers

MySQL performance in a cloud Mark Callaghan Special thanks Eric Hammond (http://www.anvilon.com) provided documentation that made all of my work much easier. What is this thing called a cloud? Deployment

Database Administration with MySQL Suitable For: Database administrators and system administrators who need to manage MySQL based services. Prerequisites: Practical knowledge of SQL Some knowledge of relational

A SURVEY OF POPULAR CLUSTERING TECHNOLOGIES By: Edward Whalen Performance Tuning Corporation INTRODUCTION There are a number of clustering products available on the market today, and clustering has become

Parallel Replication for MySQL in 5 Minutes or Less Featuring Tungsten Replicator Robert Hodges, CEO, Continuent About Continuent / Continuent is the leading provider of data replication and clustering

How to Choose your Red Hat Enterprise Linux Filesystem EXECUTIVE SUMMARY Choosing the Red Hat Enterprise Linux filesystem that is appropriate for your application is often a non-trivial decision due to

Encrypting MySQL data at Google Jonas Oreland and Jeremy Cole bit.ly/google_innodb_encryption Jonas Oreland!! Software Engineer at Google Has worked on/with MySQL since 2003 Has a current crush on Taylor

Module 14: Scalability and High Availability Overview Key high availability features available in Oracle and SQL Server Key scalability features available in Oracle and SQL Server High Availability High

Chapter 1 Storage Devices Summary Dependability is vital Suitable measures Latency how long to the first bit arrives Bandwidth/throughput how fast does stuff come through after the latency period Obvious

MySQL backups: strategy, tools, recovery scenarios Akshay Suryawanshi Roman Vynar April 15, 2015 Introduction 2 This is a brief talk on Backups for MySQL, where we will cover basics of backing up MySQL,

SAP HANA - Main Memory Technology: A Challenge for Development of Business Applications Jürgen Primsch, SAP AG July 2011 Why In-Memory? Information at the Speed of Thought Imagine access to business data,

Database Resilience at ISPs High-Availability White Paper Internet Service Providers (ISPs) generally do their job very well. The commercial hosting market is segmented in a number of different ways but

Oracle Data Guard for High Availability and Disaster Recovery John Zhong Senior Technical Specialist Global Database Management Group Citigroup and Vice President of American DBAOnline John_j_zhong@yahoo.com

Flash for Databases September 22, 2015 Peter Zaitsev Percona In this Presentation Flash technology overview Review some of the available technology What does this mean for databases? Specific opportunities