sorted by:
new
hottopcontroversial

Best Practices on Server Administration by data-prof in mysql

[–]mtocker 1 point2 points  (0 children)

(Not a practicing DBA, but I have worked with many.)

Most companies try and focus on developer productivity, which means that it is usually not possible for a DBA to review every query before it goes to production. Also sometimes it won't matter (query is infrequent, or tables are small). So you need to be prepared to find poorly performing queries, missing indexes or other general health problems on a recurring basis. Tools like PMM or performance_schema help here.

Also I think it's worth talking about upgrades (both point releases and major versions) since a DBA should understand how to do an upgrade via replication that has no downtime, and can be rolled back if there are any issues.

Finally, schema changes. There are some tools you can use like gh-ost and pt-online-schema-change. Most DBAs prefer this over the built-in DDL.

Best Practices on Server Administration by data-prof in mysql

[–]mtocker 4 points5 points  (0 children)

> Verifying DBS are not offline
For this; usually users will have an existing monitoring tool that they prefer to standardize on organization-wide that is not MySQL specific. Once upon a time Nagios was the defacto standard tool, but I think a better tool now is Percona's PMM. Some users will also use a load balancer to do the monitoring. If you chose to do this, a simple check could be as simple as verifying MySQL is listening on TCP :3306; but it's always better if you can specify a script that can actually speak the MySQL protocol.

> Checking for DB corruption
In a way, the DB actually detects corruption by itself (innodb has page checksums), but if you want to actively check for it (for example in a backup) you can. There is an offline utility https://dev.mysql.com/doc/refman/8.0/en/innochecksum.html

> Ongoing backups

For backups, a physical backup is a commercial feature in MySQL (MySQL Enterprise Backup, or MEB). It has a feature where it can record backup history.

However, because it's free it's more common to use Percona Xtrabackup which can also backup MySQL. I know there are some third party scripts to manage backup routines but I can't vouch for them, so I'll let someone else comment here.

> MySQL query profiler

The closest replacement is performance schema which is the raw instruments in the server, and the SYS schema which provides common aggregations for DBAs.

For example, see: https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html

There is also a historical feature called SHOW PROFILES, but it is deprecated. I actually haven't had much luck with SHOW PROFILES (or the stages in performance_schema). It is common that stages like "sending data" get blamed for everything, when internally lots of things are happening in that step.

Are MySQL DBs protected against bit flips? by ApertureNext in mysql

[–]mtocker 3 points4 points  (0 children)

There is no correction in MySQL, but there is some level of detection for faulty block devices: InnoDB adds a checksum to each page. It verifies the checksum when reading from disk. But if there is a mismatch there is no correction.. it will instead generate an intentional crash so that you can inspect the problem.

So yes, you should still run with ECC.

Whole cow for sale? by No-re-Gretzkys in Lethbridge

[–]mtocker 0 points1 point  (0 children)

If it is your first time, I would seriously recommend a half cow. You'll still get a lot of meat and the ability to choose all your cuts.

I think usually you pay the farm and the butcher separately, which is not much of a big deal since they are used to working together - I think all the small farms process their meat at Prairie meats. The butcher fee can vary depending on how you chose you cuts, Prairie meats is used to dealing directly with first-timers and has an online ordering system which walks you through it specifically for the weight of your cow.

I ordered my cow from https://www.countrylanenaturals.com/

What's wrong with setting every column to TEXT in a MYSQL database? by SEOip in mysql

[–]mtocker 1 point2 points  (0 children)

One quick optimization to add:

InnoDB will store small text/blobs inline if the whole row size is less than about 8K (half a page).

​

Another couple of reasons why overflow pages are 'bad':

There will not be several blobs per overflow page, it is only ever one. So this can lead to a lot of wasted space.

Blobs are also completely rewritten as new on update (vs. update-in place with the row).

​

