temporary tables created on disk
killes@www.drop.org - January 15, 2007 - 14:52
| Project: | Drupal |
| Version: | 5.x-dev |
| Component: | database system |
| Category: | bug report |
| Priority: | normal |
| Assigned: | killes@www.drop.org |
| Status: | closed |
Description
because they use the default engine type which is myISAM in most cases. This small patch fixes this to use in-memory HEAP tables instead. It degrades gracefully to iuse MyISAm should HEAP not be available.
| Attachment | Size | Status | Test result | Operations |
|---|---|---|---|---|
| heap.patch | 1.61 KB | Ignored | None | None |
#1
seems like a good idea. subscribing
#2
How does this affect memory usage on the server? Is there a recommended "make an extra 128kb available" type deal?
Sounds like a great idea!
#3
There will be of course higher memory usage, but I am not sure how big it will be. The tables are usually not that big and the memory should be reclaimed at the end of the page request.
#4
Not being any sort of MySQL guru I'd assume you'd do this for a performance improvement? Any idea what kind of ball park improvement you'd get by doing this?
#5
AFAIK, this only affects searching, that is the only module in core that makes use of it. Views_search makes use of it as well.
#6
Subscribing
#7
Based on the docs on mysql.com, it seems the right approach for such a temporary table. I think I saw somewhere else that this is already running on drupal.org?
http://dev.mysql.com/doc/refman/4.1/en/memory-storage-engine.html
#8
Indeed, this patch is running without a problem on drupal.org for about two months or so. I've rerolled it to remove fuzz and declare it ready to go.
#9
Committed to HEAD. Should this be backported to 5?
#10
#11
IMO yes. If Neil applies this to 5 I'll apply it to 4.7.
#12
I don't see how this could be called a bug, and thus i am a bit suspicious about backport. my .02
#13
There is potentially scenarios where memory could be exhausted, e.g. on shared accounts.
Just to make sure this will not break memory limits, can we please have this simple patch run on Drupal.org for a week or so, with this line added:
watchdog('debug', 'Temp table search rows: ' . $count);Before the line:
$count_query = "SELECT $count";in do_search() in search.module.
I am more concerned about common terms that return too many rows, e.g. "drupal" or "module".
#14
I updated my development area and all of a sudden I was getting an invalid filename for some C:\TEMP file. The log message is below:
Can't create/write to file 'C:\TEMP\#sql_388_0.MYI' (Errcode: 13) query: SELECT DISTINCT b.* FROM DEV_blocks b LEFT JOIN DEV_blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.theme = 'garland' AND b.status = 1 AND (r.rid IN (1) OR r.rid IS NULL) ORDER BY b.region, b.weight, b.module in E:\devshop\www\drupalCVS\HEAD-DEV\includes\database.mysql.inc on line 173.I reverted this patch and things began working again. This error occurred following a successful install.php before creating the id 1 user.
#15
I updated my development area and all of a sudden I was getting an invalid filename for some C:\TEMP file. The log message is below:
Can't create/write to file 'C:\TEMP\#sql_388_0.MYI' (Errcode: 13) query: SELECT DISTINCT b.* FROM DEV_blocks b LEFT JOIN DEV_blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.theme = 'garland' AND b.status = 1 AND (r.rid IN (1) OR r.rid IS NULL) ORDER BY b.region, b.weight, b.module in E:\devshop\www\drupalCVS\HEAD-DEV\includes\database.mysql.inc on line 173.I reverted this patch and things began working again. This error occurred following a successful install.php before creating the id 1 user.
#16
I updated my development area and all of a sudden I was getting an invalid filename for some C:\TEMP file. The log message is below:
Can't create/write to file 'C:\TEMP\#sql_388_0.MYI' (Errcode: 13) query: SELECT DISTINCT b.* FROM DEV_blocks b LEFT JOIN DEV_blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.theme = 'garland' AND b.status = 1 AND (r.rid IN (1) OR r.rid IS NULL) ORDER BY b.region, b.weight, b.module in E:\devshop\www\drupalCVS\HEAD-DEV\includes\database.mysql.inc on line 173.I reverted this patch and things began working again. This error occurred following a successful install.php before creating the id 1 user.
#17
Apologies. I had received a ``500 - Internal Server Error'' in posting and had ass-u-me-d that the post had not been posted.
#18
How did you reach that conclusion?
Error 13 means "OS error code 13: Permission denied" on WinXP. Can you please check the privileges of the user account that runs the mysql server? Also, check whether the file already exists, but is simply not writeable by the current user (example: another user created such a file).
# is a perfectly valid character on NTFS and FAT32.
#19
@Heine: Thanks for pointing out my fallicies based on what I believed to be an MS limitation just because it was MS. The ``Permission Denied'' error is being caused by slow closing of handles of the open file and the create request for the file happens before all handles to the file are closed. This seems to be prevalent on my system with some unknown criteria exacerbating the issue. I hope to try again to see the frequency of the issue. I now conclude that this is not a bug for Drupal but may cause issues to arrise as if it were. Therefore, it may be wise to consider filtering the patch to LAMP only but waiting for issues about it to queue is also a worthy consideration. Another solution is to make this use configurable.
#20
Whether it should go into D5 or not is not my call, but here's a port for 5.x anyway. (Not tested.)
#21
This looks like a good idea, but will need testing in a couple hosting environments.
#22
Hasn't been an issue in D6 for over a year, so committed to 5.x.
#23
Automatically closed -- issue fixed for 2 weeks with no activity.