Recent Posts

Pages: [1] 2 3 ... 10
1
Support / Re: Upgraded to latest release, getting errors about par2info
« Last post by homerdoh on 2017-07-26, 05:04:49 AM »
Thanks, composer install fixed a bunch of missing stuff.
2
Support / Re: Upgraded to latest release, getting errors about par2info
« Last post by Wally73 on 2017-07-26, 01:42:47 AM »
seems composer stuff is messed up

do composer install in your main nzedb dir (should fix the missing stuff)
3
Support / Re: Upgraded to latest release, getting errors about par2info
« Last post by homerdoh on 2017-07-26, 01:34:04 AM »
Manually added rarinfo from https://github.com/zeebinz/rarinfo to .../libs/zeebinz/rarinfo/, seems to work.
4
Support / Upgraded to latest release, getting errors about par2info
« Last post by homerdoh on 2017-07-25, 06:56:58 AM »
Upgraded from 0.6.3.2 to 0.7.3.3, (hopefully) did all the necessary updates in ReleaseNotes for each version in between.

The rarinfo files seem to be missing, I'm getting this in php_errors.log:

[25-Jul-2017 15:46:15 Europe/Stockholm] PHP Warning:  include(/var/www/nZEDb/app/libraries/composer/../../../libs/zeebinz/rarinfo/par2info.php): failed to open stream: No such file or directory in /var/www/nZEDb/app/libraries/composer/ClassLoader.php on line 444
[25-Jul-2017 15:46:15 Europe/Stockholm] PHP Warning:  include(): Failed opening '/var/www/nZEDb/app/libraries/composer/../../../libs/zeebinz/rarinfo/par2info.php' for inclusion (include_path='.:/usr/share/php:/usr/share/pear') in /var/www/nZEDb/app/libraries/composer/ClassLoader.php on line 444
[25-Jul-2017 15:46:15 Europe/Stockholm] PHP Fatal error:  Class 'Par2Info' not found in /var/www/nZEDb/nzedb/processing/PostProcess.php on line 109

I've tried running zed update again, says I'm up to date:

Already up-to-date.
Checking database version...
Db: 482,        File: 482
Up to date.
The Smarty compiled template cache has been cleared for you

Any ideas?
5
For a DB running on a single consumer grade SATA SSD, don't use any IOP settings values above 3-5k. Ideally you need to determine the wIOP rate of your setup at low queue depth values of 1-4 using a benchmarking tool like sysbench. I'll edit my previous post as people will just blindly copy those values.

That aside, if you're getting table locks with only 2 update_release threads, it's probable that the tables are MyISAM format. Here a few steps that should help:
1. Stop the nzed scripts and backup https://github.com/nZEDb/nZEDb/wiki/Database%20Administration#database-backup-and-restore
2. Convert the DB tables to dynamic innodb format:
Code: [Select]
php misc/testing/DB/convert_mysql_tables.php3. Use the http://mysqltuner.com/ script to help you tune the DB config further. For nzedb ignore what is recommends for query_cache_size and join_buffer_size. You will need to increase innodb_buffer_pool_size.

If you are tight on memory you can either reduce the total number of releases or change the InnoDB table format to compressed (it will use more CPU for the de/compression).
6
Look in the /etc/mysql/ directory for my.cnf that normally contains it, on Debian it is /etc/mysql/mysql.conf.d/mysql.cnf

As for IOPs, look at the specs from the manufacturer for random reads and random writes, also Iometer is not a  bad choice if on windows.  Values can vary greatly from SSD to SSD, as an older example take a look at: http://www.cdrlabs.com/images/stories/reviews/samsung_ssd_840_pro_raid/samsung%20ssd%20840%20pro%20iometer%20raid%20iops.png

As for me I was able to get it to working, but only with 1 release thread, even with 2 threads I get deadlocks on releases.

My current settings, which work, but need more tuning are:
Code: [Select]
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1

