درخواست optimization

[hamed_m]

سرور دابل زیون با 8 گیگابایت رم و استفاده دائمی اسکریپتهای پرل از دیتابیس. گاهی سرعت جالب نیست و گاهی کوئریها با تاخیر زیاد اجرا میشوند. نکته اینجاست که تمام کوئریها هم حیاتی هستند و باید اجرا شوند.
کانفیگ مای اسکیوال:


[mysqld]
skip-innodb
skip-locking
max_connections = 500
key_buffer = 1024M
myisam_sort_buffer_size = 1024M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 32M
table_cache = 1500
thread_cache_size = 64
wait_timeout = 3600
connect_timeout = 100
tmp_table_size = 512M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 32M
max_allowed_packet = 64M
max_connect_errors = 100
query_cache_limit = 2048M
query_cache_size = 1024M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
long_query_time=2
log_slow_queries=/var/log/mysql/slow_query.log
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
open_files_limit = 8192

[mysqldump]
quick
skip-lock-tables
skip-opt

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M





از اساتید گرامی خواهش دارم اگر اشکال در کانفیگ بالا یا بهینه سازی مد نظرشون هست بفرمایند.

[ealireza]

سرور دابل زیون با 8 گیگابایت رم و استفاده دائمی اسکریپتهای پرل از دیتابیس. گاهی سرعت جالب نیست و گاهی کوئریها با تاخیر زیاد اجرا میشوند. نکته اینجاست که تمام کوئریها هم حیاتی هستند و باید اجرا شوند.
کانفیگ مای اسکیوال:


[mysqld]
skip-innodb
skip-locking
max_connections = 500
key_buffer = 1024M
myisam_sort_buffer_size = 1024M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 32M
table_cache = 1500
thread_cache_size = 64
wait_timeout = 3600
connect_timeout = 100
tmp_table_size = 512M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 32M
max_allowed_packet = 64M
max_connect_errors = 100
query_cache_limit = 2048M
query_cache_size = 1024M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
long_query_time=2
log_slow_queries=/var/log/mysql/slow_query.log
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
open_files_limit = 8192

[mysqldump]
quick
skip-lock-tables
skip-opt

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M





از اساتید گرامی خواهش دارم اگر اشکال در کانفیگ بالا یا بهینه سازی مد نظرشون هست بفرمایند.
با همچین سروری خیلی جلبه نتونی یک سری کوئری بگیری
سعی کن وب سرور و دیتابیس سرورت رو جدا کنی
موفق باشید

[reza_rad]

سعی کن مشکل رو در کوئری ها حل کنی...
Explain کمک خوبی می تونه باشه....
http://www.t-scripts.com/mysql/
http://2bits.com/articles/mysql-my-cnf-configuration-for-a-large-drupal-site.html

[reza_rad]

این هم کمکت می کنه:
http://mysqldatabaseadministration.blogspot.com/2005/11/mysql-5-optimization-and-tuning-guide.html

[oxygenws]

چه چیزی روی این سرور ریختی؟؟ یعنی دقیقا دیتابیس برای چی به کار میره؟!! چه سیستمی روی این سرور نصبه؟!

[hamed_m]

ممنون از پاسخهاتون دوستان گرامی.
این سرور درخواستهای یکسری اسکریپت تلفنی پرل رو سرویس میده. کوئریها تا حد امکان بهینه سازی شده اند. سرویس وب روی این سرور ارائه نمیشه، همینطور اسکریپتهای پرل از سرورهای تلفنی جداگانه اجرا و بواسطه Mysql لایب کوئریهاشون رو اعمال میکنند.
فقط بعنوان دیتابیس سرور ازش استفاده میشه. مشکل دقیقا زمانهای ترافیک بالا بوجود میاد. فعلا با کم کردن حجم درخواستها و سرور دوم مشکل رو حل کردیم.
از مای اسکیوال پیشنهاد کلاسترینگ داشتیم اما هزینه اینقدر بالا میره که گمانم به همین شیوه دستی و کم کردن بار عمل کنیم بهتر باشه.
بازهم ممنون

[oxygenws]

امکانش هست که ساختار جداول و پر کاربرد ترین کوئری ها رو بدونم؟؟
1- ساختار جداول خیلی مهمه برام، چون سرور کاملا تک منظوره است و میشه دقیقا در همون مورد بهینه اش کرد.
2- اگر نمی تونی کوئری بدی، ممکنه در همین حد insert داری یا select یا ... یا اینکه select هات چه شکلی اند ... هم بگی بد نیست...

[hamed_m]

جدول زیر از همه بیشتر مورد استفاده است:



