...
Just my blog

Blog about everything, mostly about tech stuff I made. Here is the list of stuff I'm using at my blog. Feel free to ask me about implementations.

Soft I recommend
Py lib I recommend

I'm using these libraries so you can ask me about them.

PostgreSQL first run

This is the most common issue for PostgreSQL first run in Linux env: configure the server to allow TCP/IP connections postgresql server it can produce PostgeSQL cannot connect to database host Here I'll show how to fix it before you struggle with firewall and ports: I've found ot here: 17.3. Starting the Database Server and How Do I Enable remote access to PostgreSQL database server? My situation:

vi /var/lib/pgsql/data/pg_hba.conf

add the string:

host all all 10.10.29.0/24 trust

Postgresql_start with my range and next config:

vi /var/lib/pgsql/data/postgresql.conf

add the string:

listen_addresses='*'

Postgresql_start_ipall After that restart the service:

systemctl restart postgresql.service

also you can use an old 'service postgresql restart'

[root@centst010 ~]# service postgresql restart
Redirecting to /bin/systemctl restart  postgresql.service

And here it is:

[root@centst010 ~]# netstat -anp | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN
tcp        0      0 192.168.1.40:5432       192.168.1.22:65369      ESTABLISHED
tcp6       0      0 :::5432                 :::*                    LISTEN

   

Read...

Can't create plan: Table 'bamboo_db.NOTIFICATIONS' doesn't exist RESOLVED

An issue with clean install Bamboo + MySQL

Can't create plan: Table 'bamboo_db.NOTIFICATIONS' doesn't exist RESOLVED This is log cut:

