Warning: date(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in /home/www/domains/hppgallery_com/design/defaulttheme/tpl/pagelayouts/main.php on line 59

Warning: date(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in /home/www/domains/hppgallery_com/design/defaulttheme/tpl/pagelayouts/main.php on line 59
Friday, June 22, 2018
 

Migrate All Tables To InnoDB

You can now safely now run these query's, Gallery officially supports InnoDB from 721r.

 

ALTER TABLE  `lh_article_static` ENGINE = INNODB;
ALTER TABLE  `lh_delay_image_hit` ENGINE = INNODB;
ALTER TABLE  `lh_forgotpasswordhash` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_albums` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_categorys` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_comments` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_duplicate_collection` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_duplicate_image` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_duplicate_image_hash` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_images` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_lastsearch` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_myfavorites_images` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_myfavorites_session` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_popular24` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_searchhistory` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_upload` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_upload_archive` ENGINE = INNODB;
ALTER TABLE  `lh_group` ENGINE = INNODB;
ALTER TABLE  `lh_grouprole` ENGINE = INNODB;
ALTER TABLE  `lh_groupuser` ENGINE = INNODB;
ALTER TABLE  `lh_role` ENGINE = INNODB;
ALTER TABLE  `lh_rolefunction` ENGINE = INNODB;
ALTER TABLE  `lh_shop_base_setting` ENGINE = INNODB;
ALTER TABLE  `lh_shop_basket_image` ENGINE = INNODB;
ALTER TABLE  `lh_shop_basket_session` ENGINE = INNODB;
ALTER TABLE  `lh_shop_image_variation` ENGINE = INNODB;
ALTER TABLE  `lh_shop_order` ENGINE = INNODB;
ALTER TABLE  `lh_shop_order_item` ENGINE = INNODB;
ALTER TABLE  `lh_shop_payment_setting` ENGINE = INNODB;
ALTER TABLE  `lh_shop_user_credit` ENGINE = INNODB;
ALTER TABLE  `lh_shop_user_credit_order` ENGINE = INNODB;
ALTER TABLE  `lh_system_config` ENGINE = INNODB;
ALTER TABLE  `lh_users` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_filetypes` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_pending_convert` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_rated24` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_shard_limit` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_pallete` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_pallete_images` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_sphinx_search` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_pallete_images_stats` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_last_index` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_face_data` ENGINE = INNODB;
ALTER TABLE  `lh_forum_category` ENGINE = INNODB;
ALTER TABLE  `lh_forum_file` ENGINE = INNODB;
ALTER TABLE  `lh_forum_message` ENGINE = INNODB;
ALTER TABLE  `lh_forum_message_delta` ENGINE = INNODB;
ALTER TABLE  `lh_forum_report` ENGINE = INNODB;
ALTER TABLE  `lh_forum_topic` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_images_comment_ban_ip` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_images_delta` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_images_rate_ban_ip` ENGINE = INNODB;
ALTER TABLE  `lh_gallery_images_rate_last_ip` ENGINE = INNODB;
ALTER TABLE  `lh_oid_map` ENGINE = INNODB;
ALTER TABLE  `lh_oid_nonces` ENGINE = INNODB;
ALTER TABLE  `lh_oid_associations` ENGINE = INNODB;


/*Execute this query if your mysql server supports partitions*/
ALTER TABLE lh_gallery_pallete_images
partition BY HASH(pallete_id)
partitions 200;

Server configuration example, witch I found performing well.

CPU     Intel
Model   i7 920
Frequency       4x 2x 2.66 GHz
Memory  8 GB

Server configuration

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

query_cache_limit=8M
query_cache_size=128M
query_cache_type=1
skip-locking
#skip-innodb

interactive_timeout=20
wait_timeout=900
connect_timeout=60
thread_cache_size=128
key_buffer=128M
join_buffer=1M
max_allowed_packet=16M
#Changed from 1024
table_cache=8024
record_buffer=1M
sort_buffer_size=4M
read_buffer_size=4M
max_connect_errors=10000
max_connections=10000

log_slow_queries=/var/log/mysqld.slow.log
long_query_time=1

# Inno DB tuning
innodb_buffer_pool_size = 2GB
innodb_log_file_size = 100M
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_file_per_table=1
transaction-isolation=READ-COMMITTED


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Unlike all other open source gallery mysql does not take top row of processes. It's the last one by CPU usage actually most of the time :)

Back »

Comments: 0

Leave a reply »

 
  • Leave a Reply
    Your gravatar
    Your Name
     
     
     
     
 
About HPPG

High performance photo gallery dedicated for large numbers of image storage.

Get in touch

Donate



Download