How do I distinguish the meaning of the SQL Performance Counters as Transactions / sec. And Errors / sec? [duplicate]

This question already has an answer here:

  • Batch requests / sec. Reported by the DMV millions of times larger than the Activity Monitor

    3 answers

I have been storing a lot of SQL performance counters in the last 10 days or so. I am looking to make a dashboard in the health of the server with this data. My question arises when I start looking at my meters that involve a "metric" / sec (Transactions / sec., Errors / sec., Etc.).

According to my understanding, the name implies that these counters should show me an aggregated statistic according to the time consulted on how many errors or transactions occur per second. Everything I have read online about these performance counters seems to affirm this description. However, you will notice in the image below that the number is high (423,683 transactions / sec), and it grows significantly every day. It seems to me that you are adding the transaction counter per second to the previous sums, if that makes sense. Is this the expected behavior? If so, to get the value I'm looking for, do I need to subtract the days to get the Transactions / sec for that day?

Transactions / sec For a single database registered for many days.

E3-1220v2 | E5-2630 v2 only $ 49.45 | Dedicated High Performance SuperMicro Servers

eLite Server Management offers high-performance dedicated SuperMicro servers in a stable and multiple network from our Dallas data centers.

Key features of our managed server plans

  • Standard DDoS protection
  • 24/7 Technical Support
  • Recharge OS for free
  • Guarantee of 100% availability of the network
  • Calss C IP Address
  • Free migrations of websites and servers

Additional recurring discount of 10% on dedicated servers. ProMO code: – EliteServer

CPU: Intel Core2 ™ 5670 2.93 GHz 2 cores 4 wires
Bandwidth: – 10 TB, 0.10 x additional GB
RAM: – 8GB DDR3
Hard drive: – 500 SATA
Operating system: – Enterprise Linux – CentOS 6/7, Windows
IP addresses: – 1 public IP
Speed: – 1 Gbps
Monthly fee: $ 39.00

Order now

CPU: Intel Xeon E3-1220v2 3.10 GHz 4 cores 4 threads
RAM: – 16 GB DDR3
Primary hard drive: – 1 TB SATA
Bandwidth: – 15TB
Operating system: – Enterprise Linux – CentOS 6/7, Windows
IP addresses: – 5 public IP's
Speed: – 1 Gbps
DDoS protection: DDoS protection included
Monthly fee: $ 54.95

Order now

CPU: Intel Xeon E3-1270v2 3.50 GHz 4 Cores 8 threads
RAM: – 16 GB DDR3
Primary hard drive: – 2 TB SATA
Bandwidth: – 15TB
Operating system: – Enterprise Linux – CentOS 6/7, Windows
IP addresses: – 5 public IP's
Speed: – 1 Gbps
DDoS protection: DDoS protection included
Monthly fee: $ 74.95

Order now

CPU: Intel Xeon E3-1270v2 3.50 GHz 4 Cores 8 threads
RAM: – 32 GB DDR3
Primary hard drive: – 1 TB SATA
Bandwidth: – 15TB
Operating system: – Enterprise Linux – CentOS 6/7, Windows
IP addresses: – 5 public IP's
Speed: – 1 Gbps
DDoS protection: DDoS protection included
Monthly fee: $ 79.95

Order now

CPU: Dual Intel Xeon E5-2630 v2 2.6 GHz 6 Cores 12 threads
RAM: – 16 GB DDR3
Primary hard drive: – 2 TB SATA
Bandwidth: – 15TB
Operating system: – Enterprise Linux – CentOS 6/7, Windows
IP addresses: – 5 public IP's
Speed: – 1 Gbps
DDoS protection: DDoS protection included
Monthly fee: $ 84.95

Order now

CPU: Dual Intel Xeon E5-2630 Lv2 2.40 GHz 6 Cores 12 threads
RAM: – 32 GB DDR3
Primary hard drive: – 1 TB SATA
Bandwidth: – 15TB
Operating system: – Enterprise Linux – CentOS 6/7, Windows
IP addresses: – 5 public IP's
Speed: – 1 Gbps
DDoS protection: DDoS protection included
Monthly fee: $ 89.95

Order now

