I created a new index on a table with 35million records and its been running for nearly 1 day now. Previously when I created indexes it took 20 minutes, there columns were however floats. The new idnex is on a varchar(45)
我在一张有3500万条记录的桌子上创建了一个新索引,现在已经运行了将近1天。以前当我创建索引需要花费20分钟时,然而有些列是浮动的。新的idnex在varchar(45)上
I used the processlist command which shows the index creation is still in progress with the following output
我使用了processlist命令,该命令显示索引创建仍在使用以下输出
65417 | Repair with keycache | CREATE INDEX insert_index on checkins(dateinserted)
I was wondering if anyone could give me advice on finding out if the query is actually dead and is just sitting in the process list. Maybe something has gone wrong at some stage and I'm not aware.
我想知道是否有人可以给我建议,以查明查询是否真的死了,只是坐在进程列表中。也许某些事情在某个阶段出了问题,我不知道。
Thanks
2 个解决方案
#1
6
Your index is building, but very slowly.
你的索引正在构建,但速度很慢。
MySQL has two methods available for building indexes:
MySQL有两种可用于构建索引的方法:
- by sorting. This is the fastest method, but uses up a lot of memory.
- by keycache. Slow, slow, slow - but uses up little memory.
通过排序。这是最快的方法,但占用了大量内存。
通过keycache。缓慢,缓慢,缓慢 - 但耗尽了很少的记忆。
The keycache method is a bit like insertion sort: values are inserted into the index one at a time. This is the same method used by the server when the INSERT statement is used to add rows to the table.
keycache方法有点像插入排序:一次将值插入索引中。这与使用INSERT语句向表中添加行时服务器使用的方法相同。
The sorting method sorts all the values using quicksort, and then builds the index from that. It is very fast, but requires a lot of memory and temporary disk space.
排序方法使用quicksort对所有值进行排序,然后从中构建索引。它非常快,但需要大量内存和临时磁盘空间。
Some server variables can increase the space available to the sorting method, and so allow it to work with larger tables. See myisam_max_sort_file_size
某些服务器变量可以增加排序方法的可用空间,因此允许它使用更大的表。请参阅myisam_max_sort_file_size
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_myisam_max_sort_file_size
On Linux, you can track the progress of the index repair by checking the size of the temporary files that are used to build the index. The following command will list all the files held open by the MySQL process:
在Linux上,您可以通过检查用于构建索引的临时文件的大小来跟踪索引修复的进度。以下命令将列出MySQL进程保持打开的所有文件:
sudo ls -l /proc/[mysql-pid]/fd
Then check out the size of ones with hashes in their name - these are the temporary files.
然后查看名称中带有哈希值的大小 - 这些是临时文件。
#2
2
Keep in mind the index size will be 35M*45 at least. If it's a utf8 column then it will be 35M*45*3. That's over 4 gigs! If you don't have tons of RAM to support that it's going to have to do a lot of disk access and really kill performance.
请记住,索引大小至少为35M * 45。如果它是一个utf8列,那么它将是35M * 45 * 3。那是超过4场演出!如果你没有大量的RAM来支持它,它将不得不进行大量的磁盘访问并且真的会破坏性能。
Can you normalize this column out into another table?
你可以将这个列标准化为另一个表吗?
If not do the values tend to vary sufficiently in say the first 8 characters? You might be able to get away with just indexing the first 8 then.
如果没有,那么这些值往往会在前8个字符中变化很大?你可能可以通过索引前8个而逃脱。
#1
6
Your index is building, but very slowly.
你的索引正在构建,但速度很慢。
MySQL has two methods available for building indexes:
MySQL有两种可用于构建索引的方法:
- by sorting. This is the fastest method, but uses up a lot of memory.
- by keycache. Slow, slow, slow - but uses up little memory.
通过排序。这是最快的方法,但占用了大量内存。
通过keycache。缓慢,缓慢,缓慢 - 但耗尽了很少的记忆。
The keycache method is a bit like insertion sort: values are inserted into the index one at a time. This is the same method used by the server when the INSERT statement is used to add rows to the table.
keycache方法有点像插入排序:一次将值插入索引中。这与使用INSERT语句向表中添加行时服务器使用的方法相同。
The sorting method sorts all the values using quicksort, and then builds the index from that. It is very fast, but requires a lot of memory and temporary disk space.
排序方法使用quicksort对所有值进行排序,然后从中构建索引。它非常快,但需要大量内存和临时磁盘空间。
Some server variables can increase the space available to the sorting method, and so allow it to work with larger tables. See myisam_max_sort_file_size
某些服务器变量可以增加排序方法的可用空间,因此允许它使用更大的表。请参阅myisam_max_sort_file_size
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_myisam_max_sort_file_size
On Linux, you can track the progress of the index repair by checking the size of the temporary files that are used to build the index. The following command will list all the files held open by the MySQL process:
在Linux上,您可以通过检查用于构建索引的临时文件的大小来跟踪索引修复的进度。以下命令将列出MySQL进程保持打开的所有文件:
sudo ls -l /proc/[mysql-pid]/fd
Then check out the size of ones with hashes in their name - these are the temporary files.
然后查看名称中带有哈希值的大小 - 这些是临时文件。
#2
2
Keep in mind the index size will be 35M*45 at least. If it's a utf8 column then it will be 35M*45*3. That's over 4 gigs! If you don't have tons of RAM to support that it's going to have to do a lot of disk access and really kill performance.
请记住,索引大小至少为35M * 45。如果它是一个utf8列,那么它将是35M * 45 * 3。那是超过4场演出!如果你没有大量的RAM来支持它,它将不得不进行大量的磁盘访问并且真的会破坏性能。
Can you normalize this column out into another table?
你可以将这个列标准化为另一个表吗?
If not do the values tend to vary sufficiently in say the first 8 characters? You might be able to get away with just indexing the first 8 then.
如果没有,那么这些值往往会在前8个字符中变化很大?你可能可以通过索引前8个而逃脱。