2015-06-12 15:21:37,985 ERROR [AtlassianEvent::0-BAM::EVENTS:pool-3-thread-8] [JDBCExceptionReporter] Table 'bamboo_db.NOTIFICATIONS' doesn't exist.
2015-06-12 15:21:38,030 ERROR [http-bio-8085-exec-2] [JDBCExceptionReporter] Table 'bamboo_db.NOTIFICATIONS' doesn't exist
2015-06-12 15:21:38,046 ERROR [http-bio-8085-exec-2] [ExceptionMappingInterceptor] org.springframework.dao.InvalidDataAccessResourceUsageException: could not initialize a collection: [com.atlassian.bamboo.notification.NotificationSetImpl.notificationRules#425985]; SQL [select notificati0_.NOTIFICATION_SET as NOTIFICA4_36_1_, notificati0_.NOTIFICATION_RULE_ID as NOTIFICA1_1_, notificati0_.NOTIFICATION_RULE_ID as NOTIFICA1_37_0_, notificati0_.RECIPIENT_TYPE as RECIPIENT2_37_0_, notificati0_.RECIPIENT as RECIPIENT37_0_, notificati0_.NOTIFICATION_SET as NOTIFICA4_37_0_, notificati0_.CONDITION_KEY as CONDITION5_37_0_, notificati0_.CONDITION_DATA as CONDITION6_37_0_ from NOTIFICATIONS notificati0_ where notificati0_.NOTIFICATION_SET=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not initialize a collection: [com.atlassian.bamboo.notification.NotificationSetImpl.notificationRules#425985]
Caused by: org.springframework.dao.InvalidDataAccessResourceUsageException: could not initialize a collection: [com.atlassian.bamboo.notification.NotificationSetImpl.notificationRules#425985]; SQL [select notificati0_.NOTIFICATION_SET as NOTIFICA4_36_1_, notificati0_.NOTIFICATION_RULE_ID as NOTIFICA1_1_, notificati0_.NOTIFICATION_RULE_ID as NOTIFICA1_37_0_, notificati0_.RECIPIENT_TYPE as RECIPIENT2_37_0_, notificati0_.RECIPIENT as RECIPIENT37_0_, notificati0_.NOTIFICATION_SET as NOTIFICA4_37_0_, notificati0_.CONDITION_KEY as CONDITION5_37_0_, notificati0_.CONDITION_DATA as CONDITION6_37_0_ from NOTIFICATIONS notificati0_ where notificati0_.NOTIFICATION_SET=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not initialize a collection: [com.atlassian.bamboo.notification.NotificationSetImpl.notificationRules#425985]

Too complex log, but all I understand from it …

Read...

MySQL how to Alter multiple schema tables

To MyISAM from all InnoDB:

SET @DATABASE_NAME = 'db_name';
SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=MyISAM;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'InnoDB'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

   

Read...

MySQL key efficiency 100

MySQL key efficiency 100% key efficiency 100 This is to remember the conf for this: for 1GB RAM dedicated server:

query_cache_limit = 4MB
query_cache_size = 128MB
key_buffer_size = 128M
max_connections = 300
wait_timeout = 7200
tmp_table_size = 64MB
max_connect_errors = 1000
connect_timeout = 20
max_allowed_packet = 16M

from here: thanks man! +1 Gb RAM = 2 Gb new config:

log-slow-admin-statements
query_cache_limit = 4MB
query_cache_size = 64M
key_buffer_size = 64M
max_connections = 300
wait_timeout = 7200
tmp_table_size = 12MB
max_connect_errors = 500
connect_timeout = 60
max_allowed_packet = 8M
innodb_buffer_pool_size = 128M
performance_schema

plugin_dir = /usr/lib/plugin
default-storage-engine = InnoDB

innodb = ON

 

Read...

MySQL tuning

Вот и пришло время, когда мне вдруг пришлось лезть в дебри MySQL tuning и пробовать заставить работать базу шустрее.

[su_spoiler title=" mysqlreport"]

Key _________________________________________________________________
Buffer used 3.00k of 16.00M %Used: 0.02
 Current 2.92M %Usage: 18.26
Write hit 99.44%
Read hit 99.70%
__ Questions ___________________________________________________________
Total 46.14k 0.2/s
 QC Hits 31.06k 0.2/s %Total: 67.31
 Com_ 11.13k 0.1/s 24.13
 DMS 6.62k 0.0/s 14.36
 -Unknown 2.85k 0.0/s 6.17
 COM_QUIT 173 0.0/s 0.37
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 6.62k 0.0/s 14.36
 SELECT 6.13k 0.0/s 13.29 92.59
 INSERT 233 0.0/s 0.50 3.52
 UPDATE 198 0.0/s 0.43 2.99
 DELETE 60 0.0/s 0.13 0.91
 REPLACE 0 0/s 0.00 0.00
Com_ 11.13k 0.1/s 24.13
 show_tables 3.26k 0.0/s 7.07
 admin_comma 2.76k 0.0/s 5.99
 show_fields 2.57k 0.0/s 5.56
__ SELECT and Sort _____________________________________________________
Scan 6.60k 0.0/s %SELECT: 107.63
Range 338 0.0/s 5.51
Full join 74 0.0/s 1.21
Range check 3 0.0/s 0.05
Full rng join …

Read...

MySQL default my.cnf концигурация

Далее приведена конфигурация MySQL сервера файлом: /etc/my.cnf по принятым стандартам для хоста с 1Gb оперативной памяти:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
query_cache_limit=1M
query_cache_size=23M
query_cache_type=1
max_connections=1000
max_user_connections=300
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=64M
join_buffer_size=1M
max_allowed_packet=32M
table_cache=1024
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
max_connect_errors=10
thread_concurrency=4

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

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

[client]
socket=/var/lib/mysql/mysql.sock

Read...

MySQL troubleshooting

Возможные проблемы:

  •  отваливается сокет /var/lib/musql/mysql.sock

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Лечится при помощи проверки прав на папку в директории: Проверяем:

[root@web-server ~]# cd /var/lib/mysql
[root@web-server mysql]# ls -l

Ставим: chown -R :mysql /var/lib/mysql -R - все вложенные, получат такие же права

drwx------. 2 mysql mysql 4096 Oct 31 07:32 mysql
srwxrwxrwx. 1 mysql mysql 0 Nov 8 19:41 mysql.sock
drwx------. 2 mysql mysql 4096 Nov 4 15:38 radio_tk_def
  • не стартует служба mysqld

[root@web-server mysql]# service mysqld start
MySQL Daemon failed to start.
Starting mysqld: [FAILED]

Проверим, все ли нормально с конфигами /etc/my.cnf Самая простая и рабочая конфига:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client] …

Read...