Percona Help

MySQL - Percona Edition.

Full table scan is taking over 1 second and crashing our backend servers, the table has about 1.2 million records, if the IP is located then it returns very quickly and has no issues, eventually we hit max connections on the server if it can't find it as it does a full table scan. This server is beefy, 128GB of ram + 800GB SSD and dual hexacore processors

SELECT *
  FROM geoip_proxy_ipv4_09_2018
  WHERE INET_ATON('192.168.0.1') BETWEEN ip_from AND ip_to
  LIMIT 1

Open in new window

The table structure is this:

CREATE TABLE IF NOT EXISTS `geoip_proxy_ipv4_09_2018` (
  `ip_from` decimal(10,0) NOT NULL,
  `ip_to` decimal(10,0) NOT NULL,
  `proxy_type` varchar(3) NOT NULL,
  `country_code` varchar(2) NOT NULL,
  `country_name` varchar(128) NOT NULL,
  `region_name` varchar(128) NOT NULL,
  `city_name` varchar(128) NOT NULL,
  UNIQUE KEY `idx_ip_from_ip_to` (`ip_from`,`ip_to`),
  KEY `ip_to` (`ip_to`),
  KEY `ip_from` (`ip_from`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Open in new window

These are the indexes:

Indexes
Keyname	Type	Unique	Packed	Column	Cardinality	Collation	Null	Comment
idx_ip_from_ip_to	BTREE	Yes	No	ip_from	1254839	A	No	
ip_to	1254839	A	No
ip_to	BTREE	No	No	ip_to	1254839	A	No	
ip_from	BTREE	No	No	ip_from	1254839	A	No

Open in new window

If we do 800 requests per second (lookups) our database server and our backend servers max out their connections waiting for response from backend server... thoughts?

6 Comments

Question by

Mario Bernheim

2018-09-25SolvedPrivate

Optimize query/table for speed MySQL?

Need to make our MySQL queries faster on a large table (1544980 total records)

SELECT id,sender,recipient,message,priority,flash_message
FROM movistar_mt3_saca_nic
WHERE process=0
ORDER BY priority DESC, id ASC
LIMIT 10

Open in new window

its a transactional table, we select 10 numbers and try to bill them within 1 second for services and then they get updated with the bill status so they dont get billed twice...
a timer then selects another set of 10 numbers... the query is taking 3.5seconds...

the structure of the table is this:

CREATE TABLE IF NOT EXISTS `movistar_mt3_saca_nic` (
  `id` bigint(14) NOT NULL AUTO_INCREMENT,
  `batch_id` varchar(20) NOT NULL,
  `sender` varchar(50) NOT NULL,
  `recipient` varchar(50) NOT NULL,
  `message` varchar(180) NOT NULL,
  `sequence_number` int(11) NOT NULL DEFAULT '0',
  `command_status` varchar(11) DEFAULT NULL,
  `message_id` varchar(255) DEFAULT NULL,
  `done_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `service` bigint(14) DEFAULT NULL,
  `send_time` datetime(3) DEFAULT NULL,
  `confirm_time` datetime(3) DEFAULT NULL,
  `sequence_sending` int(11) NOT NULL DEFAULT '1',
  `process` int(11) NOT NULL DEFAULT '0',
  `priority` int(11) NOT NULL DEFAULT '0',
  `flash_message` int(11) NOT NULL DEFAULT '0',
  `transaction_id` varchar(50) DEFAULT NULL,
  `delivery_number` int(11) DEFAULT '1',
  `part_number` int(11) DEFAULT '1',
  `qty_attempts` int(11) DEFAULT '0',
  `product_id` varchar(50) DEFAULT NULL,
  PRIMARY

Open in new window

…

14 Comments

We have to serve 50,000,000,000 requests per day, our app can handle that, but we're not sure if our database skills are there to pick this up. We have a database server with 256gb of ram and 512GB SSD RAID using percona edition. Some of the data needs to stay on the database for 30 days, the transaction table where all requests are stored. what approach do we need to scale this horizontally? each endpoint requests runs about 4 queries...

13 Comments

I have found that this Dell PowerEdge T110 II that has two disks with Raid 1 configuration seems that one of the disk has failed. I have screen shots that I took from OpenManage Server Configuration for you to review. I believe that Disk 0:1 is the one has failed and I must first assigned it to be Global Hot spare and then re create the Raid1. Please review the attached file and advise if this is how I have to do it either from here or from Raid Bios.

Regards,
Change 2009
Fig-1

Fig-2

Fig-3

Fig-4

Fig-5

4--Physical-Disks-On-PERC-S100.JPG

5 Comments

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

…

Percona

Related Topics

Percona

Related Topics

Percona News