geschätzte Lesezeit: 12 min

For most application developers, the database is an altar of demon gods best left unapproached. But it need not be that way!

Other things being equal, the level of comfort a developer has with the underlying database defines their seniority level. Little database and little coding experience = junior developer; little database and good coding experience = mid-level developer; good database and good coding experience = senior developer.

It’s a harsh reality that even devs with 6-8 years under their belt struggle to explain the intricacies of the query optimizer and prefer to look heavenwards when asked about database tuning.

Why?

Surprisingly, the reason isn’t laziness (although in some part it is).

The point is that databases are a force of their own to contend with. Even traditionally, when there were only the relational types of databases to deal with, mastering them was a miracle and career path in its own; these days, we have so many types of databases that it’s just impossible to expect a single, mortal soul to master everything.

That said, there’s a good chance that you’re still happy with relational databases or are part of a team that has a product running on a relational database satisfactorily for a long, long time. And in nine cases out of ten, you’re on MySQL (or MariaDB). For these cases, diving just a little deeper under the hood yields massive benefits in boosting application performance and is every bit worth learning.

Curious? Let’s dive in!

Not curious? Well, dive in anyway, because your career depends on it! 😛

Optimize the MySQL query cache

Almost all optimization in the field of computers comes down to caching. On one end, the CPU maintains several levels of cache to speed up its computations, and on the other, web apps make aggressive use of caching solutions like Redis to server precomputed results to users rather than hitting the database every time.

But hey, even the poor MySQL database has its own query cache! That is, every time you query something, and the data is still stale, MySQL will serve up these cached results rather than running the query again, making the app ridiculously faster.

You can check whether you have query cache available (note, available, not enabled) in your database by running this query in the database console:

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_query_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+ 

So, you can see that I’m running MariaDB and that I have query caching available to be turned on. It’s extremely unlikely that you’d have it turned off if you’re using a standard MySQL install.

Now let’s see if I have the query cache actually turned on:

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_type'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | query_cache_type | ON | +------------------+-------+

Yes, I do. But in case you don’t, you can turn it on by saying:

MariaDB [(none)]> SET GLOBAL query_cache_type = ON;

Interestingly, this variable also accepts a third value that denotes “on-demand,” meaning MySQL will cache only those queries we tell it to, but we won’t go into that here.

With this, you have query caching on and have taken the first step towards a more robust MySQL setup! I say the first step because while turning it on is a major improvement, we do need to tune query caching to suit our setup. So let’s learn to do that.

The other variable of interest here is query_cache_size, whose function is self-explanatory:

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_size'; +------------------+----------+ | Variable_name | Value | +------------------+----------+ | query_cache_size | 16777216 | +------------------+----------+

So, I have a query cache of around 16 MB size. Note that even if query caching is turned on, but this size is zero, caching is effectively off. That’s why checking only one variable isn’t enough. Now, you should set a query cache size, but how much should it be? First off, please note that the query caching feature will itself need 4 KB to store its metadata, so whatever you select should be above that.

Let’s say you set the query cache size to be 500 KB:

MariaDB [(none)]> SET GLOBAL query_cache_size = 500000;

Is doing this much enough? Well, no, because how the query engine will actually end up performing depends on a couple more things:

• First off, the query_cache_size variable must be large enough to hold your the result of your queries. If it’s too small, nothing will be cached.
• Secondly, if query_cache_size is set to too high a number, there will be two types of problems: 1) The engine will have to do extra work storing and locating query results in this massive memory area. 2) If most of the queries result in much smaller sizes, the cache will get fragmented, and the benefits of using a cache will get lost.

How do you know the cache is getting fragmented? Check the total number of blocks in the cache like this:

MariaDB [(none)]> show status like 'Qcache_total_blocks'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | Qcache_total_blocks | 33 | +---------------------+-------+

If the number is very high, the cache is fragmented and needs to be flushed.

So, to avoid these problems, make sure that the size of query_cache_size is chosen wisely. If you feel frustrated that I didn’t leave you with a concrete number here, I’m afraid that’s the way things are once you move past development and step into engineering. You must look into the app you’re running and see what the query sizes for the important query results are and then set this number. And even then you might end up making a mistake. 🙂

