Differences

This shows you the differences between two versions of the page.

--- public:it:database [2021/03/18 10:11] – phil
+++ public:it:database [Unknown date] (current) – removed - external edit (Unknown date) 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Database ======
-===== Performance =====
-==== MariaDB ====
-  * [[https://mariadb.com/kb/en/mariadb-performance-advanced-configurations/|Performance Settings]]
-=== ext4 mount options ===
-  * [[https://wiki.archlinux.org/index.php/ext4#Improving_performance|The always relevant Arch Wiki]]
-^option^description^
-|noatime|access timestamps on the filesystem are not updated.|
-|commit=60|The sync interval for data and metadata can be increased by providing a higher time delay to the commit option.|
-|barrier=0/nobarrier|Careful!|
-===== Backup =====
-==== MYSQL ====
-<code>
-shell> mysqldump --databases db1 db2 db3 > dump.sql
-</code>
-<code>
-mysqldump --all-databases > dump.sql
-</code>
-==== JSONB Cheatsheet ====
-  * [[https://hackernoon.com/how-to-query-jsonb-beginner-sheet-cheat-4da3aa5082a3 | JSONB Cheat Sheet ]]
-=== Select items by the value of a first level attribute (#1 way) ===
-You can query with the `@>` operator on `metadata`. This operator can compare partial JSON strings against a JSONB column. It’s the containment operator. For this case you may need to add a GIN index on `metadata` column.
-<code>SELECT * FROM users WHERE metadata @> '{"country": "Peru"}';</code>
-=== Select items by the value of a first level attribute (#2 way) ===
-The `->>` operator gets a JSON object field as text. Use it if you want to query a simple field in a JSONB column. You might add a B-tree index on `metadata->>'country'`.
-<code>SELECT * FROM users WHERE metadata->>'country' = 'Peru';</code>
-=== Select item attribute value ===
-Once again, the `->>` operator gets a JSON object field as text. Just use directly it in the `SELECT`.
-<code>SELECT metadata->>'country' FROM users;</code>
-=== Select only items where a particular attribute is present ===
-You can use the `->>` operator with the classic operator you use on text: `=`,`<>`,`IS NULL`, etc. Do not forget to index `metadata->>'country'` with a B-tree index.
-<code> SELECT * FROM users WHERE metadata->>'country' IS NOT NULL; </code>
-=== Select items by the value of a nested attribute ===
-You can use both `@>` or `->>`, just like for first level attribute. Add an index according to your choice.
-<code>
-SELECT * FROM users WHERE metadata->'company'->>'name' = "Mozilla"; SELECT * FROM users WHERE metadata @> '{"company":{"name": "Mozilla"}}';
-</code>
-=== Select items by the value of an attribute in an array ===
-Remembering `@>` operator checks containment in a JSONB column, you can query on an array like `{"x": ["a", "b", "c"]"}` by just passing `{"x":["a"]}` to the `WHERE` clause:
-<code>SELECT * FROM users WHERE metadata @> '{"companies": ["Mozilla"]}';</code>
-=== IN operator on attributes ===
-Sometimes, we may need to select items where the attributes inside a JSONB column matches a bunch of possible values.
-<code>SELECT * FROM users WHERE metadata->>'countries' IN ('Chad', 'Japan');</code>
-=== Insert a whole object ===
-Use `UPDATE ... SET` as usual and pass the whole object as JSON.
-<code>UPDATE users SET metadata = '{"country": "India"}';</code>
-=== Update or insert an attribute ===
-Use the `||` operator to concatenate the actual data with the new data. It will update or insert the value.
-<code>UPDATE users SET metadata = metadata || '{"country": "Egypt"}';</code>
-=== Removing an attribute ===
-The operator - removes a key from an object.
-<code>UPDATE users SET metadata = metadata - 'country';</code>
-===== MYSQL/MARIADB slow queries =====
-==== Enable Logging ====
-<code>
-MariaDB [(none)]> show global variables like 'slow%log%';
-+---------------------+-------------------------+
-| Variable_name       | Value                   |
-+---------------------+-------------------------+
-| slow_query_log      | ON                      |
-| slow_query_log_file | /var/log/mysql/slow.log |
-+---------------------+-------------------------+
-</code>
-<code>
-MariaDB [(none)]> set global slow_query_log_file='/var/log/mysql/slow.log';
-Query OK, 0 rows affected (0.000 sec)
-</code>
-<code>
-MariaDB [(none)]> set global slow_query_log=ON;
-Query OK, 0 rows affected (0.001 sec)
-</code>
-https://mariadb.com/kb/en/slow-query-log-overview/#enabling-the-slow-query-log
-https://mariadb.com/resources/blog/what-is-the-best-linux-filesystem-for-mariadb/
-https://shatteredsilicon.net/blog/2020/06/05/mysql-mariadb-innodb-on-zfs/

PhilsWiki

User Tools

Site Tools

Differences

Page Tools