ot: MySQL config/tuning advice

classic Classic list List threaded Threaded
23 messages Options
12
Reply | Threaded
Open this post in threaded view
|

ot: MySQL config/tuning advice

Voytek
I have old server Postfix 2.x with MySQL, migrating to Postfix 3.x on a
new Centos 7 MariaDB 10.2, virtual user/domain, maybe 20 domain/100 users,
see abbreviated usage summary [1]

new server has been up and running few weeks with just a handful users,
just now, transferred another domain/25 users across to new server, and,
within few hours started getting mysql 'issues', it looks like I don't
have enough mysql resources

Jan  4 00:12:39 postfix/proxymap[28038]: warning: connect to mysql server
127.0.0.1: Lost connection to MySQL server at 'reading initial
communication packet', system error: 104

MariaDB is used for Postfix/Dovecot/Postfixadmin as well as Apache/PHP.

looking for suggestions, tips and advice how to determine 'optimal' mysql
and/or related settings

on startup it says:
Jan 04 07:48:24 mysqld-scl-helper[6908]: 2018-01-04  7:48:24
139791312545920 [Warning] Changed limits: max_open_files: 1024
max_connections: 151  table_cache: 431
Jan 04 07:48:24 systemd[1]: Started MariaDB 10.2 database server.

thanks, V


[1]
Grand Totals
------------
messages

   1596   received
   1977   delivered
      9   forwarded
      8   deferred  (19  deferrals)
      2   bounced
   4621   rejected (69%)
      0   reject warnings
      0   held
      4   discarded (0%)

 312364k  bytes received
 419827k  bytes delivered
    862   senders
    611   sending hosts/domains
    211   recipients
     81   recipient hosts/domains


Per-Day Traffic Summary
-----------------------
    date          received  delivered   deferred    bounced     rejected
    --------------------------------------------------------------------
    Jan  1 2018       215        211          8          0        472
    Jan  2 2018       201        221          1          0       2649
    Jan  3 2018       908       1251         10          0       1159
    Jan  4 2018       272        294          0          2        345




Reply | Threaded
Open this post in threaded view
|

Re: ot: MySQL config/tuning advice

Voytek
On Thu, January 4, 2018 12:00 pm, Voytek wrote:

> on startup it says: Jan 04 07:48:24 mysqld-scl-helper[6908]: 2018-01-04
> 7:48:24
> 139791312545920 [Warning] Changed limits: max_open_files: 1024
> max_connections: 151  table_cache: 431
> Jan 04 07:48:24 systemd[1]: Started MariaDB 10.2 database server.

tried editing /etc/my.cnf with, BUT, I don't think my MariaDB is reading
/etc/my.cnf, do I need my.cnf somewhere in /var/run/rh-mariadb102-mariadb/
 ??

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

max_connections = 250
open_files_limit = 10240

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/rh-mariadb102-mariadb/mariadb.pid

open_files_limit = 10240


Reply | Threaded
Open this post in threaded view
|

Re: ot: MySQL config/tuning advice

Phil Stracchino
On 01/03/18 20:39, Voytek wrote:

> On Thu, January 4, 2018 12:00 pm, Voytek wrote:
>
>> on startup it says: Jan 04 07:48:24 mysqld-scl-helper[6908]: 2018-01-04
>> 7:48:24
>> 139791312545920 [Warning] Changed limits: max_open_files: 1024
>> max_connections: 151  table_cache: 431
>> Jan 04 07:48:24 systemd[1]: Started MariaDB 10.2 database server.
>
> tried editing /etc/my.cnf with, BUT, I don't think my MariaDB is reading
> /etc/my.cnf, do I need my.cnf somewhere in /var/run/rh-mariadb102-mariadb/ ??

