SQL Tune Nasıl Yapılır

Bu yazıda bir sql’i tune edip sorgu sonucunun hızlandırılması konusunda index ekleme yöntemini açıklayacağım. Burada anlatacağım yöntemi oracle, postgresql, mysql ve diğer veritabanlarında kullanabilirsiniz.

İlk olarak index nedir ve neden eklenmesi gerektiğini açıklamak istiyorum. İndex oluşturulmuş bir ağaç yapısıdır ve birden çok index türü vardır. B-tree index yapısını aşağıdaki şekilde inceleyebilirsiniz. Leaf block dediğimiz yerde ilgili tablonun içerisindeki rowların id leri bulunmaktadır. Bunu adres olarak düşünebiliriz. Branch blocks dediğimiz yer ise datanın belirli bir düzene göre gruplandığı yerdir. Aşağıdaki resimde görebileceğiniz gibi job_id kolonunda 2 değerine sahip satırların id numaraları bir blokta tutulmuş. Bu da job_id = 2 koşulu istendiğinde tabloyu full okumak yerine sadece ilgili bloklara gidip okuma yapacağı anlamına gelmektedir.

Tablo full okunmadığı için ilgili sql daha hızlı sonuç üretecektir. Daha hızlı sonuç üretmesi veritabanının fazla efor harcamamasına ve daha performanslı çalışmasına neden olacaktır.

Çok fazla transaction alan veritabanlarında sql’lerin sorgu sonuç süreleri kritik bir öneme sahiptir. Şu şekilde bir hesap yapacak olursak daha anlaşılır olacaktır. Elimizde çalışması 1 sn süren bir sql olsun ve bu sql uygulamadan veritabanına 10 bin kez gönderilsin. Bu işlem veritabanında 1 sn* 10 bin kez çalışacaktır ve veritabanını toplamda 10 bin saniye / 60 = 166 dakika meşgul edecektir. Tabi cpu’nun saniye de milyarlarca işlem yapabildiği için gerçek hayatta bu işlem 166 dakikada sürmeyecektir.

İlgili sql’e index ekleyip çalışma süresini yarıya düşürdüğümüzü varsayarsak toplamda 83 dakika kazanmış olacağız ki bu muazzam bir süre. Bu örnekte saniyeler üzerinden gidiyorum fakat sql’lerin veritabanında çalışma süreleri mili saniyeleri geçmemesi gerekiyor. 0.5 saniye bile bir sorgu için aşırı büyük bir değer.

Şimdi veritabanının bir sorguyu çalıştırırken sonucu getirmek için izlediği yolu göstereceğim. Örnek olarak elimizde aşağıdaki gibi bir sql sorgusu olsun.

select firts_name, last_name from hr.employee where job_id = 2;

Bu sql’i veritabanı şu şekilde yorumlayacaktır:

– job_id kolonunda değeri 2 olan
– hr.employee tablosundaki
– first_name ve last_name kolonundaki tüm verileri getir.

Sorgu bizim aksimize tersten okunarak istenilen dataları getirmek için işlenmeye başlandı. Bu kısımda job_id kolonunda 2 değerine sahip satırlar için tabloyu satır satır okuyup eşleşen kayıtları bulmaya çalışacaktır. İlgili kolonda eğer bir index yok ise explain plan da aşağıda da görebileceğiniz gibi “full table access” yöntemi kullanılacaktır.

OPERATIONOPTIONSOBJECT_NAMEPOSITION
SELECT STATEMENT2
..TABLE ACCESSTABLE ACCESS FULLEMPLOYEES1

Eğer employees tablosunun job_id kolonunda bir index olmuş olsaydı tabloyu full okumak zorunda kalmayacak ve explain plan’ı aşağıdaki gibi olacaktı.

OPERATIONOPTIONSOBJECT_NAMEPOSITION
SELECT STATEMENT2
..TABLE ACCESSINDEX RANGE SCANEMPLOYEES1

Şekilde görüldüğü gibi index range scan yöntemi ile tabloya yapılan erişim sorgu maliyetini düşürmektedir. Elinizdeki sql’leri bu doğrultuda inceleyerek gerekli olabilecek indexleri oluşturabilirsiniz.

Son olarak aşağıdaki gibi bir sql’i ele alalım.

select firts_name, last_name from hr.employee where job_id = 2 and first_name = 'Emrah';

Bu sqli çalıştırırken job_id kolonunda index olmasına rağmen sorgu çalıştırıldığında ilgili index kullanılmayabilir. Bu durumda bileşik index oluşturulması gerekir. İlk oluşturulan basit index tipini ifade etmekteydi. Bu durumda bileşik index oluşturmak zorunda kalınabilirdi. Oracle veritabanında basit ve bileşik index aşağıdaki gibi oluşturulabilir.

