Hard disk – calculate the RAID 5/6 recovery time

I plan to create a storage scheme, but expect downtime, maintenance, and recovery times.

Some people told me that a 10-disk RAID6 of 10To (SATA) will last for about a week! Some company policies require that activity on the array be stopped when it is rebuilt.

If I use RAID 5 or 6 or 5 + 1 or 6 + 1, there is an approximate formula that gives an indication of the recovery time depending on the size and type of hard drive (SAS / SATA / SSD).
something like rpm x size (Mb) x type factor x number of disks …

I would like to be able to anticipate the entire recovery time scenario depending on the size / type of the RAID / type of hard drive.

I know it may depend on the hardware quality, but let's say I do not have dedicated hardware like 3PAR / STOREWIZE / NETAPP or anything like that. I use traditional servers with traditional SAS or SATA drives with software RAID.

innodb – Query returns no results when selected by indexed columns (Mysql 5.6)

We had a very strange encounter that all our queries using an indexed column in select return no results. On the other hand, if we do that choose * or on another non-indexed column we get the expected results.

For example,

SELECT product_id FROM product where product_id = 1; 

returns no results.

But if we do that:

SELECT * FROM product, where product_id = 1;

We receive the expected results.

The table engine used to be MyISAM. But when we switched to InnoDB, we started with these issues. We had to change it back to MyISAM because many websites were inactive because of the above problem.

The server specifications:

  • Ubuntu 16.04 LTS
  • MySQL 5,6
  • 27 GB RAM
  • 300 GB of free space when using 100 GB

my.cnf:

  1. innodb_log_file_size = 3G
  2. innodb_buffer_pool_instances = 21
  3. key_buffer_size = 500M
  4. innodb_buffer_pool_size = 22G
  5. sort_buffer_size = 512K
  6. read_rnd_buffer_size = 512K
  7. thread_cache_size = 9
  8. thread_cache_size = 9
  9. innodb_file_per_table = 1
  10. innodb_buffer_pool_dump_at_shutdown = 1
  11. innodb_buffer_pool_dump_at_shutdown = 1

We had no errors on the application side and no error logs in MySQL. The server had 6 GB of free memory, so the memory did not go out. The above recommendations were made by mysqltunner.pl

Results of Mysqltunner:

[OK]    Slow queries: 0%    
[OK] Thread cache hit rate: 93%        
[OK] Table cache hit rate: 95%     
[OK] Open file limit used: 0%       
[OK] InnoDB file per table is enabled        
[OK] InnoDB buffer pool / data size: 20.0 G / 21.3 G        
[OK] Ratio InnoDB log file size / InnoDB buffer pool size: 3G * 2 / 20G should be equal to 25%        
[OK] InnoDB buffer pool instances: 21       
[--] InnoDB Buffer Pool Chunk Size is not used or defined in your version
[OK] InnoDB Read Buffer Efficiency: 99.95%        
[OK] InnoDB Write Log Efficiency: 93%    
[OK] InnoDB protocol is waiting: 0.00% (0 waiting times / 68 writes)        

Problems with Query Caching under MySQL 5.6?

I recently discovered that our MySQL 5.6 database does not use query caching, and I wanted to change that.

I added

[mysqld]



query_cache_type = 1
query_cache_size = 50M
query_cache_limit = 2M

into a file called my-default.ini, located below C: Program Files MySQL MySQL Server 5.6, I could not find another INI file or .CNF file in any directory, and that computer has none C: ProgramData Directory where I would normally find it.

To run VARIABLES LIKE & # 39; have_query_cache & # 39; Show show it have_query_cache YES, But SHOW VARIABLES LIKEquery_cache_typeshowsOUT, AndSHOW STATUS LIKE & # 39; qcache% & # 39; `indicates all zeroes.

Is it possible that my my.cnf is missing or deleted? Do I have to recreate or rename my-default.ini to my.ini? I think maybe a colleague changed or moved the file name without knowing what it might have been.

How can I ensure at the end of the day that query caching is enabled on my MySQL 5.6 server?

MySQL join significantly underperformed after upgrading from 5.6 to 5.7

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

SELECT DISTINCT
Name, d.id, deviceType, issueBy, description, avNum, CompanyName,
BrandName, 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 the upgrade to 5.7, it takes over 30 minutes. Each table has about 2 million lines.

I started comparing the optimizer settings between 5.6 and 5.7:

# 5.6
index_merge = on
index_merge_union = on
index_merge_sort_union = on
index_merge_intersection = on
engine_condition_pushdown = on
index_condition_pushdown = on
mrr = on
mrr_cost_based = on
block_nested_loop = on
batched_key_access = off
Materialization = on
Semijoin = one
loosescan = a
firstmatch = a
subquery_materialization_cost_based = a
use_index_extensions = on
# 5.7
Optimizer settings in 5.7:
index_merge = on
index_merge_union = on
index_merge_sort_union = on
index_merge_intersection = on
engine_condition_pushdown = on
index_condition_pushdown = on
mrr = on
mrr_cost_based = on
block_nested_loop = on
batched_key_access = off
Materialization = on
Semijoin = one
loosescan = a
firstmatch = a
subquery_materialization_cost_based = a
use_index_extensions = on
condition_fanout_filter = on
derivative_merge = on
duplicateweedout = on

