skyclimate.ru

честь ...

  • Увеличить размер шрифта
  • Размер шрифта по умолчанию
  • Уменьшить размер шрифта
Newsfeeds
Planet MySQL
Planet MySQL - http://www.planetmysql.org/

  • Active-Active Replication, Performance Improvements & Operational Enhancements – some of what’s available in the new MySQL Cluster 7.4.2 DMR
    Oracle have just made availble the new MySQL Cluster 7.4.2 Development Milestone Release – it can be downloaded from the development release tab here. Note that this is not a GA release and so we wouldn’t recommend using it in production. This is the second DMR for MySQL 7.4; the delta between this DMR and 7.4.1 can be viewed in the MySQL Cluster 7.4.2 Release Notes There are three main focus areas for this DMR and the purpose of this post is to briefly introduce them: Active-Active (Multi-Master) Replication Performance Operational improvements (speeding up of restarts; enhanced memory reporting) Active-Active (Multi-Master) Replication MySQL Cluster allows bi-directional replication between two (or more) clusters. Replication within each cluster is synchronous but between clusters it is asynchronous which means the following scenario is possible: Conflict with asynchronous replication Site A Replication Site B x == 10 x == 10 x = 11 x = 20 – x=11 –> x == 11 x==20 <– x=20 –   In this example a value (column for a row in a table) is set to 11 on site A and the change is queued for replication to site B. In the mean time, an application sets the value to 20 on site B and that change is queued for replication to site A. Once both sites have received and applied the replicated change from the other cluster site A contains the value 20 while site B contains 11 – in other words the databases are now inconsistent. How MySQL Cluster implements eventual consistency There are two phases to establishing consistency between both clusters after an inconsistency has been introduced: Detect that a conflict has happened Resolve the inconsistency The following animation illustrates how MySQL Cluster 7.2 detects that an inconsistency has been introduced by the asynchronous, active-active replication: Detecting conflicts While we typically consider the 2 clusters in an active-active replication configuration to be peers, in this case we designate one to be the primary and the other the secondary. Reads and writes can still be sent to either cluster but it is the responsibility of the primary to identify that a conflict has arisen and then remove the inconsistency. A logical clock is used to identify (in relative terms) when a change is made on the primary – for those who know something of the MySQL Cluster internals, we use the index of the Global Checkpoint that the update is contained in. For all tables that have this feature turned on, an extra, hidden column is automatically added on the primary – this represents the value of the logical clock when the change was made. Once the change has been applied on the primary, there is a “window of conflict” for the effected row(s) during which if a different change is made to the same row(s) on the secondary then there will be an inconsistency. Once the slave on the secondary has applied the change from the primary, it will send a replication event back to the slave on the primary, containing the primary’s clock value associated with the changes that have just been applied on the secondary. (Remember that the clock is actually the Global Checkpoint Index and so this feature is sometimes referred to as Reflected GCI). Once the slave on the primary has received this event, it knows that all changes tagged with a clock value no later than the reflected GCI are now safe – the window of conflict has closed. If an application modifies this same row on the secondary before the replication event from the primary was applied then it will send an associated replication event to the slave on the primary before it reflects the new GCI. The slave on the primary will process this replication event and compare the clock value recorded with the effected rows with the latest reflected GCI; as the clock value for the conflicting row is higher the primary recognises that a conflict has occured and will launch the algorithm to resolve the inconsistency. After a conflict has been detected, you have the option of having the database simply report the conflict to the application or have it roll back just the conflicting row or the entire transaction and all subsequent transactions that were dependent on it. So – what’s new in 7.4.1? Detects conflicts between inserts and updates Option to roll back entire transaction (and dependent transactions) rather than just the conflicting row All conflicts are handled before switching primary – avoiding potential race conditions As mentioned at the start of this post, this is pre-GA and there are some extra enhancements we plan on including in the final version: Handle deletes which conflict with other operations Roll back transactions that have read a row that had been rolled back due to a conflict Performance Being a scaled-out, in-memory, real-time database, MySQL Cluster performance has always been great but we continue to work on making it faster each release. In particular, we want to keep pace with the trend of having more and more cores rather than faster ones. 7.4 continues along the path of better exploiting multiple cores – as can be seen from these benchmark results. Just make sure that you’re using the multi-threaded data node (ndbmtd rather than ndbd) and have configured how many threads it should use. Faster Restarts You can restart MySQL Cluster processes (nodes) without losing database service (for example if adding extra memory to a server) and so on the face of it, the speed of the restarts isn’t that important. Having said that, while the node is restarting you’ve lost some of your high-availability which for super-critical applications can make you nervous. Additionally, faster restarts mean that you can complete maintenance activities faster – for example, a software upgrade requires a rolling restart of all of the nodes – if you have 48 data nodes then you want each of the data nodes to restart as quickly as possible. MySQL 7.4.1 includes a number of optimisations to the restart code and so if you’re already using MySQL Cluster, it might be interesting to see how much faster it gets for your application. We also have some extra optimisations in the works that you can expect to see in later 7.4 versions. Extra Memory Reporting MySQL Cluster presents a lot of monitoring information through the ndbinfo database and in 7.4 we’ve added some extra information on how memory is used for individual tables. For example; to see how much memory is being used by each data node for a particular table… mysql> CREATE DATABASE clusterdb;USE clusterdb; mysql> CREATE TABLE simples (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=NDB; mysql> SELECT node_id AS node, fragment_num AS frag, \ fixed_elem_alloc_bytes alloc_bytes, \ fixed_elem_free_bytes AS free_bytes, \ fixed_elem_free_rows AS spare_rows \ FROM ndbinfo.memory_per_fragment \ WHERE fq_name LIKE '%simples%'; +------+------+-------------+------------+------------+ | node | frag | alloc_bytes | free_bytes | spare_rows | +------+------+-------------+------------+------------+ | 1 | 0 | 131072 | 5504 | 172 | | 1 | 2 | 131072 | 1280 | 40 | | 2 | 0 | 131072 | 5504 | 172 | | 2 | 2 | 131072 | 1280 | 40 | | 3 | 1 | 131072 | 3104 | 97 | | 3 | 3 | 131072 | 4256 | 133 | | 4 | 1 | 131072 | 3104 | 97 | | 4 | 3 | 131072 | 4256 | 133 | +------+------+-------------+------------+------------+ When you delete rows from a MySQL Cluster table, the memory is not actually freed up and so if you check the existing memoryusage table you won’t see a change. This memory will be reused when you add new rows to that same table. In MySQL Cluster 7.4, it’s possible to see how much memory is in that state for a table… mysql> SELECT node_id AS node, fragment_num AS frag, \ fixed_elem_alloc_bytes alloc_bytes, \ fixed_elem_free_bytes AS free_bytes, \ fixed_elem_free_rows AS spare_rows \ FROM ndbinfo.memory_per_fragment \ WHERE fq_name LIKE '%simples%'; +------+------+-------------+------------+------------+ | node | frag | alloc_bytes | free_bytes | spare_rows | +------+------+-------------+------------+------------+ | 1 | 0 | 131072 | 5504 | 172 | | 1 | 2 | 131072 | 1280 | 40 | | 2 | 0 | 131072 | 5504 | 172 | | 2 | 2 | 131072 | 1280 | 40 | | 3 | 1 | 131072 | 3104 | 97 | | 3 | 3 | 131072 | 4256 | 133 | | 4 | 1 | 131072 | 3104 | 97 | | 4 | 3 | 131072 | 4256 | 133 | +------+------+-------------+------------+------------+ mysql> DELETE FROM clusterdb.simples LIMIT 1; mysql> SELECT node_id AS node, fragment_num AS frag, \ fixed_elem_alloc_bytes alloc_bytes, \ fixed_elem_free_bytes AS free_bytes, \ fixed_elem_free_rows AS spare_rows \ FROM ndbinfo.memory_per_fragment \ WHERE fq_name LIKE '%simples%'; +------+------+-------------+------------+------------+ | node | frag | alloc_bytes | free_bytes | spare_rows | +------+------+-------------+------------+------------+ | 1 | 0 | 131072 | 5504 | 172 | | 1 | 2 | 131072 | 1312 | 41 | | 2 | 0 | 131072 | 5504 | 172 | | 2 | 2 | 131072 | 1312 | 41 | | 3 | 1 | 131072 | 3104 | 97 | | 3 | 3 | 131072 | 4288 | 134 | | 4 | 1 | 131072 | 3104 | 97 | | 4 | 3 | 131072 | 4288 | 134 | +------+------+-------------+------------+------------+ As a final example, we can check whether a table is being evenly sharded accross the data nodes (in this case a realy bad sharding key was chosen)… mysql> CREATE TABLE simples (id INT NOT NULL AUTO_INCREMENT, \ species VARCHAR(20) DEFAULT "Human", PRIMARY KEY(id, species)) engine=ndb PARTITION BY KEY(species); // Add some data mysql> SELECT node_id AS node, fragment_num AS frag, \ fixed_elem_alloc_bytes alloc_bytes, \ fixed_elem_free_bytes AS free_bytes, \ fixed_elem_free_rows AS spare_rows \ FROM ndbinfo.memory_per_fragment \ WHERE fq_name LIKE '%simples%'; +------+------+-------------+------------+------------+ | node | frag | alloc_bytes | free_bytes | spare_rows | +------+------+-------------+------------+------------+ | 1 | 0 | 0 | 0 | 0 | | 1 | 2 | 196608 | 11732 | 419 | | 2 | 0 | 0 | 0 | 0 | | 2 | 2 | 196608 | 11732 | 419 | | 3 | 1 | 0 | 0 | 0 | | 3 | 3 | 0 | 0 | 0 | | 4 | 1 | 0 | 0 | 0 | | 4 | 3 | 0 | 0 | 0 | +------+------+-------------+------------+------------+ If you get chance to try out this new release then please let us know how you get on – either through a comment on this blog, a MySQL bug report or a post to the MySQL Cluster Forum.

  • The Perfect Server - Ubuntu 14.10 (nginx, BIND, Dovecot, ISPConfig 3)
    The Perfect Server - Ubuntu 14.10 (nginx, BIND, Dovecot, ISPConfig 3) This tutorial shows how to prepare an Ubuntu 14.10 (Utopic Unicorn) server (with nginx, BIND, Dovecot) for the installation of ISPConfig 3, and how to install ISPConfig 3. ISPConfig 3 is a webhosting control panel that allows you to configure the following services through a web browser: Apache or nginx web server, Postfix mail server, Courier or Dovecot IMAP/POP3 server, MySQL, BIND or MyDNS nameserver, PureFTPd, SpamAssassin, ClamAV, and many more. This setup covers nginx (instead of Apache), BIND (instead of MyDNS), and Dovecot (instead of Courier).

  • Log Buffer #396, A Carnival of the Vanities for DBAs
    This Log Buffer Edition lists down some of valuable blog posts from across the arenas of Oracle, SQL Server, and MySQL. Oracle: OOW Recap: Industrial Innovation with IoT, enabled by Oracle Fusion Middleware and JD Edwards. PeopleSoft HCM 9.2 Update Image 9 is Now Available! – Simple and Intuitive User Interface on Multiple Devices. API Catalog (OAC) allows organizations to easily build a catalog of their APIs to provide visibility to those APIs for application development. Oracle Announces Oracle API Catalog to Simplify API Management. Tools For Generating Consistent Loads. SQL Server: Is Database Mirroring Causing The Transaction Log To Fill Up? Free SQL in the City Seminar Washington DC – Dec 5, 2014. Stop Relying on Cloud File Stores as a Backup Strategy. Stairway to SQL Server Indexes: Level 1, Introduction to Indexes. SSRS – Report for Stored Procedure with Parameters. MySQL: Can you use the binary logs to undo a DELETE? Maybe, if you’re using ROW format logging. The difference between a delete and an insert event could be just one byte – the one that maps it as a DELETE_ROWS_EVENT or a WRITE_ROWS_EVENT. Ubuntu 14.10 LAMP server tutorial with Apache 2, PHP 5 and MySQL (MariaDB). Resizing the InnoDB Buffer Pool Online. Inside Distributed Architectures: Seeing In-Flight Requests And Blockers in Realtime. Stopping a Runaway Rollback due to Failing Mass Import or ALTER TABLE.

  • How to setup High Availability PrestaShop on multiple servers with MariaDB Galera Cluster
    November 7, 2014 By Severalnines PrestaShop is a popular open source e-commerce software powering over 200,000 online stores, according to the company. We’ve seen a bit of interest into high availability PrestaShop setups, so this post will show you how to achieve that on multiple servers. Note that this setup not only caters for failures, but by load balancing traffic across multiple servers, it also allows the system to scale and handle more users. This post is similar to our previous posts on web application scalability and high availability: Magento - nginx, MySQL Galera Cluster, OCFS2 Drupal - Apache, MySQL Galera Cluster, csync2 with lsyncd Wordpress - Apache, Percona XtraDB Cluster, GlusterFS Joomla - Apache, MariaDB Cluster, GFS2   We will focus on migrating Prestashop 1.6 from standalone server installation to three separate servers with MariaDB Galera Cluster and GFS2 (Global File System) running on CentOS 6.5 64 bit. We will use a server for shared storage and ClusterControl. Please review the above posts if you’d like to use a different clustered file system or MySQL Galera vendor.    The following diagram illustrates our setup:   Our setup consists of 6 servers or nodes: prestashop1: web server + database server prestashop2: web server + database server prestashop3: web server + database server lb1: load balancer (master) + keepalived lb2: load balancer (backup) + keepalived storage1: shared storage + ClusterControl   Our major steps would be: Prepare 6 hosts Deploy MariaDB Galera Cluster onto prestashop1, prestashop2 and prestashop3 from storage1 Configure iSCSI target on storage1 Configure GFS2 and mount the shared disk onto prestashop1, prestashop2 and prestashop3 Configure Apache on prestashop1, prestashop2 and prestashop3 Configure Keepalived and HAProxy for web and database load balancing with auto failover Install Prestashop and connect it to the Web/DB cluster via the load balancer   Preparing Hosts   Add following hosts definition in /etc/hosts: 192.168.50.100 virtual-ip prestashop 192.168.50.101 prestashop1 web1 maria1 192.168.50.102 prestashop2 web2 maria2 192.168.50.103 prestashop3 web3 maria3 192.168.50.111 lb1 192.168.50.112 lb2 192.168.50.120 storage1 clustercontrol   read more

  • Undelete rows with the binary log
    Can you use the binary logs to undo a DELETE? Maybe, if you're using ROW format logging. The difference between a delete and an insert event could be just one byte - the one that maps it as a DELETE_ROWS_EVENT or a WRITE_ROWS_EVENT. Let's try it. I've already populated this table with a few rows: CREATE TABLE `undo_test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `t` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `v` varchar(20) DEFAULT NULL, `b` blob, PRIMARY KEY (`id`) ) ENGINE=InnoDB; Save a hash so we can see if the undo really works later: mysql -e "SELECT * FROM test.undo_test" | md5sum > before.md5 Delete an unwanted row: DELETE FROM undo_test; Query OK, 1693 rows affected (0.14 sec) Oops! Forgot the WHERE clause! And of course I don't have any recent backups. Is the data really gone? mysql> select count(*) from undo_test; +----------+ | count(*) | +----------+ | 0 | +----------+ But maybe there's hope. Find the delete event in the binary log. mysql> SHOW BINLOG EVENTS; +------------+--------+-------------+-----------+-------------+----- | Log_name | Pos | Event_type | Server_id | End_log_pos | Info +------------+--------+-------------+-----------+-------------+----- | bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.20-log, Binlog ver: 4 <snip> | bin.000001 | 516 | Query | 1 | 588 | BEGIN | bin.000001 | 588 | Table_map | 1 | 647 | table_id: 70 (test.undo_test) | bin.000001 | 647 | Delete_rows | 1 | 8859 | table_id: 70 | bin.000001 | 8859 | Delete_rows | 1 | 16932 | table_id: 70 | bin.000001 | 16932 | Delete_rows | 1 | 25040 | table_id: 70 | bin.000001 | 25040 | Delete_rows | 1 | 33244 | table_id: 70 | bin.000001 | 33244 | Delete_rows | 1 | 41408 | table_id: 70 | bin.000001 | 41408 | Delete_rows | 1 | 49549 | table_id: 70 | bin.000001 | 49549 | Delete_rows | 1 | 57764 | table_id: 70 | bin.000001 | 57764 | Delete_rows | 1 | 65956 | table_id: 70 | bin.000001 | 65956 | Delete_rows | 1 | 74096 | table_id: 70 | bin.000001 | 74096 | Delete_rows | 1 | 82237 | table_id: 70 | bin.000001 | 82237 | Delete_rows | 1 | 90445 | table_id: 70 | bin.000001 | 90445 | Delete_rows | 1 | 98586 | table_id: 70 | bin.000001 | 98586 | Delete_rows | 1 | 106728 | table_id: 70 | bin.000001 | 106728 | Delete_rows | 1 | 114886 | table_id: 70 | bin.000001 | 114886 | Delete_rows | 1 | 123085 | table_id: 70 | bin.000001 | 123085 | Delete_rows | 1 | 131290 | table_id: 70 | bin.000001 | 131290 | Delete_rows | 1 | 139476 | table_id: 70 | bin.000001 | 139476 | Delete_rows | 1 | 146945 | table_id: 70 flags: STMT_END_F | bin.000001 | 146945 | Xid | 1 | 146976 | COMMIT /* xid=36 */ The delete starts at position 516 and ends at 146976. Get that section of the binary log, change the event type, and pipe it back in. The event type is the byte at offset 0x31 in the query event. From sql/log_event.h we see that a delete is 0x20 and an insert is 0x1E, but remember that mysqlbinlog output is base64 encoded. Somebody will surely tell me the proper way to edit that directly, but for my purposes this worked (those surrounding bytes are probably based on the timestamp or server id or something, so I doubt this exact sed will work for you, but a bit of playing with a hex editor and a base64 decoder/encoder should tell you what to change): mysqlbinlog --start-position=516 --stop-position=146976 bin.000001 | sed s/bVCABA/bVB4BA/ | mysql Compare it to the original hash to see if it's really back: mysql -e "SELECT * FROM test.undo_test" | md5sum -c before.md5 -: OK Looks like it worked.


Who's Online

Сейчас 6 гостей онлайн

Statistics

Пользователи : 1041
Статьи : 69
Ссылки : 6
Просмотры материалов : 52119

Топ просмотров ...

1:  БРОВЦЫН Алексей Александрович (3266)
2:  БОДИСКО Михаил Андреевич (3099)
3:  УНКОВСКИЙ, Семен Яковлевич (2844)
4:  БОЛТИН Александр Арсентьевич (2836)
5:  ЛАВРОВ Сергей Флегонтович (2593)
6:  БЕРСЕНЕВ Федор Аркадьевич* (2521)
7:  АБЕРНИБЕСОВ (Обернибесов) Конон Антонович (2428)
8:  О\'БРИЕН-де-ЛАССИ Терентий Александрович (2315)
9:  НЕПЕНИН Адриан (Андриан) Иванович (2308)
10:  ВЕРХОВСКИЙ Владимир Павлович (2284)
11:  АРНАУТОВ Константин Петрович (2153)
12:  БРУСИЛОВ Лев Алексеевич (2124)
13:  ШИРИНСКИЙ-ШИХМАТОВ, кн Евгений Николаевич (2031)
14:  ЗЕНИЛОВ Николай Исаакович (2030)
15:  СИДЕНСНЕР Александр Карлович (2021)
16:  НАССАУ-ЗИГЕН, принц (1941)
17:  ПРОЗОРОВСКИЙ Илья Георгиевич (1913)
18:  АВИНОВ Александр Павлович (1870)
19:  АКИНФИЕВ Андрей Михайлович (1864)
20:  ПАРОМЕНСКИЙ Александр Иванович (1860)
21:  ВАСИЛЕВСКИЙ Кесарь Иванович (1831)
22:  АБАЗА (Abaza) Алексей Михайлович (1829)
23:  КРИГЕР Александр Христианович (1821)
24:  МАНДРАЖИ Евгений Николаевич (1791)
25:  ГИРС Александр Владимирович (1786)
26:  БАХИРЕВ Михаил Коронатович (1733)
27:  ЛИВЕН, светл кн Александр Александрович (1725)
28:  ЗОТОВ Григорий Иванович (1695)
29:  ПАНАЕВ Платон Аркадьевич (1691)
30:  ГИЛЯРОВСКИЙ Ипполит Иванович (1690)
31:  ГИЛЬТЕБРАНДТ Яков Аполлонович (1690)
32:  БАЖЕНОВ Александр Иванович (1684)
33:  ФЕРЗЕН, бар Василий Николаевич (1683)
34:  ЧЕРНИЛОВСКИЙ-Сокол Николай Иванович (1683)
35:  НЕБОЛЬСИН Аркадий Константинович (1665)