# basit index
create index [index_name] on [table_name] (column);

# bileşik index
create index [index_name] on [table_name] (column1,column2);

PostgreSQL Hot Backup Script

Bu yazıda postgreSQL’in bash scriptleri ile backup alınmasını paylaşacağım. Bu backup yöntemi sayesinde veritabanınızı kapatmaya gerek kalmadan backup alabiliriniz. Bu işlemi yaparken veritabanınızın archivelog modda olduğu varsayılmaktadır.

Postgresql’in kurulu olduğu dizin yapısı şu şekildedir.

/
postgresql
--10 
----archives
----data 
------base
------pg_wal
------logs
------....
------....
------....
--backup

/postgresql/data dizini postgresql’in base dizinidir. Burdaki dosyaları tar’layıp /postgresql/backup dizinine atacağız. Bunu yapan bash scripti aşağıdadır. Bu scriptin postgres kullanıcısı ile çalıştırılması gerekmektedir.

datetoday=$(date '+%Y-%m-%d')

psql -c "SELECT pg_start_backup('hotbackup start');"

cd /postgres/10/

tar -cf /postgres/backup/PGDBBACKUP_$datetoday.tar data/

psql -c "select pg_stop_backup();"

tar -cf /postgres/backup/PGDBBACKUPARC_$datetoday.tar archives/

find /postgres/backup/ -name '*.tar' -mtime +10 -exec rm {} \;
 
cd ./archives/

for i in $(find . -name "*.backup" -mtime +3); do
   echo "$i"
   walname=$(echo $i | cut -c3-42)
   /usr/pgsql-10/bin/pg_archivecleanup -d /postgres/10/archives "$walname"
done 

PostgreSQL Tablo Yetki Problemi – ERROR: permission denied for schema “schema_name”

Postgresql de kullanıcıların owneri olmadıkları tablolara select çekebilmesi için verilen “grant select on tbl_name to user” tek başına yeterli olmamaktadır. Bu yetki tanımlandığı halde aşağıdaki hatayı alıyorsanız çözümü bu yazıda bulabilirsiniz.,

ERROR:  permission denied for schema "schema_name"
SQL state: 42501

Bu örnekte db create edip farklı kullanıcılardan o db de bulunan tablolara select yetkisi tanımlama işlemi yapacağım.

Aşağıdaki gibi db create edilmiştir. Bu db’nin owneri postgres (Postgres default ve admin yetkisine sahip user’dir) kullanıcısıdır.

CREATE DATABASE "TESTDB"
WITH
OWNER = postgres
ENCODING = 'UTF8'
CONNECTION LIMIT = -1;

TESTDB create edildikten sonra db içerisinde default olarak public scheması gelecektir. Bu schemanın owneri postgres olacaktır. “emrah” kullanıcısına ait yeni bir schema oluşturup içerisine tablo oluşturacağım.

CREATE SCHEMA newschema
AUTHORIZATION emrah;

COMMENT ON SCHEMA newschema
IS 'this schema depend to emrah';

-- emrah kullanıcısından login olup tablo create edilmiştir.
CREATE TABLE newschema.tbl1 (
col1 character(100),
col2 character(100)
);
ALTER TABLE newschema.tbl1
OWNER to emrah;

Emrah kullanıcısından tabloya Taner kullanıcısı için select yetkisi tanımlama işlemini şağıdaki gibi yapıyorum

-- Yetki emrah kullanıcısından login olunarak tanımlanmıştır.
GRANT SELECT ON TABLE newschema.tbl1 TO taner;

Taner kullanıcısı ile login olup select çektiğimde aşağıdaki hata ile karşılaşıyorum.

SQL> SELECT * FROM newschema.tbl1;

ERROR: permission denied for schema newschema
LINE 1: SELECT * FROM newschema.tbl1
^
SQL state: 42501
Character: 15

Postgrenin bu hatayı vermesinin nedeni create edilmiş schema (newschema) üzerinde Taner kullanıcısının usage yetkisinin bulunmamasından kaynaklanmaktadır. Yetki aşağıdaki şekilde tanımlanmalıdır.

GRANT USAGE ON SCHEMA newschema TO taner;

Eğer ilgili schema içerisinde Taner kullanıcısına table create yetkisi de tanımlamak istiyorsanız aşağıdaki yetkiyi de beraberinde verebilirsiniz. Bu yetki tanımlandığında ilgili schema içerisinde; kullanıcı table create ve owneri olduğu tablolara drop/alter yetkisinde sahip olacaktır.

GRANT CREATE ON SCHEMA newschema TO taner;