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.

My Github My LinkedIn

Soft I recommend

Mobaxterm SSH RDP FTP...
Thunderbird Email client
Filezilla FTP client/server
Nirsoft Win utils
Sysinternals Win utils
Pi-Hole AD block by DNS
NUT UPS manager
Rpi MON Raspberry monitoring
Free CAD 3D modelling
Free Commander Far-like filemanager
Bitwarden Password manager

There are no referral links!

Py lib I recommend

Django web framework
celery multi-tasking
celery-beat Celery + Django
celery-results Celery + Django
Pillow Python image lib
wsgi mod Apache + Python
requests best in WEB requests
openpyxl make Excell docs
p4python Perforce + Python
paramiko SSH + Python
pyvmomi ESXi Vcenter + Python

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

There are no referral links!

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 0 0/s 0.00
Sort scan 615 0.0/s
Sort range 168 0.0/s
Sort mrg pass 0 0/s

__ Query Cache _________________________________________________________
Memory usage 1.49M of 32.00M %Used: 4.65
Block Fragmnt 8.51%
Hits 31.06k 0.2/s
Inserts 5.68k 0.0/s
Insrt:Prune 5.68k:1 0.0/s
Hit:Insert 5.46:1

__ Table Locks _________________________________________________________
Waited 0 0/s %Total: 0.00
Immediate 11.87k 0.1/s

__ Tables ______________________________________________________________
Open 85 of 400 %Cache: 21.25
Opened 888 0.0/s

__ Connections _________________________________________________________
Max used 7 of 151 %Max: 4.64
Total 243 0.0/s

__ Created Temp ________________________________________________________
Disk table 2.80k 0.0/s
Table 6.61k 0.0/s Size: 16.0M
File 6 0.0/s

__ Threads _____________________________________________________________
Running 1 of 3
Cached 4 of 8 %Hit: 97.12
Created 7 0.0/s
Slow 0 0/s

__ Aborted _____________________________________________________________
Clients 136 0.0/s
Connects 13 0.0/s

__ Bytes _______________________________________________________________
Sent 76.37M 391.8/s
Received 10.59M 54.3/s

__ InnoDB Buffer Pool __________________________________________________
Usage 11.59M of 127.98M %Used: 9.06
Read hit 99.88%
Pages
 Free 7.45k %Total: 90.94
 Data 734 8.96 %Drty: 0.00
 Misc 8 0.10
 Latched 0 0.00
Reads 540.45k 2.8/s
 From file 657 0.0/s 0.12
 Ahead Rnd 0 0/s
 Ahead Sql 0/s
Writes 67.97k 0.3/s
Flushes 2.52k 0.0/s
Wait Free 0 0/s

__ InnoDB Lock _________________________________________________________
Waits 0 0/s
Current 0
Time acquiring
 Total 0 ms
 Average 0 ms
 Max 0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
 Reads 667 0.0/s
 Writes 3.61k 0.0/s
 fsync 1.25k 0.0/s
 Pending
 Reads 0
 Writes 0
 fsync 0

Pages
 Created 78 0.0/s
 Read 656 0.0/s
 Written 2.52k 0.0/s

Rows
 Deleted 57 0.0/s
 Inserted 8.88k 0.0/s
 Read 96.96k 0.5/s
 Updated 185 0.0/s

[/su_spoiler] Отлично, первое что произошло после изменения my.cnf - база упала. Пришлось все закомментить. Пока работал над своей базой, на работе упали продовские, так что это увлекательное путешествие я пока откладываю до свободного времени. Возвращаясь к прошлому опыту, я все таки выставил настройки по образу старой системы, описанному здесь: http://www.trianglesis.org.ua/mysql-default-my-cnf-konciguraciya [su_spoiler title=" mysqlreport"]

Use of uninitialized value in formline at /usr/bin/mysqlreport line 1238, <STDIN> line 1.
MySQL 5.5.38-0+wheezy1 uptime 0 0:5:59 Tue Oct 14 14:58:14 2014

__ Key _________________________________________________________________
Buffer used 0 of 64.00M %Used: 0.00
 Current 11.67M %Usage: 18.24
