I run a gallery2 program on virtual domains on ixwebhosting.com. In these days the domain always sent me tickets about the database overload. I've turn the cache module to medium level on the g2 performance menu. The site is still small with one user and dozens of photo. Could you give us some idea what the problem is. Is it an attack or something else?
Thanks a lot.
Gallery version 2.2
PHP version 4.3.11
PHPInfo Link : http://fivestone.cn/pi.php
Webserver Apache
Database MySql 4.1.20
Operating system Linux
*** support messege ***
Most common queries chenyan_fsblog:
1.Time: 15% (10036 sec)
Amount: 11% (214 queries)
Rows Examined/Sent: 0 / 2
Avg. Query Exec/Lock Time: 46 / 0 sec.
Used databases: [chenyan_fs_photo]
Query example: use chenyan_fs_photo; SELECT g_userId, g_remoteIdentifier, g_creationTimestamp, g_modificationTimestamp, g_data FROM g2_SessionMap WHERE g_id='6977ae08d64effe136b5af3fa829222f';
2.Time: 7% (4770 sec)
Amount: 7% (150 queries)
Avg. Query Exec/Lock Time: 31 / 0 sec.
Used databases: [chenyan_fs_photo]
Query example: UPDATE g2_ItemAttributesMap SET g_viewCount = g2_ItemAttributesMap.g_viewCount + 1 WHERE g_itemId=45;
3.Time: 2% (1727 sec)
Amount: 3% (65 queries)
Rows Examined/Sent: 0 / 65
Avg. Query Exec/Lock Time: 26 / 0 sec.
Used databases: [chenyan_fs_photo]
Query example: SELECT g_parentSequence FROM g2_ItemAttributesMap WHERE g_itemId=7;
4.Time: 2% (1432 sec)
Amount: 2% (50 queries)
Rows Examined/Sent: 5760 / 628
Avg. Query Exec/Lock Time: 28 / 0 sec.
Used databases: [chenyan_fs_photo]
Query example: SELECT g2_ChildEntity.g_id, g2_ItemAttributesMap.g_orderWeight FROM g2_ChildEntity INNER JOIN g2_ItemAttributesMap ON g2_ChildEntity.g_id = g2_ItemAttributesMap.g_itemId INNER JOIN g2_Item ON g2_ChildEntity.g_id = g2_Item.g_id INNER JOIN g2_AccessSubscriberMap ON g2_ChildEntity.g_id = g2_AccessSubscriberMap.g_itemId WHERE g2_ChildEntity.g_parentId = 26 AND g2_AccessSubscriberMap.g_accessListId IN (22,458,502) ORDER BY g2_ItemAttributesMap.g_orderWeight, g2_ChildEntity.g_id LIMIT 9;
5.Time: 1% (1021 sec)
Amount: 1% (26 queries)
Avg. Query Exec/Lock Time: 39 / 0 sec.
Used databases: [chenyan_fs_photo]
Query example: INSERT INTO g2_SessionMap (g_id, g_userId, g_remoteIdentifier, g_creationTimestamp, g_modificationTimestamp, g_data) VALUES ('eaa3c213b5f9eb772a4fff9394546550',5,'a:2:{i:0;s:13:\"124.115.4.196\";i:1;s:32:\"758ec187d4b0b52bda9729ad411f60ff\";}',1215065976,121506597
Posted: Tue, 2008-07-08 06:46
Posts: 8194
Can they be more specific about 'database overload?' Does this mean too many queries or the data in the database is taking up too much space?
--Andy
Consider giving back to Gallery
Posts: 15
First, you should post your system information (System-> Maintenance). If you can get us a list of available Apache modules (via phpinfo();), we can suggest a plan.
Check these hints: http://gallery.menalto.com/node/79024
And think about this: http://phplens.com/adodb/caching.of.recordsets.html (especially if you have memcached available)
HTH,
Daniel
Posts: 10
The overload database is blocked by the domain administrator so that I cannot visit the site any more.
This is the sysinfo I get from another g2 site which runs on the same machine.
The phpinfo() link: http://fivestone.cn/pi.php
Gallery version = 2.2.5 core 1.2.0.7
PHP version = 4.3.11 apache
Webserver = Apache
Database = mysqlt 4.1.20-max-log, lock.system=flock
Toolkits = ArchiveUpload, Ffmpeg, LinkItemToolkit, NetPBM, Thumbnail, Gd
Acceleration = partial/21600, partial/21600
Operating system = Linux web229.opentransfer.com 2.6.14.4 #2 Mon Feb 11 15:31:12 CST 2008 i686
Default theme = matrix
gettext = enabled
Locale = zh_CN
Browser = Mozilla/5.0 (Windows; U; Windows NT 5.2; zh-CN; rv:1.9) Gecko/2008052906 Firefox/3.0
Rows in GalleryAccessMap table = 46
Rows in GalleryAccessSubscriberMap table = 25
Rows in GalleryUser table = 5
Rows in GalleryItem table = 24
Rows in GalleryAlbumItem table = 10
Rows in GalleryCacheMap table = 50
Posts: 8194
Can you ask them what they mean by 'database overload' exactly? What metric are they using to determine you're using too much?
You might get some relief by enabling caching in 'Site Admin' -> 'Performance' .. but the fact remains, Gallery is a database-intensive application; it goes on the assumption that queries are usually pretty cheap.
--Andy
Consider giving back to Gallery
Gallery Paid Support
Posts: 27300
214 queries and it ends up to be 11%. WOW That is some restriction if I am reading that right. On one of my hosts I get 75,000 per day and I thought that that was restrictive.
Dave
_____________________________________________
Blog & G2 || floridave - Gallery Team
Posts: 8194
Agreed, that really sucks if so. Can you ask your host what their limits are?
--Andy
Consider giving back to Gallery
Gallery Paid Support
Posts: 10
Support:
The issue has to do with the time it took to load your queries. The above queries show you how long it ran and what query it was. Please update or remove the above mentioned queries and we will be happy to re enable your database.
Posts: 8194
Ummm? Those queries are required for Gallery to operate.
If queries are taking too long (like multiple seconds), it's almost certainly your host's problem (i.e., their databases are bogged down).
--Andy
Consider giving back to Gallery
Gallery Paid Support
http://www.andylindeman.com
Posts: 8
I have the same problem this is what they said Per our conversation I have provided you with the most common queries that are causing this issue.
chmod 0 dnsphot_Gallery2
==>Most common queries dnsphot_dnsphoto:
->Query summary
Time: 17% (3584 sec) Amount: 19% (212 queries) Rows Examined/Sent: 35668 / 267 Avg. Query Exec/Lock Time: 16 / 5 sec.
Used databases: [dnsphot_Gallery2]
Query example: use dnsphot_Gallery2; SELECT g2_Derivative.g_id, g2_ChildEntity.g_parentId FROM g2_Derivative, g2_ChildEntity WHERE g2_Derivative.g_id = g2_ChildEntity.g_id AND g2_ChildEntity.g_parentId IN (250871) AND g2_Derivative.g_derivativeType IN (1);
|id rows Extra
|1 1 Using where
|1 1 Using where
|__________________
->Query summary
Time: 13% (2674 sec) Amount: 15% (177 queries) Rows Examined/Sent: 1651 / 359 Avg. Query Exec/Lock Time: 15 / 4 sec.
Used databases: [dnsphot_Gallery2]
Query example: use dnsphot_Gallery2; SELECT g2_AccessSubscriberMap.g_itemId, BIT_OR(g2_AccessMap.g_permission) FROM g2_AccessMap, g2_AccessSubscriberMap WHERE g2_AccessSubscriberMap.g_itemId IN (118904) AND g2_AccessSubscriberMap.g_accessListId = g2_AccessMap.g_accessListId AND g2_AccessMap.g_userOrGroupId IN (5,4) GROUP BY g2_AccessSubscriberMap.g_itemId;
|id rows Extra
|1 1
|1 3 Using where
|__________________
->Query summary
Time: 11% (2365 sec) Amount: 10% (119 queries) Rows Examined/Sent: 22002505 / 119 Avg. Query Exec/Lock Time: 19 / 8 sec.
Used databases: [dnsphot_Gallery2]
Query example: use dnsphot_Gallery2; SELECT g2_ImageBlockCacheMap.g_itemId FROM g2_ImageBlockCacheMap LEFT JOIN g2_ImageBlockDisabledMap ON g2_ImageBlockCacheMap.g_itemId=g2_ImageBlockDisabledMap.g_itemId WHERE g2_ImageBlockCacheMap.g_userId = 5 AND g2_ImageBlockCacheMap.g_itemType = 1 AND g2_ImageBlockDisabledMap.g_itemId IS NULL ORDER BY RAND() LIMIT 1;
|id rows Extra
|1 0 const row not found
|1 76890 Using temporary; Using filesort __________________
->Query summary
Time: 11% (2395 sec) Amount: 10% (116 queries) Rows Examined/Sent: 70 / 13 Avg. Query Exec/Lock Time: 20 / 7 sec.
Used databases: [dnsphot_Gallery2]
Query example: use dnsphot_Gallery2; SELECT g2_CacheMap.g_value FROM g2_CacheMap WHERE g2_CacheMap.g_key = '1c33b8e5129519baa31fda93440c9539' AND g2_CacheMap.g_type = 'page' AND g2_CacheMap.g_userId = 5 AND g2_CacheMap.g_timestamp > 1231569821;
|id rows Extra
|1 1 Using where
|__________________
->Query summary
Time: 8% (1721 sec) Amount: 9% (110 queries) Rows Examined/Sent: 33 / 142 Avg. Query Exec/Lock Time: 15 / 5 sec.
Used databases: [dnsphot_Gallery2]
Query example: use dnsphot_Gallery2; SELECT g2_ItemAttributesMap.g_itemId, g2_ItemAttributesMap.g_viewCount FROM g2_ItemAttributesMap WHERE g2_ItemAttributesMap.g_itemId IN (199385);
|id rows Extra
|1 1
|__________________
->Query summary
Time: 8% (1784 sec) Amount: 7% (80 queries) Rows Examined/Sent: 221287 / 36862 Avg. Query Exec/Lock Time: 22 / 8 sec.
Used databases: [dnsphot_Gallery2]
Query example: use dnsphot_Gallery2; SELECT g2_ChildEntity.g_id , g2_ItemAttributesMap.g_orderWeight FROM g2_ChildEntity, g2_DataItem, g2_AccessSubscriberMap, g2_ItemAttributesMap WHERE g2_ChildEntity.g_parentId = 82203 AND g2_ChildEntity.g_id = g2_DataItem.g_id AND g2_AccessSubscriberMap.g_itemId = g2_ChildEntity.g_id AND g2_AccessSubscriberMap.g_accessListId IN (2191,180205,218511,89117) AND g2_ItemAttributesMap.g_itemId = g2_ChildEntity.g_id ORDER BY g2_ItemAttributesMap.g_orderWeight, g2_ChildEntity.g_id LIMIT 18446744073709551615;
|id rows Extra
|1 505 Using where; Using temporary; Using filesort
|1 1 Using index
|1 1 Using where
|1 1
|__________________
->Query summary
Time: 6% (1385 sec) Amount: 5% (66 queries) Rows Examined/Sent: 147484 / 23526 Avg. Query Exec/Lock Time: 20 / 8 sec.
Used databases: [dnsphot_Gallery2]
Query example: use dnsphot_Gallery2; SELECT g2_ChildEntity.g_id , g2_ItemAttributesMap.g_orderWeight FROM g2_ChildEntity, g2_Item, g2_AccessSubscriberMap, g2_ItemAttributesMap WHERE g2_ChildEntity.g_parentId = 118698 AND g2_ChildEntity.g_id = g2_Item.g_id AND g2_AccessSubscriberMap.g_itemId = g2_ChildEntity.g_id AND g2_AccessSubscriberMap.g_accessListId IN (2191,180205,218511,89117) AND g2_ItemAttributesMap.g_itemId = g2_ChildEntity.g_id ORDER BY g2_ItemAttributesMap.g_orderWeight, g2_ChildEntity.g_id LIMIT 18446744073709551615;
|id rows Extra
|1 157 Using where; Using temporary; Using filesort
|1 1 Using index
|1 1 Using where
|1 1
|__________________
->Query summary
Time: 6% (1283 sec) Amount: 5% (59 queries) Rows Examined/Sent: 0 / 59 Avg. Query Exec/Lock Time: 21 / 7 sec.
Used databases: [dnsphot_Gallery2]
Query example: use dnsphot_Gallery2; SELECT g2_ItemAttributesMap.g_parentSequence FROM g2_ItemAttributesMap WHERE g2_ItemAttributesMap.g_itemId = 118904;
|id rows Extra
|1 1
|__________________
Respectfully
Frankie
Support Tech Representative
Click here to login and view the ticket:
https://manage.ixwebhosting.com/index.php/cpanelhelpdesk.getFrmTicketModify/hd_ticketid/945750
******************************
Subject: Server Overload - Database
Account Link: General Inquiry
Category: Technical
******************************
Can some one help me fix this or tell how I can get some one to fix it. Thanks for your help. They put my site back online but if this does not get fix they will take it down for good
Posts: 4342
Chances are there's nothing wrong; G2 is known to be quite heavy on db usage, and your host is probably being stingy with resources (how many $ per month are you paying?).
Assuming you have the various performance enhancements, caching, etc enabled in Gallery then your options are to have fewer visitors to your website or to move to a host with more generous resource limits.
Posts: 8
I pay 12.95 per month. do you know of a good web host that can handle a photographers site?
Posts: 8
oh I dont have much load for performance enhancements. the only thing I have loaded that I know of is shopping cart for paypal
Posts: 4342
Go to site admin -> performance, and try the various acceleration options there. The shopping cart thing doesn't generate a lot of queries, it won't make any difference to your load.
Other than that, no, I can't advise. I moved my gallery to a dedicated server because of the same issue. It runs much better now.