MariaDB does indeed read /etc/my.cnf, and should also read
/etc/my.cnf.d/*.cnf.  Make sure that nothing in /etc/my.cnf.d/ is
overriding what you changed.  Also, remember that changes in the config
files will not take effect until you restart mysqld.  But many MySQL
configuration variables are dynamic and can be applied from the
MySQL/MariaDB command-line shell without restarting.


> [mysqld]
> datadir=/var/lib/mysql
> socket=/var/lib/mysql/mysql.sock
>
> max_connections = 250
> open_files_limit = 10240
>
> [mysqld_safe]
> log-error=/var/log/mariadb/mariadb.log
> pid-file=/var/run/rh-mariadb102-mariadb/mariadb.pid
>
> open_files_limit = 10240




--
  Phil Stracchino
  Babylon Communications
  [hidden email]
  [hidden email]
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958
Reply | Threaded
Open this post in threaded view
|

Re: ot: MySQL config/tuning advice

John Stoffel-2
In reply to this post by Voytek
>>>>> "Voytek" == Voytek  <[hidden email]> writes:

Voytek> I have old server Postfix 2.x with MySQL, migrating to Postfix
Voytek> 3.x on a new Centos 7 MariaDB 10.2, virtual user/domain, maybe
Voytek> 20 domain/100 users, see abbreviated usage summary [1]

So what is the advantage of using mysql here?  Ease of adding/removing
users?  I think the penatly is quite high actually and that flat files
(hashed) will give you all the performance and ease of management that
you need here.

I went this route before when setting up my personal domain and it
just didn't make sense and I dropped all the mysql stuff.  If you
need/want SQL access, sqlite3 seems to be a good comprosmise in terms
of use.  

In any case, just bumping up the mysql defaults to higher numbers,
using the mysltuner script, might be all you need to do.  Google for
the script.  

Voytek> new server has been up and running few weeks with just a
Voytek> handful users, just now, transferred another domain/25 users
Voytek> across to new server, and, within few hours started getting
Voytek> mysql 'issues', it looks like I don't have enough mysql
Voytek> resources

How big a server did you get in terms of memory?  

Voytek> Jan  4 00:12:39 postfix/proxymap[28038]: warning: connect to mysql server
Voytek> 127.0.0.1: Lost connection to MySQL server at 'reading initial
Voytek> communication packet', system error: 104

Voytek> MariaDB is used for Postfix/Dovecot/Postfixadmin as well as Apache/PHP.

Voytek> looking for suggestions, tips and advice how to determine 'optimal' mysql
Voytek> and/or related settings

Voytek> on startup it says:
Voytek> Jan 04 07:48:24 mysqld-scl-helper[6908]: 2018-01-04  7:48:24
Voytek> 139791312545920 [Warning] Changed limits: max_open_files: 1024
Voytek> max_connections: 151  table_cache: 431
Voytek> Jan 04 07:48:24 systemd[1]: Started MariaDB 10.2 database server.

Voytek> thanks, V


Voytek> [1]
Voytek> Grand Totals
Voytek> ------------
Voytek> messages

Voytek>    1596   received
Voytek>    1977   delivered
Voytek>       9   forwarded
Voytek>       8   deferred  (19  deferrals)
Voytek>       2   bounced
Voytek>    4621   rejected (69%)
Voytek>       0   reject warnings
Voytek>       0   held
Voytek>       4   discarded (0%)

Voytek>  312364k  bytes received
Voytek>  419827k  bytes delivered
Voytek>     862   senders
Voytek>     611   sending hosts/domains
Voytek>     211   recipients
Voytek>      81   recipient hosts/domains


Voytek> Per-Day Traffic Summary
Voytek> -----------------------
Voytek>     date          received  delivered   deferred    bounced     rejected
Voytek>     --------------------------------------------------------------------
Voytek>     Jan  1 2018       215        211          8          0        472
Voytek>     Jan  2 2018       201        221          1          0       2649
Voytek>     Jan  3 2018       908       1251         10          0       1159
Voytek>     Jan  4 2018       272        294          0          2        345




Reply | Threaded
Open this post in threaded view
|

Re: ot: MySQL config/tuning advice

Bill Shirley
In reply to this post by Phil Stracchino
Make sure all .cnf files have permissions 644 to that all
users (apache, postfix, etc) can read them:
[0:root@elmo filter.d]$ ls -l /etc/my.cnf /etc/my.cnf.d/
-rw-r--r--. 1 root root  345 Feb 14  2016 /etc/my.cnf

/etc/my.cnf.d/:
total 24
-rw-r--r--. 1 root root 4128 Jul 27  2015 99-mystuff.cnf
-rw-r--r--. 1 root root  295 Dec 16  2015 client.cnf
-rw-r--r--. 1 root root 1107 Feb 14  2016 mariadb-server.cnf
-rw-r--r--. 1 root root  232 Dec 16  2015 mysql-clients.cnf
-rw-r--r--. 1 root root  285 Dec 16  2015 tokudb.cnf

Also, if you running with systemd as init, you need to override
the limits in the service file:
[0:root@elmo SPECS 130]$ cat /etc/systemd/system/mariadb.service
.include /usr/lib/systemd/system/mariadb.service

[Service]
#LimitNOFILE=infinity
LimitNOFILE=65536
LimitMEMLOCK=infinity

Your  open_files_limit in my.cnf won't do anything until you remove the
systemd constraints.

To check the current limits, get the pid:
[0:root@elmo filter.d]$ ps aux | grep mariadb
mysql     2523  1.5 14.1 4705824 1153820 ?     Sl    2017 1047:37 /usr/libexec/mysqld ....

which is currently 2523 and then:
[0:root@elmo filter.d]$ cat /proc/2523/limits
Limit                     Soft Limit           Hard Limit           Units    
Max cpu time              unlimited            unlimited            seconds  
Max file size             unlimited            unlimited            bytes    
Max data size             unlimited            unlimited            bytes    
Max stack size            8388608              unlimited            bytes    
Max core file size        0                    unlimited            bytes    
Max resident set          unlimited            unlimited            bytes    
Max processes             31841                31841                processes
Max open files            16895                16895                files    
Max locked memory         65536                unlimited            bytes    
Max address space         unlimited            unlimited            bytes    
Max file locks            unlimited            unlimited            locks    
Max pending signals       31841                31841                signals  
Max msgqueue size         819200               819200               bytes    
Max nice priority         0                    0                   
Max realtime priority     0                    0                   
Max realtime timeout      unlimited            unlimited            us       

You can also query mariadb itself:
[0:root@elmo filter.d]$ mysql
MySQL (root@localhost) [(none)]> SHOW GLOBAL VARIABLES LIKE '%file%';
+---------------------------------------+--------------------------------------+
| Variable_name                         | Value                                |
+---------------------------------------+--------------------------------------+
| aria_log_file_size                    | 1073741824                           |
| aria_max_sort_file_size               | 9223372036853727232                  |
| aria_pagecache_file_hash_size         | 512                                  |
| character_set_filesystem              | binary                               |
| ft_stopword_file                      | (built-in)                           |
| general_log_file                      | /var/log/mariadb/general.log         |
| init_file                             |                                      |
| innodb_buffer_pool_filename           | ib_buffer_pool                       |
| innodb_data_file_path                 | ibdata1:256M;ibdata2:256M:autoextend |
| innodb_disable_sort_file_cache        | OFF                                  |
| innodb_file_format                    | Antelope                             |
| innodb_file_format_check              | ON                                   |
| innodb_file_format_max                | Antelope                             |
| innodb_file_per_table                 | ON                                   |
| innodb_log_file_size                  | 50331648                             |
| innodb_log_files_in_group             | 2                                    |
| innodb_max_bitmap_file_size           | 104857600                            |
| innodb_open_files                     | 8192                                 |
| keep_files_on_create                  | OFF                                  |
| key_cache_file_hash_size              | 512                                  |
| large_files_support                   | ON                                   |
| local_infile                          | ON                                   |
| lower_case_file_system                | OFF                                  |
| myisam_max_sort_file_size             | 9223372036853727232                  |
| open_files_limit                      | 16895                                |
| performance_schema_max_file_classes   | 50                                   |
| performance_schema_max_file_handles   | 32768                                |
| performance_schema_max_file_instances | -1                                   |
| pid_file                              | /var/run/mariadb/mariadb.pid         |
| relay_log_info_file                   | relay-log.info                       |
| secure_file_priv                      |                                      |
| slow_query_log_file                   | /var/log/mariadb/slow_query.log      |
+---------------------------------------+--------------------------------------+

Try the above query with 'max%' too.

HTH,
Bill

On 1/3/2018 9:38 PM, Phil Stracchino wrote:
On 01/03/18 20:39, Voytek wrote:
On Thu, January 4, 2018 12:00 pm, Voytek wrote:

on startup it says: Jan 04 07:48:24 mysqld-scl-helper[6908]: 2018-01-04
7:48:24
139791312545920 [Warning] Changed limits: max_open_files: 1024
max_connections: 151  table_cache: 431
Jan 04 07:48:24 systemd[1]: Started MariaDB 10.2 database server.
tried editing /etc/my.cnf with, BUT, I don't think my MariaDB is reading
/etc/my.cnf, do I need my.cnf somewhere in /var/run/rh-mariadb102-mariadb/ ??
MariaDB does indeed read /etc/my.cnf, and should also read
/etc/my.cnf.d/*.cnf.  Make sure that nothing in /etc/my.cnf.d/ is
overriding what you changed.  Also, remember that changes in the config
files will not take effect until you restart mysqld.  But many MySQL
configuration variables are dynamic and can be applied from the
MySQL/MariaDB command-line shell without restarting.


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

max_connections = 250
open_files_limit = 10240

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/rh-mariadb102-mariadb/mariadb.pid

open_files_limit = 10240




Reply | Threaded
Open this post in threaded view
|

Re: ot: MySQL config/tuning advice

Voytek
On Thu, January 4, 2018 3:17 pm, Bill Shirley wrote:

> Also, if you running with systemd as init, you need to override
> the limits in the service file: [0:root@elmo SPECS 130]$ cat
> /etc/systemd/system/mariadb.service
> .include /usr/lib/systemd/system/mariadb.service
>
>
> [Service]
> #LimitNOFILE=infinity
> LimitNOFILE=65536
> LimitMEMLOCK=infinity
>
>
> Your  open_files_limit in my.cnf won't do anything until you remove the
> systemd constraints.

Bill, thanks!

yes, that's what precisely I'm struggling with, thanks for detailed info!!

I have it in /usr/lib/systemd/system :

ls -al /usr/lib/systemd/system/rh-mariadb102-mariadb.service
-rw-r--r-- 1 root root 3103 Oct 25 01:50
/usr/lib/systemd/system/rh-mariadb102-mariadb.service

in this file header, it says to do
# head  /usr/lib/systemd/system/rh-mariadb102-mariadb.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file
"/etc/systemd/system/rh-mariadb102-mariadb.service",
# containing
#       .include /usr/lib/systemd/system/rh-mariadb102-mariadb.service
#       ...make your changes here...


so, Ive made

cat /etc/systemd/system/rh-mariadb102-mariadb.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file
"/etc/systemd/system/rh-mariadb102-mariadb.service",
# containing
#       .include /usr/lib/systemd/system/rh-mariadb102-mariadb.service
#       ...make your changes here...

.include /usr/lib/systemd/system/rh-mariadb102-mariadb.service

LimitNOFILE=infinity
LimitMEMLOCK=infinity

 ls -al  /etc/systemd/system/rh-mariadb102-mariadb.service
-rw-r--r-- 1 root root 455 Jan  4 14:42
/etc/systemd/system/rh-mariadb102-mariadb.service

BUT, I don't think it gets picked up ??

I have put the two lines BEFORE I read your email, I'll now try with your
options

is this sufficient reload to activate changes ?
systemctl --system daemon-reload


Reply | Threaded
Open this post in threaded view
|

Re: ot: MySQL config/tuning advice

Phil Stracchino
On 01/04/18 00:17, Voytek wrote:

> On Thu, January 4, 2018 3:17 pm, Bill Shirley wrote:
>
>> Also, if you running with systemd as init, you need to override
>> the limits in the service file: [0:root@elmo SPECS 130]$ cat
>> /etc/systemd/system/mariadb.service
>> .include /usr/lib/systemd/system/mariadb.service
>>
>>
>> [Service]
>> #LimitNOFILE=infinity
>> LimitNOFILE=65536
>> LimitMEMLOCK=infinity
>>
>>
>> Your  open_files_limit in my.cnf won't do anything until you remove the
>> systemd constraints.
>
> Bill, thanks!
>
> yes, that's what precisely I'm struggling with, thanks for detailed info!!
>
> I have it in /usr/lib/systemd/system :


A curse on systemd.  I know the problems it was meant to solve, but the
cure is worse than the ailment.


--
  Phil Stracchino
  Babylon Communications
  [hidden email]
  [hidden email]
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958
Reply | Threaded
Open this post in threaded view
|

Re: ot: MySQL config/tuning advice

Voytek
In reply to this post by Voytek
On Thu, January 4, 2018 4:17 pm, Voytek wrote:
> On Thu, January 4, 2018 3:17 pm, Bill Shirley wrote:

Bill,

big big thanks!!!

I think it worked, i think the [Service] line ? made it work!!

still checking, I'll post later


V

Reply | Threaded
Open this post in threaded view
|

Re: ot: MySQL config/tuning advice

Voytek
In reply to this post by John Stoffel-2
On Thu, January 4, 2018 2:57 pm, John Stoffel wrote:

> So what is the advantage of using mysql here?  Ease of adding/removing
> users?

John, thanks

yes, ease of use, and, ability of having others able to add/edit if or as
needed


> In any case, just bumping up the mysql defaults to higher numbers,
> using the mysltuner script, might be all you need to do.  Google for the
> script.

found it, thanks, that will be next step

> How big a server did you get in terms of memory?

4GB, same as old server

On Thu, January 4, 2018 1:38 pm, Phil Stracchino wrote:

> MariaDB does indeed read /etc/my.cnf, and should also read
> /etc/my.cnf.d/*.cnf.  Make sure that nothing in /etc/my.cnf.d/ is


Phil,

thanks, as it was, the my.cnf that this server uses turned to be in
/etc/opt/rh/rh-mariadb102

so, initially I was editing wrong files, then, correct files, BUT changes
hit the systemd limit

but, I think I'm getting there, thanks again.

V

Reply | Threaded
Open this post in threaded view
|

Re: ot: MySQL config/tuning advice

Phil Stracchino
On 01/04/18 00:52, Voytek wrote:
> Phil,
>
> thanks, as it was, the my.cnf that this server uses turned to be in
> /etc/opt/rh/rh-mariadb102


I have to say, that location is pretty whacked.  I wonder who came up
with that?  Who built the MariaDB packages?


--
  Phil Stracchino
  Babylon Communications
  [hidden email]
  [hidden email]
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958
Reply | Threaded
Open this post in threaded view
|

Re: ot: MySQL config/tuning advice

Voytek
On Thu, January 4, 2018 11:58 pm, Phil Stracchino wrote:
> On 01/04/18 00:52, Voytek wrote:

> I have to say, that location is pretty whacked.  I wonder who came up
> with that?  Who built the MariaDB packages?

Phil,

and: systemctl status rh-mariadb102-mariadb

that's what happens when you blindly follow some instructions without
understanding what you doing....

CentOS SCLo Software Collections

say, what do you use for backup script ? I've used in the past one from
sf.net

tried the tuneup, need to wait to have longer time:

General recommendations:
    Control warning line(s) into
/var/opt/rh/rh-mariadb102/log/mariadb/mariadb.log file
    Control error line(s) into
/var/opt/rh/rh-mariadb102/log/mariadb/mariadb.log file
    MySQL started within last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your
configuration with skip-name-resolve=1
    Performance should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
    Read this before changing innodb_log_file_size and/or
innodb_log_files_in_group: http://bit.ly/2wgkDvS
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    performance_schema = ON enable PFS
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log
files size equals to 25% of buffer pool size.


Reply | Threaded
Open this post in threaded view
|

Re: ot: MySQL config/tuning advice

Phil Stracchino
On 01/04/18 16:08, Voytek wrote:

> On Thu, January 4, 2018 11:58 pm, Phil Stracchino wrote:
>> On 01/04/18 00:52, Voytek wrote:
>
>> I have to say, that location is pretty whacked.  I wonder who came up
>> with that?  Who built the MariaDB packages?
>
> Phil,
>
> and: systemctl status rh-mariadb102-mariadb
>
> that's what happens when you blindly follow some instructions without
> understanding what you doing....
>
> CentOS SCLo Software Collections
>
> say, what do you use for backup script ? I've used in the past one from
> sf.net

Honestly, a lot of the freeware MySQL backup scripts out there are truly
awful and don't understand that backing up a database is different from
backing up a filesystem.  I would suggest looking at using mydumper
rather than mysqldump, though again for an installation as small as I
suspect yours is it probably makes little difference.  The principal
shortcoming of mysqldump is that it is a benightedly stupid tool that
cannot walk and chew gum at the same time, or *correctly* dump both
MyISAM and InnoDB tables in the same operation.  It should have been
overhauled or completely replaced with the release of MySQL 5.0, if not
sooner.  Mydumper is what mysqldump should have evolved into ten or
fifteen years ago, but didn't.

I have my own wrapper scripts for both mydumper and mysqldump, but they
were written for my employer and I would have to check whether I can
release them for general use.


> tried the tuneup, need to wait to have longer time:
>
> General recommendations:
>     Control warning line(s) into
> /var/opt/rh/rh-mariadb102/log/mariadb/mariadb.log file
>     Control error line(s) into
> /var/opt/rh/rh-mariadb102/log/mariadb/mariadb.log file
>     MySQL started within last 24 hours - recommendations may be inaccurate
>     Configure your accounts with ip or subnets only, then update your
> configuration with skip-name-resolve=1
>     Performance should be activated for better diagnostics
>     Consider installing Sys schema from https://github.com/mysql/mysql-sys
>     Read this before changing innodb_log_file_size and/or
> innodb_log_files_in_group: http://bit.ly/2wgkDvS
> Variables to adjust:
>     query_cache_size (=0)
>     query_cache_type (=0)
>     query_cache_limit (> 1M, or use smaller result sets)
>     performance_schema = ON enable PFS
>     innodb_log_file_size should be (=16M) if possible, so InnoDB total log
> files size equals to 25% of buffer pool size.

The mysqltuner script is a good basic guide but don't take it as gospel.
 You probably actually want to turn performance_schema off; for an
installation this small you can probably leave query_cache on, since I
expect you will have a very high rate of exact repeat queries.  Do not
set the query cache size larger than 32MB without careful analysis of
cache statistics.  For best performance your InnoDB buffer pool should
be at least 25% to 30% larger than your total InnoDB data if possible,
and do not use MyISAM tables if you can avoid it.  The MyISAM storage
engine needs to just *die* already.


--
  Phil Stracchino
  Babylon Communications
  [hidden email]
  [hidden email]
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958
Reply | Threaded
Open this post in threaded view
|

Re: ot: MySQL config/tuning advice

Alex JOST-2
In reply to this post by Phil Stracchino
Am 04.01.2018 um 13:58 schrieb Phil Stracchino:
> On 01/04/18 00:52, Voytek wrote:
>> Phil,
>>
>> thanks, as it was, the my.cnf that this server uses turned to be in
>> /etc/opt/rh/rh-mariadb102
>
>
> I have to say, that location is pretty whacked.  I wonder who came up
> with that?  Who built the MariaDB packages?

Those are official packages by Red Hat. Sofware collections are a way to
concurrently install different versions of a software on the same
system. It's important that SCL packages do not interfere with 'normal'
packages.

--
Alex JOST
Reply | Threaded
Open this post in threaded view
|

Re: ot: MySQL config/tuning advice

@lbutlr
In reply to this post by Phil Stracchino
On 4 Jan 2018, at 14:40, Phil Stracchino [hidden email]> wrote:
> The principal shortcoming of mysqldump is that it is a benightedly stupid tool that cannot walk and chew gum at the same time, or *correctly* dump both MyISAM and InnoDB tables in the same operation.  

OK, I'll bite. I've been using mysql dump to backup my databases for at least decade (via a cron job), and in that time have often used it to also restore databases. So far, I'm never had an issue, but then again, I don't want to have an issue in the future either.

Is this the general consensus? Should I bee looking at another tool (MariaDB 10.0.33)?

--
My little brother got his arm stuck in the microwave. So my mom had to
take him to the hospital. My grandma dropped acid this morning, and she
freaked out. She hijacked a busload of penguins. So it's sort of a
family crisis. Bye!

Reply | Threaded
Open this post in threaded view
|

Re: ot: MySQL config/tuning advice

Phil Stracchino
On 01/05/18 20:55, @lbutlr wrote:
> On 4 Jan 2018, at 14:40, Phil Stracchino [hidden email]> wrote:
>> The principal shortcoming of mysqldump is that it is a benightedly stupid tool that cannot walk and chew gum at the same time, or *correctly* dump both MyISAM and InnoDB tables in the same operation.  
>
> OK, I'll bite. I've been using mysql dump to backup my databases for at least decade (via a cron job), and in that time have often used it to also restore databases. So far, I'm never had an issue, but then again, I don't want to have an issue in the future either.


I'll clarify the issue a little.

InnoDB is a transactional storage engine, and should in most cases be
backed up using --skip-lock-tables --single-transaction, to get a
consistent snapshot of the database without locking it.  MyISAM,
however, is non-transactional, and you cannot dump a MyISAM table
without read-locking it, which means that to get a consistent dump of a
database that contains MyISAM tables you MUST lock the database.

Mysqldump can only do one or the other for an entire backup.  It cannot
do anything table-by-table, and you have to tell it which of the two
unsatisfactory options - lock everything, or lock nothing - you want it
to use.

The third-party tool I mentioned, mydumper, is smart enough to be
storage-engine aware at the table level, so it gets a global read lock,
holds it JUST long enough to dump all of the MyISAM tables first, then
releases the read lock and dumps all of the InnoDB tables as a
background transaction.  This gives a fully consistent dump with the
shortest possible lock time and minimal impact to database access during
the dump.

You might want to give it a look.



--
  Phil Stracchino
  Babylon Communications
  [hidden email]
  [hidden email]
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958
Reply | Threaded
Open this post in threaded view
|

Re: ot: MySQL config/tuning advice

Postfix User-2
On Fri, 5 Jan 2018 21:13:36 -0500, Phil Stracchino stated:

>On 01/05/18 20:55, @lbutlr wrote:
>> On 4 Jan 2018, at 14:40, Phil Stracchino [hidden email]> wrote:  
>>> The principal shortcoming of mysqldump is that it is a benightedly stupid
>>> tool that cannot walk and chew gum at the same time, or *correctly* dump
>>> both MyISAM and InnoDB tables in the same operation.    
>>
>> OK, I'll bite. I've been using mysql dump to backup my databases for at
>> least decade (via a cron job), and in that time have often used it to also
>> restore databases. So far, I'm never had an issue, but then again, I don't
>> want to have an issue in the future either.  
>
>
>I'll clarify the issue a little.
>
>InnoDB is a transactional storage engine, and should in most cases be
>backed up using --skip-lock-tables --single-transaction, to get a
>consistent snapshot of the database without locking it.  MyISAM,
>however, is non-transactional, and you cannot dump a MyISAM table
>without read-locking it, which means that to get a consistent dump of a
>database that contains MyISAM tables you MUST lock the database.
>
>Mysqldump can only do one or the other for an entire backup.  It cannot
>do anything table-by-table, and you have to tell it which of the two
>unsatisfactory options - lock everything, or lock nothing - you want it
>to use.
>
>The third-party tool I mentioned, mydumper, is smart enough to be
>storage-engine aware at the table level, so it gets a global read lock,
>holds it JUST long enough to dump all of the MyISAM tables first, then
>releases the read lock and dumps all of the InnoDB tables as a
>background transaction.  This gives a fully consistent dump with the
>shortest possible lock time and minimal impact to database access during
>the dump.
>
>You might want to give it a look.


I have never used that application. I have been using "mysqlbackup"
<https://code.google.com/archive/p/mysqlbackup/> for my MySQL databases. It
seems to work fine for me.

--
Jerry
Reply | Threaded
Open this post in threaded view
|

Re: ot: MySQL config/tuning advice

Phil Stracchino
On 01/06/18 11:20, Postfix User wrote:
> I have never used that application. I have been using "mysqlbackup"
> <https://code.google.com/archive/p/mysqlbackup/> for my MySQL databases. It
> seems to work fine for me.

mysqlbackup is a wrapper script around mysqldump.


--
  Phil Stracchino
  Babylon Communications
  [hidden email]
  [hidden email]
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958
Reply | Threaded
Open this post in threaded view
|

Best practice when setting up a mail relay

Jonathan Sélea
Good evening,

I am in the process of setting up a smtp-relay for a hosting provider.

Basically, the relay should relay emails from hundreds of servers out to
the net. I do want some "protection" against if a website is hacked and
starts to spew out thousands of emails.
For example:
www.siteA.xyz on ServerY is hacked and someone is using mail() in order
to send hundreds of thousands email via localhost - that is relayed to
the smtp relay (that only accepts mail from internal servers). And
instead of relaying them out to the web it does stop thoose kind of email.

Is that possible? Can postfix just dump the emails "down the drain"
instead of sending them? And can that be triggered if ServerY sends 100
emails in 10 seconds for example.

I hope my problem is easy to understand :)

/ Jonathan

0x94B964DD.asc (3K) Download Attachment
smime.p7s (5K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Best practice when setting up a mail relay

Wietse Venema
Jonathan S?lea:

> Good evening,
>
> I am in the process of setting up a smtp-relay for a hosting provider.
>
> Basically, the relay should relay emails from hundreds of servers out to
> the net. I do want some "protection" against if a website is hacked and
> starts to spew out thousands of emails.
> For example:
> www.siteA.xyz on ServerY is hacked and someone is using mail() in order
> to send hundreds of thousands email via localhost - that is relayed to
> the smtp relay (that only accepts mail from internal servers). And
> instead of relaying them out to the web it does stop thoose kind of email.
>
> Is that possible? Can postfix just dump the emails "down the drain"
> instead of sending them? And can that be triggered if ServerY sends 100
> emails in 10 seconds for example.

You can use postfwd (www.postfwd.org) to enforce rate limits
on many SMTP properties (client, sender, recipient, ...).

> I hope my problem is easy to understand :)

Quite clear. Thanks for being a good network citizen.

        Wietse
Reply | Threaded
Open this post in threaded view
|

Re: Best practice when setting up a mail relay

Matthew McGehrin
Hello,

Depending on the volume of mail, you might want to consider having a
pool of outbound servers with a DNS round-robin, along with a dedicated
fallback server that only handles bounces. So that your primary queues
are only handling active deliveries, and your fallback just handles the
bounces/delayed messages.

Matthew


Wietse Venema wrote:

> Jonathan S?lea:
>  
>> Good evening,
>>
>> I am in the process of setting up a smtp-relay for a hosting provider.
>>
>> Basically, the relay should relay emails from hundreds of servers out to
>> the net. I do want some "protection" against if a website is hacked and
>> starts to spew out thousands of emails.
>> For example:
>> www.siteA.xyz on ServerY is hacked and someone is using mail() in order
>> to send hundreds of thousands email via localhost - that is relayed to
>> the smtp relay (that only accepts mail from internal servers). And
>> instead of relaying them out to the web it does stop thoose kind of email.
>>
>> Is that possible? Can postfix just dump the emails "down the drain"
>> instead of sending them? And can that be triggered if ServerY sends 100
>> emails in 10 seconds for example.
>>    
>
> You can use postfwd (www.postfwd.org) to enforce rate limits
> on many SMTP properties (client, sender, recipient, ...).
>
>  
>> I hope my problem is easy to understand :)
>>    
>
> Quite clear. Thanks for being a good network citizen.
>
> Wietse
>
>  
12