How can I monitoring, when Threads_connected increase from which place(files) or when it decrease, because some of cases it dose not fall down automatically in our production server and we are getting to max connection error there, So i need to identify issues in which files has problem, My site in magento 1.9 and max_connections=151 so i want to know what the ideal count for that and how can i monitoring ?

I'm managing a Magento server with MySQL installed in it. 16GB RAM 8 core vCPU (Digitalocean). We're having a problem with the site becoming a bit sluggish when under medium to heavy load. Under low to none, MySQL uses around 25% to 60% of CPU (according to HTOP). Under medium to heavy load MySQL takes up 150% of CPU usage (again, according to HTOP). On the graph CPU usage on all cores is stuck at 100% during medium usage of the website. My MySQL configuration is the default, a performance tuning was never done. I'm working on configuring PHP and Elasticsearch memory/CPU usage, but I need help with MySQL configuration since I never dealt with public facing servers (web store). This is what mysqltuner says:

[!!] Maximum reached memory usage: 154.1G (983.67% of installed RAM)
[!!] Maximum possible memory usage: 153.1G (977.27% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[!!] Highest connection usage: 100%  (152/151)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 2B selects)
[!!] Joins performed without indexes: 600679
[!!] Table cache hit rate: 0% (2K open / 3M opened)
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[!!] Read Key buffer hit rate: 90.7% (378 cached / 35 reads)
[!!] InnoDB buffer pool / data size: 1.0G/1.2G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 256.0M * 2/1.0G should be equal to 25%
[!!] InnoDB buffer pool  151)
    wait_timeout ( 1M, or use smaller result sets)
    join_buffer_size (> 2.0M, or always use indexes with JOINs)
    table_open_cache (> 2419)
    innodb_buffer_pool_size (>= 1.2G) if possible.
    innodb_log_file_size should be (=128M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances (=1)

We have made good progress following our [previous question about SQL Server data compression](https://dba.stackexchange.com/questions/332061/how-to-compress-data-in-sql-server-the-data-warehouse-raw-data-is-times-bigger) . For one of the tables, we compressed it from 20 GB to 2 GB, so this proves that the best compression ratio can get up to 10 times. Another table's size, measured as the table's reserved space, reduced from 72.55 GB to 67.06 GB. So, its compression benefit is not enough if compared with the best case above. The same as the previous question, we are working on a data warehouse for a Magento-v2 application, and the table name is msab_magento.sales_order_item. - This table also contains a large number of null values in various columns. - However, this table contains a product_option column in type nvarchar(max) corresponding to the same column in the MySQL source in type text. And this column stores JSON format text data. Per row, the average string length on this column is 4923, or 9 KB as each character takes two bypes in nvarchar. Regarding the JSON column, our tentative thoughts are: - Since SQL Server page-level compression works on the individual pages of size 8 KB each. So, the page size is smaller than the column's size. So, this may affect the compression. - The JSON documents come from the templates in product configuration, filled with specific information per transaction. If the consecutive transactions are not for the same product, their JSON fields will be generated from different templates, and have different keys. This makes it difficult to apply the Dictionary compression algorithm. - Nevertheless, in the global data set, each product will have large number orders, so the JSON keys are still repeating a lot, just maybe across longer range than one page (8 KB)'s size. **Our Question:** - We want to make the data compression even better and need directions. Please also remind us if the bottleneck is on something else other than the JSON column. - We think, the sales_order_item table corresponds to this model in the magento2 repository on GitHub, right? We also need pointers on how to navigate the Magento source code, if possible. Please also refer to the DDL of the table in our data warehouse in the below details and let me know of any questions. We highly appreciate any hints and suggestions. **Details:**

/****** Object:  Table [msab_magento].[sales_order_item]    Script Date: 11/8/2023 5:08:01 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [msab_magento].[sales_order_item](
	[item_id] [bigint] IDENTITY(3233417,1) NOT NULL,
	[order_id] [bigint] NOT NULL,
	[parent_item_id] [bigint] NULL,
	[quote_item_id] [bigint] NULL,
	[store_id] [int] NULL,
	[created_at] [datetime] NOT NULL,
	[updated_at] [datetime] NOT NULL,
	[product_id] [bigint] NULL,
	[product_type] [nvarchar](255) NULL,
	[product_options] [nvarchar](max) NULL,
	[weight] [decimal](12, 4) NULL,
	[is_virtual] [int] NULL,
	[sku] [nvarchar](255) NULL,
	[name] [nvarchar](255) NULL,
	[description] [nvarchar](max) NULL,
	[applied_rule_ids] [nvarchar](max) NULL,
	[additional_data] [nvarchar](max) NULL,
	[is_qty_decimal] [int] NULL,
	[no_discount] [int] NOT NULL,
	[qty_backordered] [decimal](12, 4) NULL,
	[qty_canceled] [decimal](12, 4) NULL,
	[qty_invoiced] [decimal](12, 4) NULL,
	[qty_ordered] [decimal](12, 4) NULL,
	[qty_refunded] [decimal](12, 4) NULL,
	[qty_shipped] [decimal](12, 4) NULL,
	[base_cost] [decimal](12, 4) NULL,
	[price] [decimal](12, 4) NOT NULL,
	[base_price] [decimal](12, 4) NOT NULL,
	[original_price] [decimal](12, 4) NULL,
	[base_original_price] [decimal](12, 4) NULL,
	[tax_percent] [decimal](12, 4) NULL,
	[tax_amount] [decimal](20, 4) NULL,
	[base_tax_amount] [decimal](20, 4) NULL,
	[tax_invoiced] [decimal](20, 4) NULL,
	[base_tax_invoiced] [decimal](20, 4) NULL,
	[discount_percent] [decimal](12, 4) NULL,
	[discount_amount] [decimal](20, 4) NULL,
	[base_discount_amount] [decimal](20, 4) NULL,
	[discount_invoiced] [decimal](20, 4) NULL,
	[base_discount_invoiced] [decimal](20, 4) NULL,
	[amount_refunded] [decimal](20, 4) NULL,
	[base_amount_refunded] [decimal](20, 4) NULL,
	[row_total] [decimal](20, 4) NOT NULL,
	[base_row_total] [decimal](20, 4) NOT NULL,
	[row_invoiced] [decimal](20, 4) NOT NULL,
	[base_row_invoiced] [decimal](20, 4) NOT NULL,
	[row_weight] [decimal](12, 4) NULL,
	[base_tax_before_discount] [decimal](20, 4) NULL,
	[tax_before_discount] [decimal](20, 4) NULL,
	[ext_order_item_id] [nvarchar](255) NULL,
	[locked_do_invoice] [int] NULL,
	[locked_do_ship] [int] NULL,
	[price_incl_tax] [decimal](20, 4) NULL,
	[base_price_incl_tax] [decimal](20, 4) NULL,
	[row_total_incl_tax] [decimal](20, 4) NULL,
	[base_row_total_incl_tax] [decimal](20, 4) NULL,
	[discount_tax_compensation_amount] [decimal](20, 4) NULL,
	[base_discount_tax_compensation_amount] [decimal](20, 4) NULL,
	[discount_tax_compensation_invoiced] [decimal](20, 4) NULL,
	[base_discount_tax_compensation_invoiced] [decimal](20, 4) NULL,
	[discount_tax_compensation_refunded] [decimal](20, 4) NULL,
	[base_discount_tax_compensation_refunded] [decimal](20, 4) NULL,
	[tax_canceled] [decimal](12, 4) NULL,
	[discount_tax_compensation_canceled] [decimal](20, 4) NULL,
	[tax_refunded] [decimal](20, 4) NULL,
	[base_tax_refunded] [decimal](20, 4) NULL,
	[discount_refunded] [decimal](20, 4) NULL,
	[base_discount_refunded] [decimal](20, 4) NULL,
	[free_shipping] [int] NOT NULL,
	[qty_returned] [decimal](12, 4) NOT NULL,
	[gift_message_id] [int] NULL,
	[gift_message_available] [int] NULL,
	[weee_tax_applied] [nvarchar](max) NULL,
	[weee_tax_applied_amount] [decimal](12, 4) NULL,
	[weee_tax_applied_row_amount] [decimal](12, 4) NULL,
	[weee_tax_disposition] [decimal](12, 4) NULL,
	[weee_tax_row_disposition] [decimal](12, 4) NULL,
	[base_weee_tax_applied_amount] [decimal](12, 4) NULL,
	[base_weee_tax_applied_row_amnt] [decimal](12, 4) NULL,
	[base_weee_tax_disposition] [decimal](12, 4) NULL,
	[base_weee_tax_row_disposition] [decimal](12, 4) NULL,
	[gw_id] [int] NULL,
	[gw_base_price] [decimal](12, 4) NULL,
	[gw_price] [decimal](12, 4) NULL,
	[gw_base_tax_amount] [decimal](12, 4) NULL,
	[gw_tax_amount] [decimal](12, 4) NULL,
	[gw_base_price_invoiced] [decimal](12, 4) NULL,
	[gw_price_invoiced] [decimal](12, 4) NULL,
	[gw_base_tax_amount_invoiced] [decimal](12, 4) NULL,
	[gw_tax_amount_invoiced] [decimal](12, 4) NULL,
	[gw_base_price_refunded] [decimal](12, 4) NULL,
	[gw_price_refunded] [decimal](12, 4) NULL,
	[gw_base_tax_amount_refunded] [decimal](12, 4) NULL,
	[gw_tax_amount_refunded] [decimal](12, 4) NULL,
	[event_id] [int] NULL,
	[giftregistry_item_id] [int] NULL,
	[business_area] [nvarchar](max) NULL,
	[custom_options_has_private_data] [smallint] NULL,
	[auto_ship] [smallint] NULL,
 CONSTRAINT [PK_sales_order_item_item_id] PRIMARY KEY CLUSTERED 
(
	[item_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95, DATA_COMPRESSION = PAGE) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0)) FOR [order_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [parent_item_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [quote_item_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [store_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (getdate()) FOR [created_at]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (getdate()) FOR [updated_at]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [product_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [product_type]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [weight]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [is_virtual]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [sku]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [name]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [is_qty_decimal]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0)) FOR [no_discount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [qty_backordered]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [qty_canceled]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [qty_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [qty_ordered]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [qty_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [qty_shipped]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_cost]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [price]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_price]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [original_price]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_original_price]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [tax_percent]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [tax_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_tax_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [tax_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_tax_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [discount_percent]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [discount_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_discount_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [discount_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_discount_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [amount_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_amount_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [row_total]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_row_total]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [row_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_row_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [row_weight]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_tax_before_discount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [tax_before_discount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [ext_order_item_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [locked_do_invoice]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [locked_do_ship]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [price_incl_tax]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_price_incl_tax]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [row_total_incl_tax]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_row_total_incl_tax]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [discount_tax_compensation_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_discount_tax_compensation_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [discount_tax_compensation_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_discount_tax_compensation_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [discount_tax_compensation_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_discount_tax_compensation_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [tax_canceled]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [discount_tax_compensation_canceled]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [tax_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_tax_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [discount_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_discount_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0)) FOR [free_shipping]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [qty_returned]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gift_message_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gift_message_available]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [weee_tax_applied_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [weee_tax_applied_row_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [weee_tax_disposition]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [weee_tax_row_disposition]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_weee_tax_applied_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_weee_tax_applied_row_amnt]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_weee_tax_disposition]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_weee_tax_row_disposition]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_base_price]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_price]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_base_tax_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_tax_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_base_price_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_price_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_base_tax_amount_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_tax_amount_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_base_price_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_price_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_base_tax_amount_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_tax_amount_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [event_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [giftregistry_item_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [custom_options_has_private_data]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0)) FOR [auto_ship]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'msab_magento.sales_order_item' , @level0type=N'SCHEMA',@level0name=N'msab_magento', @level1type=N'TABLE',@level1name=N'sales_order_item'
GO

For reporting purposes, we have a DWH (data warehouse) doing ETLs (extract-transform-load) to retrieve data from selected tables in a production OLTP (on-line transaction processing) database. The ETL extracts data with incremental manner, so it only fetches the changed part of data. Tentatively, we believe this should not affect the size of data as a result. It is a simple mapping, so for the selected tables, DWH has the same columns as OLTP. The DWH is SQL Server and the OLTP database is MySQL. Of course, the MySQL data types need to translate to corresponding types in SQL Server context, and we followed the standard in Microsoft SSMA (SQL Server Migration Assistant) . We noticed that the data became times bigger in SQL Server than in MySQL. For example, in an e-commerce Magento application: - The sales_order table contains 7'100'000 rows with size 5.5GB. - However, in the data warehouse, the same table sized 20GB with the same number of rows.\ Please see a partial table definition below. We checked the SQL Server database, it has SQL_Latin1_General_CP1_CI_AS collation and Simple recovery model. And the MySQL OLTP has default collation latin1_swedish_ci. **Our Questions:** - In our setting, why SQL Server got times bigger than MySQL for the same data? Please kindly point out if we missed something and the DWH can get smaller instead. - The straight mapping between OLTP and DWH was simple to implement, and it worked so far, so good. However, we knew that there are many columns fetched but never used in reporting. So, we wonder if there are better designs or best practices in data warehousing. We highly appreciate any hints and suggestions. **Details of the sample partial table definitions:** 1. MySQL OLTP, also refer to the model in Magento open-source repository :

-- msab_magento.sales_order definition

CREATE TABLE sales_order (
  entity_id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
  state varchar(32) DEFAULT NULL COMMENT 'State',
  status varchar(32) DEFAULT NULL COMMENT 'Status',
  coupon_code varchar(255) DEFAULT NULL COMMENT 'Coupon Code',
  protect_code varchar(255) DEFAULT NULL COMMENT 'Protect Code',
  shipping_description varchar(255) DEFAULT NULL COMMENT 'Shipping Description',
  is_virtual smallint(5) unsigned DEFAULT NULL COMMENT 'Is Virtual',
  store_id smallint(5) unsigned DEFAULT NULL COMMENT 'Store ID',
  customer_id int(10) unsigned DEFAULT NULL COMMENT 'Customer ID',
  base_discount_amount decimal(20,4) DEFAULT NULL COMMENT 'Base Discount Amount',
  base_discount_canceled decimal(20,4) DEFAULT NULL COMMENT 'Base Discount Canceled',
  base_discount_invoiced decimal(20,4) DEFAULT NULL COMMENT 'Base Discount Invoiced',
  base_discount_refunded decimal(20,4) DEFAULT NULL COMMENT 'Base Discount Refunded',
  base_grand_total decimal(20,4) DEFAULT NULL COMMENT 'Base Grand Total',
  base_shipping_amount decimal(20,4) DEFAULT NULL COMMENT 'Base Shipping Amount',
  base_shipping_canceled decimal(20,4) DEFAULT NULL COMMENT 'Base Shipping Canceled',
  base_shipping_invoiced decimal(20,4) DEFAULT NULL COMMENT 'Base Shipping Invoiced',
  base_shipping_refunded decimal(20,4) DEFAULT NULL COMMENT 'Base Shipping Refunded',
  base_shipping_tax_amount decimal(20,4) DEFAULT NULL COMMENT 'Base Shipping Tax Amount',
  base_shipping_tax_refunded decimal(20,4) DEFAULT NULL COMMENT 'Base Shipping Tax Refunded',
  base_subtotal decimal(20,4) DEFAULT NULL COMMENT 'Base Subtotal',
  base_subtotal_canceled decimal(20,4) DEFAULT NULL COMMENT 'Base Subtotal Canceled',
  base_subtotal_invoiced decimal(20,4) DEFAULT NULL COMMENT 'Base Subtotal Invoiced',
  base_subtotal_refunded decimal(20,4) DEFAULT NULL COMMENT 'Base Subtotal Refunded',
  base_tax_amount decimal(20,4) DEFAULT NULL COMMENT 'Base Tax Amount',
  base_tax_canceled decimal(20,4) DEFAULT NULL COMMENT 'Base Tax Canceled',
  base_tax_invoiced decimal(20,4) DEFAULT NULL COMMENT 'Base Tax Invoiced',
  base_tax_refunded decimal(20,4) DEFAULT NULL COMMENT 'Base Tax Refunded',
  base_to_global_rate decimal(20,4) DEFAULT NULL COMMENT 'Base To Global Rate',
  base_to_order_rate decimal(20,4) DEFAULT NULL COMMENT 'Base To Order Rate',
  base_total_canceled decimal(20,4) DEFAULT NULL COMMENT 'Base Total Canceled',
  base_total_invoiced decimal(20,4) DEFAULT NULL COMMENT 'Base Total Invoiced',
  base_total_invoiced_cost decimal(20,4) DEFAULT NULL COMMENT 'Base Total Invoiced Cost',
  base_total_offline_refunded decimal(20,4) DEFAULT NULL COMMENT 'Base Total Offline Refunded',
  base_total_online_refunded decimal(20,4) DEFAULT NULL COMMENT 'Base Total Online Refunded',
  base_total_paid decimal(20,4) DEFAULT NULL COMMENT 'Base Total Paid',
  base_total_qty_ordered decimal(12,4) DEFAULT NULL COMMENT 'Base Total Qty Ordered',
  base_total_refunded decimal(20,4) DEFAULT NULL COMMENT 'Base Total Refunded',
  discount_amount decimal(20,4) DEFAULT NULL COMMENT 'Discount Amount',
  discount_canceled decimal(20,4) DEFAULT NULL COMMENT 'Discount Canceled',
  discount_invoiced decimal(20,4) DEFAULT NULL COMMENT 'Discount Invoiced',
  discount_refunded decimal(20,4) DEFAULT NULL COMMENT 'Discount Refunded',
  grand_total decimal(20,4) DEFAULT NULL COMMENT 'Grand Total',
  shipping_amount decimal(20,4) DEFAULT NULL COMMENT 'Shipping Amount',
  shipping_canceled decimal(20,4) DEFAULT NULL COMMENT 'Shipping Canceled',
  shipping_invoiced decimal(20,4) DEFAULT NULL COMMENT 'Shipping Invoiced',
  shipping_refunded decimal(20,4) DEFAULT NULL COMMENT 'Shipping Refunded',
  shipping_tax_amount decimal(20,4) DEFAULT NULL COMMENT 'Shipping Tax Amount',
  shipping_tax_refunded decimal(20,4) DEFAULT NULL COMMENT 'Shipping Tax Refunded',
  store_to_base_rate decimal(12,4) DEFAULT NULL COMMENT 'Store To Base Rate',
  store_to_order_rate decimal(12,4) DEFAULT NULL COMMENT 'Store To Order Rate',
  subtotal decimal(20,4) DEFAULT NULL COMMENT 'Subtotal',
  subtotal_canceled decimal(20,4) DEFAULT NULL COMMENT 'Subtotal Canceled',
  subtotal_invoiced decimal(20,4) DEFAULT NULL COMMENT 'Subtotal Invoiced',
  subtotal_refunded decimal(20,4) DEFAULT NULL COMMENT 'Subtotal Refunded',
  tax_amount decimal(20,4) DEFAULT NULL COMMENT 'Tax Amount',
  tax_canceled decimal(20,4) DEFAULT NULL COMMENT 'Tax Canceled',
  tax_invoiced decimal(20,4) DEFAULT NULL COMMENT 'Tax Invoiced',
  tax_refunded decimal(20,4) DEFAULT NULL COMMENT 'Tax Refunded',
  total_canceled decimal(20,4) DEFAULT NULL COMMENT 'Total Canceled',
  total_invoiced decimal(20,4) DEFAULT NULL COMMENT 'Total Invoiced',
  total_offline_refunded decimal(20,4) DEFAULT NULL COMMENT 'Total Offline Refunded',
  total_online_refunded decimal(20,4) DEFAULT NULL COMMENT 'Total Online Refunded',
  total_paid decimal(20,4) DEFAULT NULL COMMENT 'Total Paid',
  total_qty_ordered decimal(12,4) DEFAULT NULL COMMENT 'Total Qty Ordered',
  total_refunded decimal(20,4) DEFAULT NULL COMMENT 'Total Refunded',
  can_ship_partially smallint(5) unsigned DEFAULT NULL COMMENT 'Can Ship Partially',
  can_ship_partially_item smallint(5) unsigned DEFAULT NULL COMMENT 'Can Ship Partially Item',
  customer_is_guest smallint(5) unsigned DEFAULT NULL COMMENT 'Customer Is Guest',
  customer_note_notify smallint(5) unsigned DEFAULT NULL COMMENT 'Customer Note Notify',
  billing_address_id int(11) DEFAULT NULL COMMENT 'Billing Address ID',
  customer_group_id int(11) DEFAULT NULL,
	...
  reward_points_balance_refund int(11) DEFAULT NULL COMMENT 'Reward Points Balance Refund',
  PRIMARY KEY (entity_id),
  UNIQUE KEY SALES_ORDER_INCREMENT_ID_STORE_ID (increment_id,store_id),
  KEY SALES_ORDER_STATUS (status),
  KEY SALES_ORDER_STATE (state),
  KEY SALES_ORDER_STORE_ID (store_id),
  KEY SALES_ORDER_CREATED_AT (created_at),
  KEY SALES_ORDER_CUSTOMER_ID (customer_id),
  KEY SALES_ORDER_EXT_ORDER_ID (ext_order_id),
  KEY SALES_ORDER_QUOTE_ID (quote_id),
  KEY SALES_ORDER_UPDATED_AT (updated_at),
  KEY SALES_ORDER_SEND_EMAIL (send_email),
  KEY SALES_ORDER_EMAIL_SENT (email_sent),
  CONSTRAINT SALES_ORDER_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID FOREIGN KEY (customer_id) REFERENCES customer_entity (entity_id) ON DELETE SET NULL,
  CONSTRAINT SALES_ORDER_STORE_ID_STORE_STORE_ID FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=71xxxxx DEFAULT CHARSET=utf8 COMMENT='Sales Flat Order';

2. SQL Server DWH, generated by Microsoft SSMA for MySQL:

/****** Object:  Table [msab_magento].[sales_order]    Script Date: 10/11/2023 3:17:43 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [msab_magento].[sales_order](
	[entity_id] [bigint] IDENTITY(2956088,1) NOT NULL,
	[state] [nvarchar](32) NULL,
	[status] [nvarchar](32) NULL,
	[coupon_code] [nvarchar](255) NULL,
	[protect_code] [nvarchar](255) NULL,
	[shipping_description] [nvarchar](255) NULL,
	[is_virtual] [int] NULL,
	[store_id] [int] NULL,
	[customer_id] [bigint] NULL,
	[discount_amount] [decimal](20, 4) NULL,
	[discount_canceled] [decimal](20, 4) NULL,
	[discount_invoiced] [decimal](20, 4) NULL,
	[discount_refunded] [decimal](20, 4) NULL,
	[grand_total] [decimal](20, 4) NULL,
	[shipping_amount] [decimal](20, 4) NULL,
	[shipping_canceled] [decimal](20, 4) NULL,
	[shipping_invoiced] [decimal](20, 4) NULL,
	[shipping_refunded] [decimal](20, 4) NULL,
	[shipping_tax_amount] [decimal](20, 4) NULL,
	[shipping_tax_refunded] [decimal](20, 4) NULL,
	[store_to_base_rate] [decimal](12, 4) NULL,
	[store_to_order_rate] [decimal](12, 4) NULL,
	[subtotal] [decimal](20, 4) NULL,
	[subtotal_canceled] [decimal](20, 4) NULL,
	[subtotal_invoiced] [decimal](20, 4) NULL,
	[subtotal_refunded] [decimal](20, 4) NULL,
	[tax_amount] [decimal](20, 4) NULL,
	[tax_canceled] [decimal](20, 4) NULL,
	[tax_invoiced] [decimal](20, 4) NULL,
	[tax_refunded] [decimal](20, 4) NULL,
	[total_canceled] [decimal](20, 4) NULL,
	[total_invoiced] [decimal](20, 4) NULL,
	[total_offline_refunded] [decimal](20, 4) NULL,
	[total_online_refunded] [decimal](20, 4) NULL,
	[total_paid] [decimal](20, 4) NULL,
	[total_qty_ordered] [decimal](12, 4) NULL,
	[total_refunded] [decimal](20, 4) NULL,
	[can_ship_partially] [int] NULL,
	[can_ship_partially_item] [int] NULL,
	[customer_is_guest] [int] NULL,
	[customer_note_notify] [int] NULL,
	[billing_address_id] [int] NULL,
	[customer_group_id] [int] NULL,
	[edit_increment] [int] NULL,
	...
	[shipping_incl_tax] [decimal](20, 4) NULL,
 CONSTRAINT [PK_sales_order_entity_id] PRIMARY KEY CLUSTERED 
(
	[entity_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY],
 CONSTRAINT [sales_order$SALES_ORDER_INCREMENT_ID_STORE_ID] UNIQUE NONCLUSTERED 
(
	[increment_id] ASC,
	[store_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [msab_magento].[sales_order] ADD  DEFAULT (NULL) FOR [state]
GO

ALTER TABLE [msab_magento].[sales_order] ADD  DEFAULT (NULL) FOR [status]
GO

...

ALTER TABLE [msab_magento].[sales_order] ADD  DEFAULT (NULL) FOR [shipping_incl_tax]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'msab_magento.sales_order' , @level0type=N'SCHEMA',@level0name=N'msab_magento', @level1type=N'TABLE',@level1name=N'sales_order'
GO

Sometimes I get issue with connetion to database. I checked and when I open product then mysql CPU is 180 - 330% CPU. Does anyone know why this is happening and what it might be? Magento 2 platform

I have a master-master replication setup between two different servers using MariaDB. One is the primary server (server A) that is connected to my web applications, and the other is a replica that would be used for disaster recovery if the primary one was down (server B). Awhile back I shut down server B rather abruptly for about 5 minutes so that I could resize it, but when I brought it back up, I received an SQL integrity constraint violation error that prevented replication from starting back up. I ended up rebuilding server B from a backup of server A later down the road and started replication back up successfully. My question is: Why would this connection issue have caused an integrity constraint error? I thought that it would be able to simply pick up from the log file position that it stopped reading from. I’m trying to figure out so that, in the future, if server B loses connection for whatever reason, that I’m able to recover from it instead of having to rebuild the database.

Database is from Magento 2's site. Issue suddenly started to occur on our live site, dev site has the same database, but just with older data and same query is running completely fine there. I've also tried dumping those three tables from dev and imported them to live dump, but the issue persists. I've found out with EXPLAIN that the query suddenly stopped using index for catalog_product_website table, there are two possible keys to use and it suddenly selects none of them. This is from the live database, which is problematic. +------+-------------+-----------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+----------------------------------+-------+--------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-----------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+----------------------------------+-------+--------------------------------------------------------------+ | 1 | SIMPLE | e | ALL | PRIMARY | NULL | NULL | NULL | 97344 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | at_status | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | magento2.e.entity_id,const,const | 1 | | | 1 | SIMPLE | at_visibility | ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | 4 | const,const | 1 | Using where | | 1 | SIMPLE | product_website | range | PRIMARY,CATALOG_PRODUCT_WEBSITE_WEBSITE_ID | CATALOG_PRODUCT_WEBSITE_WEBSITE_ID | 2 | NULL | 97960 | Using where; Using index; Using join buffer (flat, BNL join) | | 1 | SIMPLE | at_status_default | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | magento2.e.entity_id,const,const | 1 | | | 1 | SIMPLE | at_visibility_default | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | magento2.e.entity_id,const,const | 1 | | +------+-------------+-----------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+----------------------------------+-------+--------------------------------------------------------------+ And this if from the dev database, which runs that same query just fine, like live one did before: +------+-------------+-----------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+---------------------------------+-------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-----------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+---------------------------------+-------+-----------------------------+ | 1 | SIMPLE | e | ALL | PRIMARY | NULL | NULL | NULL | 94528 | Using where; Using filesort | | 1 | SIMPLE | at_status_default | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | magento2.e.entity_id,const,const | 1 | | | 1 | SIMPLE | at_status | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | magento2.e.entity_id,const,const | 1 | | | 1 | SIMPLE | at_visibility | ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | 4 | const,const | 1 | Using where | | 1 | SIMPLE | at_visibility_default | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | magento2.e.entity_id,const,const | 1 | | | 1 | SIMPLE | product_website | eq_ref | PRIMARY,CATALOG_PRODUCT_WEBSITE_WEBSITE_ID | PRIMARY | 6 | magento2.e.entity_id,const | 1 | Using index | +------+-------------+-----------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+---------------------------------+-------+-----------------------------+ I've tried ANALYZE and OPTIMIZE on all of those tables, but unfortunately no help from there. Haven't found any structural changes for those tables, everything seems to be same. What could be causing this issue? Query is automatically created by Magento. This particular query is not the only problematic one. Other ones that use those three tables and same logic seem to suffer from the same issue. Sample query: SELECT e.*, IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS status, IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) AS visibility FROM catalog_product_entity AS e INNER JOIN catalog_product_entity_int AS at_status_default ON (at_status_default.entity_id = e.entity_id) AND ( at_status_default.attribute_id = '97' ) AND at_status_default.store_id = 0 LEFT JOIN catalog_product_entity_int AS at_status ON (at_status.entity_id = e.entity_id) AND ( at_status.attribute_id = '97' ) AND ( at_status.store_id = 2 ) INNER JOIN catalog_product_entity_int AS at_visibility_default ON (at_visibility_default.entity_id = e.entity_id) AND ( at_visibility_default.attribute_id = '99' ) AND at_visibility_default.store_id = 0 LEFT JOIN catalog_product_entity_int AS at_visibility ON (at_visibility.entity_id = e.entity_id) AND ( at_visibility.attribute_id = '99' ) AND ( at_visibility.store_id = 2 ) INNER JOIN catalog_product_website AS product_website ON product_website.product_id = e.entity_id AND product_website.website_id IN ( 2 ) WHERE ( (e.created_at > '2021-01-01 00:00:00') ) ORDER BY e.created_at ASC LIMIT 50

I am trying to run query in Magento 2 Db and getting the error: >Column 'product_id' in order clause is ambiguous, The query is: SELECT main_table.* FROM wishlist_item AS main_table INNER JOIN cataloginventory_stock_status AS stockItem ON stockItem.product_id = main_table.product_id AND stockItem.stock_status = 1 INNER JOIN catalog_category_product_index_store1 AS cat_index ON cat_index.product_id = main_table.product_id AND cat_index.category_id = '2' AND cat_index.visibility IN (3, 2, 4) WHERE (wishlist_id = '1') AND (main_table.store_id IN('1')) ORDER BY find_in_set(product_id,'1,2,3'), name ASC LIMIT 12 How to fix this?

Im running my magento 2 project on 5 core 10gb ram VM. I did some benchmarks and i see that my DB queries take quite some time to execute. Traffic on this site is almost zero. Mysql is running on 5.7.31-34 Percona Server. Database size 1.7gb. Can anyone spot some obvious mistakes and give me some suggestions ? 2947 queries in 1,057.20ms (average time: 0.36ms) - 2788 queries/second 2912 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION 2618 queries in 1,132.66ms (average time: 0.43ms) - 2311 queries/second 2593 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION 2915 queries in 1,213.47ms (average time: 0.42ms) - 2402 queries/second 2878 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION 620 queries in 235.52ms (average time: 0.38ms) - 2633 queries/second 610 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION 3214 queries in 1,930.16ms (average time: 0.60ms) - 1665 queries/second 3179 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION At moment my.cnf is ... [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql explicit_defaults_for_timestamp ### MyISAM # key_buffer_size = 16M myisam-recover-options = FORCE,BACKUP ### SAFETY # innodb = force max_allowed_packet = 250M max_connect_errors = 100000 bind-address = 127.0.0.1 skip-name-resolve ### LANGUAGE # #init_connect='SET collation_connection = utf8_unicode_ci' #init_connect='SET NAMES utf8' #character-set-server=utf8 #collation-server=utf8_unicode_ci #skip-character-set-client-handshake ### CACHES AND LIMITS # back_log = 20 interactive_timeout = 7200 wait_timeout = 7200 net_read_timeout = 120 net_write_timeout = 300 sort_buffer_size = 8M read_buffer_size = 8M read_rnd_buffer_size = 2M join_buffer_size = 16M tmp_table_size = 512M max_heap_table_size = 512M query_cache_type = 1 query_cache_size = 128M max_connections = 60 thread_cache_size = 32 thread_pool_size = 16 open_files_limit = 65535 table_definition_cache = 8000 table_open_cache = 10000 ### INNODB_ # innodb_thread_concurrency = 0 innodb_lock_wait_timeout = 7200 innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_commit = 2 innodb_log_files_in_group = 2 innodb_log_file_size = 512M innodb_log_buffer_size = 64M innodb_file_per_table = 1 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_buffer_pool_instances = 2 innodb_buffer_pool_size = 2000M EDIT: Server is using SSD drives. SHOW GLOBAL STATUS https://pastebin.com/raw/KqGjHwjA SHOW GLOBAL VARIABLES https://pastebin.com/raw/97TnapvB SHOW FULL PROCESS LIST https://pastebin.com/raw/E3HQ6HUE SHOW ENGINE INNODB STATUS https://pastebin.com/raw/JWKYQe3w ulimit -a https://pastebin.com/raw/ew4XpCud iostat -xm 5 3 https://pastebin.com/raw/KJxSfnq7 htop

This is a Magento application connected to MySQL Db. When I ran show engine innodb status\GI get a list of TRANSACTIONSand it looks like some of them are pending and others are in a DEADLOCKand it doesn't matter if I kill them, they keep coming back. What can I do to prevent that? Help! Here's the log: ---TRANSACTION 284071526856416, not started estimating records in index range mysql tables in use 10, locked 0 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 284071526852056, not started estimating records in index range mysql tables in use 10, locked 0 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 284071526902632, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 284071526853800, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 284071526845080, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 39922654, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 1 lock struct(s), heap size 1136, 0 row lock(s) MySQL thread id 1654, OS thread handle 6724, query id 46131 localhost ::1 root updating UPDATE log_visitor SET session_id = 'imtbh6fl9rldnoi44vjnmepf70', first_visit_at = '2020-01-14 19:49:19', last_visit_at = '2020-01-14 19:49:27', last_url_id = '0', store_id = '1' WHERE (visitor_id='1763150') ---TRANSACTION 39922653, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 1669, OS thread handle 5184, query id 46128 localhost ::1 root updating UPDATE log_visitor SET session_id = 'fvobv8l301a79ece0006vot6a5', first_visit_at = '2020-01-14 19:49:23', last_visit_at = '2020-01-14 19:49:27', last_url_id = '0', store_id = '1' WHERE (visitor_id='1763153') ---TRANSACTION 39922646, ACTIVE 2 sec updating or deleting mysql tables in use 1, locked 1 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 1623, OS thread handle 5048, query id 46079 localhost ::1 root updating UPDATE core_session SET session_expires = '1579034965', session_data = '_secure_cookie_check|s:32:\"919e25a2e43969e5d6bfbede7bb4e55d\";core|a:5:{s:23:\"_session_validator_data\";a:4:{s:11:\"remote_addr\";s:14:\"159.138.159.72\";s:8:\"http_via\";s:0:\"\";s:20:\"http_x_forwarded_for\";s:0:\"\";s:15:\"http_user_agent\";s:166:\"Mozilla/5.0(Linux;Android 5.1.1;OPPO A33 Build/LMY47V;wv) AppleWebKit/537.36(KHTML,link Gecko) Version/4.0 Chrome/42.0.2311.138 Mobile Safari/537.36 Mb2345Browser/9.0\";}s:12:\"visitor_data\";a:15:{s:0:\"\";N;s:11:\"server_addr\";s:4:\"\n\0\";s:11:\"remote_addr\";s: ---TRANSACTION 39922645, ACTIVE 2 sec updating or deleting mysql tables in use 1, locked 1 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 1624, OS thread handle 6820, query id 46078 localhost ::1 root updating UPDATE core_session SET session_expires = '1579034965', session_data = '_secure_cookie_check|s:32:\"d1e4c0600c606a203e398f2844d33ada\";core|a:5:{s:23:\"_session_validator_data\";a:4:{s:11:\"remote_addr\";s:15:\"159.138.153.198\";s:8:\"http_via\";s:0:\"\";s:20:\"http_x_forwarded_for\";s:0:\"\";s:15:\"http_user_agent\";s:166:\"Mozilla/5.0(Linux;Android 5.1.1;OPPO A33 Build/LMY47V;wv) AppleWebKit/537.36(KHTML,link Gecko) Version/4.0 Chrome/42.0.2311.138 Mobile Safari/537.36 Mb2345Browser/9.0\";}s:12:\"visitor_data\";a:15:{s:0:\"\";N;s:11:\"server_addr\";s:4:\"\n\0\";s:11:\"remote_addr\";s

We have a situation where we encounter a MySQL error > **ERROR 1069:** Too many keys specified. Max 64 keys allowed In our database (using Magento E-commerce) we need to add 76 indexed key columns but we are not allowed to add more than 64 keys, which is the maximum limit allowed. So we have decided to increase the maximum limit to 80 by recompiling the MySQL / MariaDB, in that case what would be the implications ? Kindly let me know if you need more server resource details. Thank you !

I have a Magento 1.7 version site with 7000 configurable products. Site navigation is slow. From the moment I click on a site link to when the address in the url bar changes several seconds pass. Configuring the file my.cnf I have had improvements. But I think the configuration is not optimal. What do you suggest to edit in my my.cnf file considering that I have a VPS with the following features? CPU: ten cores Intel® Xeon® E5-2620v3, E5-2630v4 or 4114 processor 50 GB RAM (guaranteed) 1200 GB disk space (100% SSD) 100% SSD disk space UNLIMITED traffic! 1 Gbit / s port DDoS protection (details) This is my.cnf configuration: #query_cache_size = 50G #table_open_cache = 2000 #innodb_adaptive_flushing = 0 #innodb_adaptive_hash_index = 0 #innodb_autoextend_increment = 1000 #innodb_buffer_pool_instances = 64 #innodb-defragment=1 open_files_limit = 2000 innodb_io_capacity = 2000 innodb_read_io_threads = 64 innodb_thread_concurrency = 0 innodb_write_io_threads = 64 innodb_buffer_pool_size = 5G innodb_thread_concurrency = 20 innodb_flush_log_at_trx_commit = 2 thread_concurrency = 30 table_open_cache = 2000 thread_cache_size = 32 #table_cache = 2000 query_cache_size = 30M query_cache_limit = 1G join_buffer_size = 3G tmp_table_size = 2G key_buffer = 70M innodb_autoextend_increment=512 max_allowed_packet = 1G max_heap_table_size = 1G read_buffer_size = 50M read_rnd_buffer_size = 50M bulk_insert_buffer_size = 50M myisam_sort_buffer_size = 50M myisam_max_sort_file_size = 10G #myisam_max_extra_sort_file_size = 5 myisam_repair_threads = 1 innodb_buffer_pool_instances = 5

We have a MySQL Master Instance & Read (failover only) on AWS. MySQL 5.6 Aurora running Magento. Performance is great, and better than standard MySQL (we have high traffic site so see the benefits). We suffer some issues with when Magento Indexing happens even under pretty low loads. 1. Truncate functions happen on some key table one of them being: TRUNCATE TABLE catalog_product_index_group_price 2. When the above happens other queries build up / bottleneck in the process. The state changes to: "checking permissions" and they hold until the truncate is complete 3. Its fairly fast, but what it does is slows/blocks the website for a second or 2 when the site is under load. 4. No foreign key constraints on the table; catalog_product_index_group_price 5. We have query_cache_limit set to 4mb (which has helped queries perform better). Looking in Performance Schema we are trying to see what could possible be holing this up with no luck. Having read a lot of about 5.6-5.7. We have read statements like: "Fixed as of the upcoming 5.7.18, 8.0.1 release, and here's the changelog entry: A TRUNCATE TABLE operation held the dict_sys mutex while scanning for and removing pages from the buffer pool, causing concurrent DDL operations to stall. The mutex is now released during the scan and acquired again when the scan is completed. " Does anyone have any ideas or insight into the above. Especially being able to verify this behaviour. We are also going to log ticket in with AWS too.

I am writing about a problem on the character encoding that i have on my site with Magento. In practice, the special characters do not take utf8 and gives me the classic non-coded characters: Ã. These are the codes in my database: character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server latin1 character_set_system utf8 My question is ... the problem of encoding is caused by character_set_server latin1 string? How do I convert my database character_set_server latin1 to utf8? It can be a very simple Query? Thanks so much

Does the version of MySql Library have the same impact as the version of MySql server when it comes to other software requirements? Example: I run Magento 1.9.3, which specifies MySQL 5.6 as a minimum requirement. If my web server installation is running MySql Library of an older version (5.1.73), what problems will this cause, if any? I can't seem to find any definitive answer out there regarding this detail?

I just started using Magento with 1500 products and I saw the DB size is about 150MB and when I looked at the tables, the count is 250 tables. Is it normal? Should I worry about anything? I host this on EC2 micro instance.