#
# * Fine Tuning
#
key_buffer_size         = 1G
bulk_insert_buffer_size = 128M
thread_stack            = 384K
thread_cache_size       = 10
query_cache_type        = 1
sort_buffer_size        = 16M
join_buffer_size        = 512M
read_buffer_size        = 16M
table_open_cache        = 6000
max_connections         = 50

#
# * Query Cache Configuration
#
query_cache_limit       = 12M
query_cache_size        = 36M
query_cache_type        = 1

#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log

#
# ---- myism ----
#
myisam_sort_buffer_size = 8M
myisam_repair_threads   = 1
myisam-recover-options=BACKUP,FORCE

# ----- Relax transaction isolation -----------------
# http://www.mysqlperformanceblog.com/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/
transaction-isolation=READ-COMMITTED

#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
innodb_buffer_pool_size         = 2G
innodb_buffer_pool_instances    = 2
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 8M
innodb_open_files               = 400
innodb_io_capacity              = 400

# --- General InnoDB params   ---
innodb_file_format = BARRACUDA
innodb_large_prefix = 1
innodb_file_per_table
innodb_table_locks = 0
innodb_lock_wait_timeout=150    # May 17 increased 100->150
innodb_print_all_deadlocks=1
innodb_checksum_algorithm = crc32

# ---- innodb threading ----
innodb_thread_concurrency = 0
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_purge_threads = 4   # Upped from 1 - May 16

# ---- InnoDB Flushing ----
innodb_flush_method = O_DIRECT     # Prevent double buffering by OS - http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
innodb_flush_log_at_trx_commit = 0 # 0= can lose last sec of updates, 1=super safe, 2=flushes 1x per sec and at trx commit

# ---- SSD/IOP settings ----
innodb_io_capacity     = 50000   # M2 has spec max wIOPS of 280k, approx 140k at QD1 - May17 upped to 100k with optane
innodb_io_capacity_max = 90000   # See: http://www.tocker.ca/2013/09/17/what-to-tune-in-mysql-56-after-installation.html
innodb_lru_scan_depth  = 50000   # Set close to iop_max - http://mysqlha.blogspot.lu/2013/05/configuring-innodb-for-mysql-56.html
innodb_flush_neighbors = 0       # Based on https://mariadb.org/how-to-tune-mariadb-write-performance/


# ---- other esttings ----
group_concat_max_len    = 8192
max_allowed_packet      = 32M
tmp_table_size          = 1G
secure-file-priv        = ""
sql-mode                = ""
interactive_timeout     = 60
wait_timeout            = 60

Please Note: I have 24Gb of Memory on the server, and this runs at about 14GB, so it is not recommended for light systems!, much of the use comes from per thread variables, as the global is only 3.2GB.   I also have to use only one release thread (which is not an issue as the DB is fast enough that it is not a problem, but with more than one thread I have deadlocks occurring).
7
Ouch, the DB is hanging badly so some tuning changes are needed. Firstly you can reduce contention on the releases table by lowering the number of threads used for update_releases. If you're prepared to lose the last second's worth of transactions after power loss, then transaction integrity limits can be lowered (nzed is not a critical financial application so this is quite safe). Also, adjusting the innodb IOP limits to match those of the underlying hardware can bring large performance gains.

Here some tuning params that may help. Adjust the values to suit your hardware, especially the IOP limits:
Code: [Select]
# ----- Relax transaction isolation -----------------
# http://www.mysqlperformanceblog.com/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/
transaction-isolation=READ-COMMITTED

# --- General InnoDB params   ---
innodb_file_format = BARRACUDA
innodb_large_prefix = 1
innodb_file_per_table
innodb_table_locks = 0
innodb_use_sys_malloc = 1    #  Modern OS malloc now good enough
innodb_lock_wait_timeout=150    # May 17 increased 100->150
innodb_print_all_deadlocks=1
innodb_checksum_algorithm = crc32

# ---- innodb threading ----
innodb_thread_concurrency = 0
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_purge_threads = 4   # Upped from 1 - May 16

