Re: Performance Available
Thanks everybody for attention! I make some changes in hardare and in my table, and obtain a average response times of 0.09 second. Now i have a good time ! :-) Show you my changes: 1-Update MySql 4.1.18 - 5.0.21. 2-Upgrade my memory to 3 Gb. 3-Severance of temporary/logs files and data files in distinct HDs. 4-Upgrade of key_buffer 384M - 512M. 5-Run command ALTER TABLE ORDER BY on column.(Dan Suggestion. Thanks!!) I will reproduce this changes in other machine to detect the relevant change! Show you my relevant variable in config file: datadir = /data/myisam key_buffer = 512M max_allowed_packet = 1M table_cache = 200 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M thread_concurrency = 8 innodb_data_home_dir = /data/ibdata innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/iblog innodb_log_arch_dir = /usr/local/mysql/data/iblog innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Show you my compile options: (OS: Debian Linux 3.1; Hardware Intel Xeon 2.8) CFLAGS=-O3 CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions -fno-rtti -fomit-frame-pointer -ffixed-ebp ./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql --with-unix-socket-path=/tmp/mysql.sock --with-mysqld-ldflags=-all-static --enable-assembler Thanks everybody!! == Atenciosamente, Jan Gomes - [EMAIL PROTECTED] The query is: SELECT * FROM table where id_table2 in (1652272,1652293,1652316,1652362); +-+---+---+--+ | select_type | table | type | possible_keys | +-+---+---+--+ | SIMPLE | table | range | PRIMARY,table_idx1| +-+---+---+--+ ++-+--+--+--+ | key | key_len | ref | rows | Extra | +--- +-+--+--+--+ | table_idx1 | 4 | NULL | 72 | Using where| ++-+--+--+-+ If this is what the EXPLAIN is, and you are getting 4 second response times, I would guess that something is amiss. Have you tried running an ANALYZE TABLE on your table to rebuild the index? Be aware that this will issue a table lock on the table, do do it off hours. Then, issue the query again and post back time results. Also, post the output of SELECT @@version; Cheers Jay PS.: Pardon me for some thing, my english is not very good It's just fine! Hy Jay, Thanks for you attention. Show you my structure and EXPLAIN: CREATE TABLE `table` ( `id_table1` int(10) unsigned NOT NULL default '0', `id_table2` int(10) unsigned NOT NULL default '0', `field1`smallint(5) unsigned NOT NULL default '0', `field2`mediumint(8) unsigned NOT NULL default '0', `textField` text NOT NULL, PRIMARY KEY (`id_table1`,`id_table2`), KEY `table_idx1` (`id_table2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 You don't show the query you are explaining. It certainly can't be the one in your previous post below, because column names don't match. +-+---+---++ | select_type | table | type | possible_keys | +-+---+---++ | SIMPLE | table | range | PRIMARY,table_idx1| +-+---+---++ ++-+--+--+-+ | key | key_len | ref | rows | Extra | +--- +-+--+--+-+ | table_idx1 | 4 | NULL | 72 | Using where| ++-+--+--+-+ Please post your exact table schema using SHOW CREATE TABLE, and your exact query, along with an EXPLAIN SELECT for the query. Thanks! -jay Jan Gomes wrote: Hy Guys, I have a simple structure of tables, howewer has 50 million of registers and 2,5 GB of data. The table is MyIsam and has 4 integer fields and 1 Text field, one primary key with two integer fields and one btree index with one integer field. There is a select in this table using an index(with one integer field), whith a set value for this field ( select * from table where field in (value1,value2,value3,value4,etc) ). This select has delay 4s average. Is this a good time for the select ? How can I run this select in less time? I had make this optimization: 1-Compress the index 2-sort the index with myisamchk PS.: This table is read-only, hasn't an insert, update or delete. == Atenciosamente, Jan Gomes - [EMAIL PROTECTED] -- Jay Pipes
Re: Performance Available
Jan, right now I would say performance sounds pretty good for the amount of data you have. 50 million records / 2.5 GB of data is a pretty sizable dataset, so 4 seconds to retrieve a handful of records seems decent. Some suggestions for things you could do to possibly improve performance: 1 - Change to the compressed table format. See http://dev.mysql.com/doc/refman/5.0/en/compressed-format.html and http://techrepublic.com.com/5100-9592-5852557.html This will in theory allow a given number of records to be read off disk more quickly, since they are smaller. 2 - Run an ALTER TABLE command to ORDER BY the field you search against most commonly. This will in theory allow the database engine to read the table in a more orderly fashion without (as much) disk thrashing. 3 - upgrade memory in your server and change MySQL cache settings to match, in an attempt to keep the indexes and data in RAM. With the amount of data you have this should be possible but you'll need to make sure your hardware, OS, and MySQL release support an appropriate amount of RAM. 4 - if you can't do #3, and maybe even if you can, make sure you're using a very fast disk system (I'd guess you already are). I'd look at at a hardware RAID 5 or 1+0 setup on 15K RPM U320 SCSI disks. Fibre is nice if you can get it but tends to get expensive quickly. Of course #3 and #4 will be dependent on budget and importance of this problem, not to mention your MySQL hosting setup/relationship. Hope this helps, Dan Jan Gomes wrote: Hy Guys, I have a simple structure of tables, howewer has 50 million of registers and 2,5 GB of data. The table is MyIsam and has 4 integer fields and 1 Text field, one primary key with two integer fields and one btree index with one integer field. There is a select in this table using an index(with one integer field), whith a set value for this field ( select * from table where field in (value1,value2,value3,value4,etc) ). This select has delay 4s average. Is this a good time for the select ? How can I run this select in less time? I had make this optimization: 1-Compress the index 2-sort the index with myisamchk PS.: This table is read-only, hasn't an insert, update or delete. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Available
Please post your exact table schema using SHOW CREATE TABLE, and your exact query, along with an EXPLAIN SELECT for the query. Thanks! -jay Jan Gomes wrote: Hy Guys, I have a simple structure of tables, howewer has 50 million of registers and 2,5 GB of data. The table is MyIsam and has 4 integer fields and 1 Text field, one primary key with two integer fields and one btree index with one integer field. There is a select in this table using an index(with one integer field), whith a set value for this field ( select * from table where field in (value1,value2,value3,value4,etc) ). This select has delay 4s average. Is this a good time for the select ? How can I run this select in less time? I had make this optimization: 1-Compress the index 2-sort the index with myisamchk PS.: This table is read-only, hasn't an insert, update or delete. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Available
Hy Jay, Thanks for you attention. Show you my structure and EXPLAIN: CREATE TABLE `table` ( `id_table1` int(10) unsigned NOT NULL default '0', `id_table2` int(10) unsigned NOT NULL default '0', `field1`smallint(5) unsigned NOT NULL default '0', `field2`mediumint(8) unsigned NOT NULL default '0', `textField` text NOT NULL, PRIMARY KEY (`id_table1`,`id_table2`), KEY `table_idx1` (`id_table2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 +-+---+---++ | select_type | table | type | possible_keys | +-+---+---++ | SIMPLE | table | range | PRIMARY,table_idx1| +-+---+---++ ++-+--+--+-+ | key | key_len | ref | rows | Extra | +--- +-+--+--+-+ | table_idx1 | 4 | NULL | 72 | Using where| ++-+--+--+-+ Please post your exact table schema using SHOW CREATE TABLE, and your exact query, along with an EXPLAIN SELECT for the query. Thanks! -jay Jan Gomes wrote: Hy Guys, I have a simple structure of tables, howewer has 50 million of registers and 2,5 GB of data. The table is MyIsam and has 4 integer fields and 1 Text field, one primary key with two integer fields and one btree index with one integer field. There is a select in this table using an index(with one integer field), whith a set value for this field ( select * from table where field in (value1,value2,value3,value4,etc) ). This select has delay 4s average. Is this a good time for the select ? How can I run this select in less time? I had make this optimization: 1-Compress the index 2-sort the index with myisamchk PS.: This table is read-only, hasn't an insert, update or delete. == Atenciosamente, Jan Gomes - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Available
Jan Gomes wrote: Hy Jay, Thanks for you attention. Show you my structure and EXPLAIN: CREATE TABLE `table` ( `id_table1` int(10) unsigned NOT NULL default '0', `id_table2` int(10) unsigned NOT NULL default '0', `field1`smallint(5) unsigned NOT NULL default '0', `field2`mediumint(8) unsigned NOT NULL default '0', `textField` text NOT NULL, PRIMARY KEY (`id_table1`,`id_table2`), KEY `table_idx1` (`id_table2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 You don't show the query you are explaining. It certainly can't be the one in your previous post below, because column names don't match. +-+---+---++ | select_type | table | type | possible_keys | +-+---+---++ | SIMPLE | table | range | PRIMARY,table_idx1| +-+---+---++ ++-+--+--+-+ | key | key_len | ref | rows | Extra | +--- +-+--+--+-+ | table_idx1 | 4 | NULL | 72 | Using where| ++-+--+--+-+ Please post your exact table schema using SHOW CREATE TABLE, and your exact query, along with an EXPLAIN SELECT for the query. Thanks! -jay Jan Gomes wrote: Hy Guys, I have a simple structure of tables, howewer has 50 million of registers and 2,5 GB of data. The table is MyIsam and has 4 integer fields and 1 Text field, one primary key with two integer fields and one btree index with one integer field. There is a select in this table using an index(with one integer field), whith a set value for this field ( select * from table where field in (value1,value2,value3,value4,etc) ). This select has delay 4s average. Is this a good time for the select ? How can I run this select in less time? I had make this optimization: 1-Compress the index 2-sort the index with myisamchk PS.: This table is read-only, hasn't an insert, update or delete. == Atenciosamente, Jan Gomes - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Available
Hy Jay, You don't show the query you are explaining. The query is: SELECT * FROM table where id_table2 in (1652272,1652293,1652316,1652362); +-+---+---+--+ | select_type | table | type | possible_keys | +-+---+---+--+ | SIMPLE | table | range | PRIMARY,table_idx1| +-+---+---+--+ ++-+--+--+--+ | key | key_len | ref | rows | Extra | +--- +-+--+--+--+ | table_idx1 | 4 | NULL | 72 | Using where| ++-+--+--+-+ It certainly can't be the one in your previous post below, because column names don't match. Yes, this is the same, but i hide the real name of table and column for security. PS.: Pardon me for some thing, my english is not very good Jan Gomes wrote: Hy Jay, Thanks for you attention. Show you my structure and EXPLAIN: CREATE TABLE `table` ( `id_table1` int(10) unsigned NOT NULL default '0', `id_table2` int(10) unsigned NOT NULL default '0', `field1`smallint(5) unsigned NOT NULL default '0', `field2`mediumint(8) unsigned NOT NULL default '0', `textField` text NOT NULL, PRIMARY KEY (`id_table1`,`id_table2`), KEY `table_idx1` (`id_table2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 You don't show the query you are explaining. It certainly can't be the one in your previous post below, because column names don't match. +-+---+---++ | select_type | table | type | possible_keys | +-+---+---++ | SIMPLE | table | range | PRIMARY,table_idx1| +-+---+---++ ++-+--+--+-+ | key | key_len | ref | rows | Extra | +--- +-+--+--+-+ | table_idx1 | 4 | NULL | 72 | Using where| ++-+--+--+-+ Please post your exact table schema using SHOW CREATE TABLE, and your exact query, along with an EXPLAIN SELECT for the query. Thanks! -jay Jan Gomes wrote: Hy Guys, I have a simple structure of tables, howewer has 50 million of registers and 2,5 GB of data. The table is MyIsam and has 4 integer fields and 1 Text field, one primary key with two integer fields and one btree index with one integer field. There is a select in this table using an index(with one integer field), whith a set value for this field ( select * from table where field in (value1,value2,value3,value4,etc) ). This select has delay 4s average. Is this a good time for the select ? How can I run this select in less time? I had make this optimization: 1-Compress the index 2-sort the index with myisamchk PS.: This table is read-only, hasn't an insert, update or delete. == Atenciosamente, Jan Gomes - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Available
Jan Gomes wrote: The query is: SELECT * FROM table where id_table2 in (1652272,1652293,1652316,1652362); +-+---+---+--+ | select_type | table | type | possible_keys | +-+---+---+--+ | SIMPLE | table | range | PRIMARY,table_idx1| +-+---+---+--+ ++-+--+--+--+ | key | key_len | ref | rows | Extra | +--- +-+--+--+--+ | table_idx1 | 4 | NULL | 72 | Using where| ++-+--+--+-+ If this is what the EXPLAIN is, and you are getting 4 second response times, I would guess that something is amiss. Have you tried running an ANALYZE TABLE on your table to rebuild the index? Be aware that this will issue a table lock on the table, do do it off hours. Then, issue the query again and post back time results. Also, post the output of SELECT @@version; Cheers Jay PS.: Pardon me for some thing, my english is not very good It's just fine! Hy Jay, Thanks for you attention. Show you my structure and EXPLAIN: CREATE TABLE `table` ( `id_table1` int(10) unsigned NOT NULL default '0', `id_table2` int(10) unsigned NOT NULL default '0', `field1`smallint(5) unsigned NOT NULL default '0', `field2`mediumint(8) unsigned NOT NULL default '0', `textField` text NOT NULL, PRIMARY KEY (`id_table1`,`id_table2`), KEY `table_idx1` (`id_table2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 You don't show the query you are explaining. It certainly can't be the one in your previous post below, because column names don't match. +-+---+---++ | select_type | table | type | possible_keys | +-+---+---++ | SIMPLE | table | range | PRIMARY,table_idx1| +-+---+---++ ++-+--+--+-+ | key | key_len | ref | rows | Extra | +--- +-+--+--+-+ | table_idx1 | 4 | NULL | 72 | Using where| ++-+--+--+-+ Please post your exact table schema using SHOW CREATE TABLE, and your exact query, along with an EXPLAIN SELECT for the query. Thanks! -jay Jan Gomes wrote: Hy Guys, I have a simple structure of tables, howewer has 50 million of registers and 2,5 GB of data. The table is MyIsam and has 4 integer fields and 1 Text field, one primary key with two integer fields and one btree index with one integer field. There is a select in this table using an index(with one integer field), whith a set value for this field ( select * from table where field in (value1,value2,value3,value4,etc) ). This select has delay 4s average. Is this a good time for the select ? How can I run this select in less time? I had make this optimization: 1-Compress the index 2-sort the index with myisamchk PS.: This table is read-only, hasn't an insert, update or delete. == Atenciosamente, Jan Gomes - [EMAIL PROTECTED] -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED]mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
