Error: Query Error: The table 'jr_jrcore_cache' is full

@soaringeagle
7 years ago
3,304 posts

is this related to full page caching?

i am doing a db backup
but
the sites down with that error
while doing the bavckup i see

MariaDB [(none)]> SHOW PROCESSLIST;
+-----+--------------+-----------+--------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id  | User         | Host      | db           | Command | Time | State                        | Info                                                                                                 | Progress |
+-----+--------------+-----------+--------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+----------+
|  68 | greentec_jam | localhost | greentec_jam | Query   |  141 | Sending data                 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `jr_jrcore_cache`                                             |    0.000 |
|  69 | greentec_jam | localhost | greentec_jam | Query   |  147 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
|  70 | greentec_jam | localhost | greentec_jam | Query   |  147 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
|  71 | greentec_jam | localhost | greentec_jam | Query   |  147 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
|  72 | greentec_jam | localhost | greentec_jam | Query   |  146 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
|  73 | greentec_jam | localhost | greentec_jam | Query   |  143 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
|  76 | greentec_jam | localhost | greentec_jam | Query   |  140 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
|  81 | greentec_jam | localhost | greentec_jam | Query   |  138 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
|  83 | greentec_jam | localhost | greentec_jam | Query   |  134 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
|  86 | greentec_jam | localhost | greentec_jam | Query   |  131 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
|  89 | greentec_jam | localhost | greentec_jam | Query   |  127 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
|  90 | greentec_jam | localhost | greentec_jam | Query   |  126 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
|  93 | greentec_jam | localhost | greentec_jam | Query   |  125 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
|  99 | greentec_jam | localhost | greentec_jam | Query   |  121 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 100 | greentec_jam | localhost | greentec_jam | Query   |  119 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 109 | greentec_jam | localhost | greentec_jam | Query   |  106 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 110 | greentec_jam | localhost | greentec_jam | Query   |  105 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 112 | greentec_jam | localhost | greentec_jam | Query   |  103 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 115 | greentec_jam | localhost | greentec_jam | Query   |  102 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 117 | greentec_jam | localhost | greentec_jam | Query   |  100 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 118 | greentec_jam | localhost | greentec_jam | Query   |  100 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 119 | greentec_jam | localhost | greentec_jam | Query   |  100 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 120 | greentec_jam | localhost | greentec_jam | Query   |  100 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 121 | greentec_jam | localhost | greentec_jam | Query   |  100 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 122 | greentec_jam | localhost | greentec_jam | Query   |   99 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 137 | greentec_jam | localhost | greentec_jam | Query   |   64 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 138 | greentec_jam | localhost | greentec_jam | Query   |   64 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 143 | greentec_jam | localhost | greentec_jam | Query   |   63 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 144 | greentec_jam | localhost | greentec_jam | Query   |   63 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 145 | greentec_jam | localhost | greentec_jam | Query   |   63 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 146 | greentec_jam | localhost | greentec_jam | Query   |   63 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 147 | greentec_jam | localhost | greentec_jam | Query   |   63 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 148 | greentec_jam | localhost | greentec_jam | Query   |   63 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 149 | greentec_jam | localhost | greentec_jam | Query   |   63 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 150 | greentec_jam | localhost | greentec_jam | Query   |   63 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 151 | greentec_jam | localhost | greentec_jam | Query   |   63 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 152 | greentec_jam | localhost | greentec_jam | Query   |   63 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 153 | greentec_jam | localhost | greentec_jam | Query   |   63 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 154 | greentec_jam | localhost | greentec_jam | Query   |   63 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 155 | greentec_jam | localhost | greentec_jam | Query   |   63 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 156 | greentec_jam | localhost | greentec_jam | Query   |   63 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 157 | greentec_jam | localhost | greentec_jam | Query   |   63 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 160 | greentec_jam | localhost | greentec_jam | Query   |   62 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 161 | greentec_jam | localhost | greentec_jam | Query   |   61 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 164 | greentec_jam | localhost | greentec_jam | Query   |   56 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 166 | greentec_jam | localhost | greentec_jam | Query   |   54 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 171 | greentec_jam | localhost | greentec_jam | Query   |   50 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 172 | greentec_jam | localhost | greentec_jam | Query   |   50 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 174 | greentec_jam | localhost | greentec_jam | Query   |   48 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 178 | greentec_jam | localhost | greentec_jam | Query   |   46 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 179 | greentec_jam | localhost | greentec_jam | Query   |   46 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 181 | greentec_jam | localhost | greentec_jam | Query   |   43 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 184 | greentec_jam | localhost | greentec_jam | Query   |   42 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 185 | greentec_jam | localhost | greentec_jam | Query   |   41 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 190 | greentec_jam | localhost | greentec_jam | Query   |   32 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 192 | greentec_jam | localhost | greentec_jam | Query   |   30 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 193 | greentec_jam | localhost | greentec_jam | Query   |   30 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 194 | greentec_jam | localhost | greentec_jam | Query   |   29 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 195 | greentec_jam | localhost | greentec_jam | Query   |   28 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 196 | greentec_jam | localhost | greentec_jam | Query   |   28 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 199 | greentec_jam | localhost | greentec_jam | Query   |   26 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 200 | greentec_jam | localhost | greentec_jam | Query   |   26 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 201 | greentec_jam | localhost | greentec_jam | Query   |   23 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 202 | greentec_jam | localhost | greentec_jam | Query   |   23 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 205 | greentec_jam | localhost | greentec_jam | Query   |   21 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 212 | greentec_jam | localhost | NULL         | Query   |    0 | init                         | SHOW PROCESSLIST                                                                                     |    0.000 |
| 217 | greentec_jam | localhost | greentec_jam | Query   |    0 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 218 | greentec_jam | localhost | greentec_jam | Query   |    0 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 220 | greentec_jam | localhost | greentec_jam | Query   |    0 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 221 | greentec_jam | localhost | greentec_jam | Query   |    0 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 222 | greentec_jam | localhost | greentec_jam | Query   |    0 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 223 | greentec_jam | localhost | greentec_jam | Query   |    0 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 224 | greentec_jam | localhost | greentec_jam | Query   |    0 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
| 227 | greentec_jam | localhost | greentec_jam | Query   |    0 | Waiting for table level lock | UPDATE jr_jrcore_cache SET cache_expires = (cache_expires + 30) WHERE cache_key = '3865a15fc1e8d9f2a |    0.000 |
+-----+--------------+-----------+--------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+----------+
74 rows in set (0.00 sec)