Threading, thread pools, waiting, and timeouts

This is probably the most interesting part of how MySQL works and getting it right means making your app several times faster!

Threading

MySQL is a multi-threaded server. That means, every time there’s a new connection to the MySQL server, it opens a new thread with the connection data and passes off a handle to it to the client (just in case you’re wondering what a thread is, see this). The client then sends all queries over this thread and receives results. This leads us to ask a natural question: how many threads can MySQL spin up? The answer lies in the next section.

Thread pool

No program in a computer system can open as many threads as it wants. The reason is twofold: 1) Threads cost memory (RAM), and the operating system just won’t allow you to go berserk and eat up all of it. 2) Managing, say, a million threads is a massive task on its own, and if the MySQL server could create that many threads, it would die trying to deal with the overhead.

To avoid these problems, MySQL comes with a thread pool — a fixed number of threads that are part of a pool at the start. New connection requests cause MySQL to pick up one of these threads and return the connection data, and if all the threads are used up, new connections are naturally refused. Let’s see how large the thread pool is:

ariaDB [(none)]> show variables like 'thread_pool_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | thread_pool_size | 4 | +------------------+-------+

So, my machine allows a maximum of four connections at the same time. It’s interesting to note that the number 4 comes from the fact that I have a four-core processor, which means my computer can run only 4 parallel tasks at a time (I’m talking of truly parallel tasks here, not concurrent ones). Ideally, this is the limit that should drive the value of thread_pool_size, but on beefier machines increasing it does benefit to a point. If you want not to make all new connections wait and are okay to take some performance hit (again, this is an area that you can judge best based on your app’s performance under load), bumping it up to 8 might be a good idea.

However, setting it beyond 16 is a terrible idea unless you have a 32-core machine, as performance degrades significantly. The rabbit hole of thread pools in MySQL goes deep, but if you’re interested, here’s a more detailed discussion.

Waiting and timeouts

Once a thread has been created and attached to a client, it’d be a waste of resources if the client sent no queries for the next few seconds (or minutes). As a result, MySQL terminates a connection after a period of inactivity. This is controlled by the wait_timeout variable:

MariaDB [(none)]> show variables like 'wait%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 | +---------------+-------+

The resulting value is in seconds. So yes, by default MySQL is set to wait 8+ hours before it severs the cord! This may be good if you have long-running queries and actually want to wait for them (but even then, eight hours is absurd!) but terrible in most cases. When a query is run, this value is set to 0 (meaning forever), but generally, this should be set to a very low value (5 seconds, for example, or maybe even less) to free up the connection for other processes.

Tuning temporary tables

Let’s begin with what are temporary tables in MySQL.

Suppose we have a MySQL that structurally looks like this: TABLE A UNION (TABLE B INNER JOIN C). That is, we’re interested in joining tables B and C, and then performing a union of the result with table A. Now, MySQL would first proceed to join the tables B and C, but before it can perform a union, it needs to store this data somewhere. This is where temporary tables come in — MySQL uses them to store data at intermediate stages in complex queries temporarily, and once the query is over, this temporary table gets discarded.

Now the question is: why should we bother with all this?

Simply because the temporary table, just a query result, is data that is being used by MySQL in computation, and the speed of its access (among other limitations) will determine how fast the query gets executed. For instance, storing the temporary table in RAM will be several times faster than storing it on the disk.

There are two variables that control this behavior:

MariaDB [(none)]> show variables like 'MariaDB [(none)]> show variables like 'tmp_table_size';   +----------------+----------+ | Variable_name  | Value    | +----------------+----------+ | tmp_table_size | 16777216 | +----------------+----------+ '; +---------------------+----------+ | Variable_name | Value | +---------------------+----------+ | max_heap_table_size | 16777216 | +---------------------+----------+ MariaDB [(none)]> show variables like 'tmp_table_size'; +----------------+----------+ | Variable_name | Value | +----------------+----------+ | tmp_table_size | 16777216 | +----------------+----------+