CPU: Dual Intel Xeon E5-2650 2.80 GHz 8 Cores 16 threads
RAM: – 16 GB DDR3
Primary hard drive: – 1 TB SATA
Bandwidth: – 15TB
Operating system: – Enterprise Linux – CentOS 6/7, Windows
IP addresses: – 5 public IP's
Speed: – 1 Gbps
DDoS protection: DDoS protection included
Monthly fee: $ 94.95

Order now

CPU: Dual Intel Xeon E5-2650v2 8 cores 16 threads
RAM: – 64 GB DDR3
Primary hard drive: – 4 TB SATA
Secondary hard disk: – 4 TB SATA
Bandwidth: – 15TB
Operating system: – Enterprise Linux – CentOS 6/7, Windows
IP addresses: – 5 public IP's
Speed: – 1 Gbps
DDoS protection: DDoS protection included
Monthly fee: $ 124.95

Q: Are you a reseller or do you own your hardware?
A: We are not resellers and we own all the servers and routers.

Q: When is the bandwidth restored?
A: The bandwidth is reset on the expiration date of your server.

Q: What methods do you accept for payment?
A: We currently accept all major credit cards, Paypal.

Opinions
https://hostadvice.com/hosting-compa…ement-reviews/
https://eliteservermanagement.com/testimonials/

If you still have any questions about our services, talk to our 24×7 sales representative here https://eliteservermanagement.com

[WTS] 35% discount for 3 months on Centohost.com Dedicated servers + IPv6 ready + Free configuration!

You are interested High-end dedicated server hosting services?

CentoHost has been one of the main providers of web hosting services since 2002. We use the latest Dell server technology in our own data center to ensure our customers get the best performance from their websites. Our wide selection of web hosting services will adapt to your hosting needs, whether you are creating a small and simple website, or a high-powered, high-traffic website. Our main objective is to provide the best available technology with exceptional customer service. With a large customer base comes a wide range of web hosting requirements. We have adapted our company to these needs by providing flexible and scalable services.

SPECIAL OFFER: First 3 MONTHS – 35% DISCOUNT

Our dedicated servers are located in Own data center in Europe – Bosnia and Herzegovina. You will get access to the serial console with performance servers. You can also install and customize applications.

Our characteristics:

  • Unmetered bandwidth
  • Free DDoS protection
  • Ready for IPv6
  • Free configuration
  • Free hardware RAID
  • Quick configuration
  • Dell PowerEdge Servers
  • DDR3 ECC RAM
  • Guaranteed resource
  • Monitoring of the server
  • Remote reboot
  • Simple and dual CPUs

Check out Centohost Dedicated entry-level servers:
Start with a dedicated server within your budgetBandwidth without meter + Free DDoS protection + Ready for IPv6 + Free configuration)

Server Package PE-R320
Six Core Xeon E5-2420v2
Speed ​​- 6 Cores x 1.9 GHz
Memory – 16 GB DDR3
Hard drives – 2 TB (2 x 1 TB SATA3)
Bandwidth without measuring
PRICE – $ 129 / month – $ 83.85 / month – ORDER NOW

Server Package PE-R320 – 2
Six Core Xeon E5-2440
Speed ​​- 6 Cores x 2.4 GHz
Memory – 16 GB DDR3
Hard drives – 2 TB (2 x 1 TB SATA3)
Bandwidth without measuring
PRICE – $ 144 – $ 93.60 / month (3 months 35% discount) – ORDER NOW

You can also take a look at Centohost Dedicated performance servers:
More powerful servers for medium and large projects.Bandwidth without meter + Free DDoS protection + Ready for IPv6 + Free configuration)

Server Package PE R610 – 1
2 x 6 Core Xeon X5650
Speed ​​- 12 Cores x 2.7 GHz (+ HT)
Memory – 32 GB DDR3
Hard drives – 2 TB SAS (2 x 1 TB SATA) Optional
Bandwidth without measuring
PRICE – $ 243 – $ 157.95 / month (3 months 35% discount) – ORDER NOW

