summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorbnewbold <bnewbold@robocracy.org>2017-06-13 11:57:50 -0700
committerbnewbold <bnewbold@robocracy.org>2017-06-13 11:57:50 -0700
commite6af2e5439693cd1acd7d4bfe6d80c1ed8cdd0b6 (patch)
treea2c09f5cb1509aa8cf16ae3b4f16d11db3d7e9c8
parentdb8c2e8217fc67a1a506ae334ac40df54b52c739 (diff)
downloadknowledge-e6af2e5439693cd1acd7d4bfe6d80c1ed8cdd0b6.tar.gz
knowledge-e6af2e5439693cd1acd7d4bfe6d80c1ed8cdd0b6.zip
mysql/sql updates
-rw-r--r--software/mysql.page66
-rw-r--r--software/sql.page19
2 files changed, 64 insertions, 21 deletions
diff --git a/software/mysql.page b/software/mysql.page
index 5a26b2c..45eccff 100644
--- a/software/mysql.page
+++ b/software/mysql.page
@@ -1,4 +1,66 @@
+---
+format: md
+toc: no
+...
-Faster imports:
+### TSV File Dumps
-http://dba.stackexchange.com/questions/13446/slow-load-speed-of-data-from-mysqldump
+To output in tab-delimited format, do something like:
+
+ mysql -u root -p -B < some_command.sql > output.tab
+
+### Password-less User Setup
+
+On contemporary Debian, to login as root do:
+
+ sudo mysql -u root
+
+Then create a new password-less local-only user:
+
+ CREATE USER 'bnewbold'@'localhost' IDENTIFIED BY '';
+ UPDATE mysql.user SET plugin='unix_socket' WHERE user='bnewbold';
+ GRANT ALL PRIVILEGES ON * . * TO 'bnewbold'@'localhost';
+ FLUSH PRIVILEGES;
+
+If you get errors about `Plugin 'unix_socket' is not loaded`, you need to use
+MariaDB instead.
+
+### Fast Imports
+
+Add these to `/etc/mysql/my.cnf` and restart:
+
+ [mysqld]
+ # Optimizations for fast input
+ innodb_flush_method= O_DIRECT
+ innodb_doublewrite=0
+ innodb_support_xa=0
+ innodb_checksums=0
+ innodb_flush_log_at_trx_commit = 2
+ innodb_doublewrite=off
+ innodb_checksums=none
+
+ # Fast input, uses tons of RAM:
+ innodb_log_file_size=2G
+ innodb_buffer_pool_size=4G
+
+
+If you have a faster disk you want to run from, mount it, stop mysql, and copy
+over data. This assumes you have effectively no data in mysql already; if you
+do you'll need to figure out how to copy just the system/authentication tables
+over.
+
+ sudo cp -r /var/lib/mysql /mnt/fast-disk
+ sudo chown -R mysql:mysql /mnt/fast-disk
+
+Add the following to `/etc/mysql/my.cnf`:
+
+ [mysqld]
+ datadir=/mnt/fast-disk/mysql
+
+And restart.
+
+
+Via: http://dba.stackexchange.com/questions/13446/slow-load-speed-of-data-from-mysqldump
+
+Enabled by `--opt` by default?
+https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html
diff --git a/software/sql.page b/software/sql.page
index 881a040..2d2a307 100644
--- a/software/sql.page
+++ b/software/sql.page
@@ -5,25 +5,6 @@ toc: no
# SQL
-### MySQL-specific Tricks
-
-To output in tab-delimited format, do something like:
-
- mysql -u root -p -B < some_command.sql > output.tab
-
-### Setup User Permissions
-
-On contemporary Debian, to login as root do:
-
- sudo mysql -u root
-
-Then create a new password-less local-only user:
-
- CREATE USER 'bnewbold'@'localhost' IDENTIFIED BY '';
- UPDATE mysql.user SET plugin='unix_socket' WHERE user='bnewbold';
- GRANT ALL PRIVILEGES ON * . * TO 'bnewbold'@'localhost';
- FLUSH PRIVILEGES;
-
### UPDATE with JOIN
To do an update based on a join::