Good evening,

I've got to create a very large table: 180GB of data has to be stored.
In order to to this I'm using the following steps:

1. Create database structure including keys.
2. Disable keys: ALTER TABLE ut_netflow_4 DISABLE KEYS
3. Load data into the database.
4. Generate all index keys: myisamchk --sort_buffer_size=2700M
--key_buffer_size=2700M -r --tmpdir=/tmpdir --quick /ut_netflow_4.MYI
5. flush privileges: FLUSH TABLE ut_netflow_4
6. restart server.

Everything up to step 3 goes great. Step 4 and 5 on the other hand do
not seem to work. After running the command at step 4, I've got a huge
ut_netflow_4.MYI index file, but none of the indexes seem to be
enabled. If I use phpmysql to view the table, it says that the
cardinalities of the indexes are 0. Instead of the command at step 4,
I've also tried:
-myisamchk --sort_buffer_size=2700M --key_buffer_size=2700M
--sort-recover /ut_netflow_4.MYI
-myisamchk --sort_buffer_size=2700M --key_buffer_size=2700M
--parallel-recover /ut_netflow_4.MYI
-myisamchk --sort_buffer_size=2700M --key_buffer_size=2700M -r /ut_netflow_4.MYI
None of these seem to work either. The index file becomes larger, but
phpmyadmin keeps indicating that there are no indexes.

What am I doing wrong? How can I create and enable the indexes after
loading the data into the table?

Thnx in advance,

Joris

Joris Kinable wrote:
> Good evening,
>
> I've got to create a very large table: 180GB of data has to be stored.
> In order to to this I'm using the following steps:
>
> 1. Create database structure including keys.
> 2. Disable keys: ALTER TABLE ut_netflow_4 DISABLE KEYS
> 3. Load data into the database.
> 4. Generate all index keys: myisamchk --sort_buffer_size=2700M
> --key_buffer_size=2700M -r --tmpdir=/tmpdir --quick /ut_netflow_4.MYI
> 5. flush privileges: FLUSH TABLE ut_netflow_4
> 6. restart server.
>
> Everything up to step 3 goes great. Step 4 and 5 on the other hand do
> not seem to work. After running the command at step 4, I've got a huge
> ut_netflow_4.MYI index file, but none of the indexes seem to be
> enabled. If I use phpmysql to view the table, it says that the
> cardinalities of the indexes are 0. Instead of the command at step 4,
> I've also tried:
> -myisamchk --sort_buffer_size=2700M --key_buffer_size=2700M
> --sort-recover /ut_netflow_4.MYI
> -myisamchk --sort_buffer_size=2700M --key_buffer_size=2700M
> --parallel-recover /ut_netflow_4.MYI
> -myisamchk --sort_buffer_size=2700M --key_buffer_size=2700M -r /ut_netflow_4.MYI
> None of these seem to work either. The index file becomes larger, but
> phpmyadmin keeps indicating that there are no indexes.
>
> What am I doing wrong? How can I create and enable the indexes after
> loading the data into the table?

In step 4, I'd just do ALTER TABLE ENABLE KEYS. Manipulating MyISAM
files externally while the server is running is asking for trouble, in
my opinion.

In step 5, you aren't flushing privileges. You're flushing the table to
disk. But it's already flushed to disk, since you did step 4
externally. The restart will do the same thing.