# ---- InnoDB Flushing ----
innodb_flush_method = O_DIRECT     # Prevent double buffering by OS - http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
innodb_flush_log_at_trx_commit = 0 # 0= can lose last sec of updates, 1=super safe, 2=flushes 1x per sec and at trx commit

# ---- SSD/IOP settings ----
innodb_io_capacity     = 50000   # M2 has spec max wIOPS of 280k, approx 140k at QD1 - May17 upped to 100k with optane
innodb_io_capacity_max = 90000   # See: http://www.tocker.ca/2013/09/17/what-to-tune-in-mysql-56-after-installation.html
innodb_lru_scan_depth  = 50000   # Set close to iop_max - http://mysqlha.blogspot.lu/2013/05/configuring-innodb-for-mysql-56.html
innodb_flush_neighbors = 0       # Based on https://mariadb.org/how-to-tune-mariadb-write-performance/

ok stupid question, where do we put these values.
Also, what is the easiest way to determine the IOP values for my drive?

EDIT:
how can I find the current settings for these options? If I don't have any set what are the defaults, or is there a command to print out the current values to the terminal?
8
Thanks for the suggestions, trying them now, I had to disable:
innodb_use_sys_malloc = 1
as mysql 5.7 is failing to start with is present, which is odd as it is listed as supported.  I've also reduce the threads for releases from 3 to 2.  WIll update once I see how stable I am.

Eric
9
Ouch, the DB is hanging badly so some tuning changes are needed. Firstly you can reduce contention on the releases table by lowering the number of threads used for update_releases. If you're prepared to lose the last second's worth of transactions after power loss, then transaction integrity limits can be lowered (nzed is not a critical financial application so this is quite safe). Also, adjusting the innodb IOP limits to match those of the underlying hardware can bring large performance gains.

Here some tuning params that may help. Adjust the values to suit your hardware, especially the IOP limits:
Code: [Select]
# ----- Relax transaction isolation -----------------
# http://www.mysqlperformanceblog.com/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/
transaction-isolation=READ-COMMITTED

# --- General InnoDB params   ---
innodb_file_format = BARRACUDA
innodb_large_prefix = 1
innodb_file_per_table
innodb_table_locks = 0
innodb_use_sys_malloc = 1    #  Modern OS malloc now good enough
innodb_lock_wait_timeout=150    # May 17 increased 100->150
innodb_print_all_deadlocks=1
innodb_checksum_algorithm = crc32

# ---- innodb threading ----
innodb_thread_concurrency = 0
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_purge_threads = 4   # Upped from 1 - May 16

# ---- InnoDB Flushing ----
innodb_flush_method = O_DIRECT     # Prevent double buffering by OS - http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
innodb_flush_log_at_trx_commit = 0 # 0= can lose last sec of updates, 1=super safe, 2=flushes 1x per sec and at trx commit

# ---- SSD/IOP settings ----
innodb_io_capacity     = 3000   # Estimates for DB on single SATA SSD - Use sysbench to determine wIOP rates at queue depths 1-4
innodb_io_capacity_max = 5000   # See: http://www.tocker.ca/2013/09/17/what-to-tune-in-mysql-56-after-installation.html
innodb_lru_scan_depth  = 3000   # Set close to iop_max - http://mysqlha.blogspot.lu/2013/05/configuring-innodb-for-mysql-56.html
innodb_flush_neighbors = 0      # Based on https://mariadb.org/how-to-tune-mariadb-write-performance/
10
nZEDb Ubuntu 16.04 in Docker (24GB ram, 6 cores), both Mariadb and mysql (yes I tried both on different docker images) hang.  I've tracked it down to: [ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.

Likely a result of one of the following queries:
---TRANSACTION 2063127, ACTIVE 918 sec updating or deleting
mysql tables in use 2, locked 2
16 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 1
MySQL thread id 692, OS thread handle 139943852173056, query id 361885 localhost nzedb
UPDATE releases
                                SET passwordstatus = passwordstatus - 1, rarinnerfilecount = 0 , haspreview = 0
                                WHERE id = 5702