The only changes I see were the last three options in 5.7. I switched it off as follows:

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

This had no effect. Next, I investigated the differences in my Innodb settings (my 5.7 instance has more memory, hence the differences in pool size).

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

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

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

Also no effect.

I hope someone can tell me what's going on here. The query is part of a larger workflow that has become unusable. I would like to regain the behavior of 5.6, if possible, but due to my limited knowledge of DBA, things go out of my way to try. I hope someone can point me in the right direction or give me additional opportunities to investigate.

Below are my tables. Many columns have large widths and are usually 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 use latin1 while handling the slow query to eliminate this as a possible cause of slowness. (The reason for a 5.6 -> 5.7 migration is in the larger index sizes in 5.7, which can accommodate our larger UTF8 column sizes).

(I should also point out that the I would The field comes from the source data and consists of a long series of variable-width alphanumeric characters (up to ~ 30 characters), so I need it as VARCHAR.

CREATE TABLE `table_one` (
"id" varchar (200) NOT NULL DEFAULT "",
`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,
"Liberation" 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`)
) ENGINE = 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")
) ENGINE = InnoDB DEFAULT CHARSET = Latin1

mysql 5.6 – The procedure works when MySqlQorkbench tries, but returns a syntax error from Python

I have a procedure that takes a username (an email address) and retrieves all records with that username. If you run the procedure from MySqlWorkbench and type the user name into Promtped, everything seems to work. However, Python returns an error (I am using the mysql connector library).

This is the procedure:

CREATE DEFINER = `rds_sa` @`% `PROCEDURE` queryUserRoute` (
IN _Username VARCHAR (100)
)

BEGIN
SELECT * FROM activeUser
WHERE username = _username;
THE END

The following is the Python, which is very simple:

username = "bk@gmail.com"

cnx = mysql.connector.connect (** config)
Cursor = cnx.cursor ()
Cursor.callproc (username)

Line = cursor. Polling ()
While row is none none:
Print (line)
Line = cursor. Polling ()

I get the following error message:

mysql.connector.errors.ProgrammingError: 1064 (42000): An error has occurred in your SQL syntax. Check the manual for your MySQL server version for the correct syntax near & # 39; @ gmail.com () & # 39; in line 1

mysql 5.6 – Quiz System Database Schema

I want to create a database schema for a quiz system that is little different from the rest of the typical schema.

As we all know, the companies involved are involved

  1. Quiz type (s)
  2. Sections / Groups
  3. ask
  4. Responses of the target group

So far, the database schema is the same as we all should have, but in my case the point where it differs from the other is when the question falls under the section / group C, which in my case is called that AUTO FAIL when someone from the audience selects an option Yes then the entire quiz will automatically fail.

Another case I have is when the user selects from section / group D. incorrect Of the options, the question should not be considered a quiz question and their scores from the grand total should be excluded (not included in the quiz marks).

I have a table assessment_section_question_option where I have the following:

  1. question_id
  2. option value
  3. Result
  4. View Order

I need the best / best way to customize that in the database, since I'm not going to hard-code anything on the client side. Thank you in advance.

Note: It is basically a quality check.

When migrating from PHP 5.6 to 7.0, the case "mysqli – switch" does not work anymore

I have a news system with PHP 5.3 and I want to update it to at least 5.6.

I used the PHP_CodeSniffer master and the PHPCompatibility master to help me with the change. Most of the problems found were simple. mysql_query from mysqli_query,