Server Package PE R610 – 2
2 x 6 Core Xeon X5670
Speed ​​- 12 Cores x 2.93 GHz (+ HT)
Memory – 32 GB DDR3
Hard drives – 2 TB SAS (2 x 1 TB SATA) Optional
Bandwidth without measuring
PRICE – $ 259 – $ 168.35 / month (3 months 35% discount) – ORDER NOW

Server Package PE-R710 – 3
2 x 6 Core Xeon X5670
Speed ​​- 12 Cores x 2.93 GHz (+ HT)
Memory – 32 GB DDR3
Hard drives – 2 TB SAS (2 x 1 TB SATA) Optional
Bandwidth without measuring
PRICE – $ 274 – $ 178.10 / month (3 months 35% discount) – ORDER NOW

Server Package PE-R620
2 x 8 Core Xeon E5-2670
Speed ​​- 16 Cores x 2.60 GHz (+ HT)
Memory – 64 GB DDR3
Hard drives – 2 TB SAS (2 x 1 TB SATA) Optional
Bandwidth without measuring
PRICE – $ 369 – $ 239.85 / month (3 months 35% discount) – ORDER NOW

Included with dedicated server:
Quick configuration (In most cases, our servers will be ready in just 4 hours after your order)
IPv4 and IPv6 network (All dedicated servers include IPv4 and IPv6 network addresses and connections)
Traffic without filter (All our dedicated servers come with data transfer without meter)
No configuration price (All our dedicated servers have a completely FREE configuration, even if you pay monthly)
Free DDoS protection (All dedicated servers in our offer are protected with an advanced DDoS protection system)
Free hardware RAID (We offer FREE hardware raid control with all our performance servers)

=== >> Write to us:
Centohost LLC
7260 W. Azure Dr Ste. 140-796
Las Vegas, NV 89130, United States
E-mail: info@centohost.com
Phone: + 1-702-323-3802
Fax: + 1-702-323-3844

Not sure what is the best plan for you?
No problem. Send us a ticket with your questions and we will respond with a quote. Free.

.

[WTS] ♥ █ ♥ HOSTING SEO / PBN at 70% DISCOUNT 4 LIFE✅SSD ✅Cloud Linux ✅Unique A / B / C Class IP / FREE SSL█

[COLOR=Green]SEO – PBN OFFERS HOSTING 2019 !![/COLOR] [COLOR=Navy]UP TO 70% DISCOUNTS ON ALL WEB HOSTING PLANS[/COLOR] VALID ONLY FOR A LIMITED TIME PERIOD!

[COLOR=Navy]INSIGHT WEB HOSTING[/COLOR] proudly announces SEO – PBN 2019 HOSTING OFFERS in our everything SEO hosting plans from multiple locations and PBN hosting servers from multiple locations.

[COLOR=Navy]HIGH IOPS BLAZING FAST RAID10 SSD DISK PROTECTED Units + + Cloud-Linux with CageFS + Web server LiteSpeed ​​with LSCache + PREMIUM SOFTACULOSO + FREE MIGRATION + FREE SEO TOOLS + CLOUDFLARE CDN ​​+ 99.9% WARRANTY OF USE OF THE SERVER![/COLOR]

[COLOR=Green]About INSIGHT TECHNOLOGY![/COLOR]
[COLOR=Navy]Inspection technology[/COLOR] is a leader based in the United States in the development and lodging industry, having been in this industry since 2010. INSIGHT Technology is a full service technology solutions provider that provides you with web hosting, reseller hosting, SSD server in the cloud, autonomous VPS or fully managed and dedicated Servers

[COLOR=Green]Why choose us?[/COLOR]
We offer affordable, reliable and high-performance hosting and development services for people around the world. We maintain a stable hosting environment, serving customers with a dedicated team for technical support and sales / billing.

"Come and consult our service and discover the difference".

[COLOR=Green][COLOR=Navy]HURRY UP![/COLOR] Right now Take advantage of this offer for personal or commercial use, before the EXCLUSIVE SALES OFFER expires![/COLOR]
Our SPECIAL HOSTING OFFERS 2018 are only for very limited time and stock. Then, place your order right now and get the high quality accommodation at a very reasonable price.

