LSST-DB
From athena
Contents |
[edit] Setting up MySQL database on the cluster
The database in question exists on my computer at /lsst/lib/mysql/stripe82. Its in the midst of ingesting, meaning I need to copy the database over, and then continue ingesting the data on the cluster, where it will reside at athena5.npl.washington.edu:/psfs/fc-data-1/data2/lsst/mysql/.
[edit] Copying over an existing database
If I have the same system architectures on both machines, I can actually just copy the files over instead of using mysqldump. It looks like both are x86_64 machines (avoiding byte swapping problems) running MySQL ver 14.7 distrib 4.1.20. So I will actually try copying the files over as a first step.
- [root@darkstar mysql]# chmod -R o+rx stripe82
- becker46: scp -r /lsst/lib/mysql/stripe82 root@athena5.npl.washington.edu:/psfs/fc-data-1/data2/lsst/mysql/.
- [root@darkstar mysql]# chmod -R o+rx mysql
- becker47: scp -r /lsst/lib/mysql/mysql root@athena5.npl.washington.edu:/psfs/fc-data-1/data2/lsst/mysql/.
This will take several hours (33G). Afterwards, undo my permissions hack
- [root@darkstar mysql]# chmod -R o-rx stripe82
- [root@p5 mysql]# chmod -R o-rx stripe82
- [root@p5 mysql]# chown -R mysql.mysql stripe82
- [root@darkstar mysql]# chmod -R o-rx mysql
- [root@p5 mysql]# chmod -R o-rx mysql
- [root@p5 mysql]# chown -R mysql.mysql mysql
[edit] Setting up MySQL on Athena5
I need to configure MySQL to point to /psfs/fc-data-1/data2/lsst/. The file to edit is /etc/my.cnf. The default file says
[root@p5 mysql]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
I change this to
[root@p5 mysql]# cat /etc/my.cnf [mysqld] datadir=/psfs/fc-data-1/data2/lsst/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 tmpdir=/psfs/fc-data-1/data2/lsst/mysql_tmp max_connections = 1500 [mysql.server] user=mysql basedir=/psfs/fc-data-1/data2/lsst [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
To get this running, /etc/init.d/mysqld start. Now check out tables
Darkstar : becker63: mysql -h localhost -u becker -D stripe82 -p mysql> checksum table field; +----------------+------------+ | Table | Checksum | +----------------+------------+ | stripe82.field | 2317030981 | +----------------+------------+ mysql> select count(*) from source; +-----------+ | count(*) | +-----------+ | 162944307 | +-----------+ mysql> select count(*) from object; +----------+ | count(*) | +----------+ | 2909275 | +----------+ mysql> select * from source where objectID=2900000; ... 45 rows in set (18 min 25.46 sec)
Athena : [root@p5 mysql]# mysql -h localhost -u becker -D stripe82 -p mysql> checksum table field; +----------------+------------+ | Table | Checksum | +----------------+------------+ | stripe82.field | 2317030981 | +----------------+------------+ mysql> select count(*) from source; +-----------+ | count(*) | +-----------+ | 162944307 | +-----------+ mysql> select count(*) from object; +----------+ | count(*) | +----------+ | 2909275 | +----------+ mysql> select * from source where objectID=2900000; ... 45 rows in set (4 min 10.62 sec)
So it certainly looks faster. Which is nice...
- Root Password
mysqld_safe --skip-grant-tables -u root
mysql -u root
mysql> UPDATE mysql.user SET Password=PASSWORD('XXXXX') WHERE User='root';
mysql> FLUSH PRIVILEGES;
- Users and grants
mysql> select User,Host from mysql.user; +----------+------------------------+ | User | Host | +----------+------------------------+ | stripe82 | %.astro.washington.edu | | becker | localhost | | root | localhost | +----------+------------------------+ grant create temporary tables on stripe82.* to 'stripe82'@'128.95.99.%' identified by 'XXXXXX';
- Security
mysql> show grants for 'stripe82'@'%.astro.washington.edu'; | GRANT USAGE ON *.* TO 'stripe82'@'%.astro.washington.edu' IDENTIFIED BY PASSWORD 'XXXXXX' | | GRANT SELECT ON `stripe82`.* TO 'stripe82'@'%.astro.washington.edu' | mysql> show grants for 'becker'@'localhost'; | GRANT FILE ON *.* TO 'becker'@'localhost' IDENTIFIED BY PASSWORD 'XXXXX' | | GRANT ALL PRIVILEGES ON `stripe82`.* TO 'becker'@'localhost' | mysql> show grants for 'root'@'localhost'; | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'XXXXX' WITH GRANT OPTION |
- Temporary space
This thing was occasionally filling up /tmp/. Redirect the temporary space.
cd /psfs/fc-data-1/data2/lsst/ mkdir mysql_tmp chown -R mysql.mysql mysql_tmp
Now edit /etc/my.cnf and add under [mysqld]
tmpdir=/psfs/fc-data-1/data2/lsst/mysql_tmp
- All configuration variables
[root@p5 B]# mysqladmin -p variables Enter password: +---------------------------------+--------------------------------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------------------------------+ | back_log | 50 | | basedir | /usr/ | | bdb_cache_size | 8388600 | | bdb_home | /psfs/fc-data-1/data2/lsst/mysql/ | | bdb_log_buffer_size | 32768 | | bdb_logdir | | | bdb_max_lock | 10000 | | bdb_shared_data | OFF | | bdb_tmpdir | /psfs/fc-data-1/data2/lsst/mysql_tmp/ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | /psfs/fc-data-1/data2/lsst/mysql/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | expire_logs_days | 0 | | flush | OFF | | flush_time | 0 | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | group_concat_max_len | 1024 | | have_archive | NO | | have_bdb | YES | | have_blackhole_engine | NO | | have_compress | YES | | have_crypt | YES | | have_csv | NO | | have_example_engine | NO | | have_geometry | YES | | have_innodb | YES | | have_isam | YES | | have_ndbcluster | NO | | have_openssl | YES | | have_query_cache | YES | | have_raid | NO | | have_rtree_keys | YES | | have_symlink | YES | | init_connect | | | init_file | | | init_slave | | | innodb_additional_mem_pool_size | 1048576 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 8388608 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_fast_shutdown | ON | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8 | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | key_buffer_size | 8388600 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | language | /usr/share/mysql/english/ | | large_files_support | ON | | license | GPL | | local_infile | ON | | locked_in_memory | OFF | | log | OFF | | log_bin | OFF | | log_error | | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_update | OFF | | log_warnings | 1 | | long_query_time | 10 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 1048576 | | max_binlog_cache_size | 18446744073709551615 | | max_binlog_size | 1073741824 | | max_connect_errors | 10 | | max_connections | 100 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 18446744073709551615 | | max_length_for_sort_data | 1024 | | max_prepared_stmt_count | 16382 | | max_relay_log_size | 0 | | max_seeks_for_key | 18446744073709551615 | | max_sort_length | 1024 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 18446744073709551615 | | myisam_data_pointer_size | 4 | | myisam_max_extra_sort_file_size | 2147483648 | | myisam_max_sort_file_size | 9223372036854775807 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 8388608 | | myisam_stats_method | nulls_unequal | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old_passwords | ON | | open_files_limit | 1024 | | pid_file | /var/run/mysqld/mysqld.pid | | port | 3306 | | preload_buffer_size | 32768 | | prepared_stmt_count | 0 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size | 131072 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | relay_log_purge | ON | | relay_log_space_limit | 0 | | rpl_recovery_rank | 0 | | secure_auth | OFF | | server_id | 0 | | skip_external_locking | ON | | skip_networking | OFF | | skip_show_database | OFF | | slave_net_timeout | 3600 | | slave_transaction_retries | 0 | | slow_launch_time | 2 | | socket | /var/lib/mysql/mysql.sock | | sort_buffer_size | 2097144 | | sql_mode | | | sql_notes | ON | | sql_warnings | ON | | storage_engine | MyISAM | | sync_binlog | 0 | | sync_frm | ON | | sync_replication | 0 | | sync_replication_slave_id | 0 | | sync_replication_timeout | 0 | | system_time_zone | PDT | | table_cache | 64 | | table_type | MyISAM | | thread_cache_size | 0 | | thread_stack | 196608 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | tmp_table_size | 33554432 | | tmpdir | /psfs/fc-data-1/data2/lsst/mysql_tmp | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | version | 4.1.20 | | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 24, 2006) | | version_comment | Source distribution | | version_compile_machine | x86_64 | | version_compile_os | redhat-linux-gnu | | wait_timeout | 28800 | +---------------------------------+--------------------------------------------------------+
[edit] Using MySQL on Athena5
- Ingest
- [root@p5 mysql]# mkdir /psfs/fc-data-1/data2/becker
- becker64: scp Cluster_ingest2aa_cut Cluster_ingest2?? root@athena5.npl.washington.edu:/psfs/fc-data-1/data2/becker/.
- [root@p5 becker]# mysql -h localhost --local_infile=1 -u becker -D stripe82 --password=XXXXX < Cluster_ingest2aa_cut
- Remote querying
Hmmm, I get "Host '128.95.99.45' is not allowed to connect to this MySQL server". Even though I have granted access to *.astro.washington.edu. So maybe there is a problem with DNS? Let me try this.
- mysql> grant select on stripe82.* TO 'stripe82'@'128.95.99.%' IDENTIFIED BY 'XXXXX';
Works!
[edit] Setting up a whole new MySQL for MOPS
MOPS requires enough functionality that it seems to make sense to create a whole new MySQL Server for it. To do this we need to give it a particular
* MYSQL_UNIX_PORT * MYSQL_TCP_PORT
Doing this robustly means creating different configuration files, including
* /etc/init.d/mysqld; call it /etc/init.d/mysqld_MOPS * /etc/my.cnf; call it /etc/my_MOPS.cnf
/etc/my_MOPS.cnf
[mysqld] datadir=/psfs/fc-data-1/data2/lsst/MOPS/mysql tmpdir=/psfs/fc-data-1/data2/lsst/MOPS/mysql_tmp socket=/var/lib/mysql/mysql_MOPS.sock port=3307 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 max_connections = 1500 [mysql.server] user=mysql basedir=/psfs/fc-data-1/data2/lsst/MOPS [mysqld_safe] err-log=/var/log/mysqld_MOPS.log pid-file=/var/run/mysqld/mysqld_MOPS.pid
I need to make the directories as well
mkdir /psfs/fc-data-1/data2/lsst/MOPS mkdir /psfs/fc-data-1/data2/lsst/MOPS/mysql mkdir /psfs/fc-data-1/data2/lsst/MOPS/mysql_tmp chown -R mysql.mysql /psfs/fc-data-1/data2/lsst/MOPS/mysql*
/etc/init.d/mysqld_MOPS
/etc/my.cnf -> /etc/my_MOPS.cnf /var/lock/subsys/mysqld -> /var/lock/subsys/mysqld_MOPS status mysqld -> status mysqld_MOPS /usr/bin/mysqladmin -> /usr/bin/mysqladmin --port=3307 --protocol=TCP
Try running both concurrently...
[root@p5 ~]# /etc/init.d/mysqld_MOPS start Initializing MySQL database: [ OK ] Timeout error occurred trying to start MySQL Daemon. Starting MySQL: [FAILED]
Needed to "install" a database using
[root@p5 MOPS]$ mysql_install_db --defaults-file=/etc/my_MOPS.cnf --user=mysql --verbose [root@p5 MOPS]$ /etc/init.d/mysqld_MOPS start Starting MySQL: [ OK ] [root@p5 ~]# /etc/init.d/mysqld_MOPS stop Stopping MySQL: [ OK ]
Set root password
[root@p5 ~]# mysqld_safe --defaults-file=/etc/my_MOPS.cnf --skip-grant-tables -u root
Starting mysqld daemon with databases from /psfs/fc-data-1/data2/lsst/MOPS/
[root@p5 lsst]# mysql -u root --port=3307 --protocol=TCP
mysql> UPDATE mysql.user SET Password=PASSWORD('XXXXX') WHERE User='root';
mysql> FLUSH PRIVILEGES;
And start up the server for real.
[root@p5 MOPS]# /etc/init.d/mysqld_MOPS start # CRAP THIS DOES NOT WORK!!!! Timeout error occurred trying to start MySQL Daemon. Starting MySQL: [FAILED]
Wait it a minute, it does seem to work..!
[root@p5 lsst]# mysql -u root --port=3307 --protocol=TCP -p
So maybe its the form of the test in /etc/init.d/mysqld_MOPS. Indeed, I had to change the mysqladmin command to include --port=3307 --protocol=TCP.
Need to open up port 3307 too!
[root@p5 lsst]# nmap localhost ... 3306/tcp open mysql
Does not show port 3307
Not sure if this is necessary, but edit /etc/services and add
mysql 3307/tcp # MySQL mysql 3307/udp # MySQL
[edit] Using secondary MySQL server on Athena5
Some notes:
- added a mops user
- The port used is 3307 (-P 3307 --protocol=TCP is necessary at the moment)
- mops can log in from athena0 and athena5 only (because athena5 doesn't know about the names of cluster computers)
- mysql -h athena5.npl.washington.edu -u mops -p -P 3307 --protocol=TCP
