Forum
Aktif konular
Üyeler
Kurallar
Arama
Tahribat.com Forumları 
Database - Veritabanı 
SQL Tabanlı Veritabanı Optimum Çözüm ? En Çok Tıklananlar.... Fikirleriniz? SQL Tabanlı Veritabanı Optimum Çözüm ? En Çok Tıklananlar.... Fikirleriniz?
-
Arkadaşlar diyelim ki 9gag gibi bir sitemiz var. Tamamiyle varsayımsal konuşuyorum.
Minimum arama aralığı 1 Gün olacak şekilde.
Dün en çok tıklanan içerikler,
Bu hafta en çok tıklanan içerikler,
Bu ay en çok tıklanan içerikler,
Seçilebilir bir tarih aralığında en çok tıklanan içerikler
Listelemek için sistem kurmak istediğimizi düşünelim. Bunu en optimum nasıl yapabiliriz?
Benim aklıma iki tane çözüm geliyor.
1-)Bir tablo yaparız. Kullanıcı içeriğe tıkladığında, tıklanılan içeriğinID sini gösteren ve O günün tarihi olan satır varsa, o satırın tıklamaSayısı'nı bir arttırız.
Yoksa o satırı içerikid ve ogununTarihi ve TıklamaSayisi=1, şeklinde oluştururuz.
Tablo Yapısı: ID, içerikID, OgununTarihi, TıklamaSayısı
OgununTarihi indexed
içerikID foreignKey
ID primaryKey Auto İnc.
Sonrası malum zaten. Mesela dün en çok tıklananlar derse
SELECT ID FROM Tiklamalar WHERE OgununTarihi = Dün ORDER BY TıklanmaSayisi
Geçen Ay dersede SUM Tiklanma Sayilarını SUM ile toplayacağız, OgununTarihini de BETWEEN ile günleri belirleyeceğiz ve group by tiklanmaSayisi le göstereceğiz (sanırım group by dı).
2-)Kişiler her içeriğe tıkladığında tıklamalarını aşağıdaki şekildeki bir tabloya satır olarak ekleriz.
Tablo Yapısı:
ID, icerikID, TiklamaZamani(DateTime), tiklayanIP, userAgentBilgisi, TiklayanUserId, tiklamaSayisi=1
Gene aynı şekilde between(TiklamaZamani) ile tarih aralığını belirler, group by ile icerikID yi belirler, Sum(tiklamaSayisi) ilede en çok tiklananları buluruz gibime geliyor.
İkinci yöntemin avantajları günlük değil saatlik hatta dakikalık istatistikleri bile çıkarabiliriz.
İkinci yöntemin dezavantajı performas kaybı çok daha fazla olur ve veritabanında çok şişme yapacağı aşikar.
Birinci Yöntem daha optimum gibi.
Düşüncelerinizi bekliyorum arkadaşlar. Daha optimum çözüm öneriniz var mı?
SQL harici çözümlerde olur. NOsql, Neo4J(Ne kadar stabil ve hangi alanlarda kullanılması gerekiyor bilmiyorum) veya başka bir çözüm önerinizde olabilir.
Ama öncelikli olarak SQL tabanlı bir RDBMS ile bu olayı nasıl sağlayabiliriz?
Bu tür konulara pek ilgili olmuyor umarım kafa patlatanlar çıkar. -
basit bir siteyi ele alırsak ilk tercih mantıklı olan zaten ancak çok çok fazla veri kaydı tutulacaksa bu tür işlemler için partitioning olayını öneriyorlar benim hiç bu tür projem olmadığı için mantığını tam olarak bilemiyorum;
http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html
Türkçe: http://blog.mustafakirimli.com/mysql-partition-kullanimi-ve-partition-turleri/328
http://www.mssqltips.com/sqlservertip/2780/archiving-sql-server-data-using-partitioning/
Türkçe: http://www.yazilimdevi.com/Makaleler-669-sql-server-table-partitioning-and-index-partitioning.aspx
Ancak basitçe şöyle bir şey de önerebilirim; birden fazla tablo oluşturman sıkıntı olmuyorsa ve veriyi sadece 1 ay saklayıp eski verileri yavaş yavaş alttan sildireceksen günlük | haftalık | aylık tablolarda verileri saklamak o günün denk geldiği tabloda işlem yaptırmak biraz daha performanslı olacaktır bence.
pesimistzombie tarafından 23/Eki/14 01:56 tarihinde düzenlenmiştir -
pesimistzombie bunu yazdı
basit bir siteyi ele alırsak ilk tercih mantıklı olan zaten ancak çok çok fazla veri kaydı tutulacaksa bu tür işlemler için partitioning olayını öneriyorlar benim hiç bu tür projem olmadığı için mantığını tam olarak bilemiyorum;
http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html
Türkçe: http://blog.mustafakirimli.com/mysql-partition-kullanimi-ve-partition-turleri/328
http://www.mssqltips.com/sqlservertip/2780/archiving-sql-server-data-using-partitioning/
Türkçe: http://www.yazilimdevi.com/Makaleler-669-sql-server-table-partitioning-and-index-partitioning.aspx
Ancak basitçe şöyle bir şey de önerebilirim; birden fazla tablo oluşturman sıkıntı olmuyorsa ve veriyi sadece 1 ay saklayıp eski verileri yavaş yavaş alttan sildireceksen günlük | haftalık | aylık tablolarda verileri saklamak o günün denk geldiği tabloda işlem yaptırmak biraz daha performanslı olacaktır bence.
Partitioning sql aramalarında çok fayda sağlıyor(HIZ!) yeterki partition yapılan field üzerinden aramaları yap. Onu hertürlü yaparım hocam onda sıkıntı yok. Ancak dediğim gibi bunun için ekstra ve optimum bir saklama yöntemi var mı acaba.
Şimdi şöyle bir göz geçirelim.
1. Yöntemde 300K içerik olan ve günde 20K hit olan siteden hergün farklı şekilde 1K içerik'e tıklandığını düşünürsek. Hergün en az 1K row eklenecek veritabanına.
2. Yöntem zaten bu konuda çok şişme yapacağı kesin. 20K hitin (guestlerde istatistiklere kaydediliyorsa) 20K row eklenmesine sebeb olacak. Şişmenin alası.
Senin belirttiğin yöntemde AYRICA her ay ve her hafta 1K row daha eklenecek yani ayda toplam 4K+1K = 5K fazladan ROW. Ama her ay geçen ayın günlük HIT verilerini silersem (Bu da 30*1K dan 30K satır silmiş olacağım) çok rahatlayacağım kesin. fakat bu seferde geçmiş zamanların günlük hatta bir kaç günlük istatistiki bilgilerine erişemeyeceğim. Esnekliğim azalacak. Ya aylık ya da günlük verilerine erişebileceğim.
Şimdi böyle bir istatistik bilgi aslında aynı veritabanında tuttmakta büyük hata olur. Muhtemelen ayrı bir veritabanda tutacağım.
Acaba NOsql (mongoDB) veya diğer veritabanı türlerinin bu tür istatistiki bilgileri gerek saklarken gerek işlemede faydası olur mu? Olursa ne kadar olur.
İstatistiki bilgilerin karar destek sistemlerinden başka bir yerde kullanılacağını pek düşünmem.
Bu belirttiğim sistem bir 9gag değilde büyük bir e-ticaret sistemi olsaydı (mesela amazon.com). Bu verilerin önemi ileride çok fazla olacağı kesindi. Böyle büyük sistemlerde bu verilerin uçmaması çok önemli ancak benim bahsettiğim gibi 9gag gibi bir sistemde uçsada olur açıkçası pek önemli değil. O yüzden illa SQL tabanlı veri RDBMS de saklamakta gerekmiyor aslında.
DrKill tarafından 23/Eki/14 02:10 tarihinde düzenlenmiştir -
hocam soruyu ve yorumları çok dikkatli okuyamadım, baya yorgunum anlamak zor geldi :)
ama anladığım kadarıyla hızlı bir çözüm arıyorsun bunun için nosql yapısını kullanabilirsin. nosql çözümleri ilişkisel değildir o yüzden sistemin bir parçasını orada tutabilirsin. yani şöyle anlatayım..
veritabanın şuan neyse aynen onu kullan ama diğer istatisik ya da her neyse için gerekli bilgileri nosql çözümlerinde tutman işini kolaylaştırır hız açısından.. soruda belirttiğin tablo yapısnı nosql de yapabilirsin. orada ne kadar veri olduğu çok önemli değil.. nosql çözümleri CAP felsefesine göre çalışıyor. Tabi aynı anda en fazla ikisi seçilmeli (consistency, availability, partion tolerance)
http://en.wikipedia.org/wiki/CAP_theorem
burda cap ile ilgili bilgi alabilirsin.. hangisi işine yarayacak senin ? mesela AP uygun görünüyor senin için.. yani her zaman belirli bir değer dönderecek bir sistem (Availability-Partion Tolerance), bunun için kullanabileceğin cassandra, couchdb, simpledb, amazon dynamo ve linkedln voldermort. Ben cassandra yı çok sevdim :)
c# da cassandra ile ilgili yazdığım bir projenin kodlarını paylaşayım biraz fikir verir. Cassandra için CQL dilini kullanacaksın. SQL a çok benziyor..
using System; using System.Collections.Generic; using System.Diagnostics; using System.Linq; using System.Text; using System.Threading; using System.Windows.Forms; using Cassandra; using Cassandra.Data.Linq; using Cassandraemon; namespace CassandraAssignment { public class CassandraHelper { private Cluster cluster; private Session session = null; private Dictionary<string, movieHolder> actorList; private HashSet<string> genreList; private List<Movie> movieList; public int startedYear; public int endedYear; private int processCounter = 0; private object lockedObj; public delegate void Completed(); public event Completed OnCompleted; protected virtual void OnOnCompleted() { var handler = OnCompleted; if (handler != null) handler(); } #region Constructor public CassandraHelper(int started, int ended) { startedYear = started; endedYear = ended; lockedObj = new object(); } #endregion #region Connection Function public bool Connect(string[] nodes) { try { cluster = Cluster.Builder() .AddContactPoints(nodes) // .AddContactPoint("xx.xx.xx.xx") //("127.0.0.1")// // .WithQueryTimeout(5000) // .WithPort(9160) .Build(); // cluster.getConfiguration().getSocketOptions().setReadTimeoutMillis(HIGHER_TIMEOUT); session = cluster.Connect(); } catch (Exception e) { return false; } return true; } #endregion #region Create KeySpace If Not Exists and use group16 public void createKeySpaceIfNotExists(string name) { session.Execute("CREATE KEYSPACE IF NOT EXISTS group16 WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 2};"); session.Execute("Use group16"); } #endregion #region Create Tables public void createTables() { ExecuteQuery("CREATE TABLE IF NOT EXISTS Movies (title varchar PRIMARY KEY,info varchar);"); //title,year,rating,genres,actors // ExecuteQuery("CREATE TABLE IF NOT EXISTS Movies (title varchar PRIMARY KEY,year int,rating varchar,genres varchar,actors varchar)"); //CREATE TABLE actors (name varchar PRIMARY KEY, filmed_in int);") ExecuteQuery("CREATE TABLE IF NOT EXISTS Actors (actorName varchar PRIMARY KEY,filmCount int);"); ExecuteQuery("CREATE TABLE IF NOT EXISTS ActorMovies (constantId int,filmCount int,actorName varchar,PRIMARY KEY (constantId,filmcount,actorName));"); // ExecuteQuery("CREATE TABLE IF NOT EXISTS Genres (title varchar PRIMARY KEY,year int,rating float)"); ExecuteQuery("CREATE TABLE IF NOT EXISTS GenreMovies (genreName varchar,rating float,movieTitle varchar, PRIMARY KEY(genreName,rating,movieTitle));"); ExecuteQuery("CREATE TABLE IF NOT EXISTS GenreList (genreName varchar PRIMARY KEY);"); if (tablesAreEmpty()) { actorList = new Dictionary<string, movieHolder>(); genreList = new HashSet<string>(); insertMovicesFromTextDb(); } else OnOnCompleted(); } #endregion #region If Tables Are empty? (return bool function) private bool tablesAreEmpty() { var rowCountStr = ExecuteQuery("Select count(*) from Movies").GetRows().FirstOrDefault(); if (rowCountStr != null) { var count = Convert.ToInt32(rowCountStr[0].ToString()); if (count > 0) return false; } return true; } #endregion #region insertMovicesFromTextDb (reads movies from text db of imdb private void insertMovicesFromTextDb() { Utils utils = new Utils(); utils.MovieCompleted += utils_MovieCompleted; utils.setYears(startedYear, endedYear); var textDbPath = Application.StartupPath + "\\movies_dump.txt"; // utils.GetMovies(textDbPath); ThreadPool.QueueUserWorkItem(utils.GetMovies, textDbPath); } #endregion private void increaseProcessCounter() { lock (lockedObj) { processCounter++; if (movieList.Count == processCounter) cqlCompleted(); else if(movieList.Count>processCounter) { ThreadPool.QueueUserWorkItem(createCql, movieList[processCounter]); } } } #region utils_MovieCompleted Event (if getting data from text db has completed ) private void utils_MovieCompleted(List<Movie> movies) { movieList = movies; MessageBox.Show("Db den liste alındı"); // foreach (Movie movie in movies) // { ThreadPool.QueueUserWorkItem(createCql, movieList[processCounter]); increaseProcessCounter(); // ThreadPool.QueueUserWorkItem(createCql, movieList[processCounter]); // Thread.Sleep(10); // } /* BEGIN BATCH INSERT INTO users (userID, password, name) VALUES ('user2', 'ch@ngem3b', 'second user') UPDATE users SET password = 'ps22dhds' WHERE userID = 'user2' INSERT INTO users (userID, password) VALUES ('user3', 'ch@ngem3c') DELETE name FROM users WHERE userID = 'user2' INSERT INTO users (userID, password, name) VALUES ('user4', 'ch@ngem3c', 'Andrew') APPLY BATCH; */ } #endregion string movieCql = ""; private string actorCql = ""; private string genreCql = ""; private int movieCounter = 0; object objlock = new object(); #region Creating Cql Code for each movie to insert keyspace private void createCql(object m) { lock (objlock) { var movie = (Movie)m; movie.Title = movie.Title.Replace("'", " "); var actors = ""; if (movie.Actors.Count > 0) { for (int i = 0; i < movie.Actors.Count; i++) { actors += movie.Actors[i] + "|"; } actors = actors.Remove(actors.Length - 1, 1).Trim().Replace("'", " "); } var info = String.Format("Year: {0}, Rating: {1}, Genres: {2}, Actors: {3} ", movie.Year, movie.Rank.ToString(System.Globalization.CultureInfo.InvariantCulture).Replace(',', '.'), seperateString(movie.Genres), actors); movieCql += String.Format("INSERT INTO movies(title,info) VALUES('{0}','{1}');\n", movie.Title, info); // movieCql += String.Format("INSERT INTO movies(title,year,rating,genres,actors) VALUES('{0}',{1},{2},'{3}','{4}');\n", movie.Title, // movie.Year, movie.Rank.ToString().Replace(',', '.'), seperateString(movie.Genres),seperateString(movie.Actors)); // ExecuteQuery(insertMovieSql); foreach (var actor in movie.Actors) { var actor2 = actor.Replace("'", " "); increaseActorFilmCount(actor2); /* actorCql += String.Format("INSERT INTO ActorMovies (id,actorName,movieTitle) VALUES({0},'{1}','{2}');\n", counter, actor.ActorName, movie.Title); counter++; */ } foreach (var genre in movie.Genres) { genreCql += String.Format("INSERT INTO GenreMovies (genreName,rating,movieTitle) VALUES('{0}',{1},'{2}');\n", genre, movie.Rank.ToString(System.Globalization.CultureInfo.InvariantCulture), movie.Title); addGenre(genre); } // movieCounter++; increaseProcessCounter(); // cqlCompleted(); } } #endregion string seperateString(IEnumerable<string> list) { // var arrtmp = list.Split('|'); var result = ""; foreach (var tmp in list) { if (tmp != "") { result += tmp + " , "; } } if (result != "") result = result.Remove(result.Length - 3, 3).Replace("'", " ");; return result; } #region Cql Completed function, if all cql codes are ready, execute query private void cqlCompleted() { // lock (objlock) // { // if (movieCounter == movieList.Count) // { MessageBox.Show("Preperation of Queries are finished. Now they are executing.."); string query=""; query = String.Format("BEGIN BATCH\n{0}\nAPPLY BATCH;", movieCql); ExecuteQuery(query); actorCql = ""; foreach (KeyValuePair<string, movieHolder> actor in actorList) { actorCql += String.Format("INSERT INTO ActorMovies (constantId,filmCount,actorName) VALUES({0},{1},'{2}');\n", 1,actor.Value.i, actor.Key); actorCql += String.Format("INSERT INTO Actors (actorName,filmCount) VALUES('{0}',{1});\n", actor.Key,actor.Value.i); } query = String.Format("BEGIN BATCH\n{0}\nAPPLY BATCH;", actorCql); ExecuteQuery(query); // insert to genremovie table query = String.Format("BEGIN BATCH\n{0}\nAPPLY BATCH;", genreCql); ExecuteQuery(query); genreCql = ""; //insert genreList to genreList table foreach (var genre in genreList) { genreCql +=String.Format("INSERT INTO GenreList (GenreName) VALUES('{0}');\n", genre); } query = String.Format("BEGIN BATCH\n{0}\nAPPLY BATCH;", genreCql); ExecuteQuery(query); MessageBox.Show("All movies are saved in database."); OnOnCompleted(); //add genre list // var frmMain = (FrmMain) Application.OpenForms["FrmMain"]; // frmMain.GetGenres(); // } // } } #endregion #region Execution Function that returns rowset public RowSet ExecuteQuery(string query) { return session.Execute(query); } #endregion #region increase Actor filmed count. void increaseActorFilmCount( string actorName) { if (actorList.ContainsKey(actorName)) { movieHolder holder = actorList[actorName]; holder.i++; } else { actorList.Add(actorName, new movieHolder(1)); } } #endregion #region add Genres void addGenre(string genreName) { // if (actorList.ContainsKey(genreName)) // { genreList.Add(genreName); // movieHolder holder = genreList[genreName]; // holder.i++; // } // else //{ // genreList.Add(genreName, new movieHolder(1)); // } } #endregion #region Get Genre List public List<string> GetGenres() { var list= ExecuteQuery("Select GenreName from GenreList").GetRows(); var genreList = new List<string>(); foreach (var genre in list) { genreList.Add(genre[0].ToString()); } return genreList; } #endregion #region Get Top 10 Actors public RowSet GetTopActors(int count) { return ExecuteQuery("select actorName, filmCount from actormovies where constantid=1 order by filmcount desc limit " + count + ";"); } #endregion #region GetTopMovies public RowSet GetTopMovies(string genre, int count) { return ExecuteQuery("select movietitle, rating from genremovies WHERE genrename='" + genre + "' ORDER BY rating DESC LIMIT " + count + ";"); } #endregion #region GetActorFilm Count public int GetActorFilmCount(string actorName) { var count = 0; var rowset = ExecuteQuery("Select filmCount from actors where actorName='" + actorName + "';").GetRows(); var filmCount = rowset.FirstOrDefault(); if (filmCount != null) count = (int) filmCount[0]; return count; } #endregion #region unused !! delete actor from actorMovie /* public void deleteActor(string actorName) { ExecuteQuery("delete from actorMovies where constantId=1 and actorName='" + actorName + "'"); } public void AddActor(string actorName, int actorFilmCount) { var actorCql = String.Format("INSERT INTO ActorMovies (constantId,filmCount,actorName) VALUES({0},{1},'{2}');", 1,actorFilmCount,actorName); ExecuteQuery(actorCql); } */ #endregion } class movieHolder { public movieHolder(int x) { i = x; } public int i; } } -
kusura bakma baya kod uzunmuş :S eğer cassandra yı kullanacaksan zaman buldukça yardımcı olabilrim
-
Ziyaretçilerin de istatistiklerini tutmak hoş bir ayrıntı ancak suistimale açık bir ayrıntı. Kıl bir eleman her clickte ua değiştiren proxy destekli bir clicker dayarsa siteye o 1k row olur 100k row. Tek bir hücrede sadece hit tutmak daha iyidir ziyaretçiler için ya da gerekli önlemleri almak lazım.
Her gün 1k row gelse diye düşünürsek 2. gün 2k rowda count veya sum yapmak yerine parça tablolardaki 1k rowda işlem yapmak daha avantajlıdır bu nedenle 1. mantıktaki olayı tablolara bölüp desteklemek daha mantıklı geliyor bana.
nosql tarafında hiç deneyimim yok o konulara giremiyorum ancak yüzeysel olarak ele alırsam olay sadece performanssa eğer istatistikleri tutacak veritabanını kullanıcılarla anlık etkileşime sokmak yerine belirli saatlerde cronjob ile nosql tarafında tuttuğun verileri alıp arka planda işleyerek dinamik değil statik içerik bastırabilirsin işlediğin verilere göre.
+ ek yukardaki murid bahsetmiş cassandra bu konularda iyiymiş şurda bahsedilmiş http://stackoverflow.com/questions/3515114/what-is-the-best-database-storage-to-store-statistic-data
pesimistzombie tarafından 23/Eki/14 02:44 tarihinde düzenlenmiştir
Kısayol
Şikayet
Özel Mesaj
Alıntı yap
Cevapla