MariaDB [(none)]>

that (to my mostly untrained eye) looks like its not from bacikup but core maintenance worker even though sites down
1st backup attempt resulted in too many connections werror

holy crap
ny db thats usualy about 2 gigs i think maybe 6 is now 40 gig
im not sure i can even back up that 1 singlke table b4 emptying
it is safe to just empty that table.right

i think sall these tabkles being converted to innodb might be the issue
im having al;l sdorts of pronblems now

update sites back up after phpmyadmin empoty table request that took forever and timed out (504 gateway) several times 1st

i think the full page cache option has to have a limiter (if that is what caused this) to prevent the db from being overwhelmed

update again minutes later the sites somewhat up still getting same error when i try viewing queue lists
ran integrity check with repair modules and empty caches..

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
updated by @soaringeagle: 12/23/17 02:01:58AM

@michael
7 years ago
7,692 posts

my my.cnf file looks like this;

#                                                                                                                                                                            
# The MySQL database server configuration file.                                                                                                                              
#                                                                                                                                                                            
# You can copy this to one of:                                                                                                                                               
# - "/etc/mysql/my.cnf" to set global options,                                                                                                                               
# - "~/.my.cnf" to set user-specific options.                                                                                                                                
#                                                                                                                                                                            
# One can use all long options that the program supports.                                                                                                                    
# Run program with --help to get a list of available options and with                                                                                                        
# --print-defaults to see which it would actually understand and use.                                                                                                        
#                                                                                                                                                                            
# For explanations see                                                                                                                                                       
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html                                                                                                             
                                                                                                                                                                             
# This will be passed to all mysql clients                                                                                                                                   
# It has been reported that passwords should be enclosed with ticks/quotes                                                                                                   
# escpecially if they contain "#" chars...                                                                                                                                   
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.                                                                                                  
[client]                                                                                                                                                                     
port            = 3306                                                                                                                                                       
socket          = /var/run/mysqld/mysqld.sock                                                                                                                                
                                                                                                                                                                             
