MySQL performance

Sheridan · 02-25-2008, 04:48 AM

Quote:

Originally Posted by Electro

Sheridan, SAS has a little higher throughput than SATA, but the latency is still the same.

The default kernel sysctl settings are not always good for each setup. I suggest play around with sysctl settings.

I agree. IMHO, software developers are getting lazy these days. They think that they can include any feature they want and the speed of the processor will compensate. The goal of a software developer is creating software that is efficient, stable, and reliable.

I totally agree with you on devs... However, unfortunately we have a thing at our place, that when a dev say it's my fault, since they're considered "very smart and all that", they will believe them no matter what I give for proof on the contrary. They just think (altough not say so directly) that I'm making up techno-blahblah to avoid responsibility.

Anyway... upgrading to 64 bit DID help a lot. Now pageloads are faster, but still there's considerable strain on drives. I increased cache and key buffer settings skyhigh, but still problem remains.

I'll play around sysctl tonight after rush hour and let you know what I find. Thanks guys...

Tinkster · 02-25-2008, 06:32 PM

Quote:

Originally Posted by Electro

Also need to use a 64-bit setup to take advantage of all that memory.

Where did that one come from?

Linux on 32-bit systems can quite happily (keyword PAE) address
more than 4GB of RAM. I've used a 32-bit version RH 3 years ago
on a Quad-XEON with 16BG RAM quite happily, and Oracle 10g made
good use of it, too.

It's going to be slightly faster in 64-bit mode (namely due
to the extended number of registers), but there's no actual
limitation ...

Cheers,
Tink

Electro · 02-29-2008, 03:10 AM

Quote:

Originally Posted by Tinkster

Where did that one come from?

Linux on 32-bit systems can quite happily (keyword PAE) address
more than 4GB of RAM. I've used a 32-bit version RH 3 years ago
on a Quad-XEON with 16BG RAM quite happily, and Oracle 10g made
good use of it, too.

It's going to be slightly faster in 64-bit mode (namely due
to the extended number of registers), but there's no actual
limitation ...

Cheers,
Tink

PAE is magic for 80x86 32-bit processors to handle memory larger than 4 GB. PAE works by taking a pool of memory to aid in registering larger than 4 GB. Using 16 GB of memory on a 32-bit 80x86 system will take close to a gigabyte or maybe more to handle this capacity. In reality, you are not getting the memory capacity that you paid for. Also there is a performance penalty when using PAE. PAE is short for virtual memory accessing that have to do a lot of translation processing.

I did not say that a 32-bit processor can not handle more than 4 GB of memory. I am saying it can not handle more than 4 GB efficiently.

Quote:

I totally agree with you on devs... However, unfortunately we have a thing at our place, that when a dev say it's my fault, since they're considered "very smart and all that", they will believe them no matter what I give for proof on the contrary. They just think (altough not say so directly) that I'm making up techno-blahblah to avoid responsibility.

I suggest telling them that you are making the server as efficient as possible and that their job is also making the software as efficient as possible too. If they do not like this, show them what you have been doing outside of work or off work hours. Hopefully one curious developer will stand out and point out that their code is not efficient as they once thought.

trickykid · 02-29-2008, 10:36 AM

Don't believe the developers all the time. At my current job, we used MyISAM on our production databases. As admins, we control the production environment and without any help from the developers since they wouldn't listen to us, converted the MyISAM tables to use InnoDB, it's made the biggest impact on system performance since the conversion. We get less pages in the middle of the night now, if that tells you anything.

If they're already doing transactions and a lot of them, you're better off with InnoDB as the engine instead of MyISAM. If they're not willing to test it, or QA doesn't want to test using InnoDB, then test it yourself before converting to prove if it increases performance. You can throw all the disks you want at it, I still think from what you've described that you'll be better off with InnoDB, it was designed for heavy transactions. I find it hard to believe they'll need to change very little to nothing for the application using the database.

This is from their main page even: "InnoDB is the most popular transactional storage engine for MySQL, delivering high performance transaction processing, with row level locking and multi-version concurrency control. It minimizes expensive disk I/O through the efficient use of memory and processor resources. InnoDB ensures data integrity and reliability, with such capabilities as referential integrity support and automatic data recovery."