The first one, max_heap_table_size, tells us how much RAM can be used up by a MySQL table (“heap” here refers here to the data structure used in RAM allocation and management — read more here), while the second one, tmp_table_size, shows what the maximum size of the temporary table is. In my case, both are set to 16 MB, though the point I’m trying to make it that increasing only tmp_table_size will not work as overall, MySQL would still be limited by max_table_heap_size.

Now comes the point: if the temporary tables being created are larger than the limit allowed by these variables, MySQL would be forced to write them to the hard disk, resulting in extremely poor performance. Our job now is simple: do our best to guess the most accurate data size for temporary tables and tweak these variables to that limit. However, I’d like to caution against absurdity: setting this limit to 16 GB (assuming you have this much RAM) when most of your temporary tables are less than 24 MB in size is foolishness — you’re simply wasting RAM that could’ve been used by other queries or parts of the system (cache, for example).

Conclusion

It’s not possible to cover all the system variables in one article, or even all the important ones in one article when the MySQL documentation itself spans several thousand words. While we covered some universal variables here, I’d encourage you to look into the system variables for the engine you’re using (InnoDB or MyISAM).

My most desirable outcome for writing this article is for you to take away three things:

1. MySQL is a typical piece of software that works within limits set by the operating system. It’s not some mysterious program that does God-knows-what and is impossible to tame. Also, thankfully, it’s not that difficult to understand how it’s set up and gets controlled by its system variables.
2.  There is no single setting that will make your MySQL install go zoom. You have no choice but to look within your running systems (remember, optimization comes after the app is in production, not before), make the best guesses and measurements, and live with the reality that it’s never going to be perfect.
3. Tuning the variables isn’t the only way to optimize MySQL — efficient writing queries is another big deal, but it’s something I’ll address in another article. But the point is, even if you’ve done a godlike analysis and tuned these parameters to the best, it’s still possible for you to bring everything to a screeching halt.

What’s your favorite system variable for tuning? 🙂

geschätzte Lesezeit: 13 min

Results from the 3DiscGolf Survey (Part I)

By Josh Woods, PhD, and Dee Leekha ~

Disc golf has changed a lot over the last twenty years.

Two decades ago, there were 4,776 active members of the PDGA in the United States. Today, there are more than 28,861. Americans played in 329 PDGA sanctioned events in 1998. They will play in no fewer than 2,368 in the coming year, per the PDGA.

In 1998, Scott Stokely broke the World Record for distance with a jaw-dropping throw that carried 693 feet. In 2017, Simon Lizotte parked a 726-foot hole during tournament play, and the World Record is now held by David Wiggins Jr. with a distance that exceeds Stokely’s by nearly 400 feet.

Twenty or so years ago, one of the best disc golfers in the world, Eagle McMahon, did not exist.

As notable as new world records and unborn Eagles may be, perhaps the most amazing change in disc golf land can be found on the courses. In 1998, U.S. disc golfers had only 851 places to play, according to PDGA stats. Twenty-four states had fewer than ten courses to choose from. Now we have nearly 6,000 courses in the U.S., and only one state in the nation has fewer than ten.

This tremendous growth has prompted many observers to rank disc golf among the fastest growing sports in America. And yet, while the number of disc golf courses is clearly on the rise, less is known about the quality of this growth.

With so many changes afoot, we decided to take a closer look at the country’s disc golf courses by surveying the attitudes of experienced course developers.

About the Respondents

On January 13, 2018, we invited the members of a Facebook group called 3DiscGolf to fill out an online questionnaire. 3DISCgolf is a forum for anyone who supports the goal of creating safer, smarter and more sustainable disc golf.

Within two days, 156 of the group’s 354 members answered our call. The great majority of them have designed, co-designed or helped construct at least two disc golf courses. Roughly 22 percent of the respondents have worked on ten or more courses.

Members of the 3DiscGolf group are also avid players. Most of them play at least four different courses a month. Over a lifetime, the typical respondent has played more than 100 courses. They also typically play at least once or more per week, and most describe their skill levels as advanced or professional. A clear majority are (or have been) members of a local disc golf club and the PDGA.