[COLOR=Green]Web hosting features:[/COLOR]
• Intel Quadruple CPU E3 / E5 Servers!
• Super fast speed with High IOPs SSD He drives!
Data protection RAID 10 – Better presentation
Softaculous pro Self-installer, theme of paper lanterns
CloudFlare Content delivery network
Cloud linux OS + LiteSpeed Web server
Incorporated SEO Tools- Attracta
Daily Off-site mirror Support for
• File manager with Web-enabled, POP / IMap / SMTP & amp; Webmail
PHP 5 with suPHP, MySQL 5, CGI / Perl 5, CURL, GD, ImageMagick,
Ruby on Rails, FrontPage Extensions, Python, Pear, IonCube, Zend Optimizer
• Throughout the system Protection against spam
The fastest performance & Highly Insured!

Quote:

[COLOR=Navy]Use the coupon codes listed below to receive up to 70% recurring discounts on all web hosting plans.[/COLOR]

Quote:

[COLOR=Green]RECURRENT 70% DISCOUNT FOR LIFE [/COLOR] in ANNUAL BILLING DEADLINE with coupon code "INSIGHT70"

Quote:

[COLOR=Green]RECURRENT 50% DISCOUNT FOR LIFE [/COLOR] at ANY BILLING TERM with coupon code "INSIGHT50"

[COLOR=Navy]♥ ♥ ♥[/COLOR][COLOR=Green] SEO – PBN 2019 HOSTING OFFERS[/COLOR][COLOR=Navy] VALID UNTIL APRIL 30, 2019 WITH THE COUPON CODES GIVEN PREVIOUSLY. ♥ ♥ ♥ [/COLOR]

[COLOR=Navy]Use the above coupon codes to receive up to 70% of recurring discounts in all multiple locations SEO HOSTING PLANS![/COLOR]

[COLOR=navy]▐▌[/COLOR][COLOR=Green]►[/COLOR][COLOR=Navy]▐▌[/COLOR]Beginner SEO accommodation

5GB (1 GB / user / IP) RAID-10 SSD space
50GB (10GB / User / IP) Monthly bandwidth
5 Hosted domains (5 main cPanel accounts)
Unlimited Addon / Subdomains
Unlimited Email / FTP accounts
Unlimited Databases
[COLOR=Navy]Monthly price[/COLOR]: [s]$ 20 / m[/s] Now only $ 9.9 / m
[COLOR=Navy]Annual Price[/COLOR]: [s]$ 240 / year[/s] Now only $ 71.9 / year ($ 5.9 / m)
[COLOR=Navy]ORDER NOW – [/COLOR][COLOR=Green] CA / FL / NY, US | United Kingdom | FR[/COLOR]

[COLOR=navy]▐▌[/COLOR][COLOR=Green]►[/COLOR][COLOR=Navy]▐▌[/COLOR]Turn off the darkness SEO Accommodation plan

10GB (1 GB / user / IP) RAID-10 SSD space
100GB (10GB / User / IP) Monthly bandwidth
10 Hosted domains (10 main cPanel accounts)
Unlimited Addon / Subdomains
Unlimited Email / FTP accounts
Unlimited Databases
[COLOR=Navy]Monthly price[/COLOR]: [s]$ 30 / m[/s] Now only $ 14.9 / m
[COLOR=Navy]Bi-annual price[/COLOR]: [s]$ 360 / year[/s] Now only $ 107.9 / year ($ 8.9 / m)
[COLOR=Navy]ORDER NOW – [/COLOR][COLOR=Green] CA / NV / FL / NC / NY, USA UU | Canada | United Kingdom | FR | Delaware [/COLOR]

[COLOR=navy]▐▌[/COLOR][COLOR=Green]►[/COLOR][COLOR=Navy]▐▌[/COLOR]Higher SEO Accommodation plan

20GB (1 GB / user / IP) RAID-10 SSD space
200GB (10GB / User / IP) Monthly bandwidth
twenty Hosted domains (20 main cPanel accounts)
Unlimited Addon / Subdomains
Unlimited Email / FTP accounts
Unlimited Databases
[COLOR=Navy]Monthly price[/COLOR]: [s]$ 45 / m[/s] Now only $ 22.49 / m
[COLOR=Navy]Bi-annual price[/COLOR]: [s]$ 1140 / year[/s] Now only $ 341.9 / year ($ 28.49 / m)
[COLOR=Navy]ORDER NOW – [/COLOR][COLOR=Green] CA / NV / FL / NC / NY, USA UU | Canada | United Kingdom | FR | DE | SG | AU[/COLOR]

