Interview Questions

by Mark Nielsen
Copyright Dec 2021

This document is constantly changing. Expect spelling errors and such.

MySQL
AWS RDS MySQL and Aurora
AWS general
Linux SRE

MySQL

What is the difference between analyze and optimize?

      Depends on your version of MySQL and storage engine but in general:

OPTIMIZE TABLE Analyzes table, stores the key distribution for a table, reclaims the unused space and defragments the data file.

      ANALYZE TABLE Only analyzes table and stores the key distribution.

With GTID, if Slave1 gets additional transations not from the Master and there is a failover to make Slave1 the new master, whats happens when Slave2 tries to connect to it?
Short answer: the errant transaction has to be fixed.
https://scalegrid.io/blog/mysql-replication-errant-transactions-in-gtid-based-replication/
How is EC2 versus ESC for mysql compare?
Many issues upgrading old MySQL to newer MySQL
- The upgrade path may be long if upgrading a very old version to new version.
- Character sets may change: datetime, timestp, UTF etc. Check notes.
- Some upgrades required a table check and rebuild. Once done, take a backup of it and restore for other slaves or master, etc.
- Replication usually only works from previous version. Replications settings may also have changed. Do not replicate from newer version to older version ever.
- Config, query words, etc may have changed or been depreciated.
- Other things may have changed, like passwords, authentication, SQL strict mode.
- Query plans mys have changes, or queries on the new version may behave differently.
- Queries may return different results.
- Links
  - https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-upgrade.html
  - https://www.digitalocean.com/community/tutorials/how-to-prepare-for-your-mysql-5-7-upgrade
Disadvantages and advantages and potential problems with Table Partitioning.
- Bad queries without using using the field for partitioning can scan all partitions.
- If you do use only one partition, but not an index, it it scan the whole partition.
- Cannot foreign keys, full test indexes, use Declared variables, user variables, stored procedures, stored functions, loadable functions, plugins.
- DDL changes may cause locks. Lock the table for any reason is bad.
- Each partition is really its own table. This can affect open table cache, open files, buffers for each table, etc.
- Links
  - https://dev.mysql.com/doc/mysql-partitioning-excerpt/8.0/en/partitioning-limitations.html
Galera Scalability
- Galera is vertical scaling, config, load balancing, cache etc but it is NOT good at horizontal scaling.
- Add a cache, like Redis or other cache.
- More powerful systems --- only goes so far
- Optimize queries, config, etc
- Relax config options to prevent nodes getting kicked out.
- Add more nodes: This is controversial.
- Make sure you are using fast Ethernet cards, fast IO, and lots of memory to hold data. You want more memory in case another node fails. It then has to hold more data in ram.
- Add proper load balancing with detection of hot nodes. Have all writes go to one node.
- Use a MySQL slave off one of the those for time insensitive queries for ETL queries./
- Add node which takes no queries for backups.
- Links:
  - https://medium.com/swlh/nodejs-mariadb-galera-is-it-a-right-choice-for-scalable-ecommerce-api-9288aa682145
  - Simple article: https://www.cyberciti.biz/faq/howto-install-configure-mariadb-galera-master-cluster-ubuntulinux/
  - I am not sure this article is up to date: https://proxysql.com/blog/effortlessly-scaling-out-galera-cluster-with-proxysql/
  - Use SkySQL https://mariadb.com/products/skysql/docs/design/scalability/galera/
  - https://dba.stackexchange.com/questions/122951/scaling-for-mariadb-percona-galera-scaling
  - https://dba.stackexchange.com/questions/104221/9-node-percona-xtradb-cluster/104222#1042
  - https://fromdual.com/limitations-of-galera-cluster
MySQL Scaling.
- None
  - Oracle Fabric: not a fan of. It has had issues and nobody uses it.
- ALL
  - SkySQL -- service.
  - MaxScale : Each system must have a unique database name.
  - Optimize queries, config, system config, use the correct table engine.
  - Delete data no longer needed and remove data to archive.
  - PlanetScale: I never used this.
  - NDB engine, but everyone has problems making this scale.
  - Shard-query : More for big OLAP queries and not normal OLTP.
  - ProxySQL : Different sharding techniques have limitations.
- Horizontal Reads
  - Add more slaves or nodes.
  - Caching is scalable with Redis or other.
  - Load balancing can help so that one slave is not over used.
  - Table partitioning is sort of vertical scaling.
  - Software Sharding.
- Vertical Reads
  - More powerful systems
- Vertical Writes.
  - More powerful systems.
- Horizontal Writes
  - Software sharing. Shard by mod, by lookup key, or other.
  - Limited scalability with Galera Cluster.
- Links
  - https://levelup.gitconnected.com/mysql-sharding-tutorial-7340d2c26a3e
  - https://planetscale.com/blog/learn-horizontal-scaling-on-planetscaledb-with-vitess-rate-puppies-in-a-rust-app-with-sharded-mysql-database
  - https://medium.com/pinterest-engineering/sharding-pinterest-how-we-scaled-our-mysql-fleet-3f341e96ca6f
  - https://hevodata.com/learn/understanding-mysql-sharding-simplified/#:~:text=MySQL%20allows%20users%20to%20change,can%20leverage%20the%20new%20Schemas.
  - https://www.digitalocean.com/community/tutorials/understanding-database-sharding
  - https://mariadb.com/resources/blog/cloud-scale-is-beyond-sharding/
  - https://mariadb.com/kb/en/mariadb-maxscale-14/maxscale-simple-sharding-with-two-servers/
  - https://mariadb.com/resources/blog/schema-sharding-with-mariadb-maxscale-2-1-part-1/
  - https://proxysql.com/documentation/how-to-setup-proxysql-sharding/
  - https://www.percona.com/blog/2014/07/11/managing-shards-mysql-databases-mysql-fabric-2/
  - https://www.percona.com/blog/2016/08/30/mysql-sharding-with-proxysql/
  - https://archive.fosdem.org/2017/schedule/event/mysql_proxy_war/
Galera Geo backup, M-M
- Links
  - Okay video: https://www.youtube.com/watch?v=nxa2uq7VSjs
No downtime and tables must remain the same Geo.
- Apply backward compatible changes before app changes.
- Others options?

AWS RDS MySQL and Aurora

Different migrations options to get MySQL to Aurora

AWS General

Linux SRE

What is ps, filesystem, lsof and ports, file permissions, symbolic links. How would you transfer files between servers? (NFS, rsync, ftp, any protocol).

Mongo

How do you take backups? Mongodump or Percona backup. If OPS Manager, Mongo enterprise has its backups.
Bonus: Mongo Enterprise vs Percona and capabilities of each.
How do you setup a replica set?
How do you tell the replica set status of a replica set?
How do you tell the status of shards?
What could make Mongo Replica Set slow? Shard slow?
How would you find out bad queries? How would you analyze a query (get info on these bad queries)?
How would you monitor Mongo? Nagios, PMM, or Mongo Enterprise.
How would you add a check to Ops Manager or Percona?
How would you backup and restore using OPS Manager from Mongo Enterprise or Percona?

Cassandra

How would you check what all process running on the host.
How would you monitor CPU and memory usage.
How would you check what all filesystems are mounted.
How is Cassamdra ACID or not?
What are different partitioning strategies in distributed Design, develop and manage data on Cassandra cluster.