The respondents are predominately male, white, over the age of 35, and educated with a bachelor’s degree or higher. The 3DiscGolf sample has at least one representative from 39 states, with large shares hailing from Michigan, New York and Texas. (For more details, see the Appendix).

Course Maintenance and Facilities

Based on a content analysis of 30 randomly selected reviews from Disc Golf Course Review (DGCR), we identified thirteen aspects of courses related to maintenance and facilities that are commonly regarded as “cons” by reviewers. We transformed these thirteen comments into questions and included them in the survey.

We asked respondents, “Please base your answers on your general attitude toward the U.S. disc golf courses that you have played or observed. How big of a problem are the following issues related to maintenance and facilities?” Respondents rated each aspect on a four-point scale: “A big problem,” “Somewhat of a problem,” “A small problem,” or “Not a problem,” with “Don’t know” as a fifth option.

To quickly summarize the findings, we combined two categories and constructed a list of course aspects that were most likely to be considered “a big problem” or “somewhat of a problem” (see Figure 1).

On issues of course maintenance and facilities, the respondents pointed to the lack of a course map on site, no bathrooms, and garbage on courses as key problems. Fairways in need of mowing or trimming, as well as soil erosion and compaction were also considered notable concerns on the courses they played.

Some of the issues found on DGCR were not deemed significant problems by our participants. Surprisingly, for instance, only 11 percent of respondents named broken or missing baskets as a “big problem” or “somewhat of a problem.” The respondents were also less concerned about off-leash dogs, and various types of insects and wildlife. (For more details, see the Appendix).

Course Design and Construction

We also identified twelve course design and construction aspects that often appear in the “cons” list of reviewers on DGCR. We measured our respondents’ attitudes toward them using the same scale, and constructed a list of the five biggest design problems, as seen in Figures 2.

Three of the twelve course design and construction issues were related to tee pads (slippery, uneven, too short), and each one made the top-five list of problems. The difficulty of navigating between holes, as well as boring or unimaginative courses also topped the list of course design issues.

The takeaways

One purpose of this study is to provide information to course developers that may help them build safer and more enjoyable disc golf courses. To our best knowledge, this is the first (publicly available) survey of its kind. As illustrated in the Appendix, this report offers a unique ranking of twenty-five problems by some of the world’s most experienced course developers. Remedying these problems at new and existing courses would likely encourage more people to play and keep playing disc golf.

A second goal of this project is to encourage people of all backgrounds and experiences to come together and share their views. So, what do you think? What are the most important problems facing disc golf courses in the United States? Please join the discussion on the 3DiscGolf Facebook group, and by offering your comments below.

What’s Next?

In Part II of our 3DiscGolf survey results, we’ll tackle the issue of safety on disc golf courses. What are the most common safety issues, and which ones are perceived by course designers as big concerns? Is safety on disc golf courses really a problem?

~~~

Josh Woods, editor at Parked, is an associate professor of sociology at West Virginia University. His current research focuses on the growth and group dynamics of non-normative sports.

Dee Leekha, author of Business Consciousness®, is the co-founder of Circular Productions, LLC and Houck Design, a disc golf course design company. As an entrepreneur, her business operations focus on uplifting communities and lives through the sport and game of disc golf.

~~~

Parked is a non-profit, educational blog. We don’t want to sell you something. No ads. No products. No private sponsors. We just want to include you in the conversation. Please show your support and follow us by entering your email address below. You’ll receive two or three free Parked articles per month. Like Parked on Facebook and Twitter to receive notifications.

~~~

Parked is underwritten in part by a grant from the Professional Disc Golf Association.

~~~

APPENDIX

 How many disc golf courses have you designed, co-designed and/or helped construct?

Answer	%	Count
None	8.33%	13
1	14.74%	23
2-5	42.95%	67
6-9	12.18%	19
10 or more	21.79%	34
Total	100%	156

About how many disc golf courses have you played?

Answer	%	Count
None	0.64%	1
1-25	9.62%	15
26-50	21.15%	33
51-100	21.79%	34
100-200	26.92%	42
More than 200	19.87%	31
Total	100%	156

About how often, on average, do you play disc golf, not including during very cold or hot seasons.