I am asking for help in solving the problem that messages are no longer displayed, and I believe that the problem is related to the line while ($ column = @mysqli_fetch_array ($ query)) { of the code below, because the following error is displayed in phpcs:

Expecting "while (…) { n"; found "while (…) { n


Here's my page, I took it because I think the problem is not here

           

Follow the code for connection.php:

<?

/ * About connecting to the database * /

$ host = "pirapongadomato.com"; // mysqli host
$ user = "user"; // user
$ pass = "pass"; // user password
$ base = "piraponga"; // name of the database

// connect Mysqli
$ conn = mysqli_connect ($ host, $ user, $ pass, $ base) or the ("

Problems connecting to the server: "mysqli_error ()".
"); // select the database // Original code: $ banc = mysql_select_db ($ base) or the ("

Problems with the selection of the system database: "Mysql_error ()."
"); $ banc = mysqli_connect ($ host, $ user, $ pass, $ base) or the ("

Problems with selecting the system database: "mysqli_error ()".
"); ?>

What do you think it could be?

Upgrade script from PHP 5.6 to 7.2 appears incorrect for Paypal

Hello

My website is about people buying software from my website. There is an icon on the site where the user can see "Buy Now". If the user clicks on this icon, they will be redirected to Paypal and then made a payment.

Now the problem is when I switched from PHP 5.6 to PHP 7.2. This action only gives me a blank screen. This means that the PHP version for 7.2 can not stop paying with Paypal.

Just to mention that the script works well with other features like the homepage and the contact page, it is fully functional in PHP 7.2, but only the "buy now" payment can not be completed.

How can I fix this in a file?
SEMrush

Many Thanks

[DICHVUSOCKS.US] 13:15 UPDATE 24 / 24- Good socks

Payment Immediately perfect money, bitcoin, wmtransfer, DASH, ETH (Please click on buy socks).
Update Tools Client Dichvusocks.us http://dichvusocks.us/tools.php Link Test Socks http://check.dichvusocks.us/
LIVE | 50.63.13.135:38698 | 2.52 | Arizona | 85260 | ip-50-63-13-135.ip.secureserver.net | United States | Blacklist: No | Checked at http://dichvusocks.us
LIVE | 50.63.3.131:40157 | 0.32 | Arizona | 85260 | ip-50-63-26-13.ip.secureserver.net | United States | Blacklist: No | Checked at http://dichvusocks.us
LIVE | 50.63.13.135:13441 | 0.3 | Arizona | 85260 | ip-50-63-13-135.ip.secureserver.net | United States | Blacklist: No | Checked at http://dichvusocks.us
LIVE | 198.211.99.227:45361 | 0.03 | New Jersey | 07047 | Digital Ocean | United States | Blacklist: No | Checked at http://dichvusocks.us
LIVE | 50.63.13.135:24754 | 0.3 | Arizona | 85260 | ip-50-63-13-135.ip.secureserver.net | United States | Blacklist: No | Checked at http://dichvusocks.us
LIVE | 166.62.42.219:54275 | 1,98 | Arizona | 85260 | ip-166-62-42-219.ip.secureserver.net | United States | Blacklist: Yes | Checked at http://dichvusocks.us
LIVE | 192.169.237.19:5517 | 0.3 | Arizona | 85260 | ip-208-109-52-52.ip.secureserver.net | United States | Blacklist: No | Checked at http://dichvusocks.us
LIVE | 192.169.140.74:37385 | 4,54 | Unknown | Unknown | | Unknown | Blacklist: Yes | Checked at http://dichvusocks.us
LIVE | 50.62.35.225:44361 | 1.7 | Arizona | 85260 | ip-50-62-35-225.ip.secureserver.net | United States | Blacklist: No | Checked at http://dichvusocks.us
LIVE | 50.63.13.135:60694 | 0.58 | Arizona | 85260 | ip-50-63-13-135.ip.secureserver.net | United States | Blacklist: No | Checked at http://dichvusocks.us
LIVE | 192.169.197.146:8444 | 0.3 | Arizona | 85260 | ip-192-169-197-146.ip.secureserver.net | United States | Blacklist: No | Checked at http://dichvusocks.us
LIVE | 47.98.118.183:9050 | 0.87 | Unknown | Unknown | HGC | Hong Kong | Blacklist: No | Checked at http://dichvusocks.us
LIVE | 192.169.140.74:57711 | 3.13 | Arizona | 85260 | ip-192-169-140-74.ip.secureserver.net | United States | Blacklist: No | Checked at http://dichvusocks.us
LIVE | 192.169.249.80:40280 | 0.3 | Unknown | Unknown | | Unknown | Blacklist: Yes | Checked at http://dichvusocks.us
LIVE | 192.169.202.104:37448 | 0,99 | Arizona | 85260 | ip-192-169-202-104.ip.secureserver.net | United States | Blacklist: No | Checked at http://dichvusocks.us
LIVE | 5.160.89.51:64489 | 0.71 | Unknown | Unknown | | Unknown | Blacklist: Yes | Checked at http://dichvusocks.us
LIVE | 37.59.56.88:53940 | 0.32 | Unknown | Unknown | ns3269952.ip-37-59-56.eu | France | Blacklist: No | Checked at http://dichvusocks.us
LIVE | 5.160.89.51:7468 | 0,7 | Unknown | Unknown | | Unknown | Blacklist: Yes | Checked at http://dichvusocks.us

dichvusocks
Reviewed by dichvusocks on
,
[DICHVUSOCKS.US] 13:15 UPDATE 24 / 24- Good socks
Payment Immediately perfect money, bitcoin, wmtransfer, DASH, ETH (Please click on buy socks).
Update Tools Client Dichvusocks.us http://dichvusocks.us/tools.php Link Test Socks http://check.dichvusocks.us/
LIVE | 50.63.13.135:38698 | 2.52 | Arizona | 85260 | ip-50-63-13-135.ip.secureserver.net | United States | Blacklist: No | Checked at http://dichvusocks.us
LIVE | 50.63.3.131:40157 | 0.32 | Arizona | 85260 | ip-50-63-26-13.ip.secureserver.net | United States | Blacklist: No | Reviewed on

Rating: 5

,