SQL Over Quota

[corboniko]

Bonjour,
Mon hébergeur OVH vient de ma signaler que ma base SQL vient d'atteindre les 30Mo. Je suis limité à 5mo en mutualisé, donc me voici bloqué!  :-\

http://www.briards-photos.fr/photos
Pour info, ma galerie avoisine les 11500 photos et 7500 commentaires.

Je viens de vider la table copper_hit_stats .
Actuellement, ma base est légèrement supérieure à  5Mo.
Je vais faire évoluer mon quota chez OVH mais dans l'urgence, comment allerger la base?

La table copper_pictures est de 4,3Mo, quel est l'utilité des données dans:
- pic_raw/hdr/hit_ip???
- Puis-je vider le contenu?

Si oui, un petit exemple de requette SQL serait la bienvenue vu mon niveau. 

Merci pour votre aide,
Nicolas

[François Keller]

il faut désactiver les logs et autres statistiques qui permettent de ne pas ajouter de volume inutile aux tables.
voici tiré de la doc de CPG1.5.x la signification des champs de la table pictures (il se peut qu'il y ait des changements mineurs dans le nombre de champs)

pid     Integer     Primary key. Used throughout Coppermine to identify this file.     
aid    Integer    Indicates which album this picture belongs to.    cpg15x_albums.aid
filepath    Text (<=255 chars)    The path on the filesystem to the directory containing the file. This is relative to Coppermine's albums directory.    
filename    Text (<=255 chars)    The name of the file on the filesystem. There should be no path components in this field.    
filesize    Integer    The size of the file in bytes.    
total_filesize    Integer    The combined size of the file plus any resized or thumbnail versions of the file in bytes. This is used in quota calculations.    
pwidth    Integer    The width of the picture in pixels. If the file is a movie this is the width it will be displayed at.    
pheight    Integer    The height of the picture in pixels. If the file is a movie this is the height it will be displayed at.    
hits    Integer    The number of times the file has been viewed.    
mtime    Datetime    The date/time of the most recent view of this file. Used to create the last viewed meta album.    
ctime    Unix timestamp    The date/time the file was added to Coppermine.

Note: Coppermine does not use this field when sorting files by date. (It uses the pid field instead).
   
owner_id    Integer    The user who uploaded this file. 0 for anonymous users.    cpg15x_users.user_id
pic_rating    Integer    The value represents the current average rating of the file. This allows Coppermine to track the average rating without having to store all ratings ever made. Divide this number by 2000 to get a rating out of 5. See ratepic.php for how the value is calculated.    
votes    Integer    The total number of votes/ratings made on the file.    
title    Text (<=255 chars)    The short title of the file.    
caption    Text (<=65k chars)    The longer description of the file.    
keywords    Text (<=255 chars)    Used to tag the file with a given word or phrase. Used in searching, 'clickable keywords', and for the 'linked files' feature. The keyword separator is a configuration setting.    
approved    YES / NO    Whether the file has been approved by the administrator. Files that are not approved are not displayed to regular users. If approval is not enabled then the file gets a value of YES automatically.    
galleryicon    Integer    Indicates that this file has been selected as the thumbnail for a user gallery.    cpg15x_pictures.pid
user{1,2,3,4}    Text (<=255 chars)    Custom fields for image description.    
url_prefix    Integer    Designed as a way to allow some files to be stored in an alternate location to the albums directory, but this feature is not used by Coppermine itself. See the function get_pic_url() in include/functions.inc.php. Should normally be set to 0.    
pic_raw_ip    Text (<=255 chars)    The IP of the uploader. This IP is derived from the REMOTE_ADDR variable. If the webserver is behind a proxy this will probably be the IP of the proxy.    
pic_hdr_ip    Text (<=255 chars)    The IP of the uploader. This IP is derived from the HTTP_CLIENT_IP or HTTP_X_FORWARDED_FOR variables. If the webserver is behind a proxy this should be the actual client IP instead of the proxy's IP. If neither variable is present this will be the same as the pic_raw_ip value.    
lasthit_ip    Text (<=255 chars)    The IP address of the last visitor to view the file. This IP is derived from the REMOTE_ADDR variable. If the webserver is behind a proxy this will probably be the IP of the proxy.    
position    Integer    The position of the file within the album. This is only taken into account if the default sort order is set to position ascending or descending in config. The positions are set using the 'sort my pictures' page.

Il me semble avoir vu aussi des posts en anglais sur les tables qui prenaient vite de l'ampleur
Les commentaires peuvent aussi prendre de la place, ainsi que les ecartes

[corboniko]

Merci pour ces infos.
Je viens de contrôler que mes 7300 commentaires prennent 960Ko et 3,6Mo pour la table copper_pictures pour 11500 photos.
J'ai vidé la table copper_hit_stats qui avait atteint 25Mo.

Nicolas
www.briards-photos.fr

[François Keller]

pour les galeries avec beaucoup de visiteurs et d'images, (ainsi que de commentaires), il est souvent judicieux de ne pas activer les stats