Long time to run query

[ianicr]

I have a database with 2 tables in it. table1 has 250000 records,table2 has 500000. I've never worked with this size tables before and I just ran a:
select * from table2;
I've done this with 10s of thousands of records and its been almost instant. However on this RH9, Xeon 2.4, 256mb it takes 3 mins 4 seconds. Is this normal?

Thanks

 

[hos2]

have you also reserved enough cash memory for you db service ??

 

[ianicr]

How do I find that out? I just looked at the size of the table in /var/lib/mysql/database and its 500mb! Is this normal?

 

[vanekl]

It's normal to take 3 mins 4 seconds when you are running a
large database on a machine that doesn't have enough RAM to
store the entire database in RAM. You're actually having to
access the disk, which is *much* slower than RAM.


If you want faster access times then the best thing you
could do is increase RAM. (Personally I wouldn't have considered
using anything less than 512MB.)

Have you tuned the MySQL variables? Do a 'show variables;' to
see them. What table type are you using? Do a 'show create
table <some_table_name>;' to see what type of tables you are
using. Some MySQL variables are tuned differently depending on
the table type you are using.

At the very least, you should be setting 'key_buffer_size'.
If you are using innodb tables, you should also be setting
'innodb_buffer_pool_size'. Those are the two biggies.
If you are using innodb
tables, then innodb_buffer_pool_size (for a 256MB machine)
should be about 128MB. Key_buffer_size should probably be
set to around 64MB. There are a host of other variables you
can be tuning, too.


Finally, is 500MB too big for a database of 750,000 records?
It depends on how much info is stored in each record.
500MB/750,000 records = 666 bytes/record (max, not taking
into account indexes and empty buffers). If your tables hold only a few
integers in each record then, yes, 666 bytes/record is excessive and
something is wrong. But if these tables have a dozen or more
fields with some of them being text then, no, this would be
reasonable.

 

[ianicr]

In my show variables i have:
| innodb_buffer_pool_size | 8388608
and
| key_buffer_size | 8388600

How do I go about changing them?

 

[vanekl]

You should find a my.cnf configuration file at /etc/my.cnf.
If you don't have permissions to modify it, there's another
configuration file at DATADIR/my.cnf, i.e. /var/lib/mysql/database/my.cnf.

These variables should be set in the [mysqld] block of this .cnf file.

[mysqld]

set-variable = innodb_buffer_pool_size=128M
set-variable = innodb_additional_mem_pool_size=5M
set-variable = innodb_log_file_size=10M
set-variable = innodb_log_buffer_size=4M

set-variable = key_buffer_size=64M

You can also see

http://gnu.kookel.org/ftp/mysql/doc/en/Option_files.html