0

This is my server specification

my.cnf:

[mysqld]

innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=50000
default-storage-engine=MyISAM
myisam_sort_buffer_size=256M
table_open_cache = 600
thread_cache_size = 4
query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M


join_buffer_size = 256k
tmp_table_size = 20M
max_connections = 600

slow-query-log = 1
slow-query-log-file = /output/mysql-slow.log
long_query_time = 1
datadir=/disk3/mysql

Server Ram

             total       used       free     shared    buffers     cached
Mem:           62G        61G       1.5G       1.8G       2.0G        53G
-/+ buffers/cache:       5.6G        57G
Swap:         1.9G       230M       1.7G

CPU

model name      : Intel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz × 2
cpu MHz         : 2000.022
cache size      : 15360 KB

I have a very big MyIsam Table , each user has status record in it, I select insert join to it. Here is the table :

CREATE TABLE `status_userid` (
             `id` int(10) NOT NULL AUTO_INCREMENT,
             `md5id` char(32) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
             `ptype` char(1) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
             `media_id` varchar(50) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
             `media_url` varchar(100) COLLATE utf8_swedish_ci NOT NULL,
             `media_photo` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
             `user_id` varchar(50) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
             `user_name` varchar(100) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
             `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
             `reason` text COLLATE utf8_swedish_ci,
             PRIMARY KEY (`id`),
             KEY `md5id_mediaid` (`md5id`,`media_id`),
             KEY `user_id_md5id` (`user_id`,`md5id`)
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci

from mysql.slow.log:

Count: 1165  Time=11.17s (13008s)  Lock=0.00s (0s)  Rows=548.3 (638790), myinstar_ranker[myinstar_ranker]@localhost
  SELECT
  s1.id as order_by,
  s1.media_id,
  s1.media_photo,
  s1.user_id,
  s1.user_name,
  s1.date
  FROM `status_427` s1
  LEFT JOIN
  (SELECT md5id, user_id, ptype FROM `status_427`  WHERE md5id = 'S') s2
  ON
  s1.user_id = s2.user_id AND s2.ptype = 'S'
  WHERE
  s1.md5id = 'S'
  AND s2.md5id IS NULL
  AND s1.date  < NOW() - INTERVAL N DAY
  AND s1.ptype = 'S'

Count: 2247  Time=8.09s (18172s)  Lock=0.00s (1s)  Rows=1.0 (2247), myinstar_ranker[myinstar_ranker]@localhost
  SELECT
  count(N) as counter
  FROM `status_421` s1
  LEFT JOIN
  (SELECT md5id, user_id, ptype FROM `status_421`  WHERE md5id = 'S') s2
  ON
  s1.user_id = s2.user_id AND s2.ptype = 'S'
  WHERE
  s1.md5id = 'S'
  AND s2.md5id IS NULL
  AND s1.date  < NOW() - INTERVAL N DAY
  AND s1.ptype = 'S'

I manage to create separate table for each user so I don't end with a huge table if it damaged I lost everything. but this table is very big one, 8M record and counting, that's is why I decided to split it.

Mysql directory on SSD drive.

I have a huge mysql using, and the server top is:

Tasks: 781 total,   5 running, 775 sleeping,   1 stopped,   0 zombie
Cpu(s): 41.1%us, 10.1%sy,  0.0%ni, 48.5%id,  0.0%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:  65784692k total, 64190512k used,  1594180k free,  2141220k buffers
Swap:  1999868k total,   236436k used,  1763432k free, 56162120k cached

    PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 459105 mysql     20   0 12.4g 286m 6588 S 564.2  0.4 137:35.08 mysqld

So, does innoDb may help to reduce mysql Load? What is the best inndoDb my.cnf setting, I was told innoDb is very sensitive and can be damaged so fast, and even more it create a huge log file.

I am not Mysql expert, I know php and some of mysql, but not expert in tuning it.

What are the best setting I can use to reduce server load?

0
  • innodb_buffer_pool_size is the most important setting for InnoDB. See http://mysql.rjweb.org/doc.php/memory

  • Tips on converting from MyISAM: http://mysql.rjweb.org/doc.php/myisam2innodb

  • Don't use utf8 for ascii strings. md5id?

  • Change user_id_md5id to INDEX(user_id, md5id, ptype) for the LEFT JOIN ( SELECT ... ).

  • INDEX(md5id, ptype, date), with date last, for the outer query.

  • Rethink having lots of tables named status_nnn; there may be better ways to design the schema.

  • max_connections = 600 and 5 running, 775 sleeping -- It may be better to throttle the number of clients, and not let so many get to MySQL.

  • How big is the table? (rows or GB)

| improve this answer | |
  • Dear Rick,Than k you for your reply. >Don't use utf8 for ascii strings. md5id? I dont get this , you mean use asci here ? in the MD5ID ? i have now a big problem how can i go to all table and change the indexs ! i have 1450 users then 1450 table . How big is the table? (rows or GB) , i have 7M row and counting ! max_connections = 600 and 5 running, 775 sleeping -- It may be better to throttle the number of clients, and not let so many get to MySQL. Well i have 150 client "worker" is doing job but this number gos up and down – beshoo May 31 '17 at 23:43
  • MD5() generates hex, which involves 1-byte ascii characters. utf8 characters take up to 3 bytes. There is more than one table like this? One table per user is "bad schema design". How can there be more sleeping than max_connections?? – Rick James Jun 1 '17 at 0:01
  • thank you so much , regarding this big table , i know that the split is a bad idea but i got this from word press community ,they make tables set for each user , i will go back to one table but i need to make it right , cos i will get all data form all other split table to this table so it is one job to be don and i want to do it one time. alter table is not an easy thing to do each time. regarding sleep , i just show the first line of top, here is full output heypasteit.com/clip/0IILQA , Please what to do with my.cnf since i will change to inoDB , PS: can i contact your email! – beshoo Jun 1 '17 at 10:28
  • i installed mytop , i note a lot of sleep mysql and some sql statement which is i think related to gearmand workers since i have 150 worker waiting the command to be sent via mysql. dos sleep cause a huge load ? but on other hand , worker will not start mysql connection unless it start the job , while worker in sleep mode thare is no sql connection , but i have other script in loop checking mysql table each 5 sec and sleep 5 sec , here is the sql :heypasteit.com/clip/0IILQG mytop : heypasteit.com/clip/0IILQF – beshoo Jun 1 '17 at 11:16
  • after research , i found why there is a sleep in mysql , as i told you i have workers waiting for job , when this job passed , worker start using mysql to get the job , then do other works , and use mysql within this works . and die . thats why i have a lot of sleep . now my target is to make this big table work smooth . as i told you i need the correct setting of innodb , I dont like the way "I font this seeting is it good" , i am sure each case has its own settings but i dont know how to reach this since i am php programmer not mysql administrator , thats why i am looking for your help – beshoo Jun 1 '17 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.