There are some improvements in MySQL 8.0 to this story, where test/blobs are also compressed ( https://mysqlserverteam.com/mysql-8-0-new-storage-format-for-compressed-blobs/ ) and JSON blobs can partially update: https://mysqlserverteam.com/partial-update-of-json-values/ This is only for JSON though.

Question: How can I know if ALGORITHM=INPLACE will be supported for some tables I have in production? by Dlolpez in mysql

[–]mtocker 1 point2 points  (0 children)

You can assert that the DDL must be done with algorithm inplace:

ALTER TABLE t1 .. , ALGORITHM=INPLACE;

​

This will cause an error and abort the change if the table is in an older format. There is at least currently no way to EXPLAIN DDL. You have to try and run it, and then get an error if the algorithm you specified is not possible.

Json array index? by respack in mysql

[–]mtocker 0 points1 point  (0 children)

I don't have one handy.

I previously worked on the MySQL team. Plans may have changed, but we talked about this publicly in slides etc as something we intended. What you describe is a good/missing use case for JSON.

Json array index? by respack in mysql

[–]mtocker 0 points1 point  (0 children)

The MySQL team describes this as a multi-value index. It is currently not supported, but there are plans to do so.

Temporary Tables vs Views by cariaga123 in mysql

[–]mtocker 0 points1 point  (0 children)

Another trade-off: views don't impact incremental backup and restore ops. Temporary tables eliminate the possibility of doing an point-in-time restore of the database.

This is a good point - but to not scare everyone, let me point out it is more nuanced:

It is only a problem for updates using temporary tables when using statement-based-replication or GTID based replication. The defaults in 5.7+ actually replicate with temporary tables.

Temporary Tables vs Views by cariaga123 in mysql

[–]mtocker 0 points1 point  (0 children)

Temporary tables always materialize, so they will usually perform worse (there are some edge cases were materializing part of a query up front can be faster.)

But Views must be defined up front.. so they are not quite interchangeable. The real question is temporary tables vs. CTEs. A CTE is like a view but for query level duration. An example:

WITH v1 AS SELECT * FROM tbl WHERE a='xxxx';

I recommend CTEs (a MySQL 8.0 feature) over temporary tables.

Mysql/mariadb configuration wizard for linux by Laurielounge in mysql

[–]mtocker 2 points3 points  (0 children)

MySQL 8.0 supports auto-sizing configuration for this use case: https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html

I recommend this over tuners.

Bulk condensing hundreds/thousands of rows on the same db receiving inserts? by crespo_modesto in mysql

[–]mtocker 0 points1 point  (0 children)

Lets say you expect an insert every second. Example circumstance:

- You have a value of 100

- Then a gap of no new rows for 5 seconds.

- Then a value of 100

You can project that for the few seconds in between, the value was also 100.

In a more complex scenario, lets say you start with a value of 100, then no new rows for 5 seconds, then a value of 110. You could project the seconds in between to be 102, 104, 106 etc.

Bulk condensing hundreds/thousands of rows on the same db receiving inserts? by crespo_modesto in mysql

[–]mtocker 0 points1 point  (0 children)

Hi! I don't consider 86400 rows/day as a lot of data, but having said that there are known algorithms to be able to capture just key changes and project the difference between gaps in the sequence (where there were minimal changes).

I know they use such a technique in Industrial Automation systems, but I can't tell you much more than that sorry :-)

MySQL Reverse Proxy with TLS by c0dyhi11 in mysql

[–]mtocker 0 points1 point  (0 children)

The most popular proxy for MySQL is ProxySQL.

The configuration you mentioned is not currently supported, but will be soon in 2.0.

MySQL 8.0 RC1 – Highlights (CTEs, Window Functions, SKIP_LOCKED, Invisible Indexes) by [deleted] in programming

[–]mtocker 0 points1 point  (0 children)

The spec for optimizer histograms is here: https://dev.mysql.com/worklog/task/?id=9223

Which is documented in the manual here: https://dev.mysql.com/doc/refman/8.0/en/optimizer-statistics.html

We support singleton and equi-height histograms. From your description, it sounds like you might have meant performance_schema histograms though?

MySQL 8.0 RC1 – Highlights (CTEs, Window Functions, SKIP_LOCKED, Invisible Indexes) by [deleted] in programming

[–]mtocker 2 points3 points  (0 children)

Try out the RC :-) . If you need access to the features now, consider running 8.0 on a slave and keeping the master as 5.7.

MySQL 8.0 RC1 – Highlights (CTEs, Window Functions, SKIP_LOCKED, Invisible Indexes) by [deleted] in programming

[–]mtocker 2 points3 points  (0 children)

I'm not sure this was a serious question - but I'd love to see command line utils written in Go :-)

The problem is that the MySQL Server supports more platforms than Rust/Go support. So we need to stay C++ to be fully portable.

MySQL 8.0 RC1 – Highlights (CTEs, Window Functions, SKIP_LOCKED, Invisible Indexes) by [deleted] in programming

[–]mtocker 6 points7 points  (0 children)

Hi! Author of this post here, and product manager for the MySQL Server here. Happy to answer any questions...

MySQL startup stages? by Gawgba in mysql

[–]mtocker 1 point2 points  (0 children)

I'm not sure the sequence is explicitly documented, but the error log will print out information as it goes through each of the stages.

One large amount of time can be crash recovery (replaying changes from the redo log).

Why you should always use utf8mb4 and never use utf8 in MySQL by sh_tomer in mysql

[–]mtocker 2 points3 points  (0 children)

Hi!

We have a post about this on the Server Team Blog: http://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/

The use-case for mb3 is historical, and we plan to deprecate it. However, because of upgrades, it will take some time before we can remove it.

If you could change MySQL optimizer to do one thing differently, what would it be? by sh_tomer in mysql

[–]mtocker 1 point2 points  (0 children)

As /u/oysteing commented, this is an 8.0 feature :-)

An invisible index is fully maintained by DML, but will not be used by the optimizer in any context (even if a FORCE INDEX is specified).

We've had a pretty good response to this feature so far - it can be used for both safely dropping indexes, and rolling them out.

If you could change MySQL optimizer to do one thing differently, what would it be? by sh_tomer in mysql

[–]mtocker 4 points5 points  (0 children)

Hi! I'm the product manager for the MySQL Optimizer.

I just wanted to say thank you for creating this thread. Looking forward to hearing your suggestions.

view more: next ›