[COLOR=navy]▐▌[/COLOR][COLOR=Green]►[/COLOR][COLOR=Navy]▐▌[/COLOR]Amazing SEO Accommodation plan

40GB (1 GB / user / IP) RAID-10 SSD space
400GB (10GB / User / IP) Monthly bandwidth
40 Hosted domains (40 main cPanel accounts)
Unlimited Addon / Subdomains
Unlimited Email / FTP accounts
Unlimited Databases
[COLOR=Navy]Monthly price[/COLOR]: [s]$ 95 / m[/s] Now only $ 47.9 / m
[COLOR=Navy]Bi-annual price[/COLOR]: [s]$ 240 / year[/s] Now only $ 47.9 / year ($ 3.9 / m)
[COLOR=Navy]ORDER NOW – [/COLOR][COLOR=Green] CA / NV / FL / NC / NY, USA UU | Canada | United Kingdom | FR | DE | SG | AU [/COLOR]

[COLOR=Navy]Use the above coupon codes to receive up to 50% recurring discounts on all multi-location PBN hosting plans.[/COLOR]

[COLOR=navy]▐▌[/COLOR][COLOR=Green]►[/COLOR][COLOR=Navy]▐▌[/COLOR]Boot PBN hosting plan

5GB (1 GB / user / IP) RAID-10 SSD space
50GB (10GB / User / IP) Monthly bandwidth
5 Hosted domains (5 main cPanel accounts)
Unlimited Addon / Subdomains
Unlimited Email / FTP accounts
Unlimited Databases
[COLOR=Navy]Monthly price[/COLOR]: [s]$ 30 / m[/s] Now only $ 14.9 / m
[COLOR=Navy]Bi-annual price[/COLOR]: [s]$ 360 / year[/s] Now only $ 107.9 / year ($ 8.9 / m)
[COLOR=Navy]ORDER NOW – [/COLOR][COLOR=Green] Ameria / Europe / Asia[/COLOR]

[COLOR=navy]▐▌[/COLOR][COLOR=Green]►[/COLOR][COLOR=Navy]▐▌[/COLOR]PBN standard hosting plan

10GB (1 GB / user / IP) RAID-10 SSD space
100GB (10GB / User / IP) Monthly bandwidth
10 Hosted domains (10 main cPanel accounts)
Unlimited Addon / Subdomains
Unlimited Email / FTP accounts
Unlimited Databases
[COLOR=Navy]Monthly price[/COLOR]: [s]$ 40 / m[/s] Now only $ 19.9 / m
[COLOR=Navy]Bi-annual price[/COLOR]: [s]$ 480 / year[/s] Now only $ 143.9 / year ($ 11.9 / m)
[COLOR=Navy]ORDER NOW – [/COLOR][COLOR=Green] Ameria / Europe / Asia [/COLOR]

[COLOR=navy]▐▌[/COLOR][COLOR=Green]►[/COLOR][COLOR=Navy]▐▌[/COLOR]Advanced PBN hosting plan

20GB (1 GB / user / IP) RAID-10 SSD space
200GB (10GB / User / IP) Monthly bandwidth
twenty Hosted domains (20 main cPanel accounts)
Unlimited Addon / Subdomains
Unlimited Email / FTP accounts
Unlimited Databases
[COLOR=Navy]Monthly price[/COLOR]: [s]$ 50 / m[/s] Now only $ 24.9 / m
[COLOR=Navy]Bi-annual price[/COLOR]: [s]$ 600 / year[/s] Now only $ 179.9 / year ($ 14.9 / m)
[COLOR=Navy]ORDER NOW – [/COLOR][COLOR=Green] Ameria / Europe / Asia[/COLOR]

Frequent questions:

1. Do you have any uptime guarantee?
Yes, we guarantee a 99.9% uptime of the server.

