Re: speed up index creation on existing tables?
Sounds like a very nice idea to me, but this could be a problem if temp tables need to be bigger than RAM+Swap, which could easily be the case in a table with 100,000,000 rows. Gabriel PREDA schrieb: For this table this is to late... leave it running... If you want to do this on another table(s)... and in general on huge loaded MySQL servers I recomend the following... Create a directory let's say /mnt/mem_fs Mount in it /dev/shm use tmpfs as filesystem... Now you have a directory that stores all the info in memory... if the available alocated memory in consumed then it will start swaping... but compute all values so that it dosen't... In my.cnf set a MySQL directive like: tmpdir = /mnt/mem_fs This way MySQL will create temporary tables in memory rather than creating them on disk !!! I'm pretty sure you can figure out the speed improvment ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
speed up index creation on existing tables?
I have a table with ~100,000,000 rows. I recently discovered that I need to start using one of the non-indexed columns in WHERE clauses. As expected the performance is horrid. I decided to bite the bullet and create an index (innodb): mysql show full processlist\G *** 1. row *** Id: 109496 User: root Host: localhost db: orson Command: Query Time: 161079 State: copy to tmp table Info: create index ix_card on game (ender) Its already been running 2 days. I probably need to index some more columns in another table -- is there anything that can be done to speed this up? dump and re-import is impractical. Server is decent -- 4xcpu, 16GB RAM... Thanks, ds -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speed up index creation on existing tables?
For this table this is to late... leave it running... If you want to do this on another table(s)... and in general on huge loaded MySQL servers I recomend the following... Create a directory let's say /mnt/mem_fs Mount in it /dev/shm use tmpfs as filesystem... Now you have a directory that stores all the info in memory... if the available alocated memory in consumed then it will start swaping... but compute all values so that it dosen't... In my.cnf set a MySQL directive like: tmpdir = /mnt/mem_fs This way MySQL will create temporary tables in memory rather than creating them on disk !!! I'm pretty sure you can figure out the speed improvment ! -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer On 10/5/06, David Sparks [EMAIL PROTECTED] wrote: Its already been running 2 days. I probably need to index some more columns in another table -- is there anything that can be done to speed this up? dump and re-import is impractical. Server is decent -- 4xcpu, 16GB RAM... Thanks, ds -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