Answer	%	Count
Every day	2.56%	4
4 to 6 times per week	17.95%	28
1 to 3 times per week	55.13%	86
2 or 3 times per month	17.31%	27
Once per month	2.56%	4
5 to 11 times per year	1.92%	3
Less than 5 times per year	1.28%	2
Never	1.28%	2
Total	100%	156

Within a month time span, how many different courses do you typically play?

Answer	%	Count
None	2.56%	4
1	6.41%	10
2	16.67%	26
3	21.79%	34
4	16.03%	25
5	14.10%	22
6	5.77%	9
More than 6	16.67%	26
Total	100%	156

For these questions, please base your answers on your general attitude toward the U.S. disc golf courses that you have played or observed. How big of a problem are the following issues related to maintenance and facilities?

Question	A big problem	Somewhat of a problem	A small problem	Not a problem	Don’t know	Total
Garbage on courses.	22.00%	33	30.00%	45	43.33%	65	4.67%	7	0.00%	0	150
Fairways that need mowing, trimming or clearing.	8.67%	13	40.67%	61	42.00%	63	8.67%	13	0.00%	0	150
Broken or missing baskets.	2.67%	4	11.33%	17	48.00%	72	38.00%	57	0.00%	0	150
Missing, weathered or damaged tee signs.	21.33%	32	42.00%	63	30.67%	46	6.00%	9	0.00%	0	150
No course maps available at the site.	24.00%	36	41.33%	62	24.67%	37	10.00%	15	0.00%	0	150
Courses labeled as “18 hole courses” that only have 9 baskets and two tee pad locations.	2.68%	4	5.37%	8	22.82%	34	60.40%	90	8.72%	13	149
Off-leash dogs on courses.	6.08%	9	13.51%	20	36.49%	54	43.24%	64	0.68%	1	148
Insects, ticks, snakes or other wildlife on courses.	4.00%	6	28.00%	42	40.00%	60	26.00%	39	2.00%	3	150
Standing water or mud on courses.	4.67%	7	31.33%	47	50.67%	76	12.67%	19	0.67%	1	150
Soil erosion or compaction.	18.00%	27	31.33%	47	34.67%	52	14.67%	22	1.33%	2	150
No garbage cans on courses.	15.33%	23	32.00%	48	37.33%	56	14.67%	22	0.67%	1	150
No public bathrooms near courses.	21.33%	32	40.00%	60	28.67%	43	10.00%	15	0.00%	0	150
Incomplete removal of cut tree limbs, stumps or dead trees on or near fairways.	8.11%	12	27.03%	40	45.95%	68	18.24%	27	0.68%	1	148

For these questions, please base your answers on your general attitude toward the U.S. disc golf courses that you have played or observed. How big of a problem are the following issues related to course design and construction?

Question	A big problem	Somewhat of a problem	A small problem	Not a problem	Don’t know	Total
Tee pads that are too short or narrow.	17.33%	26	36.00%	54	37.33%	56	9.33%	14	0.00%	0	150
Tee pads that are slippery.	19.33%	29	42.00%	63	28.67%	43	9.33%	14	0.67%	1	150
Tee pads that are uneven, lumpy or rocky.	16.67%	25	38.00%	57	35.33%	53	9.33%	14	0.67%	1	150
Players have a hard time finding hole 1.	9.33%	14	30.67%	46	41.33%	62	18.00%	27	0.67%	1	150
Players have a hard time navigating between holes on the course.	18.00%	27	40.00%	60	33.33%	50	8.00%	12	0.67%	1	150
Players must take long walks between holes.	6.00%	9	14.67%	22	48.00%	72	30.67%	46	0.67%	1	150
Players must take a long walk after the finishing hole.	2.67%	4	14.00%	21	51.33%	77	31.33%	47	0.67%	1	150
Courses that are boring, unimaginative, too easy, or repetitive.	17.33%	26	32.00%	48	33.33%	50	16.67%	25	0.67%	1	150
Fairways that are too narrow.	4.67%	7	14.67%	22	40.00%	60	40.00%	60	0.67%	1	150
Fairways that are too open.	6.00%	9	20.00%	30	39.33%	59	34.00%	51	0.67%	1	150
Courses that favor right-handed, backhand-dominant players.	5.33%	8	20.67%	31	38.00%	57	34.67%	52	1.33%	2	150
No elevation changes.	5.33%	8	20.67%	31	36.00%	54	37.33%	56	0.67%	1	150