# Here is entries for some specific programs                                                                                                                                 
# The following values assume you have at least 32M ram                                                                                                                      
                                                                                                                                                                             
# This was formally known as [safe_mysqld]. Both versions are currently parsed.                                                                                              
[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 - 1 GIG RAM
#
key_buffer              = 32M
max_allowed_packet      = 32M
table_open_cache        = 1536
table_definition_cache  = 1536
open_files_limit        = 10240
tmp_table_size          = 32M
max_heap_table_size     = 32M
sort_buffer_size        = 1M
read_buffer_size        = 1M
read_rnd_buffer_size    = 768K
max_connections         = 50
innodb_buffer_pool_size = 48M
innodb_flush_method     = O_DIRECT
innodb_flush_log_at_trx_commit = 0
ft_min_word_len         = 3
ft_stopword_file        = ""

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#
# * Query Cache Configuration
#
query_cache_limit           = 1M
query_cache_size        = 32M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time     = 3

#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * 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!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

This thread seems to suggest that the issue is innodb related.

StackOverflow: "How to solve “The table … is full” with “innodb_file_per_table”?"
https://dba.stackexchange.com/questions/43503/how-to-solve-the-table-is-full-with-innodb-file-per-table

See if your my.cnf is similar to mine.

@soaringeagle
7 years ago
3,304 posts

thats what i asssumed cause with the update werent a whole lot of tables converted to innodb and thats when the issues started

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

@soaringeagle
7 years ago
3,304 posts

i would assume that if i have 32 gigs i should take the 1 gig tunings and multiplty by 32?
or are some based more on the total number of tables iin all databases

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

@soaringeagle
7 years ago
3,304 posts

think to be safe i'll post mine see if you spot the issues

##
[mysqld]
#bind-address = 127.0.0.1
skip_name_resolve
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

key-buffer-size                = 1024M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 128M
wait_timeout                   = 300
max-connect-errors             = 1000000
table_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
#log-bin                        = /var/lib/mysql/mysql-bin
#expire-logs-days               = 1
#sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 128M
max-heap-table-size            = 128M
query_cache_size               = 128M
query_cache_limit              = 2M
query_cache_type               = 0
join_buffer_size               = 4M
max-connections                = 160
thread-cache-size              = 160
open-files-limit               = 65535
table-definition-cache         = 8096
table-open-cache               = 8096
optimizer_search_depth         = 1


# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 2G
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 10G
innodb_buffer_pool_instances   = 10
innodb_lock_wait_timeout=120
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

@brian
7 years ago
10,136 posts

You might want to check out the Redis caching module:

https://www.jamroom.net/the-jamroom-network/networkmarket/406/redis-cache

--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net

@soaringeagle
7 years ago
3,304 posts

installed it but lil confused (theres no documentation)
whats the servers?
do i just set 1 up somehow on an ip on my server?
is there a redis specifiuc server list/ do they cost much?

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

@brian
7 years ago
10,136 posts

https://redis.io/

You can install it from your distro's package manager as well.

--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
updated by @brian: 09/19/17 09:09:14AM

@soaringeagle
7 years ago
3,304 posts

ok so i install it on my local server and use the localhost ip? (pretty sure thats right)
or do i use the shared ip
was looking over the install documentation already

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

@brian
7 years ago
10,136 posts

Yes:

- install Redis
- configure the /etc/redis/redis.conf file and set the "maxmemory" setting to something like 1G
- restart Redis
- create a new "server" in the Jamroom Redis module with server as 127.0.0.1
- go into the ACP -> System Core -> Global Config and set the "Active Cache System" to Redis

Note that we can't be the sysadmin support for you here in the forum - if you have any issues with installing or setting up your server you want to contact your hosting provider.

--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net

@soaringeagle
7 years ago
3,304 posts

nah i got it
and i am my hosting providrer
bought my own server just have it colocated

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

@soaringeagle
7 years ago
3,304 posts

i am pretty sure its all set up correctly (using password too) but i get this in stats
Unable to retrieve stats from Redis Server
is thiis cause i just started it
and how do i make sure its working right
there were too many diferent install instructions so i am not certain i have it set to run on boot

would be extremely helphul if whjen you can you ewrite up documentation and a guiode

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
updated by @soaringeagle: 09/19/17 12:00:27PM

@soaringeagle
7 years ago
3,304 posts

ok i got it working but still in poerformance test db performance SUCKS bad i mean real bad
anything in my.cnf you see as a problem (have 32 gigs ram)
(can future versions include its server stats in dashbopard0

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
updated by @soaringeagle: 09/19/17 01:16:40PM

@michael
7 years ago
7,692 posts

Don't know sorry, Server optimization isn't my strong suit. Brian's the smartest guy I know in that area.

@soaringeagle
7 years ago
3,304 posts

yea i know thats why i hope @brian can just look at my my.cnf and make any suggestions

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

@brian
7 years ago
10,136 posts

I'm not really seeing anything in your my.cnf that would account for bad performance. Here's the relevant parts from the my.cnf file for our servers with 24 gigs RAM:

#
# * Fine Tuning - 24 GIG RAM
#
key_buffer              = 1024M
max_allowed_packet      = 64M
table_open_cache        = 1536
table_definition_cache  = 1536
open_files_limit        = 10240
tmp_table_size          = 512M
max_heap_table_size     = 256M
sort_buffer_size        = 4M
read_buffer_size        = 4M
read_rnd_buffer_size    = 768K
max_connections         = 200
innodb_buffer_pool_size = 8G
innodb_flush_method     = O_DIRECT
innodb_flush_log_at_trx_commit = 0
ft_min_word_len         = 3
ft_stopword_file        = ""

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#
# * Query Cache Configuration
#
query_cache_limit	    = 1M
query_cache_size        = 512M

I do see that you're using dashes instead of underscores in a lot of your settings - not sure if that is supported, so you may want to update those.

It's important as well to make a BACKUP of your mysql data (mysqldump) - you can easily mess up your data by changing some of the innodb settings without knowing what you are changing.

--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net

@soaringeagle
7 years ago
3,304 posts

thnx i'll have to get to it tonight
and yea that was the work of a freelancer i had work on it and i couldn't figure out why some tuning settings just were not taking after i changed them..i should have noticed that

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

@soaringeagle
7 years ago
3,304 posts

holy crap
i made a few tweeks in my.cnf
and the db performance score went from an average 63.5 (high over 160.7) to 4.31

and totalk score from 100 average to 1745
so glad i finaly got that odd db issue worked out
the guy i paid screwed it u
p pretty basd but then again he did work on it months for free

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

SteveX
@ultrajam
7 years ago
2,583 posts

soaringeagle:
the guy i paid screwed it u
p pretty basd but then again he did work on it months for free

I guess you got what you paid for in the end.

You must love troubleshooting

--
¯\_(ツ)_/¯ Education, learning resources, TEL, AR/VR/MR, CC licensed content, panoramas, interactive narrative, sectional modules (like jrDocs), lunch at Uni of Bristol. Get in touch if you share my current interests or can suggest better :)

@soaringeagle
7 years ago
3,304 posts

i would not say love lol

but i often keep at it trying to figure it out haha
well what wsas originaly just to help fix a couple things not working right after my 1st time setting up an abuntu (or anything linux) server from scratch.
the dashboard and performance test and maybe 2 other pages didnt work..
then redid the server on centos (plesk had to be added to fix something) and litespeed (images 99% of images on main site didnt loadf under apache)
he did alot for the reasonable price for what i thought wasnt too much left to be done..
but left a few things unfinished and had typos and stuff in my.cnf i kept missing (only paying attention to values and couldnt figure out why they didnt take right)
real nice guy..greek.. put in a whole lot of hours over a couple months i would think when we expected it t9o be 3-5 days

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

SteveX
@ultrajam
7 years ago
2,583 posts

I hear ya, typos will always be a problem, the hours I have wasted ;(

--
¯\_(ツ)_/¯ Education, learning resources, TEL, AR/VR/MR, CC licensed content, panoramas, interactive narrative, sectional modules (like jrDocs), lunch at Uni of Bristol. Get in touch if you share my current interests or can suggest better :)

@soaringeagle
7 years ago
3,304 posts

that's why i was so glad i broke my back and didn't get into a career in mainframe programming.. 1,00,000 lines of code just to print a report and 1 misplaced . or : and the whole thing crashes

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

solved Error: Query Error: The table 'jr_jrcore_cache' is full

Tags