The biggest problem I see is that you are using a text field and you don't have an index on it anyway. Maybe you should use a different field type and index it. That should make it a little bit faster.
Donny > -----Original Message----- > From: Lancashire, Pete [mailto:[EMAIL PROTECTED] > Sent: Tuesday, February 17, 2004 1:12 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: RE: what can i change in my.cnf/mysqld to get better perfomance > > no swapping so far > > I can see where I'm 'asking for' more then 8 GB, but for testing, > I gambling that the total amount of test data is less > then 8GB. also, i'm currently only doing tests with only one query at > a time. > > one thing i've notices is the memory being consumed by mysqld has > so far not gone above 1 GB (both resident, and requested). > > -pete > > > -----Original Message----- > From: walt [mailto:[EMAIL PROTECTED] > Sent: Tuesday, February 17, 2004 10:47 AM > To: Lancashire, Pete > Cc: [EMAIL PROTECTED] > Subject: Re: what can i change in my.cnf/mysqld to get better perfomance > > > Pete, > I was just glancing at this and it looks like you have about 13.5GB of > memory allocated on a 8GB machine (4096+4096+512+4096+1024). Are you > swapping badly?? > > walt > > "Lancashire, Pete" wrote: > > > > I'm doing some tests on a small Sun server, a 280R to demo MySQL vs. > > Oracle. For the demo I using a subset of a test table. > > > > The destination server will be a Sun 880 with 8 1.2 GHz CPU's, 16 GB > > RAM, Sun T3+ RAID Array. > > > > Any suggestions on settings or other changes would be helpful. > > > > Also what would be the best variables/settings to compile a binary for > > > a server of this type ? Gcc 3.3.2, Solaris 9. > > > > number of rows is only 10,735,291 > > > > MYD is 6.7 GBytes, and MYI is 1.4 GBytes. > > > > This table is a subset of the real table of about 120 Gb. > > > > the test/lab server is a small sun box with 2 each 900 MHz CPUs, and 8 > > > GB of RAM. > > > > I'm trying to avoid using disk until I get a better performance disk > > subsystem. > > > > The version is the website binary, solaris 64 bit. > > > > the setting i currently have > > > > [mysqld] > > set-variable = myisam_sort_buffer_size=4096M > > set-variable = sort_buffer_size=4096M > > set-variable = read_buffer_size=512M > > set-variable = key_buffer_size=4096M > > set-variable = table_cache=1024 > > > > tmp_table_size = 1024M > > max_heap_table_size = 1024M > > > > a search on text fields such as > > > > select substring_index(request_uri,'/',-1) jsp, count(*) from > > apache_jan where request_is_jsp = 1 group by jsp; > > > > takes 8 1/2 minutes. > > > > A rebuild of the indexes takes over 8 hours. > > > > the table format > > > > CREATE TABLE `apache_jan` ( > > `dt` datetime NOT NULL default '0000-00-00 00:00:00', > > `hostname` varchar(125) default NULL, > > `ip` varchar(15) default NULL, > > `ipn` int(10) unsigned default NULL, > > `user` varchar(125) default NULL, > > `timetaken` float default NULL, > > `auth` varchar(255) default NULL, > > `request` text, > > `request_type` varchar(255) default NULL, > > `request_uri` text, > > `request_file` varchar(255) default NULL, > > `request_is_jsp` tinyint(1) default NULL, > > `request_is_servlet` tinyint(1) default NULL, > > `request_query` text, > > `request_version` varchar(255) default NULL, > > `status` smallint(3) unsigned default NULL, > > `status_orig` smallint(3) unsigned default NULL, > > `bytes` int(11) default NULL, > > `bytes_i` int(11) default NULL, > > `bytes_o` int(11) default NULL, > > `header` text, > > `header_uri` text, > > `header_file` varchar(255) default NULL, > > `header_is_jsp` tinyint(1) default NULL, > > `useragent` varchar(255) default NULL, > > `biz_obj` varchar(255) default NULL, > > KEY `dt` (`dt`), > > KEY `hostname` (`hostname`), > > KEY `ip` (`ip`), > > KEY `status` (`status`), > > KEY `request_is_jsp` (`request_is_jsp`), > > KEY `request_is_servlet` (`request_is_servlet`), > > KEY `request_file` (`request_file`), > > KEY `header_file` (`header_file`), > > KEY `header_is_jsp` (`header_is_jsp`), > > KEY `ipn` (`ipn`) > > ) TYPE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=200000000 > > AVG_ROW_LENGTH=600 INDEX DIRECTORY='/DISK1/MYSQL/pdm/' > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