I'd say, either convert or just switch to PostgreSQL if you want better performance cause like mentioned, using MyISAM is most likely your bottle neck at this time since you're pushing a lot of transactions creating heavy Disk I/O on the machine.

trickykid · 02-29-2008, 10:41 AM

Also I just noticed, the hardware you are using, the HP server is almost identical to what I'm dealing with, or had to deal with. It's no longer a problem now with InnoDB as the engine..

Michael_S · 02-29-2008, 12:55 PM

Sheridan,

I'm just curious, how is it working now?

trickykid · 02-29-2008, 05:40 PM

Oh and after some digging and rethinking, converting to InnoDB shouldn't affect anything. You really have nothing to prove to the developers. Apparently if they think rewriting code for InnoDB is required, they're not very bright to begin with.

Basically, if you can prove the database is disk bound since you run a bunch of transactions, MyISAM is your bottleneck, converting to InnoDB would be the best option. If you're still disk bound, the problem probably lies within the code with shitty queries being run, which would surprise me since they think converting to InnoDB requires them to rewrite the code.

If I was in your situation and being a sysadmin with production servers and they told me development ran the show, I would have already found another job.

Sheridan · 03-01-2008, 12:35 PM

Hi All,

Thanks a lot for all the answers.

I can write only now because I had another project and I was quite busy.

I have switched the entire server onto 64 bit Fedora, and compiled MySQL from source. Other than that, I spent several nights tinkering with my.cnf (I will post it later).

The result is that the site is actually faster than it ever was before and they actually let me keep my job.. :-S For what it's worth, anyway...

But the symptoms are I guess still there: including the relatively high I/O.

There's one personal downside though: Now, more than ever, - for obvious reasons - they are convinced that I f*cked up in the first place and there's absolutely nothing wrong with the code, in fact, it's very polished if anything. This comes trough even more after the speech QA gave before the oversight committe. (And frankly, deep down I'm starting to belive them... they were partially right anyway, I could have done a number on the system when I had the chance in the first place, but I kept holding that back until the last minute.)

But back to subject: I had a long talk with the product manager and quality assurance after the meeting where we finally decided that I have successfully repaired the problem (and concluded that I was ultimately responsible). Here are the results:

Point one: InnoDB engine. They said if I were to "simply" switch to Inno, many-many complicated queries that are there in the old application (most of them "uncharted" or poorly documented) would instantly break, some of them not in an obvious way and they would do lot of damage until it would be found out. He explained this with some subtle incompatibilities between inno and myisam. Since I have little idea about the code, have little reference (most of the documentation about the application is classified to devs eyes only so I cannot verify) and I lack prior hard programming experience with this, I must agree.

Point two: Optimizing queries. When I told them they should optimize queries and algorithms, they first didn't understand what it is that I want with that. Then I showed them disk usage stats, load stats, transaction logs, long query logs, evidence of failed caching because of bad query design (according to logged queries at least), etc etc etc. In the end they talked amongst themselves for a little time, then when they came back into the room, they did something planely unexpected: Their attitude towards me changed upside down, they became unreasonably hostile for no apparent reason, stated that I had _no right_ to investigate this issue in this depth, and also they haven't the time nor the need to explain or argue about such advanced things to an "untrained" sysadmin, and declared the conversation was over. Also they said if I so much as breathe a word about what I did and found, I may face disclosure violation problems in the near future, among other things. That's about it...

So... Final analysis: system is working to specifications now. And I'm looking for a new job because I'm frankly fed up with this. That's it as far as I'm concerned now.

trickykid · 03-01-2008, 03:41 PM

Quote:

Originally Posted by Sheridan

Point one: InnoDB engine. They said if I were to "simply" switch to Inno, many-many complicated queries that are there in the old application (most of them "uncharted" or poorly documented) would instantly break, some of them not in an obvious way and they would do lot of damage until it would be found out. He explained this with some subtle incompatibilities between inno and myisam. Since I have little idea about the code, have little reference (most of the documentation about the application is classified to devs eyes only so I cannot verify) and I lack prior hard programming experience with this, I must agree.

