0

Is there a way to get tokudb to put all the files it creates for a database into subdirectories.

When I started using tokudb the most irritating thing I have found is that it puts all its files into the MySQL root database directory.

This makes it difficult to see which files belong to which database and difficult to see how much space each database schema is using.

ll /srv/mysql/data/

-rw-rw----. 1 mysql mysql    16384 Apr  5 17:36 aria_log.00000001
-rw-rw----. 1 mysql mysql       52 Apr  5 17:36 aria_log_control
drwx------. 2 mysql mysql     4096 Apr 10 15:09 graphite
drwx------. 2 mysql mysql      165 Apr 10 15:09 graphite2
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_mygraph_key_account_mygraph_83a0eb3f_3c3_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_mygraph_main_3c3_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_account_mygraph_status_3c3_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_profile_key_user_id_3ce_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_profile_main_3ce_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr  6 15:54 _graphite_account_profile_status_3ce_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_variable_key_account_variable_83a0eb3f_3db_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_variable_main_3db_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_account_variable_status_3db_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_view_key_account_view_83a0eb3f_3e6_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_view_main_3e6_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_account_view_status_3e6_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_window_key_account_window_2e1a1398_3f1_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_window_main_3f1_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_account_window_status_3f1_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_group_key_name_3fc_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_group_main_3fc_2_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_group_permissions_key_auth_group_permissions_0e939a4f_407_4_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_group_permissions_key_auth_group_permissions_8373b171_407_5_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_group_permissions_key_auth_group_permissions_group_id_0cd325b0_uniq_407_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_group_permissions_main_407_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_auth_group_permissions_status_407_1_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_auth_group_status_3fc_1_1d.tokudb
-rw-rw----. 1 mysql mysql    16896 Apr  6 11:58 _graphite_auth_permission_key_auth_permission_417f1b1c_419_1_1d_B_2.tokudb
-rw-rw----. 1 mysql mysql    16896 Apr  6 11:58 _graphite_auth_permission_key_auth_permission_content_type_id_01ab375a_uniq_419_1_1d_B_1.tokudb
-rw-rw----. 1 mysql mysql    16896 Apr  6 11:58 _graphite_auth_permission_main_419_1_1d_B_0.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_auth_permission_status_412_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_groups_key_auth_user_groups_0e939a4f_42c_5_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_groups_key_auth_user_groups_e8701ad4_42c_4_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_groups_key_auth_user_groups_user_id_94350c0c_uniq_42c_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_groups_main_42c_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_auth_user_groups_status_42c_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_key_username_41f_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_main_41f_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr  6 15:54 _graphite_auth_user_status_41f_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_user_permissions_key_auth_user_user_permissions_8373b171_437_5_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_user_permissions_key_auth_user_user_permissions_e8701ad4_437_4_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_user_permissions_key_auth_user_user_permissions_user_id_14a6b632_uniq_437_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_user_permissions_main_437_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_auth_user_user_permissions_status_437_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_dashboard_main_442_2_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_dashboard_owners_key_dashboard_dashboard_owners_83a0eb3f_44c_5_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_dashboard_owners_key_dashboard_dashboard_owners_a6b0b808_44c_4_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_dashboard_owners_key_dashboard_dashboard_owners_dashboard_id_f37e04b7_uniq_44c_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_dashboard_owners_main_44c_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_dashboard_dashboard_owners_status_44c_1_1d.tokudb
-rw-rw----. 1 mysql mysql    16384 Apr  6 11:58 _graphite_dashboard_dashboard_status_442_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_template_main_457_2_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_template_owners_key_dashboard_template_owners_74f53564_461_4_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_template_owners_key_dashboard_template_owners_83a0eb3f_461_5_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_template_owners_key_dashboard_template_owners_template_id_e47a75a7_uniq_461_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_template_owners_main_461_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_dashboard_template_owners_status_461_1_1d.tokudb
-rw-rw----. 1 mysql mysql    16384 Apr  6 11:58 _graphite_dashboard_template_status_457_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_django_admin_log_key_django_admin_log_417f1b1c_46c_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_django_admin_log_key_django_admin_log_e8701ad4_46c_4_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_django_admin_log_main_46c_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_django_admin_log_status_46c_1_1d.tokudb
-rw-rw----. 1 mysql mysql    16896 Apr  6 11:58 _graphite_django_content_type_key_django_content_type_app_label_76bd3d3b_uniq_47e_1_1d_B_1.tokudb
-rw-rw----. 1 mysql mysql    16896 Apr  6 11:58 _graphite_django_content_type_main_47e_1_1d_B_0.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_django_content_type_status_477_1_1d.tokudb
-rw-rw----. 1 mysql mysql    16896 Apr  6 11:58 _graphite_django_migrations_main_48b_1_1d_B_0.tokudb
-rw-rw----. 1 mysql mysql    16384 Apr  6 11:58 _graphite_django_migrations_status_484_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_django_session_key_django_session_de54fa62_491_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_django_session_main_491_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_django_session_status_491_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_events_event_main_49b_2_1d.tokudb
-rw-rw----. 1 mysql mysql    16384 Apr  6 11:58 _graphite_events_event_status_49b_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_tagging_taggeditem_key_tagging_taggeditem_417f1b1c_4b1_5_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_tagging_taggeditem_key_tagging_taggeditem_76f094bc_4b1_6_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_tagging_taggeditem_key_tagging_taggeditem_af31437c_4b1_4_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_tagging_taggeditem_key_tagging_taggeditem_tag_id_3d53f09d_uniq_4b1_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_tagging_taggeditem_main_4b1_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_tagging_taggeditem_status_4b1_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_tagging_tag_key_name_4a6_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_tagging_tag_main_4a6_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_tagging_tag_status_4a6_1_1d.tokudb
-rw-rw----. 1 mysql mysql  6160384 Apr 10 15:06 _graphite_tags_series_key_hash_4c3_1_1d_B_1.tokudb
-rw-rw----. 1 mysql mysql  7929856 Apr 10 15:06 _graphite_tags_series_main_4c3_1_1d_B_0.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 15:06 _graphite_tags_series_status_4bc_1_1d.tokudb
-rw-rw----. 1 mysql mysql   442368 Apr 10 15:06 _graphite_tags_seriestag_key_tags_seriestag_76f094bc_4d6_1_1d_B_3.tokudb
-rw-rw----. 1 mysql mysql   507904 Apr 10 15:06 _graphite_tags_seriestag_key_tags_seriestag_a08cee2d_4d6_1_1d_B_2.tokudb
-rw-rw----. 1 mysql mysql   507904 Apr 10 15:06 _graphite_tags_seriestag_key_tags_seriestag_b0304493_4d6_1_1d_B_4.tokudb
-rw-rw----. 1 mysql mysql   524288 Apr 10 15:06 _graphite_tags_seriestag_key_tags_seriestag_series_id_ad31c493_uniq_4d6_1_1d_B_1.tokudb
-rw-rw----. 1 mysql mysql   901120 Apr 10 15:06 _graphite_tags_seriestag_main_4d6_1_1d_B_0.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 15:06 _graphite_tags_seriestag_status_4cf_1_1d.tokudb
-rw-rw----. 1 mysql mysql  1179648 Apr 10 15:06 _graphite_tags_tagvalue_key_value_4f0_1_1d_B_1.tokudb
-rw-rw----. 1 mysql mysql  1736704 Apr 10 15:06 _graphite_tags_tagvalue_main_4f0_1_1d_B_0.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 15:06 _graphite_tags_tagvalue_status_4e9_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_url_shortener_link_main_4f8_2_1d.tokudb
-rw-rw----. 1 mysql mysql    16384 Apr  6 11:58 _graphite_url_shortener_link_status_4f8_1_1d.tokudb
-rw-rw----. 1 mysql mysql 79691776 Apr 10 02:38 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Apr 10 02:38 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Apr 10 02:36 ib_logfile1
drwx------. 2 mysql mysql     4096 Apr  6 11:58 kanboard
-rw-rw----. 1 mysql mysql    16896 Apr  6 11:58 _kanboard_settings_main_549_1_1d_B_0.tokudb
-rw-rw----. 1 mysql mysql    16384 Apr  6 11:58 _kanboard_settings_status_543_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _kanboard_user_has_notifications_key_user_has_notifications_ibfk_2_569_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _kanboard_user_has_notifications_main_569_2_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _kanboard_user_has_notifications_status_569_1_1d.tokudb
-rw-------. 1 mysql mysql 26236353 Apr 10 15:09 log000000000006.tokulog29
drwx------. 2 mysql mysql      119 Apr  6 11:58 maxscale_schema
-rw-rw----. 1 mysql mysql        0 Apr  5 17:39 multi-master.info
drwx------. 2 mysql root      4096 Apr  6 09:59 mysql
drwx------. 2 mysql mysql       20 Apr  5 17:36 performance_schema
-rw-rw----. 1 mysql mysql        6 Apr  5 17:39 platformdb-isa-01.pid
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 tokudb.directory
-rw-rw----. 1 mysql mysql    16384 Apr  5 17:22 tokudb.environment
-rw-------. 1 mysql mysql        0 Apr  5 17:22 __tokudb_lock_dont_delete_me_data
-rw-------. 1 mysql mysql        0 Apr  5 17:22 __tokudb_lock_dont_delete_me_environment
-rw-------. 1 mysql mysql        0 Apr  5 17:22 __tokudb_lock_dont_delete_me_logs
-rw-------. 1 mysql mysql        0 Apr  5 17:22 __tokudb_lock_dont_delete_me_recovery
-rw-------. 1 mysql mysql        0 Apr  5 17:22 __tokudb_lock_dont_delete_me_temp
-rw-rw----. 1 mysql mysql    32768 Apr 10 15:06 tokudb.rollback
  • Those tables are rather small. Are you sure you need Tokudb? (Or, perhaps you are just starting to gather data?) – Rick James Apr 30 '18 at 14:41
  • Just an example. Less about the size, more about the mess. It can be difficult to see db usage when all the files are mix between different dbs in the same folder. – nelaaro Apr 30 '18 at 14:46
1

You can do this by setting the server variable "tokudb_dir_per_db"

It is on by default in precona, but not in MariaDB.

MariaDB [(none)]> select @@tokudb_dir_per_db;
+---------------------+
| @@tokudb_dir_per_db |
+---------------------+
|                   0 |
+---------------------+

MariaDB [(none)]> set global tokudb_dir_per_db = 1;

MariaDB [(none)]> select @@tokudb_dir_per_db;
+---------------------+
| @@tokudb_dir_per_db |
+---------------------+
|                   1 |
+---------------------+

Besure to put this on all your slaves as well.

To fix existing databases that already have all their files in the mysql data root. You need to rename all the tables into a different database name.

MariaDB [(none)]> create database tmp_rename

Then rename the tables into a temp database and rename the tables back. This should take a few minutes depending on how many files need to be renamed. You should have a working database without any config changes required for your applications.

mysql  graphite -sNe 'show tables' | while read table;  do mysql  -sNe "rename table graphite.$table to tmp_rename.$table"; done  
mysql  tmp_rename -sNe 'show tables' | while read table;  do mysql  -sNe "rename table tmp_rename.$table to graphite.$table"; done   

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.