2. Where are your servers located?
Our reseller servers are located in America (USA / Canada), Europe (United Kingdom / FR / DE / LU / IT / SW / SP / ES), Pacific / Asia (SG / AU).

3. What payment method are you accepting?
We are accepting payment through PayPal, Bitcoin, Debit / Credit Cards.

4. How long will it take to set up my account?
Your account will be set up instantly after receiving the first payment.

If you have any questions, do not hesitate to contact us through Live Chat or by opening a sales ticket. Please visit our website to get more information about our [COLOR=Navy] PREMIUM SEO – PBN HOSTING[/COLOR].

.

Dallas, Los Angeles, Chicago – Dedicated Servers DDoS – Xeons and Performance i7

Swiftnode.net It offers dedicated performance servers. All our support personnel have experience in server administration. We mitigate hundreds of DDoS attacks to our customers each month without downtime. Our network is constantly monitored to detect anomalies and attacks. We can mitigate the volumetric attacks (Layer 3/4), as well as the attacks of the application layer. (Layer 7) We maintain a monthly activity time of 99.9% in all of our locations, we have fast hardware replacement SLA and absolutely none of our support personnel is outsourced.

LOS ANGELES CALIFORNIA – Intel Core i7-7700k – 32GB DDR4 – 480GB SSD – $ 99/ month – ORDER

LOS ANGELES CALIFORNIA – Intel Xeon E5-1650v3 – 32GB DDR4 – 1TB SSD – $ 109/ month – ORDER

DALLAS, TX – Intel Xeon E3-1231v3 – 32GB DDR3 – 240GB SSD – $ 89/ month – ORDER

DALLAS, TX – Intel Xeon E3-1240v6 – 16GB DDR4 – 240GB SSD + 1TB HDD – $ 89/ month – ORDER

DALLAS, TX – Intel Xeon E3-1270v6 – 16GB DDR4 – 240GB SSD – $ 99/ month – ORDER

DALLAS, TX – Intel Core i7-7700k – 16GB DDR4 – 500GB SSD – $ 99/ month – ORDER

DALLAS, TX – Xeon E5-2670v2 dual – 128GB DDR3 – 500GB SSD – $ 119/ month – ORDER

CHICAGO, IL – Intel Core i7-7700k – 16GB DDR3 – NVMe 250GB SSD – $ 99/ month – ORDER

Try our network with our mirror: https://lg.swiftnode.net

You can see all of our dedicated server packages on our website at https://swiftnode.net/dedicated-servers

Does an eGPU increase the performance of Lightroom Classic in 2018 13 "rMBP?

With the Retina MacBook Pro 2018 13 "for Lightroom Classic.

Adding an external GPU would increase the performance of the Lightroom Classic application? And if so, which modules / parts get the most performance?

Query adjustment for performance, rewrite suggestions

Could you review the consultations and your implementation plan with all the costs and metrics?

and suggest adjustment recommendations in the rewriting of queries and index changes, etc. to reduce the cost and improve performance.

(one)

https://www.brentozar.com/pastetheplan/?id=rJdp6DsqN

(two)

https://www.brentozar.com/pastetheplan/?id=HJYhlds94

Performance considerations to use the MySQL table as a queue?

I am working on a system that reports certain events to an external service.
Whenever a relevant change is made in the database, we want to guarantee that at least one message is sent to the external service.

I'm thinking that this could be implemented with a message queue table as follows:

  1. START a transaction
  2. Make the relevant database change
  3. Insert a message about the change in the message queue table, including a UUID
  4. COMPROMISE the transaction
  5. Later (for example, in a cron job), iterate through the message queue table, trying to send each message to the external service. Every time we get a successful response, delete that row. When we do not, try again later. If the external service actually received that message the first time but the response was lost, you can discard it based on the duplicate UUID.

Assuming we can send and delete message queue The faster rows than they are created, this table would tend to have very few rows at any time.

Given this pattern of frequent insertions and deletions, I do not think we want to index the table.
Is that correct? What else can we do to minimize the impact on the overall performance of the database?

networks – Oracle network performance