---TRANSACTION 2063123, ACTIVE 919 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 1004, OS thread handle 139943847802624, query id 361876 localhost nzedb
INSERT INTO release_files
                                                (releases_id, name, size, createddate, passworded)
                                                VALUES
                                                (10594, 'XXXXXXXXXXXXXXXXXXXX.mp4', '3183312853', FROM_UNIXTIME(1500642788), 0)
---TRANSACTION 2063122, ACTIVE 919 sec updating or deleting
mysql tables in use 2, locked 2
16 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 1
MySQL thread id 699, OS thread handle 139943858530048, query id 361871 localhost nzedb
UPDATE releases
                                SET passwordstatus = 10, rarinnerfilecount = 0 , haspreview = 0
                                WHERE id = 6553
---TRANSACTION 2063119, ACTIVE 919 sec updating or deleting
mysql tables in use 2, locked 2
16 lock struct(s), heap size 1136, 8 row lock(s)
MySQL thread id 700, OS thread handle 139943852570368, query id 361851 localhost nzedb
UPDATE releases
                                SET passwordstatus = 0, rarinnerfilecount = 0 , haspreview = 0
                                WHERE id = 5977
---TRANSACTION 2063113, ACTIVE 919 sec updating or deleting
mysql tables in use 2, locked 2
6 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 967, OS thread handle 139943856146176, query id 361837 localhost nzedb Sending data
DELETE c, b, p FROM collections_184 c JOIN binaries_184 b ON(c.id=b.collections_id) STRAIGHT_JOIN parts_184 p ON(b.id=p.binaries_id) WHERE c.releases_id = 11044
---TRANSACTION 2061875, ACTIVE 922 sec updating or deleting
mysql tables in use 2, locked 2
16 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 1
MySQL thread id 975, OS thread handle 139946007910144, query id 358336 localhost nzedb
UPDATE releases SET nzbstatus = 1 , nzb_guid = UNHEX( 'a3bceec54ef006ff68eafd0ce6631b1f' ) WHERE id = 7525

...


END OF INNODB MONITOR OUTPUT
============================
InnoDB: ###### Diagnostic info printed to the standard error stream
2017-07-21T23:30:08.189842Z 0 [ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
2017-07-21 23:30:08 0x7f47cb269700  InnoDB: Assertion failure in thread 139946327709440 in file ut0ut.cc line 916
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
23:30:08 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=1073741824
read_buffer_size=16777216
max_used_connections=50
max_threads=64
thread_count=16
connection_count=16
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 3146582 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.


So basically mysql/mariadb is simply crashing on long running operations, due to a deadlock on a table?, it appears to me the root cause is, something like:
---TRANSACTION 2063122, ACTIVE 919 sec updating or deleting
mysql tables in use 2, locked 2
16 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 1
MySQL thread id 699, OS thread handle 139943858530048, query id 361871 localhost nzedb
UPDATE releases
                                SET passwordstatus = 10, rarinnerfilecount = 0 , haspreview = 0
                                WHERE id = 6553

or
6 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 967, OS thread handle 139943856146176, query id 361837 localhost nzedb Sending data
DELETE c, b, p FROM collections_184 c JOIN binaries_184 b ON(c.id=b.collections_id) STRAIGHT_JOIN parts_184 p ON(b.id=p.binaries_id) WHERE c.releases_id = 11044
---TRANSACTION 2061875, ACTIVE 942 sec updating or deleting


as these are the longest running and hence the mostly cause, notice most are blocked on the 'releases' table.


Using mysqltuner, I've confirmed it is not a configuration or memory issue, but seems related to the DB SQL operations, however again I can not say for sure, I can say that running in a Docker container is not stable for me and in fact crashes the container to the point where a server restart is needed to clear the defunct mysqld process.

Note: The nZEDB logs in debug mode are less than helpful, as the DB goes away, which I know it crashed.

Thought I's ask if anyone else had any thoughts?
ERIC


Pages: [1] 2 3 ... 10