46 Matching Annotations
  1. Aug 2022
    1. 使用@DynamicUpdate性能会好一些。因为不使用@DynamicUpdate时,即使没有改变的字段也会被更新
    1. 那么查询索引本身已经“覆盖”了需要的数据,不再需要回表查询。因此,这种情况也叫作索引覆盖

      extra中显示:use index,不是user index condition,也不是null

    1. version被其他事务抢先更新

      version拿到最新的当前值

    2. 事务B是当前读,必须要读最新版本,而且必须加锁,因此就被锁住了,必须等到事务C’释放这个锁,才能继续它的当前读。
    1. 事务中的MDL锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放。

      mdl只有在事务之后才会释放

    2. 比较理想的机制是,在alter table语句里面设定等待时间,如果在这个指定的等待时间里面能够拿到MDL写锁最好,拿不到也不要阻塞后面的业务语句,先放弃

      等待

    3. 使用参数–single-transaction的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于MVCC的支持,这个过程中数据是可以正常更新的。
    1. 第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的
    1. 恢复目标时间
    2. select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
    3. 就是当系统里没有比这个回滚日志更早的read-view的时候

      疑惑点

    4. MySQL的隔离级别设置为“读提交

      mysql默认是可重复读

    5. 创建一个视图
    6. 一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的

      非所有数据,而是所读取行

    1. 感觉oracle的DG就诞生了,物理的速度也将远超逻辑的,毕竟只记录了改动向量
    2. redo log和binlog都可以用于表示事务的提交状态,而两阶段提交就是让这两个状态保持逻辑上的一致

      如果中间出现事故,mysql会做相应处理两阶段提交

  2. Mar 2022
    1. 真实环境,数据库服务器进程和客户端进程可能运行在不同的主机中,它们之间必须通过网络进行通讯。

      MySQL 采用 TCP 作为服务器和客户端之间的网络通信协议。

      在网络环境下,每台计算机都有一个唯一的 IP 地址,如果某个进程需要采用 TCP 协议进行网络通信方面的需求,可以向操作系统申请一个端口号,这是一个整数值,它的取值范围是 0~65535。

      这样在网络中的其他进程就可以通过 IP 地址 + 端口号的方式来与这个进程链接,这样进程之间就可以通过网络进行通信了。

    Tags

    Annotators

    1. 由图中可看到,每种设备都有两个指标: - 延时(响应时间):表示硬件的突发处理能力; - 带宽(吞吐量):代表硬件持续处理的能力。

    2. 大多数情况,性能最慢的设备会是瓶颈点。

      如,下载时网络速度可能会是瓶颈点,本地复制文件时硬盘可能是瓶颈点。

      为什么这些一般的工作能快速确认瓶颈点呢?

      因为我们队这些慢速设备的性能数据有一些基本的认识,如网络带宽是 2 Mbps,硬盘是每分钟 7200 转等等。

      (结论)因此,为了快速找到 SQL 的性能瓶颈点,需要了解计算机系统的硬件基本性能指标,如当前主流计算机性能指标数据。

    3. 数据库访问优化法则

      (目的):要正确的优化 SQL;

      (条件):需要快速定位性能的瓶颈点;

      (进一步阐释说明):即是快速找到 SQL 主要的开销在哪里?

  3. Oct 2021
  4. Jun 2021
    1. The alternative for curl is a credential file: A .netrc file can be used to store credentials for servers you need to connect to.And for mysql, you can create option files: a .my.cnf or an obfuscated .mylogin.cnf will be read on startup and can contain your passwords.
      • .netrc <--- alternative for curl to store secrets
      • .my.cnf or .mylogin.cnf <--- option files for mysql to store secrets
  5. May 2020
    1. I think you should normalize if you feel that introducing update or insert anomalies can severely impact the accuracy or performance of your database application.  If not, then determine whether you can rely on the user to recognize and update the fields together. There are times when you’ll intentionally denormalize data.  If you need to present summarized or complied data to a user, and that data is very time consuming or resource intensive to create, it may make sense to maintain this data separately.

      When to normalize and when to denormalize. The key is to think about UX, in this case the factors are db integrity (don't create errors that annoy users) and speed (don't make users wait for what they want)

    2. Can database normalization be taken too far?  You bet!  There are times when it isn’t worth the time and effort to fully normalize a database.  In our example you could argue to keep the database in second normal form, that the CustomerCity to CustomerPostalCode dependency isn’t a deal breaker.

      Normalization has diminishing returns

    3. Now each column in the customer table is dependent on the primary key.  Also, the columns don’t rely on one another for values.  Their only dependency is on the primary key.

      Columns dependency on the primary key and no dependency on other columns is how you get 2NF and 3NF

    4. A table is in third normal form if: A table is in 2nd normal form. It contains only columns that are non-transitively dependent on the primary key

      3NF Definition

  6. Dec 2019
  7. unix4lyfe.org unix4lyfe.org
    1. if you care at all about storing timestamps in MySQL, store them as integers and use the UNIX_TIMESTAMP() and FROM_UNIXTIME() functions.

      MySQL does not store offset

  8. Jul 2019
    1. WordPress Database Switcheroo First, do a MySQL database export of the old database on the old server, create a new blank database on the new server, import the old data either in PHPMyAdmin or mysql directly in the command line. Make sure you have the new database selected, then run some SQL updates and replacement commands on the tables notably, wp_options, wp_posts, wp_postmeta. Use the code as below and swap in your old and new URLs, no trailing slashes. Also if necessary change the table prefix values where applicable (ie wp_ ) UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl', 'http://www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl'; UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl'); UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldurl', 'http://www.newurl'); UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl','http://www.newurl');

      This is really helpful when you're manually cloning a WP site. If you have a Domain of One's Own and you want to move a site from one user's account to another or fork a site into another user's account, this is really helpful.

  9. Apr 2019
  10. Mar 2019
    1. DBA Por Acaso: RDS, MySQL e Tuning para Iniciantes

      Outro assunto que não é explicitamente coberto por nossos tópicos, mas que é base importante para o administrador de sistemas na nuvem - e aqui coberto em um nível introdutório, para não assustar ninguém. E procura por Cloud em https://wiki.lpi.org/wiki/DevOps_Tools_Engineer_Objectives_V1 para ver como esse assunto é importante!

  11. Jan 2018
  12. Oct 2017
    1. MySQL’s replication architecture means that if bugs do cause table corruption, the problem is unlikely to cause a catastrophic failure.

      I can't follow the reasoning here. I guess it's not guaranteed to replicate the corruption like Postgres would, but it seems totally possible to trigger similar or identical corruption because the implementation of the logical statement would be similar on the replica.

  13. Mar 2015
    1. My current process for debugging stored procedures is very simple. I create a table called "debug" where I insert variable values from the stored procedure as it runs. This allows me to see the value of any variable at a given point in the script, but is there a better way to debug MySQL stored procedures?

      facing a similar issue