When I plan to port my application in Oracle to a hosting in the cloud, I have found that an important factor to consider is the volume of data transferred to / from the database, as it is limited by the cloud providers. I have not found a convenient way to estimate this volume based on usage statistics (for example, the number of queries and the average row size), nor how to measure current usage.

Is there any statistics incorporated in Oracle that can use or is it better to monitor this from the network perspective, monitoring the data transferred to / from the port used by Oracle?

MySQL join has a drastically worse performance after upgrading from 5.6 to 5.7

I have a fairly simple query that has become very slow after upgrading MySQL 5.6.35 to MySQL 5.7.12 (running on AWS RDS).

SELECT DIFFERENT
Name, d.id, deviceType, issuedBy, description, avNum, CompanyName,
Brand name, dwNumber, quant, discDate, Type
FROM table_one d
JOIN table_two i ON d.id = i.id;

In 5.6, this query is completed in 90 seconds. After updating to 5.7, it is taking more than 30 minutes. Each table has approximately 2 million rows.

I started by comparing the optimizer settings between 5.6 and 5.7:

# 5.6
index_merge = in
index_merge_union = on
index_merge_sort_union = on
index_merge_intersection = on
engine_condition_pushdown = on
index_condition_pushdown = on
mrr = en
mrr_cost_based = on
block_nested_loop = on
batched_key_access = off
materialization = in
semijoin = en
loosescan = on
firstmatch = on
subquery_materialization_cost_based = on
use_index_extensions = on
# 5.7
Optimizer settings in 5.7:
index_merge = in
index_merge_union = on
index_merge_sort_union = on
index_merge_intersection = on
engine_condition_pushdown = on
index_condition_pushdown = on
mrr = en
mrr_cost_based = on
block_nested_loop = on
batched_key_access = off
materialization = in
semijoin = en
loosescan = on
firstmatch = on
subquery_materialization_cost_based = on
use_index_extensions = on
condition_fanout_filter = on
derivative_merge = in
duplicateweedout = on

The only changes I see are the last three options in 5.7. I turned them off as follows:

SET optimizer_switch = & # 39; condition_fanout_filter = off & # 39 ;;
SET optimizer_switch = & # 39; Derivado_merge = off & # 39 ;;
SET optimizer_switch = & # 39; duplicateweedout = off & # 39 ;;

This had no effect. Next, I observed the differences in my innodb configuration (my instance of 5.7 has more memory, hence the differences in the size of the cluster).

# my innodb changes 5.6 -> 5.7
innodb_adaptive_hash_index_parts added in 5.7, set to & # 39; 8 & # 39;
innodb_additional_mem_pool_size set to 8388608 in 5.6, removed in 5.7
innodb_buffer_pool_dump_at_shutdown changed from 'OFF' to 'ON'
innodb_buffer_pool_dump_pct added in 5.7, set to '25'
innodb_buffer_pool_load_at_startup changed from 'OFF' to 'ON'
innodb_buffer_pool_size changed from 2,804,940,800 to 11,811,160,064
innodb_checksum_algorithm changed from "innodb" to "crc32"
innodb_deadlock_detect added in 5.7, set to & # 39; ON & # 39;
innodb_default_row_format added in 5.7, configured as 'dynamic'
innodb_file_format changed from "Antelope" to "Barracuda"
innodb_file_format_max changed from "Antelope" to "Barracuda"
innodb_fill_factor added in 5.7 set to & # 39; 100 & # 39;
innodb_flush_sync added in 5.7 set to & # 39; ON & # 39;
innodb_log_checksums added in 5.7 set to & # 39; ON & # 39;
innodb_log_write_ahead_size added in 5.7 set to 8192
innodb_max_undo_log_size added in 5.7 set to 1,073,741,824
innodb_mirrored_log_groups deleted in 5.7, set to & # 39; 1 & # 39; in 5.6
innodb_numa_interleave added in 5.7 set to "DISABLED"
innodb_page_cleaners added in 5.7 set to '4'
innodb_purge_rseg_truncate_frequency added in 5.7 set to & # 39; 128 & # 39;
innodb_strict_mode changed from 'OFF' to 'ON'
innodb_temp_data_file_path added in 5.7, set to & # 39; ibtmp1: 12M: autoextend & # 39; ibtmp1: 12M:
innodb_undo_log_truncate added in 5.7, set to & # 39; OFF & # 39;