CREATE TABLE `routes` (
`id` bigint(20) NOT NULL auto_increment,
`pattern` varchar(12) NOT NULL default '',
`edate` date default '0000-00-00',
`brate` float NOT NULL default '0',
`endpoint` varchar(10) default '0',
`priority` varchar(4) default '0',
`sdate` date default '0000-00-00',
`shour` time default '00:00:00',
`ehour` time default '00:00:00',
`routeplan` varchar(10) NOT NULL default '',
`active` char(3) NOT NULL default 'YES',
`bsetup` float NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `pattern` (`pattern`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=161117 ;


و این کوئری:



$querystring = "select * from routes where routeplan='$routeplan' and
pattern in( left('".$callednumber."',7)
,left('".$callednumber."',6)
,left('".$callednumber."',5)
,left('".$callednumber."',4)
,left('".$callednumber."',3)
,left('".$callednumber."',2)
,left('".$callednumber."',1)
) and active='YES' ORDER BY LENGTH(pattern) DESC , brate limit 1";

[reza_rad]

چرا روی فیلدهات ایندکس نمیذاری؟

این کمکت می کنه:
http://www.databasejournal.com/features/mysql/article.php/10897_1382791_1

توضیحات بیشتر در مورد ایندکس:
http://www.sitepoint.com/article/optimizing-mysql-application



What are Indexes?
Indexes are organized versions of specific columns in your tables. MySQL uses indexes to facilitate quick retrieval of records. With indexes, MySQL can jump directly to the records you want. Without any indexes, MySQL has to read the entire data file to find the correct record(s).

[hamed_m]

مرسی.
دو تا ایندکس وجود داره روی id بعنوان (PRIMARY KEY) و pattern.

[oxygenws]

۱- روی routeplan و active هم ایندکس بذار.
۲- active رو از نوع enum بذار!!
۳- priority رو فکر کنم بتونی tinyint بذاری!
۴- ...

به نظر می رسه جداولت اصلا بهینه و درست طراحی نشدن!
الان وقت ندارم تنظیماتت رو درست کنم، یحتمل فردا.

[hamed_m]

ممنون از راهنمایی. سعی میکنم تغییرات رو اعمال کنم اما باید با احتیاط پیش برم تا اسکریپتها دچار مشکل نشن.
بازم مرسی.

[deuce]

سلام
حجم دیتابیس شما چقدر است ؟
در گام اول حجم بافر ها را در my.cnf زیاد کنید به عنوان مثال
key_buffer_size=512M
read_buffer_size=256M
بعد از restart کردن سرویس بانک اطلاعاتی، سرعت را مقایسه کنید.
همچنین اگر ممکن است routeplan را در جدول دیگر بگذارید و در قسمت where از
routeplan_ID مربوطه استفاده کنید چون جستجو بر روی رشته ها کند تر از اعداد است

اگر بهبود کافی نبود، ممکن است ساختار دیتابیس شما طراحی اشتباهی داشته باشد

[hamed_m]

حجم دیتابیس حدود 6 گیگ هست در حال حاضر. اما دو تغییری که اشاره کردید کمک کرد. با این تفاوت که من read_buffer_size رو هم 512 کردم.
سعی میکنم routeplan رو هم جدا کنم. فکر بسیار جالبی هست.
ممنون.

[oxygenws]

سعی میکنم routeplan رو هم جدا کنم. فکر بسیار جالبی هست.
من حسن این کار رو نفهمیدم.
ممنون میشم برادر deuce توضیح مختصری ارایه بدن که از چه جهت سرعت رو افزایش میده؟

[reza_rad]

ممنون میشم برادر deuce توضیح مختصری ارایه بدن که از چه جهت سرعت رو افزایش میده؟

من فکر می کنم تازه باعث اضافه شدن join ها و پایین اومدن سرعت بشه

روی routplan ایندکس بذارید بهتره.

[hamed_m]

من حسن این کار رو نفهمیدم.
ممنون میشم برادر deuce توضیح مختصری ارایه بدن که از چه جهت سرعت رو افزایش میده؟

فکر کنم منظورشون این بود که از همون اول اگر به ازای هر پلنی یه جدول جدا داشته باشیم بهتر باشه. با توجه به اینکه پلنها انگشت شمارن میشه فکر کرد سرعت بالا بره البته به قیمت یک کوئری اضافه که بفهمیم از کدوم جدول دیتا رو بخونیم (؟؟؟).

[hamed_m]

من فکر می کنم تازه باعث اضافه شدن join ها و پایین اومدن سرعت بشه

روی routplan ایندکس بذارید بهتره.


الان ایندکس دارم و کمی بهتر از قبل شده.

[deuce]

سلام
با توجه به حجم دیتابیس شما معمولا" حجم key_buffer_size رو در حدود 1/4 کل حافظه در دسترس می گیرند بنابراین می تونید اون رو بازهم تا دو گیگابایت افزایش بدین

routeplan ها varchar تعریف شدند و چون در query که فرستادین از routeplan در whereاستفاده شده ، عملا" موجب string search میشه که سرعت query رو کاهش می ده. این کاهش در صورتی که تنوع routeplan ها کم باشه محسوس نیست و نیازی به جدا کردن اون نیست ولی وقتی تنوع اون ها بالا بره ، جدا کردن لازمه
آقای hamed_m هم اشتباه منظور رو متوجه شدند برای هر routeplan جدول جدا لازم نیست بلکه یه جدول با این محتویات اضافه میشه :



RoutePlan_ID RoutePlanName_VC
--------------------------------------
1 'routeplanName1'
2 'routeplanName2'
3 'routeplanName3'
...

[hamed_m]

شاید بهتر باشه routeplan ها tinyint باشه؟

[oxygenws]

شاید بهتر باشه routeplan ها tinyint باشه؟اگر می تونی، انجام بده و استخاره نکن :)

[hamed_m]

innodb
من بعضی جداول myisam این سرور رو به innodb تغییر دادم و نتیجه عالی بود. از قرار موقعی که update و select تقریبا همزمان انجام میشه، مای اسکیوال جداول رو قفل میکنه. علاوه بر این بعد از هر عمل باید شاخصها دوباره سازی شوند و اینهم سرعت رو پایین میاره.

این هم توضیح مختصری در این زمینه:
http://dev.mysql.com/doc/refman/5.1/en/internal-locking.html


Well, consider the following use case :

- rather large table, a bit larger than RAM
- lots of concurrent selects
- concurrent updates

If the hot set where most of the SELECTs happen fits in RAM, you'll have a very high SELECT throughput, since everything is cached.

Now, when you UPDATE a row, you think "well, it's only a row, so I can use MyISAM, it won't stay locked for long".

WRONG.

If, by any chance, you update an old row, which isn't in the cache, the table will stay locked for the time of a few disk seeks... a few indexes also have to be updated... say, 5-10 head seeks, maybe ? 50 ms ? During that time, you could have done about 500 or 1000 simple SELECTs...

Another example, I call this the select/update sandwich.

You have a quad dual core xeon CPU. Suppose (for an instant) that you are dumb and use MyISAM in the following configuration, which corresponds to the "users" table on a website I had to fix :

- table fits in RAM
- heavy small SELECT load (get the current user at each page)
- respectable UPDATE load (update last activity time every 5 minutes for each user)
- a slow frequent query (about 100 milliseconds) to get some statistics to display on the front page.

Now, since all those come in random order all the time, and SELECTs block UPDATEs, the query queue ends up like a sandwich :

- UPDATE
- SELECT
- UPDATE
- SELECT
- UPDATE
- SELECT
- UPDATE
- SELECT
- etc.

And since UPDATEs are non-concurrent, everything gets serialized, and only ONE cpu core is used.

If you have enough of the slow 50-100 ms SELECT coming, throughput drops to 10-20 queries per second since it sandwiches between the UPDATEs and locks everything.

So, use your brain (and don't use MyISAM)


سرور واقعا سریعتر شده. لود از 1 به 0.01 رسیده.
اما در استفاده از innodb کمی بی تجربه ام. علاوه بر این کلی گزارش کرش دیدم که نمیدونم استفاده ازش در حالت production بدون مشکله یا خیر.
دوستان صاحب تجربه راهنمایی بفرمایند لطفا.

[oxygenws]

من بعضی جداول myisam این سرور رو به innodb تغییر دادم و نتیجه عالی بود. از قرار موقعی که insert و update و select تقریبا همزمان انجام میشه، مای اسکیوال جداول رو قفل میکنه و این قفل ها به ترتیب اعمال میشه تا یکی جلو بیفته. علاوه بر این بعد از هر عمل باید شاخصها دوباره سازی شوند و اینهم سرعت رو پایین میاره
myisam امکان قفل کردن جدول رو داره و innodb امکان قفل کردن سطر و این باعث افزایش سرعت و کم شدن نسبی مصرف حافظه میشه (و نه مستقیما کم شدن پروسس)

[hamed_m]

myisam امکان قفل کردن جدول رو داره و innodb امکان قفل کردن سطر و این باعث افزایش سرعت و کم شدن نسبی مصرف حافظه میشه (و نه مستقیما کم شدن پروسس)

Since UPDATEs are non-concurrent, everything gets serialized, and only ONE cpu core is used