How to understand why it MySQL?


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
8 views
Server C2Q 2 x 8G ram. RAID 5( 3 hdd ), the mysql 5.1.26-rc \\ Red Hat 4.1.2-14


When collecting(two years ago) were young and stupid, but the server generally fit into their parameters.


So we have a relatively high load on MySQL 601.90 requests per second, of them updates\\inserts — 2% and ~70% — stmp prepare\\execute\\close, to share pure select remains 34.84%


And a week ago the base has learned to die has created heaps of processes which worked for half an hour.
Oddity 1 — one hour all repaired ITSELF


In General, began raking the state of the server.

As one of the points in the engine's code was added to dump the runtime operations in the database back to the database.

This code worked for queries that took longer than 0.1 sec slow_log them does not see, but this is brakes...


In General, there went the strangeness — the most common query that's running it handles, performs 0.0001 will report to the database that it runs 0.5 or even TWO seconds...

Oddity number two is the brakes are going in small batches at 5-10 ping requests approximately every 11 seconds.

And at this point, usually only a few tables Toromont( ie I see a pack is essentially the same query in the log at this point)


Since 99 brake had queries on innoDB tables was performed some dances — enabled file_per_table and tables of total landfill(11Гб) extinct in their little files( final total size of 4Gb, fragmentation there was dibou )


LA servers, 0.9

disposal screw — 15-20%


Config here

Free memory is.

Idea where the brakes are and what to do — no


As Percona or MariaDB (5.1.6?)

Bonus pack — when mysql hangs — hear from him is not off, the processes are not completed.

Nothing except kill -9....
by | 8 views

7 Answers

0 like 0 dislike
> RAID keeps the normal gelezyaka
>LSI
is ridiculous. batteries and memory, there is in fact no?
\r
>RAID5
>innoDB
>utilization of screw — 15-20%
>innodb_flush_log_at_trx_commit = 2
>the most common query... TWO seconds...
\r
The entry in RAID5 already necessary logical operations difficult, and in the implementation of LSI usually it turns out worse. As here already spoke, is preferable for databases, RAID1,RAID10 and variations.
\r
Is to reduce the intensity of the write operations. Try first
innodb_flush_log_at_trx_commit=0
innodb_support_xa=0
tmpdir = /tmp/ — transfer in tmps
\r
In the second place more dangerous parameters:
innodb_doublewrite=0
delay_key_write=ALL
innodb_flush_method=nosync — do not know does someone is. the value of even the undocumented, but somewhere you can find it using. if performance requirements are high and the server is not overloaded all at once ( and why would he be transferred in a good datacenter?), you can use.
\r
read about each option, because this change is a compromise between speed and data reliability.
\r
bin-log — know exactly why you need it?
query_cache_size = 0 — do you have it all ineffective? put well, at least 16Mb.
sort_buffer_size = 256M — be careful with that, it stands out completely in each processor regardless of the actual needs. At overload, server requests, possibly exhaustion of memory and ubitye first mysqld.
by
0 like 0 dislike
Not terrible with 3 hdd in RAID5? (IMO it's the problem)
by
0 like 0 dislike
What is the status of mysql at the time of the hang?
\r
Iron eyes looked? The first suspicion falls on the RAID. First, he is fifth with all the consequences, in the second for the current winchesters 2 years — it is currently the retirement age.
by
0 like 0 dislike
I think the problem is in RAID 5. It is not very suitable for databases. I would put another screw and made 2 RAID 0. The speed of the drive will increase significantly.
by
0 like 0 dislike
SELECT SQL_NO_CACHE?
by
0 like 0 dislike
how many processes in mysql?
it shows "vmstat 1" during the brakes during normal operation? compare to context switching.
by
0 like 0 dislike
sort_buffer_size = 256M
This parameter is set on EACH CLIENT. If you have heavy queries, or heavy hranilki, then twist it inside session. Although in my experience the sense of twisting no more than 1M. 256 *100 customers = 25G operatives :)
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
2 answers
asked Apr 12, 2019 by robi_ds
0 like 0 dislike
1 answer
asked Apr 14, 2019 by KuzmenkoArtem
0 like 0 dislike
1 answer
0 like 0 dislike
4 answers
110,608 questions
257,186 answers
0 comments
26,166 users