I have reviewed the reference manual.
and tried to turn off the following:

innodb_strict_mode = 'OFF'
innodb_deadlock_detect = 'OFF'
innodb_flush_sync = & # 39; OFF & # 39;
innodb_log_checksums = 'OFF'

Also, it has no effect.

I hope someone can tell me what is happening here. The query is part of a larger workflow, which has become unusable as a result. I would like to recover the behavior of 5.6 if possible, but with my limited knowledge of DBA, I am running out of things to try. I hope someone can point me in the right direction or give me additional avenues to investigate.

Below are my tables. Many of the columns have large widths, and are mostly defined as VARCHAR (X) DEFAULT NULL because the data source is not clean (I have no control over the source). Note that I need utf8 data, but I am using latin1 while troubleshooting slow query problems, to eliminate that as a possible cause of slowness. (The reason we need to migrate from 5.6 -> 5.7 is for the larger index sizes in 5.7, which can accommodate our larger UTF8 column sizes).

(In addition, I must point out that the I.D the field comes from the source data and is a long string of alphanumeric characters of variable width (up to ~ 30 characters), so I need it to be a VARCHAR).

CREATE TABLE `table_one` (
`id` varchar (200) NOT NULL DEFAULT & # 39; & # 39 ;,
`RecordKey` varchar (50) DEFAULT NULL,
`VersionStat` varchar (200) DEFAULT NULL,
`Status` varchar (200) DEFAULT NULL,
`VersionNumber` varchar (50) DEFAULT NULL,
`VersionDate` varchar (20) DEFAULT NULL,
`PublishDate` varchar (20) DEFAULT NULL,
`DistStart` varchar (20) DEFAULT NULL,
`DistCommStat` varchar (2000) DEFAULT NULL,
`BrandName` varchar (100) DEFAULT NULL,
`VersionModelNumber` varchar (100) DEFAULT NULL,
`Catalog` varchar (100) DEFAULT NULL,
`dwNumber` varchar (100) DEFAULT NULL,
`CompanyName` varchar (500) DEFAULT NULL,
`DeviceCount` varchar (200) DEFAULT NULL,
`description` varchar (3000) DEFAULT NULL,
`Exemption` varchar (1100) DEFAULT NULL,
`PreMarket` varchar (1500) DEFAULT NULL,
`DevDRMT` varchar (1000) DEFAULT NULL,
`DTKit` varchar (200) DEFAULT NULL,
`Combination` varchar (250) DEFAULT NULL,
`Usage` varchar (500) DEFAULT NULL,
`SingleBatch` varchar (50) DEFAULT NULL,
`SerialNumber` varchar (250) DEFAULT NULL,
`ManuDate` varchar (20) DEFAULT NULL,
`ExpDate` varchar (20) DEFAULT NULL,
`Donation` varchar (50) DEFAULT NULL,
`LabeldWithMLO` varchar (50) DEFAULT NULL,
`NLabledMLO` varchar (50) DEFAULT NULL,
`MLOStatus` varchar (1000) DEFAULT NULL,
`BTT` varchar (50) DEFAULT NULL,
`OPP` varchar (50) DEFAULT NULL,
`BRC` varchar (50) DEFAULT NULL,
`PriorUse` varchar (100) DEFAULT NULL,
PRIMARY KEY (`id`)
) MOTOR = InnoDB DEFAULT CHARSET = latin1
CREATE TABLE `table_two` (
`id` varchar (200) DEFAULT NULL,
`Name` varchar (200) DEFAULT NULL,
`deviceType` varchar (50) DEFAULT NULL,
`issueBy` varchar (200) DEFAULT NULL,
`avNum` varchar (50) DEFAULT NULL,
`quant` varchar (50) DEFAULT NULL,
`discDate` varchar (50) DEFAULT NULL,
`PkgStatus` varchar (50) DEFAULT NULL,
`Type` varchar (50) DEFAULT NULL,
KEY `idx_table_two_id` (` id`)
) MOTOR = InnoDB DEFAULT CHARSET = latin1