Percona

5

Solutions

20

Content

i
The collection of articles, videos, and posts on this topic.

23

Contributors

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).

Share tech news, updates, or what's on your mind.

Sign up to Post

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?
0
6 Comments
Become a Microsoft Certified Solutions Expert
LVL 13
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Learn More!
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

…
0
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...
0
13 Comments
Task availableI 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
Storage Dashboard

Fig-2
Health
Fig-3
Connector
Fig-4
Task available
Fig-5

Task available4--Physical-Disks-On-PERC-S100.JPG
0
5 Comments
MySQL Hot backup for 60GB database.

First I've read that mysql dump is can be slower than another enterprise app so that is where I'm exploring now.  
Trying to find a solution for a hot backup of a 60GB database.  It needs to be encrypted because of sensitive data so looking for the software to use, commands, and tips.

MySQL Enterprise Backup - Is what I'm looking at 30 day free trial but cannot find how much it cost?
If I wanted this recoverable up to the closet time like 15 minutes/1hour how can that be acomplished?  Which command?

I've also seen Percona XtraBackup 2.4.8 but seems to be Linux based and I would have to spin up and pay for another Linux server.  I know the db is small but the nightly backups we take would be bad if I could recover 23 hours ago.  I need a smaller window.  

Also since the DB is so small is it faster to backup to same server and then have an ftp script send or copy it somewhere else.
The fear is with this application it says there are reasons for a table to become corrupt.  So  I also won't know the time it does become corrupt is backing up every hour or every change just going to overwrite that?  How do I achoomplish the simple goal of Daister Recovery up to the minute of incident.  If it is a corrupt table or an entire database that needs to be restored for some reasons.
0
7 Comments

Percona

5

Solutions

20

Content

i
The collection of articles, videos, and posts on this topic.

23

Contributors

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
>
Hall of FameAnnual Expert Awards