Write hit 0.00%
Read hit 0.00%

__ Questions ___________________________________________________________
Total 332 0.9/s
 DMS 153 0.4/s %Total: 46.08
 Com_ 108 0.3/s 32.53
 QC Hits 51 0.1/s 15.36
 COM_QUIT 46 0.1/s 13.86
 -Unknown 26 0.1/s 7.83
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 153 0.4/s 46.08
 SELECT 153 0.4/s 46.08 100.00
 REPLACE 0 0/s 0.00 0.00
 DELETE 0 0/s 0.00 0.00
 INSERT 0 0/s 0.00 0.00
 UPDATE 0 0/s 0.00 0.00
Com_ 108 0.3/s 32.53
 show_tables 38 0.1/s 11.45
 show_create 26 0.1/s 7.83
 admin_comma 19 0.1/s 5.72

__ SELECT and Sort _____________________________________________________
Scan 80 0.2/s %SELECT: 52.29
Range 6 0.0/s 3.92
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 10 0.0/s
Sort range 1 0.0/s
Sort mrg pass 0 0/s

__ Query Cache _________________________________________________________
Memory usage 274.22k of 23.00M %Used: 1.16
Block Fragmnt 0.52%
Hits 51 0.1/s
Inserts 83 0.2/s
Insrt:Prune 83:1 0.2/s
Hit:Insert 0.61:1

__ Table Locks _________________________________________________________
Waited 0 0/s %Total: 0.00
Immediate 165 0.5/s

__ Tables ______________________________________________________________
Open 100 of 1024 %Cache: 9.77
Opened 404 1.1/s

__ Connections _________________________________________________________
Max used 3 of 1000 %Max: 0.30
Total 49 0.1/s

__ Created Temp ________________________________________________________
Disk table 72 0.2/s
Table 278 0.8/s Size: 16.0M
File 6 0.0/s

__ Threads _____________________________________________________________
Running 1 of 1
Cached 2 of 128 %Hit: 93.88
Created 3 0.0/s
Slow 0 0/s

__ Aborted _____________________________________________________________
Clients 2 0.0/s
Connects 6 0.0/s

__ Bytes _______________________________________________________________
Sent 363.46k 1.0k/s
Received 36.03k 100.4/s

__ InnoDB Buffer Pool __________________________________________________
Usage 10.14M of 127.98M %Used: 7.92
Read hit 96.00%
Pages
 Free 7.54k %Total: 92.08
 Data 648 7.91 %Drty: 0.00
 Misc 1 0.01
 Latched 0 0.00
Reads 16.21k 45.1/s
 From file 649 1.8/s 4.00
 Ahead Rnd 0 0/s
 Ahead Sql 0/s
Writes 1 0.0/s
Flushes 1 0.0/s
Wait Free 0 0/s

__ InnoDB Lock _________________________________________________________
Waits 0 0/s
Current 0
Time acquiring
 Total 0 ms
 Average 0 ms
 Max 0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
 Reads 659 1.8/s
 Writes 7 0.0/s
 fsync 7 0.0/s
 Pending
 Reads 0
 Writes 0
 fsync 0

Pages
 Created 0 0/s
 Read 648 1.8/s
 Written 1 0.0/s

Rows
 Deleted 0 0/s
 Inserted 0 0/s
 Read 823 2.3/s
 Updated 0 0/s

[/su_spoiler]

Modified: 2024 April 21 (Sun) 17:53

Alex 2014 Sept. 04 (Thu) 01:08 SQL DB

tech

Tech posts, about installing or setting-up something.

raspberry

Everything related to raspberry

python

Using Python or coding in Python.

linux

octopus

Octopus is a framework for test execution, statistics collection and virtual machine deployment automation.

windows

WIndows OS and related issues and stuff.

REST

REST API for\from different services

Django

Django Web(Server) Framework

virtualization

SQL DB

SQL type database and related issues

project man

Project management tools and thoughts, software and not.

web

Site hostings, web server, web browsing, web developing.

personal

Just personal thoughts, they aren't always readable or adequate

photo

My photos or photo related stuff.