Percona

Percona is the only company that delivers enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms that maximize application performance while streamlining database efficiencies. Our global 24x7x365 consulting team has worked with over 3,000 clients worldwide, including the largest companies on the Internet, who use MySQL, Percona Server, Amazon® RDS for MySQL, MariaDB® and MongoDB.

Percona consultants have decades of experience solving complex database and database performance issues and design challenges. We consult on the full LAMP stack, from hardware to operating systems and right up through the database and web tiers. Because we are both broadly and deeply experienced, we can help build complete solutions. Our consultants work both remotely and on site. We can also provide full-time or part-time interim staff to cover employee absences or provide extra help on big projects.

Percona was founded in August 2006 by Peter Zaitsev and Vadim Tkachenko and now employs a global network of experts with a staff of over 125 people. Our customer list is large and diverse and we have one of the highest renewal rates in the business. Our expertise is visible in our widely read Percona Database Performance blog (https://www.percona.com/blog) and our book High Performance MySQL. To put Percona's expertise to work for you, please contact us (https://www.percona.com/about-percona/contact).

While doing initial testing of for an upcoming database server migration, we were surprised to find that the “new”, much better, machine is underperforming compared to the “old”, production, environment.     Specifically, we found that ALTER operations run significantly slower in the new.  A 1.9Gb table with 25407229 records took 2m 15s to complete an ALTER query ( ALTER TABLE _tmp_table ENGINE=InnoDB; ) in the “old” machine, while the same query took 7m 38s in the “new” machine.  Tests were performed when both machines experienced minimal load.     We appreciate any information or ideas that could explain (and solve) the observed underperformance.    

Thanks    

“Old” server specifications:
Dell PowerEdge R920
Processor: Cores (40) - Intel(R) Xeon(R) CPU E7-8891 v2 @ 3.20GHz
Ubuntu 14.04.1 LTS  Percona 5.6.22
RAM: 1511 GB
HDD: 598.9 GB + 1199.1 GB

“New” server specifications:
Dell PowerEdge R640
Processor: Cores (32) - Intel(R) Xeon(R) Gold 6244 CPU @ 3.60GHz
Ubuntu 18.04.3 LTS  Percona 5.7.28  
RAM: 1510 GB
SSD: 3.5 TB  


/etc/mysql/percona-server.conf.d/mysqld.cnf

skip-name-resolve
back_log = 1500
innodb_adaptive_hash_index = 0
innodb_buffer_pool_instances = 64
innodb_buffer_pool_size = 710G
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0
innodb_io_capacity = 15000
innodb_io_capacity_max = 25000
innodb_lru_scan_depth = 8192
innodb_open_files = 2000
innodb_page_cleaners = 8
join_buffer_size = 600M
key_buffer_size = 6000M
0
To enable binlog in percona cluster we need to add variables to my.cnf, where we need to add server-id and log-bin

As per percona documentation they recommend PXC nodes share the same server-id.

Does this mean if we have 3 nodes in the cluster should we keep as server-id=1 for all three nodes?
0
Dear Experts,

I deploy a Database System using Percona XtraDB ver.5.7 (3 Nodes) and 1 HaProxy as DB Load Balancer with Read/Write splitting config. Write on 1 Node, 2 nodes remain is back up. And Read will load balance between 3 nodes.
All 3 Node have 6CPU, 16GB RAM, and run on a local network.

My issue is when run queries, specially alter query, Node Write become conflict and cannot select data.
Percona Log:

2017-12-30T11:23:36.595013Z 624933 [Note] WSREP: --------- CONFLICT DETECTED --------
2017-12-30T11:23:36.595128Z 624933 [Note] WSREP: cluster conflict due to high priority abort for threads:

2017-12-30T11:23:36.595143Z 624933 [Note] WSREP: Winning thread: 
   THD: 624933, mode: total order, state: executing, conflict: no conflict, seqno: 2819156
   SQL: ALTER TABLE `sales_flat_order_address` ADD INDEX (`address_type`)

2017-12-30T11:23:36.595157Z 624933 [Note] WSREP: Victim thread: 
   THD: 630854, mode: local, state: executing, conflict: no conflict, seqno: -1
   SQL: SELECT `main_table`.*, COUNT(*) as count_item, ( SUM(main_table.row_total_incl_tax) + IFNULL( sfois.shipping_amount, 0 ) ) AS `sum_row_total_incl_tax`, `sfo`.`increment_id` AS `order_increment_id`, `sfo`.`delivery_date`, `sfo`.`delivery_time`, `sfo`.`entity_id` AS `order_id`, `sfo`.`increment_id` AS `order_increment_id`, CONCAT(sfoas.street, ' ', sfoas.city) AS `s_address`, CONCAT(sfoas.firstname, ' ', sfoas.lastname) AS `s_fullname`, `sfoas`.`telephone` AS `s_telephone`, 

Open in new window

0
I heard that replication (master slave) can help minimize outages due to heavy schema changes, yet I'm not sure I fully understand that!
1-Do we run changes on the slave then promote that slave to master? If so how is it possible to write to master?
2-what are the cases where replication can help with avoiding or minimizing outage?
0

Percona

Percona is the only company that delivers enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms that maximize application performance while streamlining database efficiencies. Our global 24x7x365 consulting team has worked with over 3,000 clients worldwide, including the largest companies on the Internet, who use MySQL, Percona Server, Amazon® RDS for MySQL, MariaDB® and MongoDB.

Percona consultants have decades of experience solving complex database and database performance issues and design challenges. We consult on the full LAMP stack, from hardware to operating systems and right up through the database and web tiers. Because we are both broadly and deeply experienced, we can help build complete solutions. Our consultants work both remotely and on site. We can also provide full-time or part-time interim staff to cover employee absences or provide extra help on big projects.

Percona was founded in August 2006 by Peter Zaitsev and Vadim Tkachenko and now employs a global network of experts with a staff of over 125 people. Our customer list is large and diverse and we have one of the highest renewal rates in the business. Our expertise is visible in our widely read Percona Database Performance blog (https://www.percona.com/blog) and our book High Performance MySQL. To put Percona's expertise to work for you, please contact us (https://www.percona.com/about-percona/contact).

Top Experts In
Percona
<
Monthly
>