Finally, we would like to ask you a few background questions. First, are you now or have you ever been a member of the Professional Disc Golf Association?

Answer	%	Count
Yes	93.88%	138
No	6.12%	9
Total	100%	147

How old are you?

Answer	%	Count
18-24 years old	0.00%	0
25-34 years old	13.61%	20
35-44 years old	35.37%	52
45-54 years old	29.25%	43
55-64 years old	19.05%	28
65-74 years old	2.72%	4
75 years or older	0.00%	0
Total	100%	147

What is your sex?

Answer	%	Count
Male	96.58%	141
Female	3.42%	5
Total	100%	146

 

Please specify your ethnicity (or Race).

Answer	%	Count
White	88.51%	131
Hispanic or Latino	2.70%	4
Black or African American	0.68%	1
Native American or American Indian	1.35%	2
Asian / Pacific Islander	1.35%	2
Other	5.41%	8
Total	100%	148

What is the highest degree or level of school you have completed? If currently enrolled, highest degree received.

Answer	%	Count
No schooling completed	0.68%	1
Nursery school to 8th grade	0.00%	0
Some high school, no diploma	0.00%	0
High school graduate, diploma or the equivalent (for example: GED)	5.41%	8
Some college credit, no degree	25.68%	38
Trade/technical/vocational training	6.08%	9
Associate degree	8.11%	12
Bachelor’s degree	33.78%	50
Master’s degree	14.19%	21
Professional degree	3.38%	5
Doctorate degree	2.70%	4
Total	100%	148

What is your current state of residence?

Answer	%	Count
Alabama	2.70%	4
Alaska	0.00%	0
Arizona	1.35%	2
Arkansas	0.00%	0
California	2.70%	4
Colorado	2.03%	3
Connecticut	1.35%	2
Delaware	0.68%	1
Florida	2.03%	3
Georgia	3.38%	5
Hawaii	0.00%	0
Idaho	0.68%	1
Illinois	1.35%	2
Indiana	0.00%	0
Iowa	0.68%	1
Kansas	0.68%	1
Kentucky	0.68%	1
Louisiana	2.70%	4
Maine	0.00%	0
Maryland	1.35%	2
Massachusetts	1.35%	2
Michigan	14.19%	21
Minnesota	5.41%	8
Mississippi	0.00%	0
Missouri	0.68%	1
Montana	0.68%	1
Nebraska	0.00%	0
Nevada	2.03%	3
New Hampshire	0.68%	1
New Jersey	0.68%	1
New Mexico	0.00%	0
New York	10.81%	16
North Carolina	4.05%	6
North Dakota	0.00%	0
Ohio	1.35%	2
Oklahoma	1.35%	2
Oregon	1.35%	2
Pennsylvania	2.70%	4
Rhode Island	0.00%	0
South Carolina	0.68%	1
South Dakota	0.68%	1
Tennessee	2.03%	3
Texas	13.51%	20
Utah	0.68%	1
Vermont	0.00%	0
Virginia	2.03%	3
Washington	2.03%	3
West Virginia	0.68%	1
Wisconsin	2.70%	4
Wyoming	0.00%	0
I do not reside in the United States	3.38%	5
Total	100%	148

Are you a member of a disc golf club?

Answer	%	Count
Yes	89.12%	131
No	10.88%	16
Total	100%	147

How would you rate your skill level as a disc golfer?

Answer	%	Count
Professional	33.11%	49
Advanced	35.81%	53
Intermediate	22.30%	33
Recreational	7.43%	11
Beginner	1.35%	2
Total	100%	148

~~~

Cover art photo sources: Icy pad, photo by Full Metal Basket Editor James McDonald; “Dirty discer,” pinterest.com; extreme erosion, martindiscgolfclub.wordpress.com; grassy basket, timescall.com; short pad, dgcoursereview.com.