0

I have two servers.

Server A : 32 core ,128GB ram , centos 7 , ssd (Dedicated Cpu Cloud Server)
Server B : 8 core 32GB ram , centos 7 , ssd (Bare Metal Server) .

I have a issue with server A . It suddenly got slower. I didnt make any changes on mysql config , or on server config , no update nothing. I spoke to server support , they told me hostsystem has no problem.

Both server has same version of mysql and php. For testing purposes I run simple insert

INSERT INTO test_table (test) VALUES (1);

Both test tables were empty , there were no traffic when I did test (actually traffic is not affecting test result ) , Both server has huge amount of free ram , load averages were 1 on both servers when I did test.

problem is Server-A is 2 times slower than Server-B .

I often need to insert and update bulk data on Server-A so performance problem affecting my project so much.

I did same insert test with php for each loop , inserted 1000 record. On Server-A I noticed iowait jumps from 0 to 0.6 .

How should I track where mysql slows down ?

6
  • What made you conclude this is a database problem? Dec 11, 2021 at 11:13
  • Thank you for your answer. I realized this problem because of database performance, where should I look for the problem ? can it be io related ?
    – PanelMaker
    Dec 11, 2021 at 11:18
  • 1
    Where? With the cloud, isn't the sky the limit? But IO would also be my first, since you mention having a "dedicated cpu cloud server", which leaves storage free for all. Run a simple IO benchmark (eg. copying files larger than available memory) on both servers. Then I'd still check CPU performance, followed by network. Come back with the results, I'm curious. BTW, you were able to discern a difference of 100% performance by inserting a single row consisting of one integer? Did that table have other columns apart from "test", default values, triggers, foreign keys, indices? Dec 11, 2021 at 12:09
  • 1
    To Gerard's point: "I did same insert test with php for each loop, inserted 1000 record" - looping 1,000 times to do singleton inserts is not a good test of database issues, generally, because there can be some additional variability between one iteration to the next, at the application level. Databases are meant to process sets of data, so a better test would be to insert 1,000 records in a single batch, and compare the performance difference between the two servers of that task.
    – J.D.
    Dec 11, 2021 at 13:25
  • 1
    Additional information request FROM Server A, please. Post on pastebin.com and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; F) SHOW CREATE TABLE test_table; G) SHOW ENGINE INNODB STATUS; H) SHOW TABLE STATUS WHERE name LIKE 'test_table'; for server workload tuning analysis to provide suggestions. Dec 12, 2021 at 13:38

1 Answer 1

0

(Partial answer)

Set long_query_time = 0 and otherwise turn in the "slowlog". Then rerun your test. Then use pt-query-digest or mysqldump -s t to summarize the slowlog. That will identify any database queries that are causing the slowdown. (It won't identify non-MySQL things.)

After finishing, set long_query_time = 1 but leave the slowlog on. It may come in handy later.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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