Point two: Optimizing queries. When I told them they should optimize queries and algorithms, they first didn't understand what it is that I want with that. Then I showed them disk usage stats, load stats, transaction logs, long query logs, evidence of failed caching because of bad query design (according to logged queries at least), etc etc etc. In the end they talked amongst themselves for a little time, then when they came back into the room, they did something planely unexpected: Their attitude towards me changed upside down, they became unreasonably hostile for no apparent reason, stated that I had _no right_ to investigate this issue in this depth, and also they haven't the time nor the need to explain or argue about such advanced things to an "untrained" sysadmin, and declared the conversation was over. Also they said if I so much as breathe a word about what I did and found, I may face disclosure violation problems in the near future, among other things. That's about it...

So... Final analysis: system is working to specifications now. And I'm looking for a new job because I'm frankly fed up with this. That's it as far as I'm concerned now.

I'd be looking as well. If they really think their crappy sounding queries are going to break, they don't seem that intelligent and you're probably better off not working for people like that anyways. They'll soon learn when the system just comes to a crawl and they'll be sitting around with their thumbs up their asses.

What company is this? I want to avoid ever working there.

But even then, if you didn't have any issues with performance, with heavy transactions going on, you're still better off with InnoDB. Clearly these people you work for don't have a clue. They'll maybe learn one day.

Before you leave after finding a new job, point them to start reading this blog: http://www.mysqlperformanceblog.com/

And maybe start off with the InnoDB vs MyISAM post: http://www.mysqlperformanceblog.com/...chmarks-part-1

chrism01 · 03-02-2008, 11:47 PM

Sheridan,
you have my sympathy.
If they came back suddenly that hostile, sounds like you (and we) were right but they hate to admit it.
If they were right all along, they wouldn't need to be aggressive about it.
If you are going to stay a sysadmin in your new job, it's a good idea to learn enough aboput propgramming to be able to show people simple examples of bad code ie you can easily play around in SQL setting up tables with bad/no indexing and demonstrate to yourself how much slower it goes.
MySQL also have a great query analyser: http://dev.mysql.com/doc/refman/5.0/en/explain.html etc which you can use to drop someones SQL into and it'll show simply how the engine will run it in terms of loops and indexes.
Good luck at your next place.

Sheridan · 03-05-2008, 10:41 AM

Hi Guys,

I promised that I'll send back the settings which finally got the configuration working up to specs. under the conditions dicussed above.

1) OS: Fedora 7, 64 bit, base install, but no LVM. There's a _completely_ separate partition for MySQL datafiles and the rest of the system. All partitions ext3.

2) Relevant parts from My.cnf:

Code:

key_buffer=8192M
sort_buffer_size=8M
read_buffer_size=8M
max_allowed_packet=512M
thread_stack=4M
max_connections=3000
max_connect_errors=10000
ft_min_word_len=2

# DO NOT Set unless necessary
#set-variable   = wait_timeout=10

skip-locking
table_cache = 5000
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M

thread_cache = 200

query_cache_type = 1
query_cache_size = 450M
query_cache_limit = 60M

thread_concurrency = 16

3) Sysctl:

Code:

# ADDITION #
net.core.rmem_max = 4677721
net.core.wmem_max = 4677721
net.ipv4.tcp_rmem = 4096 87380 4677721
net.ipv4.tcp_wmem = 4096 65536 4677721
net.core.netdev_max_backlog = 2500
net.ipv4.tcp_no_metrics_save = 1
net.ipv4.tcp_moderate_rcvbuf = 1
fs.file-max = 500000

4) Misc. tuning:

Code:

echo 30 > /proc/sys/net/ipv4/tcp_fin_timeout
echo 3000 > /proc/sys/net/core/netdev_max_backlog
echo 3000 > /proc/sys/net/core/somaxconn
echo 15 > /proc/sys/net/ipv4/tcp_keepalive_intvl
echo 5  > /proc/sys/net/ipv4/tcp_keepalive_probes
echo 1024 65000 > /proc/sys/net/ipv4/ip_local_port_range

5) MySQL Compilation command:

Code:

./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --with-mysqld-ldflags=-all-static --enable-assembler; make

That's it. Nothing too fancy. Hope it will help someone find the right tune out there sometime. Thank you guys once again.

Electro · 03-05-2008, 05:23 PM

Fedora is OK for a server, but it is not secure for a production server. I suggest CentOS which is a free alternative to Redhat Enterprise that deals with security, stability, and reliability in mind.

I think you can do better than what you provided for sysctl. I suggest do some research and benchmarks while tweaking the vm and IO subsystem settings. You do not need to use echo because those settings can be set with sysctl too. Also you want to add some optimizations to the compiler, so it makes more efficient code for MySQL. Are you sure you want to compile MySQL as static because that might take up more memory when running multiple processes of MySQL.

You can try use a compiler from Intel to increase efficiency.

Sheridan · 03-06-2008, 02:04 AM

Quote:

Originally Posted by Electro

Fedora is OK for a server, but it is not secure for a production server. I suggest CentOS which is a free alternative to Redhat Enterprise that deals with security, stability, and reliability in mind.

I think you can do better than what you provided for sysctl. I suggest do some research and benchmarks while tweaking the vm and IO subsystem settings. You do not need to use echo because those settings can be set with sysctl too. Also you want to add some optimizations to the compiler, so it makes more efficient code for MySQL. Are you sure you want to compile MySQL as static because that might take up more memory when running multiple processes of MySQL.

You can try use a compiler from Intel to increase efficiency.

I think you are right. Thanks a lot, just for the sport of it I'll review things according to what you said. Altough I don't feel much incentive to improve it after their "big speech" about me being a jerk, but the couriosity wins I guess... And this will be useful in future

Fedora will stay for this one, becuase I don't feel like staying in overnight to reinstall. However, rest of the things I'll go trough and I'll start experimenting with CentOS too in my free time, see what happens. I might come to like it, as I did Fedora.

Until now, I experimented with Mandriva, Fedora and Ubuntu. Ubuntu I didn't like. I don't mean to hurt any Ubuntu fans, I'm sure it has it's advantages. But after using Mandriva for 4 years as a desktop and some of my very first servers, Ubuntu felt very... well, weird. Especially the no-root-password thing. Fedora I came to know 2 years ago, and since then I use it only for servers, for desktop I find it a little rigid, but it may be only me. However as a server I found it very reliable and rock-solid. For desktop I stayed with Mandriva, customized with some extra stuff like Wine. I don't know what CentOS will be like, I'm looking forward to it though.

Ok... Abut this optimization, I don't know how much time it will take, but I'll make sure to post back my new results here for review as soon as I have them

Cheers!

L.

vsurlan · 07-09-2010, 05:01 AM

Be careful about having TOO LARGE Query Cache. It can cause the server to spend a bunch of time sorting query cache index and end up hindering the purpose of the cache. You can read a bit more about MySQL Query Cache gotchas here: MySQL Query Cache - in depth.

Quote:

Originally Posted by Sheridan

2) Relevant parts from My.cnf:

Code:

key_buffer=8192M
sort_buffer_size=8M
read_buffer_size=8M
max_allowed_packet=512M
thread_stack=4M
max_connections=3000
max_connect_errors=10000
ft_min_word_len=2

# DO NOT Set unless necessary
#set-variable   = wait_timeout=10

skip-locking
table_cache = 5000
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M

thread_cache = 200

query_cache_type = 1
query_cache_size = 450M
query_cache_limit = 60M

thread_concurrency = 16

jlinkels · 07-09-2010, 06:30 AM

Quote:

Originally Posted by Sheridan

There're MyISAM tables and lots of transactions... But the problem is that I can't switch to Inno becuase we already investigated it and our outsource development partner says it would cost a lot to rewrite necessary PHP code so that's not an option at this point...

I also heard that InnoDB is faster than MyISAM. However the statement by your PHP developers that code needs to be rewritten is pure nonsense. You can convert MyISAM tables to InnoDB in a database without doing anything in your code. It is only the storage engine which changes and this is completely transparant to the API. Your application doesn't even know what engine is being used, let alone you have to adapt your code.

jlinkels