13GB DB - Performance Question [message #1295] |
Wed, 23 May 2007 16:22  |
GeoffreyF67 Messages: 21 Registered: May 2007 |
Junior Member |

|
|
I have a 13 GB MYSQL table with the following definition:
CREATE TABLE `WikiParagraphs` (
`ID` int(10) unsigned NOT NULL auto_increment,
`Paragraph` text NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=73035281 ;
I query it like this:
select Paragraph from WikiParagraphs where ID in (1,2,3,4)
the 1,2,3,4 bit comes from the Sphinx FullText engine that gives me the IDs I need that match my query within about 500 milliseconds.
But retrieving the data itself takes approximately 3-6 seconds.
Obviously, I'd like to speed this query up.
Any ideas?
G-Man
|
|
|
|
|
|
|
| Re: 13GB DB - Performance Question [message #1300 is a reply to message #1295 ] |
Wed, 23 May 2007 17:06   |
GeoffreyF67 Messages: 21 Registered: May 2007 |
Junior Member |

|
|
Well, if it's faster, I could break it down into chunks of 50 or 100 until I have enough data I guess...
+---------------------------------+-------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------+
| have_innodb | YES |
| innodb_additional_mem_pool_size | 12582912 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 134217728 |
| innodb_data_file_path | innodb/ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_fast_shutdown | ON |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 30 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 33554432 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
+---------------------------------+-------------------------------+
The server has 4GB of memory.
[Updated on: Wed, 23 May 2007 17:10] G-Man
|
|
|
|
|
|
|
|
| Re: 13GB DB - Performance Question [message #1307 is a reply to message #1295 ] |
Thu, 24 May 2007 06:25   |
bluesaga Messages: 20 Registered: December 2006 |
Junior Member |
|
|
I really wouldnt recommend the alter table, talking from experiance (50 gig table) it will be slow, and crummy!
I do queries similar to:
select Paragraph from WikiParagraphs where ID in (1,2,3,4, *1000)
and im looking at subsecond queries!
I think you should improve the buffers your applying to these tables sufficiently!
One thing that will help you, is do all the matching with sphinx, do any phpside ordering of the data and then only pull the data you actually need! This should help greatly, in terms of speed at least.
|
|
|
|
| Re: 13GB DB - Performance Question [message #1309 is a reply to message #1295 ] |
Thu, 24 May 2007 13:25   |
bluesaga Messages: 20 Registered: December 2006 |
Junior Member |
|
|
You could try forcing the charsets?
Are both tables in the same character set anyway?
Oh wait, are you SURE it dropped the rows? innoDB does not keep a record of the actual amount of rows in the table, so it may be that?
The only way (i can think of) is to do something similar to:
SELECT count(*) FROM innoDBtable
But that may take a long time, you could try two seperate count()'s from a boolean field (do a search for true and one for false) may be quicker.
[Updated on: Thu, 24 May 2007 13:28]
|
|
|
|
|
|
|
|
|
|
|
| Re: 13GB DB - Performance Question [message #1344 is a reply to message #1311 ] |
Thu, 31 May 2007 15:01  |
|
If the query exceeds the maximum amount of memory available, it will fail. I've had this happen before, in fact, i have it happen nightly at least once for a query I have yet to optimize as MySQL is running its backups processes
|
|
|