Re: example when indexing hurts simple select?
Hi Gasper, MySql allows to package the index - to get its size smaller and to gain performance. Some information about that can be found here: http://www.mysqlperformanceblog.com/2006/05/13/to-pack-or-not-to-pack-myisam-key-compression/ Gaspar Bakos schrieb: Hi, RE: Have you tried analyze table x; This was quick: mysql analyze table TEST; Table Op Msg_typeMsg_text CAT.TEST analyze status Table is already up to date -- mysql show index from TEST; +---+++--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---+++--+-+---+-+--++--++-+ | TEST | 1 | MMtestfiel |1 | MMtestfiel | A | 838 | NULL | NULL | | BTREE | NULL| | TEST | 1 | MMi_m |1 | MMi_m | A | 25857 | NULL | NULL | YES | BTREE | NULL| +---+++--+-+---+-+--++--++-+ --- I am trying to figure out what the Packed field means. Gaspar smime.p7s Description: S/MIME Cryptographic Signature
example when indexing hurts simple select?
Hello, There is a table (TEST) with ~100 million records, 70 columns (mostly integers, some doubles, and a few short fixed char()), and has a ~100Gb size. The table has a single (not unique) index on one integer column: MMi. If I invoke a simple select based on MMi, then the selection is VERY slow: nohup time mysql CAT -u catadmin -p$MPWD -e create table test2 select * from TEST where MMi 9000; ( this selects only ~0.5 % of the table, by the way, so test2 is a small table, and the time is not spent with writing it on disk) Time used: 47 minutes: 0.00user 0.00system 47:17.37elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+416minor)pagefaults 0swaps If I do the same but ignore the index, the select time drops to 1/5th !!! nohup time mysql CAT -u catadmin -p$MPWD -e create table test3 \ select * from TEST ignore index (MMi) where \ MMi 9000; Time used: 11 minutes: 0.00user 0.00system 11:08.23elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+415minor)pagefaults 0swaps Without the index, MySQL does a simple thing; it reads in sequentially the 100Gb database, and while reading, it parses the lines, and determines if the MMi is 9000. This is done with about 16Mb/s speed. With the index, it performs a large number of random seeks. The data (.MYD) is probably not organized on the disk according to sorted MMi. Questions; 1. Is there a way to decrease random seeks? E.g. mysqld config parameters, increase some buffer/cache sizes? 2. Optimize table: is there a way to rearrange data so that random seeks are minimized? 3. If we have to live with large number of random seeks does anyone know how the underlying FS can be optimized? Cheers, Gaspar ( All this TEST DB is kept on FC3 Redhat + MySQL 5.0.22, XFS filesystem on a 1.5Tb RAID-5 array of 4 SATA disks (served by a 3ware RAID controller). The computer is running on two opteron 2.0GHZ CPUs and 4Gb RAM. ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: example when indexing hurts simple select?
Questions; 1. Is there a way to decrease random seeks? E.g. mysqld config parameters, increase some buffer/cache sizes? 2. Optimize table: is there a way to rearrange data so that random seeks are minimized? 3. If we have to live with large number of random seeks does anyone know how the underlying FS can be optimized? What is the EXPLAIN output of each? Have you tried analyze table x; optimize table x; On a 100GB, these might table a while. You probably want to LOCK the table before running them. Is it MyISAM or Innodb ? -- Philip M. Gollucci ([EMAIL PROTECTED]) 323.219.4708 Consultant / http://p6m7g8.net/Resume/resume.shtml Senior Software Engineer - TicketMaster - http://ticketmaster.com 1024D/A79997FA F357 0FDD 2301 6296 690F 6A47 D55A 7172 A799 97F It takes a minute to have a crush on someone, an hour to like someone, and a day to love someone, but it takes a lifetime to forget someone... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: example when indexing hurts simple select?
Hi, Philip, RE: What is the EXPLAIN output of each? OK, first I naively typed: explain create table test2 select * from TEST where MMi 9000; but of course, this does not work. The simple select that uses MMi_m as index (and takes up to an hour): mysql explain select * from TEST where MMi_m 9000; ++-+---+---+---+---+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--++-+ | 1 | SIMPLE | TEST | range | MMi_m | MMi_m | 3 | NULL | 406649 | Using where | ++-+---+---+---+---+-+--++-+ The select with ignoreing the index (takes only 11 minutes) mysql explain select * from TEST ignore key (MMi_m) where MMi_m 9000; ++-+---+--+---+--+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+---+-+ | 1 | SIMPLE | TEST | ALL | NULL | NULL | NULL| NULL | 470992970 | Using where | ++-+---+--+---+--+-+--+---+-+ Have you tried analyze table x; optimize table x; Not yet. As regards optimize table, I thought it would not make too much sense, because: OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). But I have just uploaded this table from ASCII, and made no changes. Nevertheless, I will give a try, maybe there is some feature of OPTIMIZE TABLE I don't know of. What did you think of? Is it MyISAM or Innodb ? MyISAM. I'll keep you posted. I am very curious about how this can be resolved. Cheers, Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
